ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 通过REMAP_SCHEMA参数来实现不同用户之间的数据迁移

通过REMAP_SCHEMA参数来实现不同用户之间的数据迁移

原创 Linux操作系统 作者:tianya_2011 时间:2013-06-25 11:46:51 0 删除 编辑
IMPDP工具在进行数据迁移时,如果迁移的数据在不同用户之间进行,该如何实现呢?

通过实验来说明一下:把scott用户下的 5张数据表迁移到测试用户test下。

1,确认scott和test用户下的表和数据大小。

SQL> conn scott/tiger
Connected.
SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
T1

查看较大表t1的数据量:

SQL> select count(*) from t1;

  COUNT(*)
----------
     10000


SQL> conn test/test
Connected.
SQL> select table_name from user_tables;

no rows selected

2,创建expdp目录DUMP_FILE_DIR

create or replace directory DUMP_FILE_DIR as '/u01/expdp';

select * from dba_directories where DIRECTORY_NAME='DUMP_FILE_DIR';

OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ ------------------------------
SYS                                DUMP_FILE_DIR                   /u01/expdp


3,把目录DUMP_FILE_DIR的读写权限授权给scott,test。

SQL> grant read,write on directory DUMP_FILE_DIR to scott;

Grant succeeded.

SQL> grant read,write on directory DUMP_FILE_DIR to test;

Grant succeeded.


4,导出scott下表。

[oracle@ora10g u01]$ expdp scott/tiger directory=DUMP_FILE_DIR DUMPFILE=expdp_scott.dmp logfile=expdp_scott.log

Export: Release 10.2.0.1.0 - Production on Tuesday, 25 June, 2013 10:55:42

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** directory=DUMP_FILE_DIR DUMPFILE=expdp_scott.dmp logfile=expdp_scott.log 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 384 KB
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
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."T1"                                82.83 KB   10000 rows
. . exported "SCOTT"."DEPT"                              5.656 KB       4 rows
. . exported "SCOTT"."EMP"                               7.820 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.585 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  /u01/expdp/expdp_scott.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:56:21

5,在impdp时,通过REMAP_SCHEMA实现scott与test之间的数据迁移。

[oracle@ora10g u01]$ impdp test/test directory=DUMP_FILE_DIR DUMPFILE=expdp_scott.dmp REMAP_SCHEMA=scott:test logfile=impdp_test.log

Import: Release 10.2.0.1.0 - Production on Tuesday, 25 June, 2013 10:59:10

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_FULL_01":  test/******** directory=DUMP_FILE_DIR DUMPFILE=expdp_scott.dmp REMAP_SCHEMA=scott:test logfile=impdp_test.log 
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 "TEST"."T1"                                 82.83 KB   10000 rows
. . imported "TEST"."DEPT"                               5.656 KB       4 rows
. . imported "TEST"."EMP"                                7.820 KB      14 rows
. . imported "TEST"."SALGRADE"                           5.585 KB       5 rows
. . imported "TEST"."BONUS"                                  0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "TEST"."SYS_IMPORT_FULL_01" successfully completed at 10:59:15

6,验证迁移是否成功。

SQL> conn test/test
Connected.
SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
T1

SQL> select count(*) from t1;

  COUNT(*)
----------
     10000


=================================================================

参考oracle官方文档:

http://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_import.htm#sthref340

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 embedded 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 USERmetadata 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 existingscott 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.


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25373498/viewspace-764757/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 双网卡bonding配置
请登录后发表评论 登录
全部评论

注册时间:2011-04-11

  • 博文量
    85
  • 访问量
    605955