Oracle DROP TABLESPACE operator

The DROP TABLESPACE operator is used to remove tabular space from the Oracle database. TABLESPACE is used to allocate space in the Oracle database where schema objects are stored.

Syntax for the DROP TABLESPACE operator

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

Parameters and arguments of the operator

• tablespace_name – the name of the tabular space to be deleted from the Oracle database.
• INCLUDING CONTENTS – optional. If you specify INCLUDING CONTENTS, all table space content will be deleted. If there are objects in the table space and you do not specify INCLUDING CONTENTS, you will receive an error message.
• AND DATAFILES is optional. It will delete the associated operating system files. If you are using files managed by Oracle, you can omit the AND DATAFILES option, because Oracle will automatically remove the linked operating system files.
• KEEP DATAFILES is optional. If specified, the linked operating system files will NOT be removed. When using files managed by Oracle, if you want to keep operating system binaries, you must specify the KEEP DATAFILES option.
• CASCADE CONSTRAINTS is optional. If you specify CASCADE CONSTRAINTS, all reference integrity restrictions will be removed, which will meet the following criteria: reference integrity restriction from a table outside the tablespace_name that refers to a primary key or a unique key in a table that is inside the tablespace_name.

Let us consider a simple example of the DROP TABLESPACE operator.
For example:

DROP TABLESPACE tbs_01
INCLUDING CONTENTS
CASCADE;

This will remove the tbs_01 table space, remove all content from the tbs_01 table space, and remove all reference integrity restrictions (reference integrity restrictions from a table outside the table name that relate to a primary key or a unique key in a table that is inside the tablespace_name).

Now let’s look at another DROP TABLESPACE operator.
For example:

DROP TABLESPACE tbs_02
INCLUDING CONTENTS AND DATAFILES
CASCADE;

This will remove tbs_02 table space, remove all content from tbs_02 table space, remove associated operating system files and remove all reference integrity restrictions (reference integrity restrictions from a table outside tablespace_name that refers to a primary key or a unique key in a table that is inside tablespace_name).

Let’s consider one more DROP TABLESPACE operator.
For example:

DROP TABLESPACE tbs_03
INCLUDING CONTENTS KEEP DATAFILES;

This will remove the tbs_03 tabular space, remove all content from the tbs_03 tabular space, but keep the linked operating system files.

How To Create and Drop Tablespace in Oracle 11g