Skip to content
≡

Administrator

Developer

Tools

External Tools

DROP TABLESPACE

DROP TABLESPACE#

Syntax#

drop_tablespace ::=#

drop_tablespace

Prerequisites#

Only the SYS user and users with the DROP TABLESPACE system privilege can execute the DROP TABLESPACE statement.

Description#

This statement is used to remove a tablespace from the database.

tablespace_name#

This is used to specify the name of the tablespace to drop.

INCLUDING CONTENTS#

This is used to specify that all of the contents of the tablespace are to be deleted. If one or more objects exist in the tablespace, this clause must be specified in order to remove the tablespace. If this clause is not specified, Altibase will return an error and the DROP TABLESPACE statement will fail.

AND DATAFILES#

If the AND DATAFILES clause is additionally specified along with the INCLUDING CONTENTS clause, all of the files related to the tablespace are deleted from the file system.

When a disk tablespace is dropped, all of the data files in the disk tablespace are deleted from the file system.

When a memory tablespace is dropped, all the checkpoint image files for the memory tablespace are deleted from the file system. However, the checkpoint paths are not deleted.

The AND DATAFILES clause cannot be used when dropping a volatile tablespace.

CASCADE CONSTRAINTS#

The CASCADE CONSTRAINTS clause must be specified in order to drop all referential integrity constraints in tables that are stored in tablespaces other than the tablespace being dropped but that refer to primary and unique keys in tables in the tablespace being dropped. If this clause is omitted when such referential integrity constraints exist, an error will be returned and the attempt to drop the tablespace will fail.

Limitation#

The following tablespaces are system tablespaces, and thus cannot be removed using the DROP TABLESPACE statement:

  • SYS_TBS_MEM_DIC

  • SYS_TBS_MEM_DATA

  • SYS_TBS_DISK_DATA

  • SYS_TBS_DISK_UNDO

  • SYS_TBS_DISK_TEMP

Examples#

<Query 1> Drop the tablespace user_data.

iSQL> DROP TABLESPACE user_data;
Drop success.

<Query 2> Delete the disk tablespace user_data along with all associated objects and data files.

iSQL> DROP TABLESPACE user_data INCLUDING CONTENTS AND DATAFILES;
Drop success.

<Query 3> Delete the memory tablespace user_data along with all associated objects and data files. 

iSQL> DROP TABLESPACE user_memory_tbs INCLUDING CONTENTS AND DATAFILES;
Drop success.

<Query 4> Delete the tablespace user_data along with all objects stored therein and all referential integrity constraints that refer to primary and unique keys in all tables in the tablespace.

iSQL> DROP TABLESPACE user_data INCLUDING CONTENTS CASCADE CONSTRAINTS;
Drop success.