Drop Tablespace Scenarios
The DROP TABLESPACE remove the tablespace from the database.
syntax:
DROP TABLESPACE tablespace_name
[INCLUDING CONTENTS [AND | KEEP] DATAFILES]
[CASCADE CONSTRAINTS];
Syntax Explanation:
First - "DROP TBALESPCE" keyword and followed by actual Tablespace Name.
Second - use the INCLUDE CONTENTS to delete all contents of the tablespace. If the tablespace has any objects, you must use this option to remove the tablespace. Any attempt to remove a tablespace that has objects without specifying the INCLUDING CONTENTS option will result in an error.
Third - AND DATAFILES option to instruct Oracle to delete the datafiles of the tablespace and KEEP DATAFILES option to leave the datafiles.
Fourth - If the tablespace that has objects with referenced by referential integrity constraints from other tablespace, you must use the CASCADE CONSTRAINTS option to drop these constraints. If you omit the CASCACDE CONSTRAINTS clause in such situations, Oracle returns an error and does not remove the tablespace.
You can use the DROP TABLESPACE to remove a tablespace regardless of whether it is online and offline.
Its best practice to take the tablespace offline before removing it to ensure no sessions are accessing the tablespace.
Note:
1. We cannot drop the SYSTEM tablespace.
2. We can drop SYSAUX tablespace in database MIGRATE mode.
DROP TABLESPACE system privilege is mandatory to execute the DROP TABLESPACE statement.
To drop the SYSAUX tablespace, you need to have the SYSDBA system privilege.
Examples:
1) Using Oracle DROP TABLESPACE to remove an empty tablespace example
First, create a new tablespace named TBS1:
CREATE TABLESPACE TBS1
DATAFILE 'TBS1.dbf'
SIZE 10M;
Second, use the DROP TABLESPACE to remove the TBS1 tablespace:
DROP TABLESPACE TBS1;
2) Using Oracle DROP TABLESPACE to remove a non-empty tablespace example
First, create a new tablespace named TBS2:
CREATE TABLESPACE TBS2
DATAFILE 'TBS2.dbf'
SIZE 5M;
Second, create a new table T2 in the tablespace TBS2:
CREATE TABLE T2 (
c1 INT
) TABLESPACE TBS2;
Third, use the DROP TABLESPACE statement to drop the TBS2 tablespace:
DROP TABLESPACE TBS2;
Oracle issued the following error:
ORA-01549: tablespace not empty, use `INCLUDING CONTENTS` option
To drop the TBS2 tablespace, we need to use the INCLUDING CONTENTS option:
DROP TABLESPACE TBS2
INCLUDING CONTENTS;
3) Using Oracle DROP TABLESPACE to remove a tablespace whose tables are referenced by referential constraints
First, create two tablespaces named TBS3 and TBS4:
CREATE TABLESPACE TBS3
DATAFILE 'TBS3.dbf'
SIZE 5M;
CREATE TABLESPACE TBS4
DATAFILE 'TBS4.dbf'
SIZE 5M;
Next, create a new table T3 in the TBS3 tablespace:
CREATE TABLE T3(
c1 INT PRIMARY KEY
) TABLESPACE TBS3;
Then, create a new table T4 in the TBS4 tablespace:
CREATE TABLE T4 (
c1 INT PRIMARY KEY,
c2 INT NOT NULL,
FOREIGN KEY(c2) REFERENCES T3(c1)
) TABLESPACE TBS4;
After that, drop the tablespace TBS3:
DROP TABLESPACE TBS3
INCLUDING CONTENTS;
Oracle issued the following error:
ORA-02449: unique/primary keys in table referenced by foreign keys
Finally, use the DROP TABLESPACE that includes the CASCADE CONSTRAINTS option to drop the tablespace:
DROP TABLESPACE TBS3
INCLUDING CONTENTS AND DATAFILES
CASCADE CONSTRAINTS;
It worked as expected.
Regards,
Mallik
