Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Database Deletion

Oracle Tips by Burleson

Databases are deleted by shutting them down and then removing all of their associated files from the system. No command to perform this is provided by Oracle prior to Oracle. In Oracle, the DROP TABLESPACE command enables the DBA to drop the operating system files, as well as the logical database structures. The Database Creation Assistant (DBCA) does allow a DBA to remove a database in 8i and 9i. Let's look at an example using the DBCA on Linux.

The DBCA is started by issuing the DBCA command at the Oracle user in Linux or UNIX (or selecting it from the appropriate menu in Windows or NT.) The first screen that is displayed is shown in Figure 2.21. Select the Next button to display the Operations screen (see Figure 2.22). There, select the option Delete a Database and then select the Next button.

Figure 2.21 Welcome Screen of the Database Configuration Assistant

Figure 2.22 Operations screen of the Database Configuration Assistant.

The DBCA will display the Database screen next (shown in Figure 2.23). On the Database screen, select the database you wish to delete and then select the Next button.

Figure 2.23 Database screen of the Database Configuration Assistant.

Once you have made a database selection, the DBCA will show a Summary screen (shown in Figure 2.24) to give you a first chance to abort the deletion, just in case you chose the wrong database SID on the previous screen. Once you have verified that the database being deleted is in fact the one you want deleted, select the OK button to continue with the deletion process.

Figure 2.24 Summary Screen of the Database Configuration Assistant

After you select OK on the Summary screen, the DBCA program gives you one last chance to decide not to delete the instance. This screen isn't titled, so I call it the "Are you really, really sure" screen; it’s  shown in Figure 2.25.. If you are really, really sure you want to delete the database, select Yes, and the delete operation will commence. This is the point of no return.

Figure 2.25 The "Are you really, really sure?" screen of the Database Configuration Assistant.

The next screen shown in the DBCA for the database deletion process displays the actual deletion status (Figure 2.26), that is, the progression through the various steps of deleting a database: connect, delete the instance and datafiles, and update network files. Following the final step, the DBCA automatically goes to the completion screen.

Figure 2.26 Database deletion status screen of the Database Configuration Assistant.

The final screen of the deletion process is the Database Deletion Completed screen )shown in Figure 2.27). It asks the question "Do you want to perform another operation?" The choices are Yes, to return to DBCA, or No, exit from DBCA.  If you are done deleting databases, choose No.

Figure 2.27 Database deletion completion screen of the Database Configuration Assistant.

Manual Database Deletion

If you must manually delete a database, first log in as a DBA account, then use the following commands to get a listing of all the datafiles associate with the database:

Spool db_files.lis

Select file_name from dba_data_files;

Select file_name from v$log_files;

Select value from v$parameter where name='control_files';

Spool off

Once you have your file listing, shut down the database. And I suggest doing a complete, final backup (rest assured, someone will require something from this database sometime in the future) of the database before deleting it.

Once you have backed up the database that you wish to delete, delete the physical files from your list. There are several files that contain data concerning the database and the instance. If deletion is required of both, it is suggested that a UNIX “grep” or “find” command be used to locate the files that contain the instance or database name. On UNIX, the files of concern are oratab, listener.ora, tnsnames.ora, and any user-created scripts or files. On NT, you will need to remove the services and delete the database names from the listener.ora and tnsnames.ora files. The name and related data must be removed from these files, or the files deleted, if the user wishes to reuse the instance name. Generally, the files on UNIX or Linux are in the /etc or /var/opt/oracle directory or (in the case of the network files) in $ORACLE_HOME/network/ admin. If only the database needs to be deleted, and then re-created, edit the CREATE script and rerun it under SVRMGR or SQLPLUS with the database shut down. This will reinitialize the database.

Changing the “dbname” for a Database

Follow these steps to change the dbname for a database:

1.    At the operating system command line enter:

      % svrmgrl or sqlplus /nolog

2.    Once the server manager or sqlplus command line appears, enter:

     connect internal (for svrmgrl) or connect / as sysdba or connect sys as            sysdba (for sqlplus)

3.    When the server manager or sqlplus indicates that you are connected, enter         the command:

      alter database backup controlfile to trace resetlogs;

This will write in a trace file, the CREATE CONTROLFILE command that would re-create the control file as it currently exists. This trace file will be located in the USER_DUMP_DEST location specified in the v$parameter table (select value from v$parameter where name='user_dump_dest').

4.    Exit and go to the directory where your trace files are located. They are usually in the $ORACLE_HOME/admin/<sid./udump directory, if you haven’t explicitly set the location in your initialization file. If user_dump_dest is set in the init<SID>.ora, then go to the directory listed in the user_dump_dest variable. The trace file will have the form “ora_NNNN.trc,” with NNNN being a number that will correspond (either in decimal or hexadecimal) to your system process ID.

5.    Get the “CREATE CONTROLFILE” command from the trace file and put it in a new file called something like crt_cf.sql.

6.    Edit the “crt_cf.sql” file and modify the “CREATE CONTROLFILE” command. Change the word “REUSE” to “SET”, and “NORESETLOGS” to “RESETLOGS”, and modify the “dbname.”

Old line:


New line:


Then save the “crt_cf.sql” file.

7.    Rename the old control files for backup purposes and so they are not in the way of creating the new ones.

8.    Edit initSID.ora so that db_name = ”new_name”.

9.    At the operating system prompt type:

      % svrmgrl(pre-8i) or sqlplus /nolog (post 8i)

10.   Once the server manager or sqlplus prompt appears, enter:

connect internal (pre-8i) or connect / as sysdba or connect sys as sysdba (post-8i)

11.   Once you are connected as INTERNAL or SYS, enter:

        startup nomount

12.   Type:


13.   Once the file stops executing, enter:

      alter database open;

14.   Verify that the database is functional; you can check that the database name         has been reset with the command:

     select name from v$database;

15.   Shut down and back up the database. Make sure any local variables are         also changed to reflect the SID (for example, ORACLE_SID).

Other Database Administration Tasks

Let’s look at some of the other operations that may need to be performed against a database.

See Code Depot for Full Scripts

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter