It is not uncommon for Oracle DBAs to create, drop tablespaces for various reasons throughout the life of a database management system. It is a good practice to double check whether a tablespace is really empty before dropping it. One way is to visually check the "Tablespaces" section in Oracle Enterprise Manager (OEM) database console. However sometimes the graphical interface may return false positives. Another way is to rely on data dictionary views to obtain relevant information as accurately as possible. In the latter case, DBA_SEGMENTS / USER_SEGMENTS views are useful in mapping segments such as table, index, table/index partition etc., to a tablespace.

The following example queries USER_SEGMENTS view to list the segments and their types stored in a tablespace called "TS_SALES_DATA32K".

SQL> COLUMN SEGMENT_NAME FORMAT A30
SQL> COLUMN SEGMENT_TYPE FORMAT A30
SQL> 
SQL> SELECT SEGMENT_NAME, SEGMENT_TYPE
  2  FROM USER_SEGMENTS
  3  WHERE TABLESPACE_NAME = 'TS_SALES_DATA32K';

SEGMENT_NAME                   SEGMENT_TYPE
------------------------------ ------------------------------
SALES_DATA                     TABLE

The tablespace "TS_SALES_DATA32K" is holding one table called "SALES_DATA". The following query returns no rows meaning tablespace "TS_SALES_DATA" is empty - hence it can be dropped with no hesitation.

SQL> SELECT SEGMENT_NAME, SEGMENT_TYPE         
  2  FROM USER_SEGMENTS       
  3  WHERE TABLESPACE_NAME = 'TS_SALES_DATA';

no rows selected

Here is another query that lists out all the tablespaces in a database along with the number of segments/objects stored in each of those tablespaces. Note that it is possible to extract similar information in different ways using more efficient queries.

SQL> COLUMN TABLESPACE FORMAT A40
SQL> 
SQL> SELECT UT.TABLESPACE_NAME "TABLESPACE", COUNT (US.SEGMENT_NAME) "NUM SEGMENTS"
  2  FROM USER_TABLESPACES UT, USER_SEGMENTS US
  3  WHERE UT.TABLESPACE_NAME = US.TABLESPACE_NAME
  4  GROUP BY (UT.TABLESPACE_NAME)
  5  ORDER BY COUNT (US.SEGMENT_NAME) DESC;

TABLESPACE                               NUM SEGMENTS
---------------------------------------- -----------
TS_DP                                         114989
TS_DP_X                                          306
..
TS_SALES_DATA32K                                   1
TS_SALES_DATA                                      0

13 rows selected.

SEE ALSO:
Oracle Database Concepts : Data Blocks, Extents, and Segments

Posted by: Giri Mandalika.    # 12:52 AM Tweet