To view the database default table space
Sql> Select Property_value from database_properties where property_name = ' default_permanent_tablespace ';
Property_value
--------------------------------------------------------------------------------
USERS
View the tablespace used by the user
sql> Select Owner,tablespace_name,round (sum (bytes)/1024/1024,2) "Used (M)"
2 from Dba_segments
3 Where owner = ' SCOTT '
4 GROUP BY Owner,tablespace_name
5 order by sum (bytes) desc;
OWNER Tablespace_name Used (M)
-------- --------------- ----------
SCOTT USERS. 31
View the amount of space occupied by all objects in the user
sql> Select Owner,tablespace_name,segment_name,round (sum (bytes)/1024/1024) "UserD (M)"
2 from Dba_segments
3 Where owner = ' SCOTT '
4 GROUP BY Owner,tablespace_name,segment_name,segment_type
5 order by sum (bytes) desc;
OWNER tablespace_name segment_na userd (M)
-------- --------------- ---------- ----------
SCOTT USERS EMP 0
SCOTT USERS pk_emp 0
SCOTT USERS pk_dept 0
SCOTT USERS DEPT 0
SCOTT USERS Salgrade 0
To view the amount of space remaining in a table space
sql> Select Tablespace_name, sum (bytes)/(1024*1024) Free_space from Dba_free_space Group by Tablespace_name;
Tablespace_name Free_space
--------------- ----------
Sysaux 34.1875
UNDOTBS1 98
USERS. 8125
SYSTEM 9.75
EXAMPLE 2.9375
"ORACLE" DDL operations on Permanent tablespace (11g)