Wednesday, September 7, 2011

DROP DATABASE;

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!

8 comments:

Unknown said...

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

Marko Sutic said...

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

bex said...

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

Jeremy Schneider said...

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! :)

oraclenerd said...

@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.

Nitesh said...

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

Anonymous said...

This saved me some investigation, thanks!
A. Evert

Unknown said...

Hi

dropping database steps:::::::::::
SQL> conn / as sysdba

SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 150667264 bytes
Fixed Size 1342736 bytes
Variable Size 92275440 bytes
Database Buffers 50331648 bytes
Redo Buffers 6717440 bytes
Database mounted.


SQL> alter system enable restricted session;

System altered.

SQL> select name from v$database;

NAME
---------
TEST


SQL> drop database;

Database dropped.

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