qfreespace.sql

The qfreespace script will show the datafile size, freespace MB, %used and whether autoextend is on or off and the maxbytes size

SQL

set pages 1000

set lines 132
set trims on
col tablespace_name format a16 heading “Tablespace Name”
col file_name format a65 heading “Datafile Name”
col total_size format 999,999.00 heading “Size MB”
col free_space format 999,999.00 heading “Free MB”
col pct_used format 999.00 heading “% Used”
col autoextensible format a4 heading “Auto”
col maxbytes format 999,999.00 heading “Max Size MB”

clear breaks

select df.tablespace_name
, df.file_name
, df.bytes/1024/1024 total_size
, nvl(fr.bytes/1024/1024,0) free_space
, ((df.bytes-nvl(fr.bytes,0))/df.bytes)*100 pct_used
, df.autoextensible
, df.maxbytes/1024/1024 maxbytes
from (select sum(bytes) bytes
, file_id
from dba_free_space
group by file_id) fr
, dba_data_files df
where df.file_id = fr.file_id(+)
order by 1, df.file_id
/

Output

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