Do you need datafile add in your Oracle?


This is a plain old script only written in a different way  but very handy, when someone is doing export import. I have just added a little variation to it…


COLUMN tablespace_name FORMAT A15
COLUMN TS_SZ_TB FORMAT 9999.999
COLUMN TS_Free_TB FORMAT 99999999.999
COLUMN TS_Full_Prcnt FORMAT 9999999999.99
COLUMN Largest_Free_Ext_GB FORMAT 999999999999.999

Break on report

Compute sum of TS_SZ_TB on report
Compute sum of Ts_Full_TB on report

select a.tablespace_name, a.TS_SZ_TB, b.Ts_Free_TB, ((a.TS_SZ_TB-b.Ts_Free_TB)/a.TS_SZ_TB)*100 TS_Full_Prcnt,b.Largest_Free_Ext_GB
from
(select tablespace_name,sum(bytes/1024/1024/1024/1024) TS_SZ_TB from dba_data_files group by tablespace_name
union
select tablespace_name,sum(bytes/1024/1024/1024/1024) TS_SZ_TB from DBA_TEMP_FILES group by tablespace_name) a,
(select tablespace_name,Sum(bytes/1024/1024/1024/1024) TS_Free_TB, Max(bytes/1024/1024/1024) Largest_Free_Ext_GB from dba_free_space group by tablespace_name
union
select tablespace_name,Sum(bytes/1024/1024/1024/1024) TS_Free_TB, Max(bytes/1024/1024/1024) Largest_Free_Ext_GB from DBA_TEMP_FILES group by tablespace_name) b
where a.tablespace_name (+) =b.tablespace_name
/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s