2 Replies Latest reply on Jul 12, 2014 1:57 PM by user633661

    Tablespace level export, schema level import - is it possible?

    user633661

      Hi,

       

      if I have a tablespace level DataPump export (performed with TABLESPACES=<list of tablespaces>), is it possible to import only the tables and dependent objects of a specific schema residing in the exported tablespaces? DB version is 11.2.0.3.0. According to the documentation it should be possible: http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#i1011943 : A schema import is specified using the SCHEMAS parameter. The source can be a full, table, tablespace, or schema-mode export dump file set or another database.  

       

      However, performing a quick test seems it isn't so:

       

      1) Source DB - I have two tablespaces (TS1, TS2) and two schemas (USER1, USER2):

       

      SQL> select owner, segment_name, segment_type, tablespace_name

      from dba_segments

      where owner in ('USER1', 'USER2');

        2    3

      OWNER  SEGMENT_NAME    SEGMENT_TYPE       TABLESPACE_NAME

      ------ --------------- ------------------ ----------------

      USER1  UQ_1            INDEX              TS1

      USER1  T2              TABLE              TS1

      USER1  T1              TABLE              TS1

      USER2  T4              TABLE              TS2

      USER2  T3              TABLE              TS2

       

      2) I make a tablespace level export:

       

      $ expdp system directory=dp_dir tablespaces=ts1,ts2 dumpfile=test.dmp

       

      Export: Release 11.2.0.3.0 - Production on Fri Jul 11 14:02:54 2014

       

      Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

      Password:

       

      Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

      With the Partitioning, OLAP, Data Mining and Real Application Testing options

      Starting "SYSTEM"."SYS_EXPORT_TABLESPACE_01":  system/******** directory=dp_dir tablespaces=ts1,ts2 dumpfile=test.dmp

      Estimate in progress using BLOCKS method...

      Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

      Total estimation using BLOCKS method: 256 KB

      Processing object type TABLE_EXPORT/TABLE/TABLE

      Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

      Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

      Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

      . . exported "USER1"."T1"                                5.007 KB       1 rows

      . . exported "USER1"."T2"                                5.007 KB       1 rows

      . . exported "USER2"."T3"                                5.007 KB       1 rows

      . . exported "USER2"."T4"                                5.007 KB       1 rows

      Master table "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded

      ******************************************************************************

       

      3) I try to import only the objects belonging to USER1 and I get the "ORA-39039: Schema expression " IN ('USER1')" contains no valid schemas" error:

       

      $ impdp system directory=dp_dir schemas=USER1 dumpfile=test.dmp

       

      Import: Release 11.2.0.3.0 - Production on Fri Jul 11 14:05:15 2014

       

      Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

      Password:

       

      Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

      With the Partitioning, OLAP, Data Mining and Real Application Testing options

      ORA-31655: no data or metadata objects selected for job

      ORA-39039: Schema expression " IN ('USER1')" contains no valid schemas.

       

      4) However, the dump file clearly contains the owner of the tables:

       

      impdp system directory=dp_dir dumpfile=test.dmp sqlfile=imp_dump.txt

       

      excerpt from imp_dump.txt:

       

      -- new object type path: TABLE_EXPORT/TABLE/TABLE

      CREATE TABLE "USER1"."T1"

         (    "DUMMY" VARCHAR2(1 BYTE)

         ) 

       

      So is it possible to somehow filter the objects belonging to a certain schema?

       

      Thanks in advance for any suggestion.

       

      Jure