Import as restore of database instance

[dklloyd]

As a backup procedure we are currently using the export function. The command used is:
exp system/passwd@CMAL file=out.dmp log=file.log full=y.
All the tables are successfully exported using this facility. The problem now is to use this to restore the database to a couple of weeks ago. I have tried to use:
imp commandsystem/passwd@CMAL full=y inctype=restore file=out.dmp
but this does not appear to drop the tables or indexes so I get errors on every table & primary key as it tries to add the data. What would be the best way to use the export file to restore back?
Any ideas anyone?
Thanks in advance..

 

[karluk]

It's normal behavior for an import to generate errors when the tables already exist. You can either drop all tables prior to the import, or truncate the tables and import using the "ignore=y" parameter. "Ignore=y" instructs Oracle to suppress errors caused by the objects already existing.

Naturally you should do a backup on your current database prior to either dropping tables or truncating. You don't want to be in a position of dropping a table only to discover later that you need it back again.

 

[dklloyd]

Thanks Karluk
Do you know if I can truncate or drop all the tables for one Schema in one go?

 

[karluk]

The issue of dropping all tables in a schema was discussed recently in thread186-78497. Sem suggested dropping the user id (presumably you would later recreate it), and Carp supplied a script that would generate the "drop table" commands.

 

[karluk]

Correction. Carp's script drops all tables except those owned by SYS and SYSTEM. You would have to modify the where clause to make it specific to a single schema.

 

[dklloyd]

Thanks Karluk for all the info.
If there wasn't a function that could drop all a user's tables I was looking for some SQL that would do similar and the thread you mentioned did just what I needed, so now I have all the data back as I need it.
One point I am trying to fathom is whether I can import just one user's schema using the full export file or whether I have to accept that all users have to be imported, including system & sys?

 

[karluk]

You can use the full export and restrict the import to a single user. The parameters that do this are "fromuser=" and "touser=". If you specify one or more ids using these parameters, the import will ignore all the other data in the export file.

 

[dklloyd]

Many thanks for all your help on this one!