Pages

Tuesday, January 5, 2021

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;

2 comments:

  1. Thank You for sharing this useful piece of information and content with us.
    Apart from this, If someone is looking for the Best SAP Training Institute in Delhi, they must contact us on 827519649. or visit the given link-
    Sap training Delhi

    ReplyDelete
  2. Great blog! Easy to understand and informative. I learned a lot. Keep sharing more insightful content like this. If you are looking for Professional Courses then visit Appwars Technologies Training Institute in Noida for courses like.
    Python Training in noida
    Data Science Training in noida
    full stack development training in noida
    Software Testing Training in Noida
    Digital Marketing Training in Noida

    ReplyDelete