Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

how can I drop a database

Giada Bonfà
Frequent Advisor

how can I drop a database

Hi,
I would like to drop a database.
I did:

drop database namedatabase;

but it doesn't work.
How can I do?

thanks
9 REPLIES
Christian Gebhardt
Honored Contributor

Re: how can I drop a database

Hi

- shutdown the database 'shutdown abort'
- delete all datafiles, init.ora, redologs, archives, ...

Chris
Ravi_8
Honored Contributor

Re: how can I drop a database

Hi,

If your database is of oracle:
login as oracle

$ export DISPLAY=:0.0
$dbca (if oracle is 9i)
choose delete a database

if oracle 8i
$svrmgrl
svrmgrl> connect internal
svrmgrl> shudown database
svrmgrl> drop database

If database is DB2:
Login as DB2 user id

$db2 "force application all"
$db2 " drop database "

never give up
Sergejs Svitnevs
Honored Contributor

Re: how can I drop a database

If you want to drop database in Oracle, all you need to do is shutdown the database instance, remove references to the database and to remove all files that are associated with the database.

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

Regards,
Sergejs
Indira Aramandla
Honored Contributor

Re: how can I drop a database

It depends if you want to delete the database completey and do not want to go back to this SID any more.

If you do not need this SID any more and do not intend to create a new one of the same name then the simple procedure is.

1. Shutdown the database.
2. Physically delete the data (.dbf) file, redo logs, control files (.ctl).
3. Physically remove the links for the init ora file and configuration files.
4. Remove the entry in the oratab file.

If you need to clean up this database and then intend to create a new one like this, please refer to the attachment which has a step by step procedure for delete a oracle database in UNIX environment.
Never give up, Keep Trying
T G Manikandan
Honored Contributor

Re: how can I drop a database

check your ORACLE_SID and ORACLE_HOME.
Make sure that you are pointing the right ORACLE_SID

01. $svrmgrl
02. startup the database if it's not already started. The database must at least mounted.
svrmgrl>startup mount
03. spool /databasefiles.lst
svrmgrl>spool databasefiles.lst
04. select name from v$datafile; (This will get all the datafiles; alternatively, you can select file_name from dba_data_files)
svrmgrl>select name from v$datafile;
05. select member from v$logfile;
svrmgrl>select * from v$logfile;
06. select name from v$controlfile;
svrmgrl>select * from v$controlfile;
07. archive log list (archive_log_dest is where the archived destination is)
svrmgrl>archive log list;
Go to the destination directory of archive_log_dest parameter and remove all the files.
08. locating ifile by issuing show parameter ifile (alternatively, check the content of init.ora)
09. spool off
svrmgrl>spool off
10. Delete in O/S level the files listed in databasefiles.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.

Thanks
Alexander E. Ivanov
Frequent Advisor

Re: how can I drop a database

If you installed Database Configuration Assistant, use it for delete your database.
Yogeeraj_1
Honored Contributor

Re: how can I drop a database

hello,

to add to all those post above, i would recommend that you do a backup first, just in case...


so my steps would be:
Get an error free Export of the entire database before I dropped it. Just in case someone needs any data from that database after it's gone.

With the database running issue and spool the following select statements.

Make sure you are on the correct database:
SELECT name from v$database;

Get a list of all the data files for this database.
SELECT file_name from dba_data_files;

Find the control files for this database:
SELECT name, value from V$parameter
where lower(name) like '%control_files%';

Shutdown the database.

Use Database Configuration Assistant to drop the database

delete the datafiles.

hth
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Sunil Sharma_1
Honored Contributor

Re: how can I drop a database

Hi,

the easiest way to drop database in oracle is do it through GUI.
login as a oracle(oracle owner) user
export your display and run dbassist
same tool u can use for creating databse also.

Sunil
*** Dream as if you'll live forever. Live as if you'll die today ***
R. Allan Hicks
Trusted Contributor

Re: how can I drop a database

As others have already pointed out, you can find the datafiles from the data dictionary.

You might find it handy to:

sqlplus

connect / as sysdba

spool destroy_the_world.sh

SELECT 'rm -f'||file_name from dba_data_files;

spool off

shutdown abort

quit

at the shell prompt, su to root. (After all, root should be the only one allowed to destroy the world)

chmod +x destroy_the_world.sh

destroy_the_world.sh

Using this technique with the other respondents' suggestions for finding files allows you to generate scripts of mass destruction so that if you are just playing with the database creation and destruction, things will be simplier. BTW I've found through years of screwing up, that it's always a good idea to make an off-line backup of the database. In case you change your mind.

Rule #1 in computer science.....
Never do _anything_ you can't undo.

-Good Luck
"Only he who attempts the absurd is capable of achieving the impossible