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

Question

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?

score 2 · Accepted Answer · answered Nov 15 '10 at 04:30

2

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)

answered Nov 15 '10 at 04:30

Jon Heller

Will look into that and accept if it leads to a solution. Thanks! – Lunivore Nov 18 '10 at 09:52
Okay - it didn't lead to a solution, but it did lead to the recognition that the version we've got really doesn't work particularly well with DDL changes. Your suggestion would work fr the next version up. Getting a patch. Thank you! – Lunivore Nov 19 '10 at 17:33

1 Answers1