1 Reply Latest reply: Dec 12, 2012 4:38 AM by hitgon RSS

    Skip the import of a schema in the dumpfile

      Platform : RHEL 5.4

      Imagine you have 100 schemas backed up (expdp) in a dumpfile and you want to import just one schema from that dumpfile in a DB. You can specify just that one schema you want using SCHEMAS parameter in the impdp. But things are not straightforward when you want use REMAP_SCHEMA.

      Here is my scenario:

      I took the expdp dump of schemas A and B in one go. So, dumpfile has objects from both A and B.
      The dumpfile name is : schemas_AandB.dmp

      Now , I want to create schema C from A using REMAP_SCHEMA parameter

      -- Putting each parameter in a separate line for readability
      impdp PSTREF/PSTREF_123 
      Everything goes fine. Schema C is created from Schema A in the dumpfile.

      But impdp is trying to create schema B as well because schema B was present in the dumpfile. Since the schema B and its objects are already in the DB , I get the following errors.
      ORA-31684: Object type USER:"B" already exists
      ORA-31684: Object type PROCEDURE:"B"."SP_CLEAREXPIREDSESSIONDATA" already exists
      ORA-31684: Object type PROCEDURE:"B"."SP_DELETESESSIONDATA" already exists
      ORA-31684: Object type PROCEDURE:"B"."SP_DELETESTATECONTEXTINFO" already exists
      ORA-31684: Object type PROCEDURE:"B"."SP_GETNEWCONTEXT" already exists
      ORA-31684: Object type PROCEDURE:"B"."SP_GETNEWSESSION" already exists
      ORA-31684: Object type PROCEDURE:"B"."SP_GETNEXTPART" already exists
      ORA-31684: Object type PROCEDURE:"B"."SP_GETSESSIONDATA" already exists
      ORA-31684: Object type PROCEDURE:"B"."SP_GETSTATECONTEXTINFO" already exists
      Since I don't want Schema B to be imported, I tried using the SCHEMAS parameter by mentioning just A.

      -- Adding SCHEMAS parameter
      impdp PSTREF/PSTREF_123 
      schemas=A   ---------------------------> Trying to avoid schema B in the dumpfile from being imported by specifying SCHEMAS
      But I got the following error
      ORA-39065: unexpected master process exception in MAIN
      ORA-12801: error signaled in parallel query server PZ99, instance oracth214:HEWRAC1 (1)
      ORA-01460: unimplemented or unreasonable conversion requested
      Maybe REMAP_SCHEMA and SCHEMAS parameters won't work together. Is there any way to prevent the impdp from importing user B and its objects ?