Oracle – How to Restore a Single Table

exportimportoracle

In a large database, an activity was performed to upload the data.
Due to some mistake, a month's data was entered again, which was left in the temp holding table.
So, to cover this up, we took dump of 2 tables from the back-up restored on another machine.
Renamed the tables where error occurred and restored from the backup.
But when the tables were restored the constraints failed to load as the same name is already in use.
Used the oracle exp/imp commands to export and import the data.

I'm not a DBA, but need help on this issue.

Best Answer

The constraints are kept for the renamed table. Drop them before you import the tables with the correct data.

alter table t1 drop constraint c;

There is also another way to do it if you do not want to lose the wrong data. You can create a new table based on the 2 tables:

create table t1 as select * from t;

Then you must disable the constraints and delete the content of table t. Now you can import the data into the table and enable the constraints.

alter table t disable constraint c;
truncate table t;

Now import the 'old' table t and ignore the constraint errors. If you do not want the errors then add the option constraints=n to the imp.

alter table t enable constraint c;

Be aware that between the disable and enable there is no check on the inserts. If there is a row that does not comply with the constraint then the enable will fail. The same goes if there were rows inserted between the old and the new situation.

Related Question