Drop Tablespace for Oracle Database after Upgrade

Steps : 

1. Check whether the tablespace is empty with the following command

    SQL> select count(*) from dba_segments where tablespace_name='old_Tablespace';

If it show empty which indicates that all segments are stored in the new tablespace, then you can delete old tablespace. If it's not empty, you have to reorganize all the objects from the old tablespace to the new tablespace either manually or by brspace.

2. Check whether data class is assigned to correct tablespace. "SLDEF", "SLEXC", "SSDEF", "SSEXC" should be assigned to PSAPSR3<REL>.

    Tcode : SE16 - > Table Name: TAORA and IAORA

3. Check the objects which still exists in PSAPSR3<OLDREL> as follows:

SQL> select table_name from dba_tables where tablespace_name='PSAPSR3740X' ; 

TABLE_NAME 

------------------------

TTREE 

TTREED 

TTREEP 

TTREET 

TTREEN 

TTREE_APPL 

TTREEI 

TTREE_FLNK

SQL> select index_name from dba_indexes where tablespace_name='PSAPSR3740X';

INDEX_NAME

-------------------------

TTREE_APPL~0

TTREE_APPL~PAR

TTREED~0

TTREEN~0

TTREE~0

TTREE~I01

TTREE~TYP

TTREEI~0

TTREEI~NOD

TTREET~0

TTREEP~0

TTREEP~PAR

TTREET~ID

TTREET~TXT

TTREE_FLNK^0

4. Move the shown table above from old table space to new table space

Link

5. Now again check for objects as shown in step 3.

6. If the tablespace is empty, now it can be dropped.

SQL > drop tablespace PSAPSR3740X including contents and datafiles;