ORACLENERD: DROP DATABASE;

DROP DATABASE;

Wednesday, September 7, 2011

I decided to blow away one of my newly created databases. Instead of using the DBCA, I decided to try it manually.

DROP DATABASE;

DROP DATABASE
*
ERROR at line 1:
ORA-01586: database must be mounted EXCLUSIVE and not open for this operation

Not it.

SQL> SHUTDOWN;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT EXCLUSIVE;
ORACLE instance started.

Total System Global Area 1048059904 bytes
Fixed Size                  2219992 bytes
Variable Size             608174120 bytes
Database Buffers          432013312 bytes
Redo Buffers                5652480 bytes
Database mounted.
SQL> DROP DATABASE;
DROP DATABASE
*
ERROR at line 1:
ORA-12719: operation requires database is in RESTRICTED mode

Ugh.

SQL> STARTUP NOMOUNT;
ORACLE instance started.

Total System Global Area 1048059904 bytes
Fixed Size                  2219992 bytes
Variable Size             608174120 bytes
Database Buffers          432013312 bytes
Redo Buffers                5652480 bytes
SQL> DROP DATABASE;
DROP DATABASE
*
ERROR at line 1:
ORA-01507: database not mounted


SQL> ALTER DATABASE MOUNT;

Database altered.

SQL> DROP DATABASE;
DROP DATABASE
*
ERROR at line 1:
ORA-12719: operation requires database is in RESTRICTED mode

Not it. Here's the SQL Reference. Maybe that RESTRICTED reference means something...

SQL> STARTUP MOUNT RESTRICTED;
ORACLE instance started.

Total System Global Area 1048059904 bytes
Fixed Size                  2219992 bytes
Variable Size             608174120 bytes
Database Buffers          432013312 bytes
Redo Buffers                5652480 bytes
ORA-01504: database name 'RESTRICTED' does not match parameter db_name
'TESTING'

Barnacles. Perhaps the Administrator's Guide has something.

SQL> STARTUP MOUNT TESTING;
ORACLE instance started.

Total System Global Area 1048059904 bytes
Fixed Size                  2219992 bytes
Variable Size             608174120 bytes
Database Buffers          432013312 bytes
Redo Buffers                5652480 bytes
Database mounted.
SQL> DROP DATABASE;
DROP DATABASE
*
ERROR at line 1:
ORA-12719: operation requires database is in RESTRICTED mode


SQL> ALTER DATABASE RESTRICTED;
ALTER DATABASE RESTRICTED
                        *
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE


SQL> ALTER DATABASE READ ONLY;
ALTER DATABASE READ ONLY
                    *
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE

Last try

SQL> STARTUP NOMOUNT RESTRICT;
ORACLE instance started.

Total System Global Area 1048059904 bytes
Fixed Size                  2219992 bytes
Variable Size             608174120 bytes
Database Buffers          432013312 bytes
Redo Buffers                5652480 bytes
SQL> DROP DATABASE;
DROP DATABASE
*
ERROR at line 1:
ORA-01507: database not mounted


SQL> ALTER DATABASE MOUNT;

Database altered.

SQL> DROP DATABASE;

Database dropped.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Voila!

Labels: dba, howto, oradb

¶ 9/07/2011 02:20:00 AM

Comments:

Isn't it good to know that it's not easy to drop a database? :)

# posted by

Gokhan Atil : September 7, 2011 at 3:12 AM

Hi Chet,

I'm using this commands to drop database:
SQL> startup mount exclusive restrict;
SQL> drop database;

I've found on OTN forums that "startup mount exclusive is deprecated long ago" but it worked perfectly last time I was dropping database manually.

Regards,
Marko

# posted by

Marko Sutic : September 7, 2011 at 4:28 AM

ahh... it's like a fairy tale. Boy makes database, boy hates database, boy drops database... But database comes back to HAUNT him!

# posted by

bex : September 8, 2011 at 12:40 PM

LOL... usually I don't admit to this. But honestly, my sessions look almost exactly like this - more often than I let on. Sure, a quick search on google or tahiti would have gotten the syntax... and yet there I go saying: "I *know* I'm close and if I just try a few more ideas then I'm sure I'll guess it faster than looking it up..."

PS - will you be at openworld? hope to see you there! :)

# posted by

Jeremy Schneider : September 9, 2011 at 9:48 AM

@jeremy

I'm glad to know that I'm not the only one that does that. It's those rarely used commands that have to be simple...just one more try.

Will definitely be at OOW. Can't wait.

# posted by

oraclenerd : September 9, 2011 at 10:55 AM

Here is how you wack a database in less than no time!

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 146472960 bytes

Fixed Size 1311940 bytes

Variable Size 92277564 bytes

Database Buffers 50331648 bytes

Redo Buffers 2551808 bytes

Database mounted.

SQL> drop database;

drop database

*

ERROR at line 1:

ORA-12719: operation requires database is in RESTRICTED mode

SQL> alter system enable restricted session;

System altered.

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

# posted by

Nitesh : March 28, 2012 at 7:33 AM