spool all_tsps1.rslt 

set linesize 132;
set pagesize 1000;
set long 50;
set echo on;

/* break on "TBL SPACE" on "OWNER"; */

/* ****************************************************** */
/*                                                        */
/* substitute the table_name variable with the new        */
/* name in CAPITAL LETTERS                                */
/*  - will show the contents of a tablespace              */
/*                                                        */
/* @C:\rkupcuna05_ora\OracleDBA\SQL\all_tsps1.sql            */ 
/*                                                        */
/* ****************************************************** */

/* *********************************** */
/* show the tablespace statistics      */
/* *********************************** */

set echo off;

select
   /* file_name, */
   substr(Tablespace_name, 1, 26)    		"TBL SPACE",
   to_char(sum(bytes)/(1024*1024),'99,999.99')  	"SIZE(M)",
   AUTOEXTENSIBLE  			  	       "AUTO?"  
from sys.dba_data_files
group by /* file_name,  */
	substr(Tablespace_name, 1, 26),
      AUTOEXTENSIBLE 
order by 1, 2, 3;

set echo on;
set echo off;
spool off;
set long 80;
undefine tablespace;
commit;









