spool show_tsp.rslt 

set linesize 120;
set pagesize 1000;
set long 50;
set echo off;

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\Oracle\SQL\show_tsp.sql      */ 
/*                                                        */
/* ****************************************************** */

col dummy     	noprint 		format a1;
col OWNER 	heading "Owner" 	format a10;
col TYPE 	heading "Type" 		format a20;
col NAME 	heading "Name" 		format a30;
col SIZE 	heading "Size(M)" 	format a10;

set echo on;
/* ******************************************** */
/* Part I - show the contents of the tablespace */
/* ******************************************** */

set echo off;

select
   substr(owner, 1, 10)              		"OWNER",
   substr(segment_type, 1, 20)       		"TYPE",
   substr(segment_name, 1, 30)       		"NAME",
   to_char(bytes/(1024*1024*1024), '9,999.999')  	"SIZE(Gb)"       
from sys.dba_segments
where TABLESPACE_NAME = upper('&&tablespace')
order by 1, 2, 3; 

set echo on;

/* ******************************************** */
/* Part Ia - show the type of tablespace        */
/* ******************************************** */

set echo off;

select
   contents              "TYPE"    
from sys.dba_tablespaces
where TABLESPACE_NAME = upper('&&tablespace'); 

/* ******************************* */
/* Compute the tablespace size     */
/* ******************************* */

create table tablespace_utilization
    (tablespace_name      varchar2(50),
     total_bytes          number,
     used_bytes           number)
PCTFREE              10
STORAGE (initial     8k
         next        8k
         minextents  1
         maxextents  245
         pctincrease 0);

insert into tablespace_utilization
    (tablespace_name, total_bytes) 
select B.TABLESPACE_NAME, sum(B.bytes)
from sys.dba_data_files B
where B.TABLESPACE_NAME = upper('&&tablespace')
group by B.tablespace_name;

update tablespace_utilization
set used_bytes = (select sum(A.bytes)
	from sys.dba_segments A
	where A.TABLESPACE_NAME = upper('&&tablespace')
	group by A.tablespace_name);

set echo on;

/* ********************************************* */
/* Part II - Display the percentage used         */
/* ********************************************* */

set echo off

select substr(tablespace_name,1,20)     					"TABLESPACE",
     to_char(total_bytes/(1024*1024*1024), '9,999.99')            	"SIZE(Gb)",
     to_char(used_bytes/(1024*1024*1024), '9,999.99')             	"USED(Gb)",
     to_char((total_bytes-used_bytes)/(1024*1024*1024), '9,999.99') 	"AVAIL(Gb)",
     to_char((used_bytes/total_bytes)*100, '99.99')||'%'       	"USED"
from tablespace_utilization;

drop table tablespace_utilization;
set echo on;

/* ******************************************* */
/*  Part III - show the size of the tablespace */
/* ******************************************* */

set echo off;

select
   substr(tablespace_name, 1, 20)    		"TBL SPACE",
   substr(owner, 1, 10)              		"OWNER",
   substr(segment_type, 1, 20)       		"TYPE",
   to_char(sum(bytes)/(1024*1024*1024),'99,999.99') "SIZE(Gb)"       
from sys.dba_segments
where TABLESPACE_NAME = upper('&&tablespace')
group by owner,
     tablespace_name,
     segment_type
order by 1, 2, 3;

set echo on;

/* ******************************************* */
/*  Part IV - show the tablespace statistics   */
/* ******************************************* */

set echo off;

break on "TBL SPACE" on "FILE NAME";

select
   file_id       						"FILE ID",
   substr(file_name, 1, 70)          			"FILE NAME"
from sys.dba_data_files
where TABLESPACE_NAME = upper('&&tablespace')
order by 1, 2;

select
   file_id       						"FILE ID",
   blocks                            			"BLOCKS",
   to_char(bytes/(1024*1024*1024),'99,999.99') 		"SIZE(Gb)",
   decode(status, 'AVAILABLE', 'Yes', 'No') 		"AVL",
   decode(AUTOEXTENSIBLE, 'YES', 'ON        ', 'OFF       ') 		"AUTOEXTEND"   
from sys.dba_data_files
where TABLESPACE_NAME = upper('&&tablespace')
order by 1, 2, 3;


set echo on;

/* ******************************************** */
/* Part V - show storage usage by user          */
/* ******************************************** */

set echo off;
select substr(A.owner, 1, 10)              "OWNER",
       substr(A.segment_type, 1, 10)       "TYPE",
       sum(A.bytes)/(1024*1024*1024)            "USED(Gb)"
from sys.dba_segments   A
where A.TABLESPACE_NAME = upper('&&tablespace')
group by A.owner,
         A.tablespace_name,
         A.segment_type
order by 1, 2, 3;
set echo on;
spool off;
set long 80;
undefine tablespace;
commit;
