0

I need to put together a plan to execute some change scripts to a Oracle 10g database, which will add some values, change some values a drop a table and create a new table.

I have a step for creating a DMP of the original database before the changes and for running a script that will execute all the changes. But I need a back-out plan if things go wrong. I was wondering what is the bets step(s) to drop the database and import the old DMP file?

I know about the sqlplus command:

imp <touser>/<password>@<host_address>/<service> file=<filename>.dmp fromuser=<fromuser> touser=<touser> log=backup_import.log

But what is the best way to drop the existing database? Is it just

drop user <schema_user> cascade;

Thanks in advance.

K

migrated from stackoverflow.com Jun 7 '13 at 11:43

This question came from our site for professional and enthusiast programmers.

  • imp is not a "SQLPlus" command. It's a commandline tool. – a_horse_with_no_name Jun 7 '13 at 8:39
  • 1
    Do you really want to drop "the database"? Note that a "database" in Oracle is basically the whole installation, not just a single "namespace" inside the system (as e.g. in Postgres or SQL Server). It seems you just want to drop the schema (=user). In that case, drop user ... cascade is the best option. – a_horse_with_no_name Jun 7 '13 at 8:41
  • ... as long as you're really sure you have a good and complete dump of everything you'll need. You'd also need to recreate the user with all its non-table privileges before running the import. You should look at datapump expdp/impdp rather than the older exp/imp though, since you're on 10g. – Alex Poole Jun 7 '13 at 8:51
1

The best way to drop database is to use DBCA graphic tool. Or you can use this sequence of commands:

shutdown immediate 
startup exclusive mount
alter system enable restricted session;
drop database;

But instead I suggest using RMAN backup and restore.

0

You could also use restore points for your back-out plan. Before you start your changes, issue a

create restore point insert_meaningful_name;

Then apply all your changes. When something goes wrong, issue a

flashback database to restore point insert_meaningful_name;

This will revert your database to the exact same state it had when you created the restore point.

Please note that you need SYS-privileges to use restore points and flashback database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.