Empty a Database
In my case, a phased out database server is going to transfer to another project team, my job is to remove all data in the database. An empty database means that there's almost nothing but native schemas, data and functions.
Question, why don't we just create a new database and destroy the old one? This is simply because emptying a database is faster than creating a new database by DBCA, even better, I don't have to reconfigure related settings while the instance is running.
The most efficient way to empty a database is to remove the data files directly from the bottom. So our approach is to make the idea work.
There're 4 steps should be implemented to empty an Oracle database.
Step 1: Offline All Non-Native Tablespaces
Before we can actually drop all non-native tablespaces, we should offline them first.
SQL> ALTER TABLESPACE ERP_TBS_01 OFFLINE NORMAL;
Tablespace altered.
SQL> ALTER TABLESPACE ERP_TBS_02 OFFLINE NORMAL;
Tablespace altered.
SQL> ALTER TABLESPACE ERP_TBS_03 OFFLINE NORMAL;
Tablespace altered.
SQL> ALTER TABLESPACE ERP_TBS_04 OFFLINE NORMAL;
Tablespace altered.
Step 2: Drop All Non-Native Tablespaces
Now we can drop the tablespace including all contents and data files, everything related. That's why we can expect that the disk space is dramatically freed in this step.
SQL> DROP TABLESPACE ERP_TBS_01 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
Tablespace dropped.
SQL> DROP TABLESPACE ERP_TBS_02 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
DROP TABLESPACE ERP_TBS_02 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS
*
ERROR at line 1:
ORA-22868: table with LOBs contains segments in different tablespaces
SQL> DROP TABLESPACE ERP_TBS_03 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
Tablespace dropped.
SQL> DROP TABLESPACE ERP_TBS_04 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
DROP TABLESPACE ERP_TBS_04 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS
*
ERROR at line 1:
ORA-22868: table with LOBs contains segments in different tablespaces
If you saw ORA-22868 like the above, you don't have to handle it, just keep going. Don't waste your time to solve it, those segments will be vanished in the next step.
Step 3: Drop All Non-Native Users
We drop users with CASCADE modifier.
SQL> DROP USER ERP_USER1 CASCADE;
User dropped.
SQL> DROP USER ERP_USER2 CASCADE;
User dropped.
SQL> DROP USER ERP_USER3 CASCADE;
User dropped.
SQL> DROP USER ERP_USER4 CASCADE;
User dropped.
Step 4: Drop Previously Undropped Tablespaces
Now we can drop the remaining tablespaces for good.
SQL> DROP TABLESPACE ERP_TBS_02 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
Tablespace dropped.
SQL> DROP TABLESPACE ERP_TBS_04 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
Tablespace dropped.
The result of emptying the Oracle database is clean and fast.