Oracle DROP TABLESPACE tips

The Oracle DROP TABLESPACE command

The Oracle DROP TABLESPACE command is used to remove a tablespace from the database. In the following example, we will drop the USELESS tablespace from our database using the Oracle DROP TABLESPACE command: 

Drop tablespace useless;

The Oracle DROP TABLESPACE command Oracle will generate an error if objects reside in the tablespace. Do the following to remove all objects in the tablespace prior to using Oracle DROP TABLESPACE command:

Drop tablespace useless including contents;

Oracle does not clean up after itself wile using the Oracle DROP TABLESPACE command by default. An option exists to force Oracle to clean up after itself using the Oracle DROP TABLESPACE command. The following example illustrates the Oracle DROP TABLESPACE command used with the including contents and datafiles keywords:

Drop tablespace useless including contents and datafiles;

Using this Oracle DROP TABLESPACE command, we can drop the tablespace, any/all objects within it, and all associated data files on the hard disk.

BE CAREFUL when using Oracle DROP TABLESPACE.  If a tablespace is dropped with the Oracle DROP TABLESPACE command, it is very difficult to bring the tables dropped with Oracle DROP TABLESPACE back.  A high quality backup plan must be in place to retrieve data after using Oracle DROP TABLESPACE. Even then, if Oracle DROP TABLESPACE is used, you are guaranteed to lose data unless you take extraordinary measures during recovery.

 

 

 

---