9 Replies Latest reply: Jul 15, 2014 8:59 AM by 942572 RSS

    Datapump to export certain tables

    942572

      Hi All,

      I would like to export certain tables via datapump. For example, all tables start with EMP, but not EMP_ADMIN. The following script does NOT work for me:

       

      expdp / SCHEMAS=MYSCHEMA INCLUDE=TABLE:"LIKE 'EMP%'" EXCLUDE=TABLE:"LIKE 'EMP_ADMIN%'" DUMPFILE=dpump_dir1:exp_inc.dmp NOLOGFILE=y

       

      Any suggestion on how to use the name clause in the INCLUDE parameter?

       

      Thanks!

        • 1. Re: Datapump to export certain tables
          Kundan Sinha

          when INCLUDE parameter is used, only those objects specified by it will be included in the export/import, where as the EXCLUDE parameter is used, all objects except those specified by it will be included.

           

           

           

          The two parameters are mutually exclusive

           

           

           

           

           

          INCLUDE=object_type[:name_clause] [, ...]

          EXCLUDE=object_type[:name_clause] [, ...]

           

           

           

          Use Par File, may be helpful as TABLES may be more in numbers and may not be well accomodated in the screen limit:

           

          parfile content:-

           

           

           

          SCHEMAS=<schema.name>     /** INCLUDE param used, is for the specific schema name you specified

          include=TABLE:"IN ('TABLE_NAME01', 'TABLE_NAME02')"

          directory=<DIRECTORY_NAME>

          dumpfile=dump_name.dmp

          NOLOGFILE=y

           

           

          --------------------------------------------------------------------------------------------------------------------------------------------------------------------

          though you specified "NOLOGFILE=y" progress and error information is still written to the standard output device of the attached clients,

          including the client that started the original export operation. If there are no clients attached to a running job, you run the risk of losing important progress

          and error information.

          ---------------------------------------------------------------------------------------------------------------------------------------------------------------------

           

          Regards,

          Kundan Sinha

          • 2. Re: Datapump to export certain tables
            Kundan Sinha

            correction:-

             

            "when INCLUDE parameter is used, only those objects specified by it will be included in the export/import, where as when the EXCLUDE parameter is used, it excludes specific object types specified. "


            Regards,

            Kundan Sinha

            • 3. Re: Datapump to export certain tables
              Srini Chavali-Oracle

              One option is to create a custom table with all of the table names you want exported, then in the INCLUDE clause use a SQL statement that selects from this table.

               

              See Export selected tables at runtime for an example

               

              HTH
              Srini

              • 4. Re: Datapump to export certain tables
                Richard Harrison .

                Hi,

                let SQL do the work for you - as long as you can write a select statement that returns the tables you want it's easy - see example here

                 

                Oracle DBA Blog 2.0: expdp dynamic list of tables

                 

                Cheers,

                Rich

                • 5. Re: Datapump to export certain tables
                  942572

                  I am using a custom table to do the export, however I need two more tables in addition to the content of the custom table. The following command in the parfile does NOT work, it has 'invalid argument value' error for INCLUDE command.

                   

                  include=TABLE:"IN (SELECT tbNm FROM TB_custom UNION 'TB_ADD1' UNION 'TB_ADD2')"

                   

                  By the way, the command to export table base on custom table works fine for me. Eg, include=TABLE:"IN (SELECT tbNm FROM TB_custom")

                   

                  Any sugestion? Thanks!

                  • 6. Re: Datapump to export certain tables
                    Srini Chavali-Oracle

                    What does "does NOT work" mean ? Any error messages ?

                     

                    Is there a reason you cannot add the two additional tables names to the custom table ?

                    • 7. Re: Datapump to export certain tables
                      942572

                      I have invalid argument value error for include command:

                       

                      "ORA-39001: invlid argument value

                      ORA-39071: Value for INCLUDE is badly formed

                      ORA-00928: missing SELECT keyword"

                       

                      Since the custom table is used somewhere else, I prefer to to hard-code these two additional table without changing the content of the custom table.

                       

                      Thanks!

                      • 8. Re: Datapump to export certain tables
                        Richard Harrison .

                        Hi,

                        I updated by blog post link above - i replied to your comment there.

                         

                        The problem is you can't say

                         

                        select x from y

                        union z

                         

                        you have to say

                         

                        select x from y

                        union

                        select z from dual

                         

                        The additional table names have to be selected from somewhere.

                         

                        Cheers,

                        Rich

                        • 9. Re: Datapump to export certain tables
                          942572

                          It works, thanks Rich!