7 Replies Latest reply on May 16, 2012 5:46 PM by Raj Jamadagni

    Regading Data Pump

    Mehul Mehta
      Hello,

      I would like to improt/export data from one schema, but while exporting/importing i would like to have only tables with out data for few tables with data.

      Ex:

      If i have 20 tables in one schema and i would like to have 15 tables with their data and 5 with out data.

      How can we do the same using expdp/impdp functionality.


      Regards

      Mehul
        • 1. Re: Regading Data Pump
          anand prakash - oracle
          Hi,

          What i can think of is creating parfile with the list of tables mentioned in tables parameter and what would differ in these 2 parfiles is the content parameter. For tables without data using content=metadata_only.
          CONTENT
          Specifies data to unload.
          Valid keyword values are: [ALL], DATA_ONLY and METADATA_ONLY.
          Anand
          1 person found this helpful
          • 2. Re: Regading Data Pump
            kuljeet singh -
            create 2 dmp file one with data and one without data by below option in expdp.

            CONTENT
            Specifies data to unload.
            Valid keyword values are: [ALL], DATA_ONLY and METADATA_ONLY.
            1 person found this helpful
            • 3. Re: Regading Data Pump
              Mehul Mehta
              But how should i use it? I think if i use METADATA_ONLY then only tables with out data will be imported.


              Mehul
              • 4. Re: Regading Data Pump
                Mehul Mehta
                AS i have mentioned earlier,

                If i have 20 tables in one schema and i would like to have *15 tables with their data* and +5 with out data+.


                Mehul
                • 5. Re: Regading Data Pump
                  anand prakash - oracle
                  but while exporting/importing i would like to have only tables with out data for few tables with data.
                  As you mentioned you need few tables without data and rest with data, so have a parfile mentioning those few tables in the tables parameter along with content=metadata_only. This will have the table structure without any data. When you import, only tables will be created. There won;t be any data as you export dump didn't had any.

                  Anand
                  • 6. Re: Regading Data Pump
                    NunzioC
                    Mehul Mehta wrote:
                    But how should i use it? I think if i use METADATA_ONLY then only tables with out data will be imported.


                    Mehul
                    If you use METADATA_ONLY you will export ALL THE TABLES, but only the definition and not the data.

                    In your case, as other person suggest you, I think you have 2 run 2 export, the first one for the 5 tables and METADATA_ONLY option, and the second export with the normal option.

                    Hope this helps

                    regards
                    Nunzio
                    1 person found this helpful
                    • 7. Re: Regading Data Pump
                      Raj Jamadagni
                      Mehul Mehta wrote:
                      AS i have mentioned earlier,
                      If i have 20 tables in one schema and i would like to have *15 tables with their data* and +5 with out data+.
                      Mehul, create a parfile and for the 5 tables you do not want to export data, use the query parameter with something like 'where 1=2' ... so it will skip data. Make sure you put all query clauses (one for each table) on consecutive lines.

                      so your parfile would look like this ...
                      directory=my_dir
                      dumpfile=...
                      logfile=..
                      schemas=schema_owner
                      # now only list tables for which you do not need data
                      query=schema_owner.table1:"where 1=2"
                      query=schema_owner.table2:"where 1=2"
                      query=schema_owner.table3:"where 1=2"
                      query=schema_owner.table4:"where 1=2"
                      query=schema_owner.table5:"where 1=2"
                      ...
                      This will export all tables in the schema_owner, and data for all tables except for tables listed above.
                      HTHS
                      Raj

                      Edited by: rjamya on May 16, 2012 10:41 AM