Drop Tablespace in Oracle

Dropping a tablespace in an Oracle Database involves removing a logical storage structure and, optionally, the physical datafiles associated with it. This operation requires careful planning and consideration due to its potentially irreversible nature. Here's an explanation of the theory behind dropping a tablespace:

1. Definition of Tablespace
   • A tablespace is a logical storage container in Oracle that maps to one or more physical datafiles.
   • It stores database objects such as tables, indexes, and partitions.
   • Each tablespace is associated with a specific purpose (e.g., SYSTEM, SYSAUX, USERS, TEMP).
2. Reasons to Drop a Tablespace
   • Cleanup: Remove obsolete or unused tablespaces to free up resources.
   • Database Reorganization: Restructure or consolidate the storage layout.
   • Migration or Archival: Move data to another tablespace or database, then remove the old tablespace.
3. Pre-Requisites and Precautions
   • Ensure No Dependency: Verify no database objects depend on the tablespace being dropped.
   • Backup: Create a full backup of the database or relevant tablespaces before proceeding.
   • Check Contents: Query data dictionary views (e.g., DBA_TABLES, DBA_INDEXES) to confirm what is stored in the tablespace.
   • Plan for Datafiles: Decide whether to keep or delete associated datafiles.
4. Theoretical Process
   1. Drop Command:
      • The DROP TABLESPACE statement is used to remove a tablespace.
      • Example:

                  DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;
                

   2. Contents Deletion:
      • If INCLUDING CONTENTS is specified, all objects within the tablespace are deleted.
      • If not specified, the operation fails if the tablespace contains objects.
   3. Datafile Deletion:
      • If INCLUDING DATAFILES is specified, physical files on disk are also removed.
      • If not specified, the datafiles remain, which requires manual cleanup.
5. Impact on the Database
   • Logical Removal: The tablespace and its metadata are removed from the control file.
   • Object Inaccessibility: Any objects stored in the dropped tablespace become permanently inaccessible.
   • Space Reclamation: Disk space is reclaimed if datafiles are deleted.
6. Important Considerations
   • SYSTEM and SYSAUX Tablespaces:
     • These are mandatory for database operation and cannot be dropped.
   • References in Other Tablespaces:
     • Ensure there are no cross-tablespace references (e.g., indexes in another tablespace pointing to tables in the one being dropped).
   • Undo Tablespaces:
     • Cannot drop an active undo tablespace; switch to a new undo tablespace first.
7. System Views and Commands to Support
   • Check Objects:

     SELECT TABLE_NAME FROM DBA_TABLES WHERE TABLESPACE_NAME = 'tablespace_name';
         

   • Check Datafiles:

     SELECT FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'tablespace_name';
         

   • Drop Command:

     DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;
         

Conclusion
Dropping a tablespace is a significant administrative task that eliminates its associated data permanently unless backups are available. Careful analysis, confirmation of contents, and preparation for datafile cleanup ensure the process is executed safely and efficiently.

You can remove a tablespace from a database using the DROP TABLESPACE statement. You might need to drop a tablespace if you are reorganizing your storage or if you are getting rid of an application. The following MouseOver describes the syntax used for the DROP TABLESPACE statement:

DROP TABLESPACE tablespace_name
[INCLUDING CONTENTS
[CASCADE CONSTRAINTS]];

Normally, when you drop a tablespace, it must be empty. If a tablespace contains objects, you can force Oracle to drop it by including the keywords INCLUDING CONTENTS in your DROP command; Oracle will automatically drop the tablespace and the contents. If you use INCLUDING CONTENTS to drop a tablespace where the tables contained in the dropped tablespace reference other tables in your database, the DROP will fail unless you also specify CASCADE CONSTRAINTS. This causes Oracle to also drop constraints referencing the objects being dropped. You will learn about read-only tablespaces in the next lesson.