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!
at

8 comments:

  1. UnknownSeptember 7, 2011 at 3:12 AM

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

    ReplyDelete
  2. Marko SuticSeptember 7, 2011 at 4:28 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

    ReplyDelete
  3. bexSeptember 8, 2011 at 12:40 PM

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

    ReplyDelete
  4. Jeremy SchneiderSeptember 9, 2011 at 9:48 AM

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

    ReplyDelete
  5. oraclenerdSeptember 9, 2011 at 10:55 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.

    ReplyDelete
  6. NiteshMarch 28, 2012 at 7:33 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

    ReplyDelete
  7. AnonymousMay 31, 2013 at 4:40 AM

    This saved me some investigation, thanks!
    A. Evert

    ReplyDelete
  8. UnknownJanuary 16, 2017 at 2:24 AM

    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

    ReplyDelete