【IMPDP】實現不同使用者之間的資料遷移——REMAP_SCHEMA引數

secooler發表於2010-04-01
REM
眾所周知，IMP工具的FROMUSER和TOUSER引數可以實現將一個使用者的的資料遷移到另外一個使用者。同樣的功能在IMPPDP工具中如何得以體現呢？
答案就是：使用IMPPDP的REMAP_SCHEMA引數實現。

簡單演示一下，供參考。

任務：將sec使用者中的資料遷移到secooler使用者。

1.分別確認sec和secooler使用者下的表和資料情況
1）sec使用者下有一張T表，含有24360行資料
sys@ora10g> conn sec/sec
Connected.
sec@ora10g> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T                              TABLE

sec@ora10g> select count(*) from t;

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

2）確認secooler使用者不包含表T
secooler@ora10g> conn secooler/secooler
Connected.
secooler@ora10g> select * from tab;

no rows selected

2.建立目錄物件expdp_dir
sys@ora10g> create or replace directory expdp_dir as '/expdp';

Directory created.

3.將目錄物件expdp_dir的讀寫許可權授權給sec和secooler使用者
sys@ora10g> grant read,write on directory expdp_dir to sec;

Grant succeeded.

sys@ora10g> grant read,write on directory expdp_dir to secooler;

Grant succeeded.

4.生成sec的備份檔案
ora10g@secDB /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, 01 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
Job "SEC"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:29:20

生成的備份檔案資訊如下：
ora10g@secDB /expdp$ ls -l *sec.dmp
-rw-r----- 1 oracle oinstall 2.5M Apr  1 10:29 20100401102917_sec.dmp

5.使用IMPDP的REMAP_SCHEMA引數實現secooler使用者的資料匯入
ora10g@secDB /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, 01 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，遷入任務完成。

6.確認最後的遷移遷移成果
連線到secooler使用者確認T表及其中的資料是否已經完成匯入。
sec@ora10g> conn secooler/secooler
Connected.
secooler@ora10g> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T                              TABLE

secooler@ora10g> select count(*) from t;

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

OK，搞定。

7.進一步參考資料
最好的參考資料就是Oracle的官方文件，參考連結如下：

便於參考，copy一份在此：

REMAP_SCHEMA

Default: none

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 ××ded within the body of definitions of types, views, procedures, and packages.

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 for the source schema 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 your dump file set does not contain the metadata necessary to create a schema, or if you do not have privileges, then the target schema must be created before the import operation is performed. This is because the unprivileged 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]

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, SCOTT can remap his BLAKE's objects to SCOTT, but SCOTT cannot remap SCOTT's objects to BLAKE.

Example

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_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.

8.小結
在從EXP備份工具轉到EXPDP工具的過程中，有很多的變動一定要注意，防止因誤用導致效率低下。
無論是從功能上還是效率上講，EXPDP都是首選（需要在伺服器端使用）。

Good luck.

secooler
10.04.01

-- The End --

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-631042/，如需轉載，請註明出處，否則將追究法律責任。

相關文章