Rem Created date: 2006-04-21 12:00
Rem Author : dbstyle
Rem Name : datafile.sql
Rem Description:
Rem this script is used to get datafile information that used exceed 95%.

Rem MODIFIED (MM/DD/YYYY)
Rem dbstyle 04-21-2006 create script

set pagesize 999
col file_name format a40
col tablespace_name format a10
select df.file_name,df.tablespace_name,df.bytes/1024/1024 size_M from dba_data_files df,
(select
a.tablespace_name tsname,
round(a.bytes,2) Total_Mb,
round(b.bytes,2) Free_Mb,
round(a.MAXsize,2) MAXSIZE_Gb,
(1 – (b.bytes/a.bytes))*100 Pct_used
from
(
select tablespace_name,sum(MAXBYTES/1024/1024/1024) MAXsize,sum(bytes)/1024/1024 bytes
from dba_data_files
group by tablespace_name
) a,
(
select tablespace_name,sum(bytes)/1024/1024 bytes
from dba_free_space
group by tablespace_name
) b
where a.tablespace_name = b.tablespace_name
and (1 – (b.bytes/a.bytes))*100>95) tn
where tn.tsname=df.tablespace_name
order by df.file_name;