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 
      directory=HOME_DIR logfile=IMPDP_COPY_BACKUP.log 

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 
    mtu.base_process_order = NVL((SELECT mts1.process_order 
    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 
    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.


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
REM 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?

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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