Drop Database Manually


1. Login to sqlplus as sysdba and execute below script :
oradba@ANARDB:~$ export ORACLE_SID=ANARDB

oradba@ANARDB:~$ sqlplus

SQL> select name from v$database;

NAME
———
ANARDB

2. Open the set server output on to get the result from below query.

SQL> set serveroutput on;

3. Execute the query to get the information of datafiles, control files and redo log files location from below mentioned script.

SQL>DECLARE
 TYPE string_arr IS TABLE OF VARCHAR2(2048);
file_list string_arr;
BEGIN 
SELECT t.file_path BULK COLLECT
INTO file_list 
FROM (SELECT NAME file_path
 FROM V$DATAFILE 
UNION
SELECT MEMBER file_path
FROM V$LOGFILE
UNION 
SELECT NAME file_path
FROM v$controlfile
UNION 
SELECT VALUE file_path 
FROM v$parameter 
WHERE NAME LIKE ‘%dest’ 
UNION 
SELECT VALUE file_path 
FROM v$parameter2 
WHERE NAME = ‘utl_file_dir’ 
UNION 
SELECT ‘$ORACLE_BASE/admin/$ORACLE_SID’ file_path 
FROM dual
) t;
FOR i IN file_list.FIRST .. file_list.LAST LOOP 
DBMS_OUTPUT.PUT_LINE(‘rm -f ‘ || file_list(i)); 
END LOOP; 
END;
/

PL/SQL procedure successfully completed.

You will get output like

rm -f $ORACLE_BASE/admin/$ORACLE_SID
rm -f /oracle/bases/ANARDB/admin/trace/bdump
rm -f /oracle/bases/ANARDB/admin/trace/cdump
rm -f /oracle/bases/ANARDB/admin/trace/udump
rm -f /oracle/bases/ANARDB/controlfile/control01.ctl
rm -f /oracle/bases/ANARDB/datafile/db_data01.dbf
rm -f /oracle/bases/ANARDB/datafile/db_temp01.dbf
rm -f /oracle/bases/ANARDB/datafile/tools01.dbf
rm -f /oracle/bases/ANARDB/datafile/users01.dbf
rm -f /oracle/bases/ANARDB/data1/control02.ctl
rm -f /oracle/bases/ANARDB/data1/redo01A.rdo
rm -f /oracle/bases/ANARDB/data1/redo02A.rdo
rm -f /oracle/bases/ANARDB/data1/redo03A.rdo
rm -f /oracle/bases/ANARDB/data2/control03.ctl
rm -f /oracle/bases/ANARDB/data2/redo01B.rdo
rm -f /oracle/bases/ANARDB/data2/redo02B.rdo
rm -f /oracle/bases/ANARDB/data2/redo03B.rdo
rm -f /oracle/bases/ANARDB/system/system01.dbf
rm -f /oracle/bases/ANARDB/work/undotbs01.dbf
rm -f 1
rm -f ?/dbs/arch
rm -f ?/rdbms/audit
rm -f
4. Save the output for further use and shutdown your database.

SQL> shut immediate;

5. Remove the file from OS command.

oradba@ANARDB:~$ rm -f $ORACLE_BASE/admin/$ORACLE_SID
oradba@ANARDB:~$ rm -rf /oracle/bases/ANARDB/admin/trace/bdump
oradba@ANARDB:~$ rm -rf /oracle/bases/ANARDB/admin/trace/cdump
oradba@ANARDB:~$ rm -rf /oracle/bases/ANARDB/admin/trace/udump
oradba@ANARDB:~$ rm -f /oracle/bases/ANARDB/controlfile/control01.ctl
oradba@ANARDB:~$ rm -f /oracle/bases/ANARDB/datafile/db_data01.dbf
oradba@ANARDB:~$ rm -f /oracle/bases/ANARDB/datafile/db_temp01.dbf
oradba@ANARDB:~$ rm -f /oracle/bases/ANARDB/datafile/tools01.dbf
oradba@ANARDB:~$ rm -f /oracle/bases/ANARDB/datafile/users01.dbf
oradba@ANARDB:~$ rm -f /oracle/bases/ANARDB/data1/control02.ctl
oradba@ANARDB:~$ rm -f /oracle/bases/ANARDB/data1/redo01A.rdo
oradba@ANARDB:~$ rm -f /oracle/bases/ANARDB/data1/redo02A.rdo
oradba@ANARDB:~$ rm -f /oracle/bases/ANARDB/data1/redo03A.rdo
oradba@ANARDB:~$ rm -f /oracle/bases/ANARDB/data2/control03.ctl
oradba@ANARDB:~$ rm -f /oracle/bases/ANARDB/data2/redo01B.rdo
oradba@ANARDB:~$ rm -f /oracle/bases/ANARDB/data2/redo02B.rdo
oradba@ANARDB:~$ rm -f /oracle/bases/ANARDB/data2/redo03B.rdo
oradba@ANARDB:~$ rm -f /oracle/bases/ANARDB/system/system01.dbf
oradba@ANARDB:~$ rm -f /oracle/bases/ANARDB/work/undotbs01.dbf

Congratulation your database deleted.

Drop Database in Oracle 11g and above version.

Very easy to delete 11g and above veriosn databse because it will support the drop database command.

1. Check the database name.

SQL> select name from v$database;

NAME
———
ANARDB

2. Check the database size before drop (Take the backup for further use if needed)

SQL> SELECT SUM (a.log_space + b.data_space + c.tempspace) “Total_DB_Size (GB)”
 FROM (SELECT ROUND (SUM (BYTES/1024/1024/1024), 2) data_space
 FROM dba_data_files) b,
 (SELECT ROUND (SUM (BYTES*members/1024/1024/1024), 2) log_space
 FROM v$log) a,
 (SELECT NVL(ROUND(SUM(BYTES/1024/1024/1024),2), 0) tempspace
 FROM dba_temp_files) c;

Total_DB_Size (GB)
—————–
 846.47

Note: If you need you can take the database backup for further use. Make sure before delete.

3. Shutdown the database.

SQL> shutdown immediate;
ORACLE instance shut down.

4. Start the database in restricted mode.

SQL> startup mount exclusive restrict;
ORACLE instance started.

Total System Global Area 419430400 bytes
Fixed Size 2041048 bytes
Variable Size 285219624 bytes
Database Buffers 125829120 bytes
Redo Buffers 6340608 bytes
Database mounted.

5. Drop the database.

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options

SQL> exit
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: