Using DROP TABLESPACE statement in Oracle

This article is written about how to use the Oracle DROP TABLESPACE statement with syntax and examples.

Description

The DROP TABLESPACE announcement is used to put off a tablespace from the Oracle database. A tablespace is used to allocate area in the Oracle database the place 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 identify of the tablespace to put off from the Oracle database. INCLUDING CONTENTS Optional. If you specify INCLUDING CONTENTS, all contents of the tablespace will be dropped. If there are objects in the tablespace, you ought to specify INCLUDING CONTENT or you will receive an error. AND DATAFILES Optional. It will delete the associated operating device files. When using Oracle-managed files, you can pass over the AND DATAFILES alternative because Oracle will mechanically delete the related operating machine files. KEEP DATAFILES Optional. It will NOT delete the related operating device files. When using Oracle-managed files, if you prefer to hold the related working system files, you ought to 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 predominant key or unique key on a desk that is internal 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 referred to as tbs_perm_01, delete all contents from the tbs_perm_01 tablespace, and drop all referential integrity constraints (Referential integrity constraints from a table backyard tablespace_name that refers to a most important key or special key on a desk that is internal tablespace_name.)

Let’s seem to be at a every other DROP TABLESPACE statement.

For example:

DROP TABLESPACE tbs_perm_02
  INCLUDING CONTENTS AND DATAFILES
    CASCADE CONSTRAINTS;

This would drop tablespace referred to as tbs_perm_02, delete all contents from the tbs_perm_02 tablespace, get rid of the associated operating machine files, and drop all referential integrity constraints (Referential integrity constraints from a table backyard tablespace_name that refers to a main 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 known as tbs_perm_03, delete all contents from the tbs_perm_03 tablespace, but preserve the related running system files.