Sunday, September 21, 2008

REMAP_SCHEMA

Purpose

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


Syntax and Description
REMAP_SCHEMA=source_schema:target_schema

Multiple REMAP_SCHEMA lines can be specified, but the source schema must be different for each one. However, different source schemas can map to the same target schema. The mapping may not be 100 percent complete, because there are certain schema references that Import is not capable of finding. For example, Import will not find schema references embedded within the body of definitions of types, views, procedures, and packages.

This parameter requires the IMP_FULL_DATABASE role.

If the schema you are remapping to does not already exist, the import operation creates it, provided the dump file set contains the necessary CREATE USER metadata and you are importing with enough privileges. For example, the following Export commands would create the dump file sets with the necessary metadata to create a schema, because the user SYSTEM has the necessary privileges:

> expdp SYSTEM/password SCHEMAS=hr
> expdp SYSTEM/password FULL=y

If you do not have enough privileges to perform an import that creates dump files containing the metadata necessary to create a schema, then you must create the target schema before performing the import operation. This is because the dump files do not contain the necessary information for the import to create the schema automatically.

If the import operation does create the schema, then after the import is complete, you must assign it a valid password in order to connect to it. The SQL statement to do this, which requires privileges, is:

SQL> ALTER USER [schema_name] IDENTIFIED BY [new_pswd]

When you remap a schema, it is possible to constrain the rows loaded into the destination table by specifying a query. However, Data Pump Import performs remapping before applying any queries. This means that by the time the query is applied, the schema has already been remapped. Therefore, to constrain the rows that are loaded, the query must be applied to the target schema table rather than to the source schema table.


Example

Suppose that you execute the following Export and Import commands to remap the hr schema into the blake 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_SCHEMA command will add objects from the hr schema into the existing scott schema. You can connect to the scott 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 for scott on the target database after the import completes.

1 comment:

Shony said...

Good post. I found lot of useful articles on datapump in this link.
http://www.acehints.com/p/site-map.html