How do I delete an entire database ?

How do I delete an entire database ?

The key to removing a database is locating all of the files that may be relevant to it. Typically this will consist of:

database files
redo logs
archived logs
control files
OS related files (alert log, trace files)
UTL_FILE directories
Password files
SQL Net configuration files
(NT) Service entries
(NT) Registry entries
In most cases, the database assistant (dbassist or dbca on most platforms) can be used to delete a database as appropriate for the relavant platform. Alternatively, the following process can be used as a rough guide (assuming your database still is running)

Task How to
List database files select name from V$DATAFILE
List redo logs select member from V$LOGFILE
List archived logs select value from v$parameter where name like 'log_archive_dest%'
List control files select name from v$controlfile
List OS related files (alert log, trace files) select value from v$parameter where name like '%dest'
List UTL_FILE directories select value from v$parameter2 where name = 'utl_file_dir'
List Password files location will vary from platform to platform
List SQL Net configuration files Search for files listener.ora, tnsnames.ora, oratab, or use the Network manager tool to remove the appropriate entries
(NT) Service entries Services under NT
(NT) Registry entries Via regedit

You can now shut the database. In most cases, the appropriate files as determined from above can then be deleted, but don't forget common sense. For example, your listener.ora file may be shared by other databases, the directories for UTL_FILE may also be shared etc. Even your database files could be shared if you were running parallel server (or sharing read-only tablespaces between two totally separate databases)

-------------------------------------------------------------
How do I delete an entire database (UNIX only)?
The following shows the steps to drop a database in Unix enviroment. In order to delete a database, there are few things need to be taken care of. First, all the database related files eg *.dbf, *.ctl, *.rdo, *.arc need to be deleted. Then, the entry in listener.ora and tnsnames.ora need to be removed. Third, all the database links need to be removed since it will be invalid anyways.

It depends how you login to oracle account in Unix, you should have environment set for the user oracle. To confirm that the environment variable is set, do a env|grep ORACLE and you will notice that your ORACLE_SID=SOME_SID and ORACLE_HOME=SOME_PATH. If you do not already have the ORACLE_SID and ORACLE_HOME set, do it now.

Make sure also, that you set the ORACLE_SID and ORACLE_HOME correct else you will end up deleting other database. Next, you will have to query all the database related files from dictionaries in order to identify which files to delete. Do the following:

01. Login as connect / as sysdba at svrmgrl
02. startup the database if it's not already started. The database must at least mounted.
03. spool /tmp/deletelist.lst
04. select name from v$datafile; (This will get all the datafiles; alternatively, you can select file_name from dba_data_files)
05. select member from v$logfile;
06. select name from v$controlfile;
07. archive log list (archive_log_dest is where the archived destination is)
08. locating ifile by issuing show parameter ifile (alternatively, check the content of init.ora)
09. spool off
10. Delete in O/S level the files listed in /tmp/deletelist.lst
11. remove all the entries which refer to the deleted database in tnsnames.ora and listener.ora (located in $ORACLE_HOME/network/admin)
12. remove all database links referring to the deleted database.
13. check "/var/opt/oracle/oratab" to make sure there is no entry of the database deleted. If yes, remove it.
14. DONE


--------------------------------------------------------------------------------