10 Replies Latest reply: Sep 6, 2012 3:00 AM by user5716448 RSS

    expdp - tables

    user5716448
      Hi,

      Have a parfile to export tables and currently have them listed as

      TABLES=TABLE1,TABLE2 etc but will have over 30 or so tables.

      Can we use a query to export the tables name
      e.g TABLES="SELECT TABLE_NAME
                          FROM USER_TABLES
                          WHERE TABLE_NAME LIKE '%BAK%'"
      Have tried above but get

      ORA-31655: no data or metadata objects selected for job


      guess wrong sytnax if possible.
      tHANKS

      Edited by: user5716448 on 05-Sep-2012 05:53
        • 1. Re: expdp - tables
          415289
          i don't think it will work,better generate list of table by this query and put table names in par file
          expdp parfile=expdp.par

          http://docs.oracle.com/cd/B28359_01/server.111/b28319/dp_export.htm
          • 2. Re: expdp - tables
            user5716448
            Thanks
            • 3. Re: expdp - tables
              957206
              TRY USING THIS:


              TABLES="SELECT WM_CONCAT(TABLE_NAME)
              FROM USER_TABLES
              WHERE TABLE_NAME LIKE '%BAK%'"
              • 4. Re: expdp - tables
                user5716448
                Hi,

                Thanks for update.

                query runs fine in sql and gives table names but in expdp parfile get message

                ORA-31655: no data or metadata objects selected for job

                Thanks
                • 5. Re: expdp - tables
                  415289
                  could be syntax issue,post your cmd with parfile.

                  31655, 00000, "no data or metadata objects selected for job"
                  // *Cause:  After the job parameters and filters were applied,
                  // the job specified by the user did not reference any objects.
                  // *Action: Verify that the mode of the job specified objects to be moved.
                  // For command line clients, verify that the INCLUDE, EXCLUDE and
                  // CONTENT parameters were correctly set. For DBMS_DATAPUMP API
                  // users, verify that the metadata filters, data filters, and
                  // parameters that were supplied on the job were correctly set.
                  oracle@$
                  • 6. Re: expdp - tables
                    user5716448
                    Hi,
                    tables="select wm_concat(table_name) from user_tables where table_name like '%CPAY%'"
                    userid=archive/v3rs1on
                    DIRECTORY=data_exports_dir
                    DUMPFILE=por_archive.dmp
                    LOGFILE=por_exp.log
                    PARALLEL=16
                    CONTENT=DATA_ONLY
                    command used expdp parfile=parfile.

                    expdp works fine when use tables as list e.g tables=table1,table2

                    just when try make table list name dynamic
                    • 7. Re: expdp - tables
                      415289
                      can you try include parameter
                      include=TABLE:'IN (select wm_concat(table_name) from user_tables where table_name like '%CPAY%')"

                      also have a look
                      http://www.gokhanatil.com/2011/06/oracle-datapump-include-parameter-and-limit-of-4000-chars.html
                      • 8. Re: expdp - tables
                        user5716448
                        Hi,

                        Got message below.

                        ORA-39168: Object path TABLE was not found.
                        ORA-31655: no data or metadata objects selected for job


                        Thanks
                        • 9. Re: expdp - tables
                          Dean Gagne
                          the tables= parameter takes a list, not a query, however, you could run a schema mode export and use the include parameter, which in this case, is the same as running the table mode export:

                          expdp user/password schemas=schema_foo include=tables:'IN ((SELECT TABLE_NAME FROM MY_TABLE_CONTAINING_NAMES))'

                          It is best to put the include parameter into a parameter file so you don't have to worry about extra quoting and escaping.

                          Dean
                          • 10. Re: expdp - tables
                            user5716448
                            Thanks