Oracle DataPump “INCLUDE” parameter and limit of 4000 chars

I’ve seen a question about the INCLUDE parameter of DataPump at OTN Forums. The user needed to enter 900+ tables into include parameter but she got “UDE-00014: invalid value for parameter, ‘include'”:

Although there’s no limit on the number of objects you enter, “include” parameter accepts only 4000 characters. A simple workaround is to use a table to hold the names of the tables/objects and use this table with include parameter:

Please share
  • 1
  •  
  •  
  •  
  •  
  •  

AWS Big Data Specialist. Oracle Certified Professional (OCP) for EBS R12, Oracle 10g and 11g. Co-author of "Expert Oracle Enterprise Manager 12c" book published by Apress. Awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for the continuous contributions to the Oracle users community. Founding member, and vice president of Turkish Oracle User Group (TROUG). Presented at various international conferences including Oracle Open World.

11 Comments

  1. Lucky

    Can we do the same with INCLUDE for SCHEMA parameter?
    if yes, can you please show in an example par file.

    Thanks & regards.

  2. Sk

    What if I have to export partition from different tables and the their number is huge?

  3. Ravivarman

    Hi

    I want to do multiple tables export by giving values dynamically  in metadata filter. Either by using NAME_LIST or NAME_EXPN.

    Here I am getting table names from a IN parameter.

  4. rst.oracle

    Hi  Gokhan

    Once again , my special thanks for your posting. It’s great.

    $ expdp system/manager dumpfile=dp:f1.dmp full=y EXCLUDE =SCHEMA:”=’SCOTT'”

    LRM-00116: syntax error at ‘SCHEMA:’ following ‘=’

    What i came to know, Expdp exclude is not given correctly in Oracle documentation. Is this really true?

    Thanks in advance.

    • ch

      I think this way is wrong,I tried many times didn’t succeed

  5. Burak

    güzel yazınız için teşekkürler.  Fakat verdiğiniz format linux’de bir türlü çalışmadı.  aşağıdaki hatayı veriyor.

    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
    With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
    Data Mining and Real Application Testing options
    ORA-39001: invalid argument value
    ORA-39071: Value for INCLUDE is badly formed.
    ORA-00936: missing expression

    include parametresini aşağıdaki gibi düzenlediğimde linux üzerinde çalıştırabildim.

    include=”table:\” in (SELECT tbl_name FROM list_of_tables)\””

     

    Tekrardan teşekkürler…

     

  6. Denys

    oracle datapump “include” or “exclude” parameters does have some limit, but you can try to use “parfile” parameter and split your queries into smaller ones:

    expdp DADM/passwd DIRECTORY=DMP_DIR SCHEMAS=DADM DUMPFILE=dadm_tables_data.dmp CONTENT=DATA_ONLY parfile=import.par

    content of import.par:
    INCLUDE=TABLE:”IN (‘CBTRFCC’,’CBTFCCN’,’CGTGCDD’,’CGDSALC’)”
    INCLUDE=TABLE:”IN (‘CGTRSGR’,’CBTFCCE’,’CGTREPD’,’CPDORPG’)”

Leave a Reply to Denys Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.