EXCLUDE CONTENT OF A TABLE AND INCLUDE METADATA AT ONCE

EXCLUDE CONTENT OF A TABLE AND INCLUDE METADATA AT ONCE

I have one schema's expdp backup which has around 200GB data. Now I came across situation to exclude content of one of the table (out of 1000) and include metadata only, in single import. 

For practical purpose, chosen KB sized schema

SQL> conn TEST
Enter password:
Connected.
SQL>
SQL>
SQL> sho user
USER is "TEST"
SQL>
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
EMP                            TABLE

HP                             TABLE

1 row selected.

SQL> select count(1) from emp;

  COUNT(1)
----------
         4

1 row selected.

SQL> desc emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(10)

SQL> select * from emp;

        ID
----------
         1
         2
         3
         4

4 rows selected.

SQL> desc HP
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(10)

SQL> select * from HP;

        ID
----------
         1
         2
         3
         4


SQL>

[oracle@centos export]$ expdp test/test dumpfile=test_bkp.dmp directory=EX logfile=test_bkp.log schemas=TEST exclude=statistics

Export: Release 11.2.0.4.0 - Production on Wed May 23 16:54:26 2018

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "TEST"."SYS_EXPORT_SCHEMA_01":  test/******** dumpfile=test_bkp.dmp directory=EX logfile=test_bkp.log schemas=TEST exclude=statistics
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "TEST"."EMP"                                5.078 KB       4 rows
. . exported "TEST"."HP"                                  5.078 KB       4 rows
Master table "TEST"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
  /u01/test/export/test_bkp.dmp
Job "TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed May 23 16:55:05 2018 elapsed 0 00:00:36


[oracle@centos ~]$ impdp directory=EX dumpfile=test_bkp.dmp logfile=imp_test_bkp.log remap_schema=TEST:TEST1 QUERY='EMP:" WHERE ID = 0" '

Import: Release 11.2.0.4.0 - Production on Wed May 23 17:07:00 2018

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA directory=EX dumpfile=test_bkp.dmp logfile=imp_test_bkp.log remap_schema=TEST:TEST1 QUERY=EMP:" WHERE ID = 0" 
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST1"."EMP"                              5.078 KB       0 out of 4 rows

. . imported "TEST1"."HP"                                5.078 KB       4 rows

Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Wed May 23 17:07:09 2018 elapsed 0 00:00:04

[oracle@centos ~]$ 
[oracle@centos ~]$
SQL> conn test1
Enter password:
Connected.
SQL>
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
EMP                            TABLE
HP                             TABLE

SQL> select * from emp;

no rows selected

SQL> select * from HP;

        ID
----------
         1
         2
         3
         4

SQL> desc emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(10)

SQL> desc HP;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(10)

SQL>

Conclusion: The QUERY parameter lets you filter the data you want to import.
It can be table-specific, or be applied to all the tables in the source file.

In my case, no rows where returned for EMP tab because the "WHERE ID=0" condition is never satisfied, so only Metadata has been imported.

Cheers !!!