11 Replies Latest reply: Oct 23, 2012 8:11 AM by Osama_Mustafa RSS

    exp datapump problem

    R. Royal
      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
          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
            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
              Check this
              http://sphoot.blogspot.com/2012/09/export-selective-1000-tables-using.html
              • 4. Re: exp datapump problem
                R. Royal
                OK,
                but if tables has different schema?
                • 5. Re: exp datapump problem
                  R. Royal
                  I have also same tables on different schema:

                  for example:

                  owner1.table1

                  owner2.table1
                  • 6. Re: exp datapump problem
                    Osama_Mustafa
                    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
                      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
                        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
                          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
                            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
                              I am not saying that understand me well