oracle_11g data migration between different users

As we all know, the Fromuser and Touser parameters of the Imp tool can be used to migrate one user's data to another user. How can the same function be embodied in the IMPPDP tool?
The answer is: Use the Remap_schema parameter of the IMPPDP implementation.

A brief demonstration, for reference.

Task: Migrate data from SEC users to secooler users.

1. Confirm the table and data conditions of the SEC and Secooler users separately
1) The SEC user has a T-table containing 24360 rows of data
[Email protected]> Conn Sec/sec
Connected.
[Email protected]> select * from tab;

Tname Tabtype Clusterid
------------------------------ ------- ----------
T TABLE

[Email protected]> Select COUNT (*) from T;

COUNT (*)
----------
24360

2) Confirm that the Secooler user does not include the table T
[Email protected]> Conn Secooler/secooler
Connected.
[Email protected]> select * from tab;

No rows selected

2. Create a directory object Expdp_dir
[Email protected]> Create or replace directory Expdp_dir as '/EXPDP ';

Directory created.

3. Authorization of Read and write permissions for directory object Expdp_dir to SEC and Secooler users
[Email protected]> grant Read,write on the directory expdp_dir to SEC;

Grant succeeded.

[Email protected]> grant Read,write on the directory Expdp_dir to Secooler;

Grant succeeded.

4. Generate the SEC backup file
[Email protected]/expdp$ EXPDP sec/sec directory=expdp_dir dumpfile= ' date + '%y%m%d%h%m%s ' ' _sec.dmp logfile= ' date + '%Y% m%d%h%m%s "' _sec.log

Export:release 10.2.0.3.0-64bit Production on Thursday, April, 2010 10:29:17

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to:oracle Database 10g Enterprise Edition Release 10.2.0.3.0-64bit Production
With the partitioning, Oracle Label Security, OLAP and Data Mining scoring Engine options
Starting "SEC". " Sys_export_schema_01 ": sec/******** directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_ Sec.log
Estimate in progress using BLOCKS method ...
Processing Object Type Schema_export/table/table_data
Total estimation using BLOCKS method:3 MB
Processing Object Type Schema_export/user
Processing Object Type Schema_export/system_grant
Processing Object Type Schema_export/role_grant
Processing Object Type Schema_export/default_role
Processing Object Type Schema_export/pre_schema/procact_schema
Processing Object Type Schema_export/table/table
Processing Object Type Schema_export/table/index/index
Processing Object Type Schema_export/table/constraint/constraint
Processing Object Type Schema_export/table/index/statistics/index_statistics
Processing Object Type Schema_export/table/comment
. . Exported "SEC". " T "2.259 MB 24360 rows
Master table "SEC". " Sys_export_schema_01 "Successfully loaded/unloaded
******************************************************************************
Dump file set for SEC. SYS_EXPORT_SCHEMA_01 is:
/expdp/20100401102917_sec.dmp
The Job "SEC". Sys_export_schema_01 "successfully completed at 10:29:20

The resulting backup file information is as follows:
[Email protected]/expdp$ ls-l *sec.dmp
-RW-R-----1 Oracle Oinstall 2.5M APR 1 10:29 20100401102917_sec.dmp

5. Use IMPDP's Remap_schema parameter to implement data import for Secooler users
[Email protected]/expdp$ IMPDP secooler/secooler directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile= 20100401102917_sec_impdp.log Remap_schema=sec:secooler

Import:release 10.2.0.3.0-64bit Production on Thursday, April, 2010 10:32:10

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to:oracle Database 10g Enterprise Edition Release 10.2.0.3.0-64bit Production
With the partitioning, Oracle Label Security, OLAP and Data Mining scoring Engine options
Master table "Secooler". " Sys_import_full_01 "Successfully loaded/unloaded
Starting "Secooler". " Sys_import_full_01 ": secooler/******** directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_ Sec_impdp.log Remap_schema=sec:secooler
Processing Object Type Schema_export/user
Ora-31684:object type USER: "Secooler" already exists
Processing Object Type Schema_export/system_grant
Processing Object Type Schema_export/role_grant
Processing Object Type Schema_export/default_role
Processing Object Type Schema_export/pre_schema/procact_schema
Processing Object Type Schema_export/table/table
Processing Object Type Schema_export/table/table_data
. . Imported "Secooler". " T "2.259 MB 24360 rows
Job "Secooler". " Sys_import_full_01 "completed with 1 error (s) at 10:32:12

OK, the move-in task is complete.

6. Confirm the final migration migration results
Connect to the Secooler user to confirm that the T table and the data in it have been imported.
[Email protected]> Conn Secooler/secooler
Connected.
[Email protected]> select * from tab;

Tname Tabtype Clusterid
------------------------------ ------- ----------
T TABLE

[Email protected]> Select COUNT (*) from T;

COUNT (*)
----------
24360

OK, get it done.

7. Further references
The best reference is Oracle's official documentation, with the following links:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_import.htm#sthref340
Easy reference, copy a copy here:
Remap_schema

Default:none

Purpose

Loadsall objects from the source schema into a target schema.

Syntax and Description

Remap_schema=source_schema:target_schema

Multipleremap_schemalines can is specified, but the source schema must is different for each one. However, different source schemas can map to the same target schema. The mapping percent complete, because there was certain schema references that Import was not capable of fin Ding. For example, Import would not find schema referencesxxded within the body of definitions of types, views, procedures, and Packages.

If the schema is remapping to does not already exist, the import operation creates it, provided the dump file set con Tains the Necessarycreateusermetadata for the source schema and your is 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 Usersystemhas the necessary privileges:

> EXPDP system/passwordschemas=hr
> EXPDP system/passwordfull=y

If your dump file set does not contain the metadata necessary to create a schema, or if your do not has privileges, then T He target schema must was created before the import operation is performed. This is because the unprivileged dump files does not contain the necessary information for the import to create the schema a Utomatically.

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

sql> ALTER USER [schema_name] identified by [NEW_PSWD]

Restrictions

unprivileged users can perform. Schema remaps only if their schema is the target schema of the remap. (Privileged users can perform. Unrestricted schema remaps.)

For Example,scottcan remap Hisblake ' s objects toscott, Butscottcannot Remapscott ' s objects toblake.

Example

Suppose that you execute the following Export and Import commands to remap Thehrschema into Thescottschema:

> EXPDP system/passwordschemas=hr directory=dpump_dir1 dumpfile=hr.dmp

> IMPDP system/passworddirectory=dpump_dir1 dumpfile=hr.dmp
Remap_schema=hr:scott

In this example, if Userscottalready exists before the import and then the Importremap_schemacommand would add objects from th Ehrschema into the Existingscottschema. You can connect to Thescottschema after the import by using the existing password (without resetting it).

If userscottdoes not exist before to execute the import operation, import automatically creates it with an unusable PASSW Ord. This is possible because the dump FILE,HR.DMP, was created Bysystem,which have the privileges necessary to create a dump fi Le that contains the metadata needed to create a schema. However, cannot connect Toscotton completion of the import, unless you reset the password Forscotton the target Databa Se after the import completes.
8. Summary
In the process of transferring from the EXP Backup tool to the EXPDP tool, there are a number of changes that must be noted to prevent inefficiencies resulting from misuse.
EXPDP is preferred both functionally and efficiently (needs to be used on the server side).

Good luck.

oracle_11g data migration between different users