advertisements
_____________________________________________________________________________________________________________________
In conventional import (IMP) utility we have fromuser and touser parameter to import the data dump which is taken from one user to another user. In Data Pump impdp oracle introduced new parameter called REMAP_SCHEMA.
Syntax:
impdp REMAP_SCHEMA=source:target where source and target are the schema names.
More than one schema mapping can be done in impdp where the source schema must be different for each one. Target schema can be the same or different.
It is not mandatory to have the target schema in the database. If the target schema does not exist the impdp will create the target schema, provided the impdp invoking user has sufficient privileges to create the user in the database.
There is restriction for unprivileged user. Unprivileged user cannot import or remap the objects to another target schema where they don’t have access.
Example:
Taking the export of one schema.
$ expdp schemas=scott directory=exp_dir dumpfile=remap.dmp logfile=remap.log compression=all
Export: Release 11.2.0.2.0 - Production on Fri May 11 03:05:31 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": /******** AS SYSDBA schemas=scott directory=exp_dir dumpfile=remap.dmp logfile=remap.log compressi on=all
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 860.2 MB
. . .
. . .
. . .
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/shony/remap.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 03:06:45
2. Importing the dump to remap_test schema which is not existing in the database.
$ impdp directory=exp_dir dumpfile=remap.dmp logfile=imp_remap.log remap_schema=scott:remap_test
Import: Release 11.2.0.2.0 - Production on Fri May 11 03:10:16 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": /******** AS SYSDBA directory=exp_dir dumpfile=remap.dmp logfile=imp_remap.log remap_schema=scott:remap_test
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
. . .
. . .
. . .
Job "SYS"."SYS_IMPORT_FULL_01" completed successfully at 03:11:11
Now you have to reset the password for remap_test schema.
SQL> alter user remap_test identified by remap_test;
User altered.
SQL> connect remap_test/remap_test
Connected.
SQL> select count(*) , owner from all_objects where owner in ('SCOTT','REMAP_TEST') GROUP BY OWNER;
COUNT(*) OWNER
---------- ------------------------------
31 REMAP_TEST
31 SCOTT
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment