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. |
