6 Replies Latest reply on Jan 7, 2009 9:51 AM by 678552

    drop database vs. dbca delete database

      So I'd like to delete this database: control files, datafiles, log files, spfiles, everything. leave no trace.

      I can use good old sqlplus and run "drop database;", or I can use DBCA and do "delete database".
      What is the difference?

      Documentation clearly explains what "drop database" will do, but it does not explain what DBCA is doing (maybe just running drop database behind the curtains?).
        • 1. Re: drop database vs. dbca delete database
          Maybe dbca does some hidden "WinDoze" stuff (if db is on WinDoze).

          DROP DATABASE works fine on *nix.                                                                                                                                                                                                               
          • 2. Re: drop database vs. dbca delete database
            Yes, looks like the description of delete database from DBCA is kind of vagues.


            I guess the only way to find out is test it out :)
            • 3. Re: drop database vs. dbca delete database
              Test it?
              By running DBCA with strace, so I can see exactly what files it touches?

              I can test to see if it deletes the same files that drop DB does (it does), but it is very difficult to check if it changes anything else in addition...
              • 4. Re: drop database vs. dbca delete database
                Well, there are only so many files associated with database. Besides the one will be dropped by 'drop database' command, there's only those files under dump destination. Or perhaps the backup files depends backup method chosen, DBCA may or may not have knowledge of.

                I would say the logical guess is DBCA will remove all files that will be created by DBCA. I seems remember one time I have to cancel a DBCA install, it will remove these files under bdump/cdump etc..
                • 5. Re: drop database vs. dbca delete database
                  Laurent Schneider
                  strace do not sound to be the best approach, as the datafiles are deleted by the server not by the client.

                  dbca for example should remove the entry in /etc/oratab, what drop database will not do.

                  I would prefer something like
                  find / -type f -ls > /tmp/before
                  dbca -silent -drop ...
                  find / -type f -ls > /tmp/after
                  diff /tmp/before /tmp/after

                  somehow it sounds more reliable than strace

                  .... - .... :)
                  • 6. Re: drop database vs. dbca delete database
                    from the book of «OCP - Oracle Database 10g Administration I», Chap 3. -- Using the DBCA to Delete a Database p 3 - 14

                    «Dropping a database involves removing its data files, redo log files, control files, and
                    initialization parameter files. The DROP DATABASE statement deletes all control files and all
                    other database files listed in the control file. To use the DROP DATABASE statement
                    successfully, all the following conditions must apply:
                    • The database must be mounted and closed.
                    • The database must be mounted exclusively—not in shared mode.
                    • The database must be mounted as RESTRICTED.
                    An example of this statement is:
                    DROP DATABASE;
                    The DROP DATABASE statement has no effect on archived log files nor does it have any effect
                    on copies or backups of the database. It is best to use Recovery Manager (RMAN) to delete such
                    files. If the database is on raw disks, then the actual raw disk special files are not deleted.»

                    there is no distinction between the DBCA Delete a database and the drop database statement.