4

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.

3

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.

  • In your second alter table statement, do I have to enable the constraints on new table or old table? And is the 'c' a parameter or name of constraint? – user3141985 Aug 7 '15 at 12:35
  • You speak about the second option. There you do not have constraints on t1. So this means that you only need to disable and enable on the 'original' table. The c must be replaced by the name of the constraint. So t is the original table, t1 has the same structure as t but has the wrong data of t and c is the name of the constraint. – Marco Aug 7 '15 at 12:43
  • I tried to import the data on the existing table, what was already empty, but if failed to load. How can I load only data into table using imp command without creating table structure and disabling constraints? – user3141985 Aug 7 '15 at 12:48
  • 1
    You must add the parameter ignore=y to the imp command. Now the imp does not stop when the table already exists. Check on the parameters by typing imp help=y. You get a short overview on all the options. – Marco Aug 7 '15 at 12:50
  • We'll try and share the results. – user3141985 Aug 7 '15 at 13:02
6

You could use Flashback to return the table data back to its original state.

Is Flashback enabled?

Database needs to be in archive mode:

select log_mode from v$database;

Should return "ARCHIVEMODE"

Check if flashback is enabled:

select flashback_on from v$database;

Should return "YES"

If "NO", you can enable flashback by runing:

alter database flashback on;

Check undo retention:

show parameter undo

Default is 900 seconds (15-minutes). So you can Flashback a table to 15-minutes ago using this command:

Alter table t enable row movement ;

FLASHBACK TABLE t TO TIMESTAMP TO_TIMESTAMP ('2015-aug-11 12:00:00', 'YYYY-MON-DD HH24:MI:SS');

Other Flashback commands:

  • Flashback database = > enables you to take the entire database to a past point in time (using flashback logs, db_flashback_retention_target).
  • Flashback drop => lets you retrieve accidentally dropped tables and indexes (using the recycle bin).
  • Flashback table => lets you recover a table to a time in the past (using undo data).
  • Flashback query => lets you query and restore data rows to a point in time (using undo data).

For Flashback table using SCN see this link: https://oracle-base.com/articles/10g/flashback-10g#flashback_table

  • Many thanks @StringerBell for detailed answer, although problem has been resolved but it is a great share. – user3141985 Aug 17 '15 at 12:12
1

Try to flashback your table

ALTER TABLE your_table  ENABLE ROW MOVEMENT;

FLASHBACK TABLE your_table TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' minute);

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.