Syntax and Description
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
If the schema you are remapping to does not already exist, the import operation creates it, provided the dump file set contains the necessary
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.
Suppose that you execute the following Export and Import commands to remap the
hr schema into the
> expdp SYSTEM/password SCHEMAS=hr DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp
> impdp SYSTEM/password DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp
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).
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,
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.