Expert: Peter Choi - 7/7/2009

Question
Hi.
I have a question about how to clearing Oracle 10.2.0.3 Database (clearing database files, free it) without deleteing it. Program which i use is in test implementation thats why I need restore backed up database very frequently and then import existing data.
How can I clear (drop) all data (tables, indexes ...)
from Oracle DataBase Instance to make it like just created one.
Thanks.

Answer
Hi Magerram,

NOTE: This will remove the database as well as all the data in this database. Tables and tablespaces will be dropped and removed!

SQL> shutdown abort;
SQL> startup mount exclusive restrict;
SQL> drop database;
SQL> exit

Reference: http://www.adp-gmbh.ch/ora/sql/drop_database.html

Alternatively, you can run the dbca (on either MS-Windows or Unix based operating system). The "Database Configuration Assistant" can also be used to drop your database using its GUI application.

Hope this helps.

Peter