I am attempting to achieve the following within a single data pump export:

  • SCHEMA1 = Structures only

  • SCHEMA2 = Structures only

  • SCHEMA3 = Structures and data

The answer in this question only works on the table level:

It suggests removing data from a specific table using a filter:

SCHEMA2.table1: WHERE 1=2

I need the same but applied to all data in the schemas other than SCHEMA3.

How can this be done?

  • You should use a parameter file, then use simple SQL to generate QUERY clauses. something like select 'query=' || owner || '.' || table_name || ':"where 1=2"' from dba_tables where owner in ('SCHEMA1','SCHEMA2');. then specify schemas=schema1,schema2,schema3 , that should mostly do it for you. – Raj Mar 23 '16 at 3:28

Filtering options in Data Pump are still something that should be improved. Anyway, you could try the below parameters:

exclude=TABLE_DATA:" IN (select table_name from dba_tables where owner in ('SCHEMA1', 'SCHEMA2') union select partition_name from dba_tab_partitions where table_owner in ('SCHEMA1', 'SCHEMA2'))"

This excludes TABLE_DATA for all tables and partitions whose name match the result of the query provided in the exclude part.

And that is why this may not be appropriate for you. If you have a table called T1 both in SCHEMA1 and SCHEMA3, the data of SCHEMA3.T1 will be excluded, because the above query returns T1 from SCHEMA1, and Data Pump will ignore TABLE_DATA of all table called T1. If you have a partitioned table in SCHEMA2, with a partition called P1, the data of the table SCHEMA3.P1 will be excluded. The above may work if your table and partition names are distinct accross all referenced schemas.

Or you could dynamically generate the expdp command or use DBMS_DATAPUMP to filter out all the table data from SCHEMA3.

But I would rather just run 2 seperate exports for SCHEMA1,SCHEMA2 with CONTENT=METADATA_ONLY and a default export (CONTENT=ALL) for SCHEMA3.

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.