FEW SCRIPTS

TABLSPACE MANAGEMENT:

free1.sql --> Tablespace Usage Report

column pct_used format a10
set pages 100
SELECT a.tablespace_name, to_char(100-(100*sum_free_blocks/sum_alloc_blocks), '999.99') || '%' AS pct_used FROM 
(SELECT tablespace_name,sum(blocks) AS sum_alloc_blocks FROM dba_data_files GROUP BY tablespace_name) a,
(SELECT tablespace_name,sum(blocks) AS sum_free_blocks FROM dba_free_space b GROUP BY tablespace_name) b
where a.TABLESPACE_NAME = b.TABLESPACE_NAME ORDER BY pct_used;





freespace.sql --> Tablespace (Total Space, Used  and Freee)

set pages 100 
set line 300 
select f.tbs "Tablespace Name",df.tot" TotalSpace(MB)",ds.used "TotalSpaceUsed" ,f.free "TotalSpaceFree",
to_char((ds.used/df.tot)*100,'00.99') Percentage_Used   from 
(select tablespace_name tbs,sum(bytes)/1024/1024 free from dba_free_space group by tablespace_name) f,
(select tablespace_name tbs,sum(bytes)/1024/1024 tot from dba_data_files group by tablespace_name ) df,
(select tablespace_name tbs,sum(bytes)/1024/1024 used from dba_segments group by tablespace_name ) ds 

where df.tbs=f.tbs and f.tbs=ds.tbs order by f.tbs;





freespace1.sql (Report for Given Tablespace)
set pages 100 
set line 300 
select f.tbs "Tablespace Name",df.tot" TotalSpace(MB)",ds.used "TotalSpaceUsed" ,f.free "TotalSpaceFree",
to_char((ds.used/df.tot)*100,'00.99') Percentage_Used   from 
(select tablespace_name tbs,sum(bytes)/1024/1024 free from dba_free_space group by tablespace_name) f,
(select tablespace_name tbs,sum(bytes)/1024/1024 tot from dba_data_files group by tablespace_name ) df,
(select tablespace_name tbs,sum(bytes)/1024/1024 used from dba_segments group by tablespace_name ) ds 
where df.tbs=f.tbs and f.tbs=ds.tbs 
and ds.tbs='&tblspc_name'

order by f.tbs;

dbf_time.sql
select to_char(CREATION_TIME,'DD-MON-YYYY HH24:MI:SS'),NAME from v$datafile order by creation_time;