This discussion is archived
7 Replies Latest reply: Aug 8, 2012 2:14 PM by Dean Gagne RSS

expdp to export multiple schemas

smee Newbie
Currently Being Moderated
Hi all,

I have a question related to Oracle Data Pump.

So, I want to export two schemas from database with condition:

1. I want to export scheme_1 with all metadata objects + data.
2. I want to export scheme_2 with only metadata objects.

Oracle version is Oracle EE 10.2.0.4.0, OS - Microsoft Server 2003R2.

As far as I know I can not use parameter EXCLUDE like: EXCLUDE =TABLES:" IN ('SCHEMA_NAME.TABLE')" (-but this parameter will give me no tables at all) or I can not use CONTENT=SCHEMA_NAME.METADATA_ONLY, maybe I can use QUERY=where table in (select tablename where schema is .... - but I have tables with same name in both schemas).

Maybe someone had same request and found solution.

Tnx in advice.
  • 1. Re: expdp to export multiple schemas
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    You will need to invoke expdp twice, once for each schema with the necessary parameters.

    HTH
    Srini
  • 2. Re: expdp to export multiple schemas
    Dean Gagne Expert
    Currently Being Moderated
    You can use the query parameter. It may be a large import command if there are lots of tables, but it should work.

    query=scheme_2.tab1:"where rownum = 0"
    query=scheme_2:tab2:"where rownum = 0"
    .
    .
    .


    If you list the same query parameter for each table in the schema where you don't want data, then this will work.

    This also works - just list all of the table data that you don't want. The only problem is that if you have the same table name in both schemas, then it will exclude both of these.

    exclude=TABLE_DATA:"IN ('EMP', 'BONUS')"

    Dean
  • 3. Re: expdp to export multiple schemas
    smee Newbie
    Currently Being Moderated
    Srini,

    this is for surely, one possibilities - the best one if I could merge two dmp files.

    Tnx for your quick response.
  • 4. Re: expdp to export multiple schemas
    smee Newbie
    Currently Being Moderated
    Dean,

    Thank for your quick response. Your solution is suitable for me, however I am curious:

    1. It would be wonderful if I could write one query for all tables. Something like: query = 'schema_2.<all_tables>: "where rownum=0"'. I apparently do not understand what kind of query can be written. Is there any good documents about expdp QUERY parameter?

    2. If I write query, what you have suggested, I would like to include only meta data for indexes to. I also can not write query like: query='schema_2.index_name_for_table_1:"where rownum=0"'. I do not know is there solution for this? It would be great if I do not need to rebuild indexes.

    Allow me to describe my goal.

    I would like to export database, more precisely two schemas. But I have problem with amount of data in tables and large size of the indexes from one schema, hence I would like to export only meta data for these schema objects.

    Tnx in advance
  • 5. Re: expdp to export multiple schemas
    Dean Gagne Expert
    Currently Being Moderated
    smee.,
    1. It would be wonderful if I could write one query for all tables. Something like: query = 'schema_2.<all_tables>: "where >rownum=0"'. I >apparently do not understand what kind of query can be written. Is there any good documents about expdp >QUERY parameter?
    For explanation, you would want to look in the utility guide. I can't think of a single query that would work. The query is described as:

    [schema].[table]:where clause here

    The [] mean that it is optional, but... If you don't specify any schema or table, then the query applies to all tables. If you specify only 1 name, then it is defined as the table name, not the schema name. I can't think of a way to specify just the schema and not the table. Sounds interesting though.
    2. If I write query, what you have suggested, I would like to include only meta data for indexes to. I also can not write query like: >query='schema_2.index_name_for_table_1:"where rownum=0"'. I do not know is there solution for this? It would be great if I do >not need to rebuild indexes.
    I'm not sure what you mean for metadata only for indexes. Data Pump doesn't move anything but metadata for indexes. When we import an index, the index gets created on the fly. (unless you are using transportable tablespace) In that case, you can't filter out the data.
    Allow me to describe my goal.

    I would like to export database, more precisely two schemas. But I have problem with amount of data in tables and large size of the >indexes from one schema, hence I would like to export only meta data for these schema objects.
    The data can be eliminated for the one schema as show above. The data for the index will not be moved. It will only be the metadata. The index will be rebuilt during the import command.

    Dean
  • 6. Re: expdp to export multiple schemas
    smee Newbie
    Currently Being Moderated
    Dean,

    I tried to execute query only with schema name:
    expdp ... query=schema:where clause 
    But it can not be done. So, my solution, for now, will be parameter file with n queries within it (where n is number of schema table).

    Tnx for your responses
  • 7. Re: expdp to export multiple schemas
    Dean Gagne Expert
    Currently Being Moderated
    Hi,

    If you only specify one name, Data PUmp defaults it to the table name. If you specify 2, then the order is schema.table. I can't think of a way that you can only specify the schema.

    Dean

Legend

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