How to check Tablespace on Oracle

When creating a new users in Oracle database (new schema), you need to verify the existing tablespace availability. This query will show you what’s there and how much space are free to use :

Query Check

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 
group by tablespace_name) df, (select 
round(sum(bytes)/(1024*1024)) totalusedspace, 
tablespace_name from dba_segments group by 
tablespace_name) tu where 
df.tablespace_name = tu.tablespace_name ;

www.diarycoding.com

Leave a Comment.