How to get exact percentage of Oracle data files became full ?

[KVS Raju]

Hi Friends.

I would to like to know that any direct method or direct query is available in Oracle Data base, to find out the how much percentage of Oracle data files is fulled ?

The tablespaces of that data fiels has unlimited max_extents and on data files the auto_extend feature is on.

(I think generally we will get these details from DBA_EXTENTS and DBA_SEGMENTS and DBA_FREE_SPACE tables.)

[Steven E. Protter]

Some versions of oracle ignore the auto exend/max extent settings.

They have a hard limit of 100 extents.

You should either defragment the database or manually increase max_extents

As stupid as that sounds same thing happened to us.

SEP

[Hein van den Heuvel]

The Oracle websites, otn and metalink are sure to have useful script in this space (sic)

I find that ixora is often good starting point for these queries. That is queries that surely have been solved before.

http://www.ixora.com.au/
search for DBA_FREE_SPACE gives a likely solution:
http://www.evdbt.com/chk_spc.sh
and
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:112012348062

and many more...


This forum also has prior solution. Just use 'search' for DBA_FREE_SPACE or Oracle AND free and tablespace or some such query:

http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=328266

Enjoy,
Hein.

[Brian Crabtree]

Define full.

select file_id,a.total,b.free,(a.total-b.free) used from
(select file_id,sum(bytes) from dba_data_files group by file_id) a,
(select file_id,sum(bytes) from dba_free_space group by file_id) b
where a.file_id = b.file_id group by file_id;

This will give you total/free/used space on the datafiles. Not sure if this is what you are looking for though.

Thanks,

Brian

[Nicolas Dumeige]

Just an improvement on the last script

set timing off
set time off
set linesize 160 pagesize 100
set verify off
set feedback off
set colsep ' | '

col FILE_NAME format A75
col TABLESPACE_NAME format A25

select
a.file_name,
a.tablespace_name,
round(a.total/(1024*1024)) as "TOTAL (Mo)",
round(b.free/(1024*1024)) as "FREE (Mo)",
round((a.total-b.free)/(1024*1024)) as "USED (Mo)",
round(((a.total-b.free)/total)*100, 2) as "% OCCUPATION"
from
(select file_id,file_name,tablespace_name,sum(bytes) total
from dba_data_files
group by file_id,file_name,tablespace_name) a,
(select file_id,sum(bytes) free
from dba_free_space
group by file_id) b
where
a.file_id = b.file_id
order by
a.tablespace_name
/