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 ;
Continue reading