how can I drop a database

[Giada Bonfà]

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

drop database namedatabase;

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

thanks

[Christian Gebhardt]

Hi

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

Chris

[Ravi_8]

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 "

[Sergejs Svitnevs]

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]

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.

[T G Manikandan]

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]

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

[Yogeeraj_1]

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

[Sunil Sharma_1]

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

[R. Allan Hicks]

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