This discussion is archived
7 Replies Latest reply: May 16, 2012 10:46 AM by User286067 RSS

Regading Data Pump

Mehul Mehta Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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
  • 2. Re: Regading Data Pump
    KuljeetPalSingh Guru
    Currently Being Moderated
    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.
  • 3. Re: Regading Data Pump
    Mehul Mehta Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
  • 7. Re: Regading Data Pump
    User286067 Journeyer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points