SoFunction
Updated on 2025-04-12

Examples of how Oracle deletes tablespace syntax and code

Preface

Deleting tablespaces is an important operation in Oracle database management, usually when the tablespace is no longer needed or needs to be reorganized for storage resources. When deleting a tablespace, you need to pay special attention to the backup of data and business impact, because deleting a tablespace will permanently delete all data in it.

Steps to delete tablespaces

  • Make sure the tablespace is empty(Optional): Make sure that all objects in the tablespace have been deleted or transferred before deleting the tablespace.
  • Delete tablespace:useDROP TABLESPACECommand deletes tablespace.
  • Delete data files(If needed): When deleting tablespaces, you can choose to delete the associated data files at the same time.

Basic syntax for deleting tablespaces

DROP TABLESPACE tablespace_name
    [INCLUDING CONTENTS [AND DATAFILES]]
    [CASCADE CONSTRAINTS];
  • tablespace_name: The name of the tablespace to be deleted.
  • INCLUDING CONTENTS: Delete all objects in it at the same time when deleting the tablespace.
  • AND DATAFILES: Delete the associated data file at the same time when deleting the tablespace.
  • CASCADE CONSTRAINTS: When deleting a tablespace, all constraints related to tables in the tablespace are deleted.

Example: Delete a tablespace

Suppose we have a name calledusers_tablespaceThe following are several ways to delete this tablespace.

Delete tablespace but keep data files

DROP TABLESPACE users_tablespace
    INCLUDING CONTENTS;

This will deleteusers_tablespaceThe tablespace and its contents, but retains the associated data file.

Delete tablespace and its data files

DROP TABLESPACE users_tablespace
    INCLUDING CONTENTS AND DATAFILES;

This will deleteusers_tablespaceThe tablespace and its contents and delete the associated data file.

Delete tablespace and its data files and delete related constraints

DROP TABLESPACE users_tablespace
    INCLUDING CONTENTS AND DATAFILES
    CASCADE CONSTRAINTS;

This will deleteusers_tablespaceThe tablespace and its contents, deletes the associated data files, and deletes all constraints related to tables in the tablespace.

Delete temporary tablespace

The steps to delete a temporary tablespace are similar to deleting a normal tablespace, but you need to make sure that no session is using the temporary tablespace.

Example: Delete a temporary tablespace

Suppose we have a name calledtemp_userspacetemporary tablespace.

DROP TABLESPACE temp_userspace
    INCLUDING CONTENTS AND DATAFILES;

Check tablespace status

Before deleting the tablespace, it is recommended to check the current state of the tablespace to make sure no object or session is using it.

Example: Checking objects in tablespace

SELECT TABLESPACE_NAME, SEGMENT_NAME, SEGMENT_TYPE
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME = 'USERS_TABLESPACE';

Example: Check the usage of temporary tablespaces

SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 AS SIZE_MB
FROM DBA_TEMP_FILES
WHERE TABLESPACE_NAME = 'TEMP_USERSPACE';

Things to note

  • Backup data: Make sure that all important data in it has been backed up before deleting the tablespace.
  • Business impact: Notify relevant business personnel to ensure that deleting table spaces will not have an adverse impact on business operations.
  • Check dependencies: Check whether there are other database objects that depend on the tablespace to be deleted.

Summarize

Deleting tablespaces is an irreversible operation and must be performed with care. By usingDROP TABLESPACECommands can delete tablespaces and their contents that are no longer needed, and can also choose to delete associated data files and related constraints. Make sure to back up the data and evaluate its impact on the business before deleting the tablespace.