DROP TABLESPACE statement

This Oracle tutorial explains how to use the Oracle DROP TABLESPACE statement with syntax and examples.

Description

The DROP TABLESPACE statement is used to remove a tablespace from the Oracle database. A tablespace is used to allocate space in the Oracle database where schema objects are stored.

Syntax

The syntax for the DROP TABLESPACE statement is:

DROP TABLESPACE tablespace_name
  [ INCLUDING CONTENTS [ {AND DATAFILES | KEEP DATAFILES ]
    [ CASCADE CONSTRAINTS ] ] ;

Parameters or Arguments

tablespace_name

The name of the tablespace to remove from the Oracle database.

INCLUDING CONTENTS

Ooptional. If you specify INCLUDING CONTENTS, all contents of the tablespace will be dropped. If there are objects in the tablespace, you must specify INCLUDING CONTENT or you will receive an error.

AND DATAFILES

Optional. It will delete the associated operating system files. When using Oracle-managed files, you can omit the AND DATAFILES option because Oracle will automatically delete the associated operating system files.

KEEP DATAFILES

Optional. It will NOT delete the associated operating system files. When using Oracle-managed files, if you want to keep the associated operating system files, you must specify the KEEP DATAFILES option.

CASCADE CONSTRAINTS

Optional. If you specify CASCADE CONSTRAINTS, all referential integrity constraints will be dropped that meet the following criteria: A referential integrity constraint from a table outside tablespace_name that refers to a primary key or unique key on a table that is inside tablespace_name.

Example

Let's look at a simple DROP TABLESPACE statement.

For example:

DROP TABLESPACE tbs_perm_01
  INCLUDING CONTENTS
    CASCADE CONSTRAINTS;

This would drop tablespace called tbs_perm_01, delete all contents from the tbs_perm_01 tablespace, and drop all referential integrity constraints (Referential integrity constraints from a table outside tablespace_name that refers to a primary key or unique key on a table that is inside tablespace_name.)

Let's look at a another DROP TABLESPACE statement.

For example:

DROP TABLESPACE tbs_perm_02
  INCLUDING CONTENTS AND DATAFILES
    CASCADE CONSTRAINTS;

This would drop tablespace called tbs_perm_02, delete all contents from the tbs_perm_02 tablespace, remove the associated operating system files, and drop all referential integrity constraints (Referential integrity constraints from a table outside tablespace_name that refers to a primary key or unique key on a table that is inside tablespace_name.)

Let's look at a one file DROP TABLESPACE statement.

For example:

DROP TABLESPACE tbs_perm_03
  INCLUDING CONTENTS KEEP DATAFILES;

This would drop tablespace called tbs_perm_03, delete all contents from the tbs_perm_03 tablespace, but keep the associated operating system files.