0

Oracle 11.2g XE

I'm trying to write a initial deployment script for my application. There are several tables with data that I want to include in datapump export with a QUERY.

I have a table which includes these queries and table names to be exported.

Table: T_DEFINITION
============================

TABLE_NAME | WHERE_CONDITION
-----------+----------------
TBL1       | WHERE CMP_ID=0
TBL2       | WHERE 1=0
TBL3       | WHERE SEQ_ID=10 OR CMP_ID=0
...

Relevant portions of my expdp PARFILE as follows:

INCLUDE=TABLE:"IN (SELECT TABLE_NAME FROM T_DEFINITION WHERE (WHERE_CONDITION!= '1=0' OR WHERE_CONDITION IS NULL))"
QUERY=DEV.TBL1:"WHERE CMP_ID=0"
QUERY=DEV.TBL2:"WHERE 1=0"
...

As you can see, I must have manually define QUERY parameter for each table that I need. Is there a way to define QUERY parameters that SELECT where conditions from my T_DEFINITION table?

Something like this:

QUERY="SELECT TABLE_NAME||':'||WHERE_CONDITION FROM T_DEFINITION"
  • I have had good luck exporting with data pump from a materialized view. Just refresh the MV before you start the data pump export. You can then use any logic that you want. – Gandolf989 Oct 27 '16 at 18:52
1

No, there is not.

You have already defined manually the filters for each table, why not use that? Just dynamically generate the parameterfile before running expdp:

select 'QUERY=' || table_owner || '.' || table_name || ':"' || where_condition || '"'
from t_definition;

QUERY=DEV.TBL1:"WHERE CMP_ID=0"
QUERY=DEV.TBL2:"WHERE 1=0"
QUERY=DEV.TBL3:"WHERE SEQ_ID=10 OR CMP_ID=0"
...

Or use DBMS_DATAPUMP.

  • I'm already generating the PARFILE on-demand with a procedure. I just want to learn about if there is a better way to accomplish that. Thank you for the answer. – Hazar Oct 28 '16 at 7:18

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.