Search code examples
sqloracle-databaseflashback

How can I migrate data, adding a new column with constraints, with flashback archives enabled?

I've currently got a string value in one table, which I would like to change so that it references another table. For instance, currently I have:

| Table: Animal       | 
-----------------------
| Name      | Class   |
|-----------|---------|
| Horse     | Mammal  |
| Crocodile | Reptile |

And I want these to reference my new "AnimalClass" table, so that they look like:

| Table: Animal       | 
-----------------------
| Name      | ClassId |
|-----------|---------|
| Horse     |    3    |
| Crocodile |    14   |

I've created my new table and set the ClassId without any trouble.

Unfortunately when I try to add a foreign key constraint between my new Id and the AnimalClass table, I get this error:

SQL Error: ORA-55610: Invalid DDL statement on history-tracked table

This makes sense, because it invalidates the "Undo".

Is there any way to migrate the historic data in line with my new data, so that I can add my constraint?

Solution

  • What version of Oracle are you using (client and server)? According to this thread there are issues with using older clients: http://forums.oracle.com/forums/thread.jspa?threadID=945177 If nothing else works, can you use DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA/REASSOCIATE_FBA? (As recommended by http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17125/adfns_flashback.htm#BJFFDCEH)