We have a number of Oracle databases on a couple of nodes.
We are trying to export 4 schemas from one database to another.
When we import, we get the following errors because of object types in the schemas:
IMP-00003: ORACLE error 2304 encountered
ORA-02304: invalid object identifier literal
IMP-00017: following statement failed with ORACLE error 2304:
"CREATE TYPE "PROCESSDEFINITIONRESULT" TIMESTAMP '2005-12-02:17:33:22' OID '"
" OBJECT ( "OBJECTIVE" VARCHAR2(500), "RESULT" VARCHAR2(500),"
" "OWNER" VARCHAR2(750), "MANAGER" VARCHAR2(750), "LOCATION""
" NUMBER, "[TRIGGER]" VARCHAR2(500))"
I understand that this error occours because the OID is unique to the object, and the import is unable to re-create the objects with the same OID.
PLEASE NOTE: All objects are used ONLY in stored procs. There are no tables (apart from temp tables) that use these objects.
After doing lots of reading online, I understand the following method would allow us to do the import:
1) Move all objects to a new schema.
2) Create synonyms to the new objects and update all exisitng references.
3) Create the 'object schema' in the new database.
4) Import the original schema as normal.
This however is not a good solution for us, as this is a production database and we really don't want to change the schema.
One possible solution we have come up with is as follows. I need to know if this will work:
1) Re-create the schema without any data in the new database from exisitng scrpits (this is trivial to do)
2) Export the data only
3) Import the data only
If we do this, will the PK/FK relationships be maintained, and is there anything that can go wrong or I need to look out for?
We have a final option, but again we are not sure of the implications:
1) Export full schemas
2) Import with 'ignore warnings' - in my understanding, this will just skip the object creation
3) Re-create objects from script (including temp tables that use the objects)
4) Re-compile stored procs that use these objects
Please advise on these 3 methods, and which (if any) are suitable for us.
Many thanks in advance!