shows allocated and used space and percentages.
# tbs_usage.sql
def aps_prog = 'tf.sql'
def aps_title = 'Database Freespace Summary'
set linesize 200
col tsname format a24 heading 'Tablespace'
col nfrags format 999,990 justify c heading 'Free|Frags'
col mxfrag format 99,990 justify c heading 'Largest|Free Frag'
col totsiz format 99,999,990 justify c heading 'Total|Mbytes'
col filsiz format 99,999,990 justify c heading 'File|Mbytes'
col avasiz format 99,999,990 justify c heading 'Available|Mbytes'
col pctusd format 990.99 justify c heading 'Percent|Used'
set pages 200
select
total.tablespace_name tsname,
count(free.bytes) nfrags,
nvl(max(free.bytes)/1048576,0) mxfrag,
total.bytes/1048576 totsiz,
total.file_bytes/1048576 filsiz,
nvl(tot_free.free_bytes,0)/1048576 avasiz,
--nvl(used.bytes,0)/total.bytes*100 pctusd
(total.file_bytes-nvl(tot_free.free_bytes,0))/total.bytes*100 pctusd
from
(select
tablespace_name,
sum(bytes) file_bytes,
SUM(CASE WHEN maxbytes > bytes THEN maxbytes ELSE bytes END) bytes
from
dba_data_files
where tablespace_name not like 'UNDO%'
group by
tablespace_name)
total,
(select
tablespace_name,
nvl(sum(bytes),0) free_bytes
from
dba_free_space
group by
tablespace_name) tot_free,
dba_free_space free
where
total.tablespace_name = free.tablespace_name(+)
and total.tablespace_name = tot_free.tablespace_name(+)
group by
total.tablespace_name,
total.bytes,
free_bytes,
file_bytes
order by 7
/
select
total.tablespace_name tsname,
count(free.bytes) nfrags,
nvl(max(free.bytes)/1048576,0) mxfrag,
total.bytes/1048576 totsiz,
total.file_bytes/1048576 filsiz,
nvl(tot_free.free_bytes,0)/1048576 avasiz,
--nvl(used.bytes,0)/total.bytes*100 pctusd
(total.file_bytes-nvl(tot_free.free_bytes,0))/total.bytes*100 pctusd
from
(select
tablespace_name,
sum(bytes) file_bytes,
SUM(CASE WHEN maxbytes > bytes THEN maxbytes ELSE bytes END) bytes
from
dba_data_files
group by
tablespace_name)
total,
(select
tablespace_name,
nvl(sum(bytes),0) free_bytes
from
dba_free_space
group by
tablespace_name) tot_free,
dba_free_space free
where
total.tablespace_name = free.tablespace_name(+)
and total.tablespace_name = tot_free.tablespace_name(+)
group by
total.tablespace_name,
total.bytes,
free_bytes,
file_bytes
order by 1
/
col tsname heading "Temp Tablespace"
select
total.tablespace_name tsname,
total.bytes/1048576 totsiz,
total.file_bytes/1048576 filsiz
from (
select
tablespace_name,
sum(bytes) file_bytes,
SUM(CASE WHEN maxbytes > bytes THEN maxbytes ELSE bytes END) bytes
from
dba_temp_files
group by
tablespace_name
) total
/
col name for a30 heading "ASM DiskGroup"
select name, free_mb, total_mb from v$asm_diskgroup order by 1;
