I'm trying to copy a SCHEMA in its entirity using oracle's IMPDP tool. I've setup a database link back to itself and the link works fine. Then I'm running this:
impdp username/password@instancename schemas=TARGET_SCHEMA
network_link=LINK_BACK_TO_TARGET
directory=HOME_DIR logfile=IMPDP_COPY_BACKUP.log
remap_schema=TARGET_SCHEMA:SOURCE_SCHEMA
TRANSFORM=STORAGE:n:table;
Everything works fine in my two tests systems, but in Oracle 11gR2 production, it gets to the moving of tables and produces this:
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39126: Worker unexpected fatal error in
KUPW$WORKER.UPDATE_TD_BASE_PO_INFO [UPDATE
"ADMIN"."SYS_IMPORT_SCHEMA_04" mtu SET
mtu.base_process_order = NVL((SELECT mts1.process_order
FROM "ADMIN"."SYS_IMPORT_SCHEMA_04" mts1 WHERE
mts1.process_order > 0 AND mts1.duplicate = 0
AND mts1.object_schema = mtu.base_object_schema
AND mts1.object_name = mtu.base_object_name AND
mts1.object_type = mtu.base_object_type AND
mts1.processing_state != :1 ),
(-1000 - (SELECT MIN(mts2.process_order) FROM
"ADMIN"."SYS_IMPORT_SCHEMA_04" mts2 WHERE
mts2.process_order > 0 AND mts2.duplicate = 0
AND mts2.object_schema = mtu.base_object_schema
AND mts2.object_name = mtu.base_object_name
AND mts2.object_type = mtu.object_type)))
WHERE mtu.process_order > 0 AND mtu.duplicate = 0
AND mtu.object_type = 'TABLE_DATA'
AND mtu.processing_status = :2]
ORA-01427: single-row subquery returns more than one row
I've tried it a few times, but I'm clueless whats going on.
I'm also open to other ways to clone a schema.
Best Answer
If you just want to get the job done and not worry about why it works on test but not on production then the old export/import methods will work. You don't mention an operating system so I assume Windows because that's what I work on. Something like:
This does not address your error using the data pump which is likely to be connected to the difference between test and production. Nor does it address the wisdom of importing data sets into production from production. If this operation fails what are the consequences?