how to find current size, available size, used size and total size in a Tablespace .

For used size:

 select sum(bytes) from dba_segments where tablespace_name=''; For available size: select sum(bytes) from dba_datafiles where tablespace_name='';
select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files where tablespace_name like '%xxxxxxxxxxxxxx%'
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments where tablespace_name like '%xxxxxxxxxxxxx%'
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name;

------- In place of xxxxxxx please add tablespace name.

Visit Us On FacebookVisit Us On Google PlusVisit Us On Youtube