spool tbs_rep.html
PROMPT <P><H3>Tablespace Space Allocation and Utilization</H3>
PROMPT <HTML>
PROMPT <TITLE>
SELECT ‘ Oracle Databases Tablespace Report ran on ‘ || TO_CHAR(SYSDATE, ‘MON-DD-YYYY HH24:MM:SS PM’) FROM DUAL ;
PROMPT </TITLE>
set head off feed off
PROMPT <br>
SELECT ‘ Oracle Databases Tablespace Report ran on ‘ || TO_CHAR(SYSDATE, ‘MON-DD-YYYY HH24:MM:SS PM’) FROM DUAL ;
select ‘Database Name : ‘||name from v$database;
set head on
SET HEADING ON
SET FEEDBACK OFF
— PROMPT <H3> Ensure that all tablespaces have atleast 20% of free space </H3>
set pages 200 lines 150
col TOTAL_GB format 99999999.00
col USED_GB format 99999999.00
col FREE_GB format 99999999.00
col PCT_USED format 999.99
col PCT_FREE format 999.99
select (select name from v$database) DB_NAME,
u.size_GB total_GB
,u.size_GB-f.free_GB used_GB
,f.free_GB
,((u.size_GB-f.free_GB)/u.size_GB)*100 pct_used
,(f.free_GB/u.size_GB)*100 pct_free
from
(select sum(a.bytes)/1024/1024/1024 size_GB
from dba_data_files a) u
,(select sum(b.bytes)/1024/1024/1024 free_GB
from dba_free_space b
) f
/
set feed on
select
u.tablespace_name
,u.size_GB total_GB
,u.size_GB-f.free_GB used_GB
,f.free_GB
,((u.size_GB-f.free_GB)/u.size_GB)*100 pct_used
,(f.free_GB/u.size_GB)*100 pct_free
from
(select a.tablespace_name,sum(a.bytes)/1024/1024/1024 size_GB
from dba_data_files a
group by a.tablespace_name) u
,(select b.tablespace_name,sum(b.bytes)/1024/1024/1024 free_GB
from dba_free_space b
group by b.tablespace_name ) f
where u.tablespace_name=f.tablespace_name(+)
order by pct_used desc
/
PROMPT <A href=”#TopOfPage”>Back to the Top of Report</A>
PROMPT </BODY>
PROMPT </HTML>
spool off
SET MARKUP HTML OFF ;
