Sunday 17 January 2016

Query to Find Free Space in a Tablespace

undefine tbsp
set lines 152
set echo off

col tablespace_name for a20
select utbs.tablespace_name,
round(utbs.mb) "Allocated Used/Unused MB",
round(Ftbs.mb) "Allocated_Free MB",
round((100/utbs.mb)*Ftbs.mb) "%Allocated_Free MB",
decode(sign(round(utbs.Maxmb-utbs.mb)),-1,0,round(utbs.Maxmb-utbs.mb)) "Space_AutoExtensible MB",
Ftbs.MaxBytes "MaxChunk MB"
(select ddf.tablespace_name,sum(ddf.bytes)/1048576 MB,sum(ddf.maxbytes)/1048576 MaxMB
from dba_data_files ddf
group by ddf.tablespace_name) Utbs,
(select dfs.tablespace_name,sum(dfs.bytes)/1048576 MB,max(dfs.bytes)/1048576 MaxBytes
from dba_free_space dfs
group by dfs.tablespace_name) Ftbs
where utbs.tablespace_name=ftbs.tablespace_name
and utbs.tablespace_name like '%&&TBSP%'
order by round(Ftbs.mb)

No comments:

Post a Comment

Was this Post Helpful?

Feel free to suggest your opinions in the comments section!