Google+ Followers

Pages

Saturday, July 16, 2011

Oracle Datapump parameter REMAP_SCHEMA


Loads all objects from the source schema into a target schema.

Syntax

REMAP_SCHEMA=source_schema:target_schema

Suppose that you execute the following Export and Import commands to remap the hr schema into the scott schema:
> expdp SYSTEM/password SCHEMAS=hr DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp


> impdp SYSTEM/password DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp
REMAP_SCHEMA=hr:scott
In this example, if user scott already exists before the import, then the Import REMAP_SCHEMAcommand will add objects from the hr schema into the existing scott schema. You can connect to thescott schema after the import by using the existing password (without resetting it).
If user scott does not exist before you execute the import operation, Import automatically creates it with an unusable password. This is possible because the dump file, hr.dmp, was created by SYSTEM, which has the privileges necessary to create a dump file that contains the metadata needed to create a schema. However, you cannot connect to scott on completion of the import, unless you reset the password forscott on the target database after the import completes.

4 comments:

  1. NICE AND I USED IT IN MY OFFICE SO MANY TIMES
    THANKS
    ALI

    ReplyDelete
  2. Question for u sir?

    If a table already exist in schema and we import same table int that schema then error generate already exist ,how to overcome this error?

    ReplyDelete
  3. if you are using datapump then use parameter
    content=data_only

    CONTENT={ALL | DATA_ONLY | METADATA_ONLY}

    ALL loads any data and metadata contained in the source. This is the default.


    EXAMPLE
    impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp CONTENT=METADATA_ONLY

    DATA_ONLY loads only table row data into existing tables; no database objects are created.

    METADATA_ONLY loads only database object definitions; no table row data is loaded.

    ReplyDelete
  4. Thanks rafi your post is very good
    i used this practical in live database so many times
    your work is highly appreciated

    ReplyDelete