Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 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:

REM from a DOS .bat script located on the server

SET oracle_sid=YourDatabaseName

REM export the schema

C:\app\YourOraclePath\bin\exp SchemaOwner/SchemaPass@SchemaName file=(C:\Dump\SchemaName01,C:\Dump\SchemaName02,C:\dump\SchemaName03) filesize=1GB log=C:\dump\Logs\SchemaName.log

REM drop and recreate the new user if they have existing objects otherwise comment out this line

sqlplus /nolog @C:\Dump\DropRecreateUsers.sql

imp NewSchemaOwner/NewSchemaOwnerPassword LOG =C:\dump\logs\importNewSchemaOwner.log file =SchemaOwner01.dmp fromuser=SchemaOwner touser=NewSchemaOwner

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?

share|improve this answer
    
I believe part of the point of what rfusca was doing is not to have to go through dump files. There is no "exp" part in his technique. I doubt that it would fail with a normal expdp/impdp combination. –  Mat Oct 2 '12 at 14:25
    
Quite right, the data pump should work, but doesn't for the OP. They noted "I'm also open to other ways to clone a schema." and this answer will do the job. –  kevinsky Oct 2 '12 at 14:48
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.