Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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
add comment

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

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

2 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.