This discussion is archived
11 Replies Latest reply: Oct 23, 2012 6:11 AM by Osama_Mustafa RSS

exp datapump problem

R. Royal Newbie
Currently Being Moderated
Hi,
I need to export more 1000 tables with clause INCLUDE of expdp but I Know that the maximum length allowed is 4000 bytes including quotation marks, which means that the actual maximum length allowed is 3998 bytes.

How can I bypass this problem into clause INCLUDE of expdp?
  • 1. Re: exp datapump problem
    sb92075 Guru
    Currently Being Moderated
    Raf Royal wrote:
    Hi,
    I need to export more 1000 tables with clause INCLUDE of expdp but I Know that the maximum length allowed is 4000 bytes including quotation marks, which means that the actual maximum length allowed is 3998 bytes.

    How can I bypass this problem into clause INCLUDE of expdp?
    specify them in a control file
  • 2. Re: exp datapump problem
    R. Royal Newbie
    Currently Being Moderated
    What means "specify them in a control file"??

    my tables are in different schema

    owner1.table1
    owner2.table2
    owner1.table3
    ....................
  • 3. Re: exp datapump problem
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Check this
    http://sphoot.blogspot.com/2012/09/export-selective-1000-tables-using.html
  • 4. Re: exp datapump problem
    R. Royal Newbie
    Currently Being Moderated
    OK,
    but if tables has different schema?
  • 5. Re: exp datapump problem
    R. Royal Newbie
    Currently Being Moderated
    I have also same tables on different schema:

    for example:

    owner1.table1

    owner2.table1
  • 6. Re: exp datapump problem
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Raf Royal wrote:
    I have also same tables on different schema:

    for example:

    owner1.table1

    owner2.table1
    if you have same table on different schema then i suggest before doing export start filtering your database did you ever hear about synonym and view or grant you more than option to avoid duplicate tables on your schema in this case you will export less tables .
  • 7. Re: exp datapump problem
    myOra_help Journeyer
    Currently Being Moderated
    Its better to export schema wise, it helps you to distinguised tables already exported with different schemas. Also it is easy to maintain and rectify any errors during export/import.
  • 8. Re: exp datapump problem
    User286067 Journeyer
    Currently Being Moderated
    Osama_mustafa wrote:
    if you have same table on different schema then i suggest before doing export start filtering your database did you ever hear about synonym and view or grant you more than option to avoid duplicate tables on your schema in this case you will export less tables .
    I have NO idea what you say here, if in the tables list you include schema1.table_1 and schema2.table_1 are specified AND the exporting user has appropriate privileges, Oracle and expdp are intelligent enough to distinguish between 2 tables in different schema. No idea why synonyms, grants, views should matter, as long as object name is prefixed by schema, you should be ok.

    Dear OP,

    You want to export 1000 tables, but you dont tell us those 1000 are out of 1100 or 10000. If it is out of 1100 then it is easier to EXCLUDE the ones you don't want, but the best option might be to list owner and table_Name into a table and provide using IN clause rather than providing a long list of tables.

    Also you CANNOT provide both INCLUDE and EXCLUDE in one commandline, so I'd recommend you should use a table which contains owner and table names that you like to export. It will make your life easier. As long as exporting user (i.e the one that run expdp) has appropriate privileges, you should have no issues.

    Raj
  • 9. Re: exp datapump problem
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    you said in your post that you have same table on schema !!! WHY and is this table contain same data , same structure
  • 10. Re: exp datapump problem
    User286067 Journeyer
    Currently Being Moderated
    Osama_mustafa wrote:
    you said in your post that you have same table on schema !!! WHY and is this table contain same data , same structure
    it is entirely possible ... I dont believe that there is some kind of rule that says that you should have EXACTLY one type of per db :)

    It is their db, their app,they can do whatever that want. How to solve 1000 table export problem has nothing to do with how many tables they have with same names or same columns or even same data.

    Raj
  • 11. Re: exp datapump problem
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    I am not saying that understand me well

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points