Drop Database Manually Oracle

————- Drop Database Manually Oracle ———————

1. First get the datafiles, control files and redo log files location

Login to sqlplus as sysdba and execute below script :


DECLARE

TYPE string_arr IS TABLE OF VARCHAR2(1024);

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;

/

It will generate output like

rm -f $ORACLE_BASE/admin/$ORACLE_SID

rm -f +DATA/orcl/control01.ctl

rm -f +DATA/orcl/control02.ctl

rm -f +DATA/orclstandby/datafile/mgmt_ad4j_ts.270.805385125

rm -f +DATA/orclstandby/datafile/mgmt_ecm_depot_ts.268.805385111

rm -f +DATA/orclstandby/datafile/mgmt_tablespace.269.805385119

rm -f +DATA/orclstandby/redo01.log

rm -f +DATA/orclstandby/redo02.log

rm -f +DATA/orclstandby/redo03.log

rm -f +DATA/orclstandby/sysaux01.dbf

rm -f +DATA/orclstandby/system01.dbf

rm -f +DATA/orclstandby/undotbs01.dbf

rm -f +DATA/orclstandby/users01.dbf

rm -f +FRA

rm -f +FRA/orclstandby/onlinelog/group_4.368.801307819

rm -f +FRA/orclstandby/onlinelog/group_5.369.801307819

rm -f +FRA/orclstandby/onlinelog/group_6.370.801307821

rm -f +FRA/orclstandby/onlinelog/group_7.371.801307821

rm -f /oracle/app/oracle

rm -f /oracle/app/oracle/admin/orcl/adump

rm -f /oracle/app/oracle/diag/rdbms/orclstandby/orcl1/cdump

rm -f /oracle/app/oracle/diag/rdbms/orclstandby/orcl1/trace

rm -f 1

rm -f ?/dbs/arch

rm -f

2. Drop the database using command

SQL > drop database;

ORA-02231: missing or invalid option to ALTER DATABASE If you get like above error then restart database and execute command below ………………….

SQL> alter system enable restricted session;

System altered.

SQL> drop database;

3. Delete all the OS files using the commands generated in output before

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