Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, we will explain how to use the Drop Tablespace statement to remove a tablespace from the Database.
It is best to make a tablespace offline before dropping it.
1 2 3 | SQL> ALTER TABLESPACE HR_RENAME_DATA OFFLINE; Tablespace altered. |
After ensuring that the tablespace is not used, it can be dropped as follows.
1 2 3 | SQL> DROP TABLESPACE HR_RENAME_DATA INCLUDING CONTENTS; Tablespace dropped. |
If we want the datafiles connected to the tablespace to be dropped from the operating system along with the tablespace;
1 2 3 | SQL> DROP TABLESPACE HR_TEMP INCLUDING CONTENTS AND DATAFILES; Tablespace dropped. |
If a table in one tablespace is connected to a table in another tablespace with a primary key/foreign key relationship, an error is received when dropping it.
To prevent this;
1 2 3 | SQL> DROP TABLESPACE HR_DATA INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS; Tablespace dropped. |
