spool all_tsps.rslt 

set linesize 80;
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\all_tsps.sql     */ 
/*                                                        */
/* ****************************************************** */

set echo on;

/* ***************************************** */
/*  Part 1 - show the size of the tablespace */
/*           only those that are have data   */
/* ***************************************** */

set echo off;

select
   substr(A.tablespace_name, 1, 30)    			"TBL SPACE",
   substr(A.owner, 1, 10)              			"OWNER",
   substr(A.segment_type, 1, 10)       			"TYPE",
   to_char(sum(A.bytes)/(1024*1024),'9,999,999.99') 	"USED(M)"       
from sys.dba_segments   A
group by A.owner,
     A.tablespace_name,
     A.segment_type
order by 1, 2, 3;

set echo on;

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

set echo off;

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

select
   substr(Tablespace_name, 1, 26)    "TBL SPACE",
   substr(file_name, 1, 40)          "FILE NAME",
   to_char(bytes/(1024*1024),'9,999,999.99') "SIZE(M)"    
from sys.dba_data_files
where status = 'AVAILABLE'
union
select
   substr(Tablespace_name, 1, 26)    "TBL SPACE",
   substr(file_name, 1, 40)          "FILE NAME",
   to_char(bytes/(1024*1024),'9,999,999.99') "SIZE(M)"    
from sys.dba_TEMP_files
where status = 'AVAILABLE'
order by 1, 2, 3;

set echo on;

/* ***************************************** */
/*  Part 3 - Tablespace Utilization          */
/* ***************************************** */

set echo off;

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

insert into table_utilization
    (tablespace_name, total_bytes) 
     select tablespace_name,sum(bytes)
       from dba_data_files
       group by tablespace_name;

insert into table_utilization
    (tablespace_name, total_bytes) 
     select tablespace_name,sum(bytes)
       from dba_temp_files
       group by tablespace_name;

update table_utilization b
  set free_bytes = (select sum(a.bytes) 
                      from dba_free_space a
                      where a.tablespace_name = b.tablespace_name),
      fragments  = (select count(*) 
                      from dba_free_space c
                      where c.tablespace_name = b.tablespace_name);

commit;

set feedback on
set verify on

col tablespace_name heading "TableSpace|Name" format a30
col total_bytes heading "Size|(Mbytes)" format 999,999.99
col free_bytes heading "Free|(MBytes)" format 999,999.99
col pct heading "%|Util" format 990.990
col fragments heading "Fragments" format 9990

select tablespace_name,
    total_bytes/(1024*1024)             total_bytes,
    free_bytes/(1024*1024)               free_bytes,
    (total_bytes-free_bytes)/total_bytes * 100 PCT,
    fragments
from table_utilization
order by 1 asc;

set verify off
set feedback off


set echo on;

set echo on;

/* ***************************************** */
/*  Part 4 - Tablespaces in trouble          */
/* ***************************************** */

set echo off;

select tablespace_name,
    total_bytes/(1024*1024)             total_bytes,
    free_bytes/(1024*1024)               free_bytes,
    (total_bytes-free_bytes)/total_bytes * 100 PCT,
    fragments
from table_utilization
WHERE (total_bytes-free_bytes)/total_bytes*100 >85
order by 1 asc;


drop table table_utilization;

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









