14 Replies Latest reply: Feb 8, 2011 10:20 AM by user13122474 RSS

    Performing an export on multiple tables

    792559
      I'm trying to export multiple tables into a single file where restriction (query) are applied on only one of them:

      Command I'm trying:
      exp USERID=admin/admin tables=table1, table2 QUERY=\”WHERE param2_1=’set’\”

      Desired outcome:
      Export file includes all of table1 and rows in table2 where the param2_1 value is 'set'

      Restriction/Problem:
      As the QUERY statement in export is apparently applied to all tables, the result of my statement is an error that table1 does not contain the specified parameter.

      Can anyone suggest a proper method to do this?
        • 1. Re: Performing an export on multiple tables
          Rajesh Lathwal
          please use :

          QUERY = [schema.][table_name:] query_clause

          If a [schema.]table_name is not supplied, the query is applied to (and must be valid for) all tables in the export job.

          Regards
          Rajesh
          • 2. Re: Performing an export on multiple tables
            792559
            When I try running the following cmd:
            exp USERID=user_id/user_ps tables=table1, table2 query = [schema.][table2:] \"where table2col2 = 'ON'\"

            I get a response back of:
            LRM-00112: multiple values not allowed for parameter query.

            I've already tried
            exp USERID=user_id/user_ps tables=table1, table2 query = schema.table2: \"where table2col2 = 'ON'\"
            where I get EXP-00056/ORA-00930 errors
            exp USERID=user_id/user_ps tables=table1, table2 query = \"where schema.table2:table2col2 = 'ON'\"
            where I get EXP-00056/ORA-00920 errors

            Am I missing something?
            • 3. Re: Performing an export on multiple tables
              Dean Gagne-Oracle
              I'm not sure if the query command on classic exp/imp works with multiple tables. I'm not sure if you can add the schema.table to be query parameter. This works for datapump, but I'm not sure if it works for exp/imp. I'll need to look at the docs.

              Dean
              • 4. Re: Performing an export on multiple tables
                792559
                What is the syntax for data pump then:
                expdp tables=table1, table2 query = schema.table2: \"where table2col2 = 'ON'\"
                Im still confused is this is even possible.
                • 5. Re: Performing an export on multiple tables
                  Srini Chavali-Oracle
                  Pl post details of OS and database versions.

                  HTH
                  Srini
                  • 6. Re: Performing an export on multiple tables
                    792559
                    OS: RHEL 5
                    DB: Oracle 10g
                    • 7. Re: Performing an export on multiple tables
                      Dean Gagne-Oracle
                      Yes this is possible.

                      You would list your tables to export in the table parameter

                      tables=table_a,table_b,table_c,...

                      and you would list your queries using multiple query parmeters

                      query=schema1.table_a:your query here" query=schema1.table_b:"your other query here" ...

                      This works for both export and import.

                      Dean
                      • 8. Re: Performing an export on multiple tables
                        792559
                        Alright, there are two options for syntax that I am trying to setup, and neither one is working:

                        The starting case, that works, is:
                        tables = table_a
                        query = \"where parameter_a = 'ON'\"

                        Except that I want to include multiple tables, so I try the following:

                        1.
                        tables = table_a, table_b
                        query = schema.table_a:\"where parameter_a = 'ON'\"

                        2.
                        tables = table_a, table_b
                        query = \"where schema.table_a:parameter_a = 'ON'\"

                        Am I typing these commands wrong? Remember that my goal is to include in my dmp file all of table_b and only rows from table_a where parameter_a is "ON'.

                        Thanks.
                        • 9. Re: Performing an export on multiple tables
                          Dean Gagne-Oracle
                          Hi,

                          Can you list the errors that you are getting. Trying to help without the error is difficult.

                          Thanks

                          Dean
                          • 10. Re: Performing an export on multiple tables
                            792559
                            *0.*
                            ORACLE CALL:
                            - tables = table_a
                            query = "where parameter_a = \'ON\'\"
                            OUTCOME:
                            - Filtered data from table_a exported in output files.
                            - Warning: EXP-00091: Exporting questionable statistics.

                            *1.*
                            ORACLE CALL:
                            - tables = table_a, table_b
                            query = "where parameter_a = \'ON\'\"
                            OUTCOME:
                            - Filtered data from table_a exported in output files. No data from table_b is exported.
                            - Warning: EXP-00091: Exporting questionable statistics.
                            - Error: EXP-00056: ORACLE error 904 encountered
                            ORA-00904: "parameter_a": invalid identifier

                            *2.*
                            ORACLE CALL:
                            - tables = table_a, table_b
                            query = schema.table_a:\"where parameter_a = '\ON\'\"
                            OUTCOME:
                            - No data exported.
                            - Errors: EXP-00056: ORACLE error 933 encountered
                            ORA-00933: SQL command not properly ended

                            *3.*
                            ORACLE CALL:
                            - tables = table_a, table_b
                            query = \"where schema.table_a:parameter_a = 'ON'\"
                            OUTCOME:
                            - No data exported.
                            - Errors: EXP-00056: ORACLE error 920 encountered
                            ORA-00920: invalid relational operator
                            • 11. Re: Performing an export on multiple tables
                              Dean Gagne-Oracle
                              0.
                              ORACLE CALL:
                              - tables = table_a
                              query = "where parameter_a = \'ON\'\"
                              OUTCOME:
                              - Filtered data from table_a exported in output files.
                              - Warning: EXP-00091: Exporting questionable statistics.
                              This is what I would have expected. The questionable statistics are due to exporting only some of the data. The statistics are exported for the complete table and you only exported statistics where parameter_a 'ON'. You can add statistics = none if you want, but you won't get any statistics on the target system. There are other options for statistics that you can specify.

                              1.
                              ORACLE CALL:
                              - tables = table_a, table_b
                              query = "where parameter_a = \'ON\'\"
                              OUTCOME:
                              - Filtered data from table_a exported in output files. No data from table_b is exported.
                              - Warning: EXP-00091: Exporting questionable statistics.
                              - Error: EXP-00056: ORACLE error 904 encountered
                              ORA-00904: "parameter_a": invalid identifier

                              This looks right. You gave a generic query (one for all tables) and since table_b does not have column parameter_a, it failed. Same reason for questionable statistics as above.

                              2.
                              ORACLE CALL:
                              - tables = table_a, table_b
                              query = schema.table_a:\"where parameter_a = '\ON\'\"
                              OUTCOME:
                              - No data exported.
                              - Errors: EXP-00056: ORACLE error 933 encountered
                              ORA-00933: SQL command not properly ended

                              I think I said this earlier in this thread that I wasn't sure if exp supported a table name with a query. I just looked at my docs and according to what I just read, there is no support for specifying a table name with a query. The query is applied to all tables in the job. Data Pump allows this syntax. You can specify which table a query should be used on. If no table is specified, it is for all tables.

                              3.
                              ORACLE CALL:
                              - tables = table_a, table_b
                              query = \"where schema.table_a:parameter_a = 'ON'\"
                              OUTCOME:
                              - No data exported.
                              - Errors: EXP-00056: ORACLE error 920 encountered
                              ORA-00920: invalid relational operator

                              This is invalid syntax.

                              For datapump, you would use step #2 from above. What you are trying to do is not supported in exp/imp. It is only supported in expdp/impdp.

                              Dean
                              • 12. Re: Performing an export on multiple tables
                                792559
                                -----
                                2.
                                ORACLE CALL:
                                - tables = table_a, table_b
                                query = schema.table_a:\"where parameter_a = '\ON\'\"
                                OUTCOME:
                                - No data exported.
                                - Errors: EXP-00056: ORACLE error 933 encountered
                                ORA-00933: SQL command not properly ended

                                I think I said this earlier in this thread that I wasn't sure if exp supported a table name with a query. I just looked at my docs and according to what I just read, there is no support for specifying a table name with a query. The query is applied to all tables in the job. Data Pump allows this syntax. You can specify which table a query should be used on. If no table is specified, it is for all tables.*
                                -----

                                Great that helped with the syntax!
                                expdb user_id/password_id tables = table_a, table_b query schema.table_a:\"where parameter_a = \'ON\'\"
                                It works!

                                Though now I'm running into some issue translating my previous exp command to expdp. I thought I found online a table that showed comparison between the two, but I cant locate it now. What can you make of these>
                                a. FILE to DUMPFILE: FILE allowed for definition of path in filename, but expdp gives me errors when I try to. Files seem to always be saved in /oracle/products/10.2/rdbms/log/. Can this be changed? Same with LOG to LOGFILE.
                                b. How do GRANTS, INDEXES, and ROWS translate from exp to expdb?
                                • 13. Re: Performing an export on multiple tables
                                  Dean Gagne-Oracle
                                  Though now I'm running into some issue translating my previous exp command to expdp. I thought I found online a table that showed >comparison between the two, but I cant locate it now. What can you make of these>
                                  I think there is something in the documentation about how to translate. In 11.2, you can actually run your old exp command and just change the exp to expdp and use your old parameters and Data Pump will map your old parameters to new parameters. It does the best it can. It will show you the new command that it generated. You can either execute it or edit it to your liking.
                                  a. FILE to DUMPFILE: FILE allowed for definition of path in filename, but expdp gives me errors when I try to. Files seem to always be >saved in /oracle/products/10.2/rdbms/log/. Can this be changed? Same with LOG to LOGFILE.
                                  I'm not sure what you are asking here. The logfiles will be created in the same directory that you specified in the directory=xxx parameter. If you want the logfile to go someplace else, then I think you can specify logfile=your_dir:your_logfile

                                  The your_dir above is an Oracle directory object, not an os directory.
                                  b. How do GRANTS, INDEXES, and ROWS translate from exp to expdb?
                                  What are you doing with these? I'll assume:

                                  1. GRANTS = Y/N

                                  Data Pump has include and exclude parameters. Things are included by default. If you specify include, then anything that you don't specify will be excluded. If you said export table foo include=grants, then you dumpfile will only have grants in it. You will not get the table or any data for you table. (You didn't include it in your job).

                                  So, if you want to exclude grants, then just add:

                                  exclude=grants

                                  2. INDEXES= Y/N

                                  Same as above. If you include indexes and nothing else, then you will only have indexes in the dumpfile. If you exclude indexes, then add:

                                  exclude=indexes

                                  3. ROW=Y/N

                                  If you say rows=n, then all you are going to get is the metadata for the objects. (create table statements, create grants, ect.) If this is what you want, then say:

                                  content=metadata_only

                                  If you say rows=y, then I think this just says get the data. I don't remember if it gets the metaata also. In datapump, by default we get the data and the metadata. If all you want is the data, then add:

                                  content=data

                                  Hope this helps.

                                  Dean
                                  • 14. Re: Performing an export on multiple tables
                                    user13122474
                                    Example multiple tables

                                    the field "LOG_DATE" must exist in all tables,
                                    works in exp or expdp:

                                    tables=schema.table1,schema.table12,schema.table3
                                    query="WHERE LOG_DATE >= SYSDATE - 30"

                                    Regards
                                    Marcos