expdp&impdp使用remap

大家好，欢迎来到IT知识分享网。

测试不使用remp的默认行为：

1. 环境：

SELECT directory_name, directory_path FROM dba_directories WHERE directory_name=’DATA_PUMP_DIR’;   —/u01/app/oracle/admin/orcl/dpdump/

Create tablespace user2 datafile ‘/home/oracle/oradata/orcl/users_02.dbf’ size 10m;

drop user scott cascade;

create user scott identified by tiger;

grant resource, connect to scott;

grant read, write on directory DATA_PUMP_DIR to scott;

alter user system identified by oracle;

drop user hr cascade;

create user hr identified by hr;

grant resource, connect to hr;

grant read, write on directory DATA_PUMP_DIR to hr;

conn scott/tiger

create table thistest(id number) tablespace user2;

insert into thistest values (1);

Commit;

1. 测试

1. 导出schemas时使用dba权限则导出create user，否则不会导出

cd /u01/app/oracle/admin/orcl/dpdump/

expdp scott/tiger schemas=scott directory=DATA_PUMP_DIR dumpfile=scott.dmp nologfile=yes

drop user scott cascade;

# 啥也没导入

impdp hr/hr directory=DATA_PUMP_DIR dumpfile=scott.dmp nologfile=yes  

# 会报错scott用户不存在需要提前创建

impdp system/oracle directory=DATA_PUMP_DIR dumpfile=scott.dmp nologfile=yes  

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

cd /u01/app/oracle/admin/orcl/dpdump/

expdp system/oracle schemas=scott directory=DATA_PUMP_DIR dumpfile=scott.dmp nologfile=yes

drop user scott cascade;

# 会创建scott用户并导入

impdp system/oracle directory=DATA_PUMP_DIR dumpfile=scott.dmp nologfile=yes  

1. 导出tables时使用dba权限也不会导出create user语句

cd /u01/app/oracle/admin/orcl/dpdump/

expdp system/oracle tables=scott.thistest directory=DATA_PUMP_DIR dumpfile=tabletest.dmp nologfile=yes

drop user scott cascade;

# 会报错scott用户不存在需要提前创建

impdp system/oracle directory=DATA_PUMP_DIR dumpfile=tabletest.dmp nologfile=yes  

1. 使用full导入导出不测试了，没权限的用户导出是没有create user信息的
2. 以schema导出并不导出create tablespace语句

cd /u01/app/oracle/admin/orcl/dpdump/

expdp system/oracle schemas=scott directory=DATA_PUMP_DIR dumpfile=scott.dmp nologfile=yes

drop tablespace user2 including contents and datafiles;

# 会报错没有user2表空间

impdp system/oracle directory=DATA_PUMP_DIR dumpfile=scott.dmp nologfile=yes  

1. 以full导出会出create tablespace

cd /u01/app/oracle/admin/orcl/dpdump/

expdp system/oracle full=y directory=DATA_PUMP_DIR dumpfile=full.dmp nologfile=yes

drop tablespace user2 including contents and datafiles;

# 报错表空间user2不存在

impdp system/oracle schemas=scott directory=DATA_PUMP_DIR dumpfile=full.dmp logfile=full.log

# ok已创建user2表空间并导入thistest表

impdp system/oracle directory=DATA_PUMP_DIR dumpfile=full.dmp logfile=full.log

1. 以tablespace选项导出，既不会导出creat user也不会导出create tablespace

cd /u01/app/oracle/admin/orcl/dpdump/

expdp system/oracle tablespaces=user2 directory=DATA_PUMP_DIR dumpfile=tbsuser2.dmp nologfile=yes

drop tablespace user2 including contents and datafiles;

# 报错没有tablespace user2

impdp system/oracle directory=DATA_PUMP_DIR dumpfile=tbsuser2.dmp nologfile=yes  

以下使用remap:

（一）EXPDP

REMAP_DATA=[schema.]tablename.column_name:[schema.]pkg.function

用于把源表某列的值通过remap function转为新值并存于dumpfile，常用于把生产数据迁移到测试库，把一些敏感信息替代。这里的pkg即为使用的PL/SQL包名，function为包下的函数名

Example

$ expdp hr DIRECTORY=dpump_dir1 DUMPFILE=remap1.dmp TABLES=employees

REMAP_DATA=hr.employees.employee_id:hr.remap.minus10

REMAP_DATA=hr.employees.first_name:hr.remap.plusx

Restrictions

1. Remap function的返回值要与表的相应列的定义相符

The data types and sizes of the source argument and the returned value must both match the data type and size of the designated column in the table.

1. Remapping functions should not perform commits or rollbacks except in autonomous transactions.
2. 表名不能使用别名The use of synonyms as values for the REMAP_DATA parameter is not supported. For example, if the regions table in the hr schema had a synonym of regn, an error would be returned if you specified regn as part of the REMPA_DATA specification.
3. Remapping LOB column data of a remote table is not supported.
4. Columns of the following types are not supported by REMAP_DATA: User Defined Types, attributes of User Defined Types, LONGs, REFs, VARRAYs, Nested Tables, BFILEs, and XMLtype.

• IMPDP

如果是DBA导出文件必须使用DBA用户导入

如果是其它用户导出文件，可以用非DBA用户remap到自己的schema

REMAP_DATA=[schema.]tablename.column_name:[schema.]pkg.function

使用同expdp，A common use is to regenerate primary keys to avoid conflict when importing a table into a preexisting table on the target database.

$ impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expschema.dmp

TABLES=hr.employees REMAP_DATA=hr.employees.first_name:hr.remap.plusx

REMAP_DATAFILE=source_datafile:target_datafile

把源库引用数据库文件SQL转为其它数据文件，包括create tablespace, create library与create directory.

Changes the name of the source data file to the target data file name in all SQL statements where the source data file is referenced: CREATE TABLESPACE, CREATE LIBRARY, and CREATE DIRECTORY.

Remapping data files用于迁移的两个库所有系统的文件命名方式不同时.另外建议数据文件名使用引号引起，来避免名中带有的冒号被操作系统转义，如下面例子是把VMS文件转为UNIX文件的命名方式，

$ cat payroll.par

DIRECTORY=dpump_dir1

FULL=YES

DUMPFILE=db_full.dmp

REMAP_DATAFILE=”‘DB1$:[HRDATA.PAYROLL]tbs6.dbf’:’/db1/hrdata/payroll/tbs6.dbf'”

$ impdp hr PARFILE=payroll.par

REMAP_DIRECTORY=source_directory_string:target_directory_string

把源库引用数据库目录的SQL转为目标库目录，包括create tablespace, create library与create directory.

用于同时把多个数据文件转到不同目录，它与remap_datafile选项冲突

Remapping a directory is useful when you move databases between platforms that have different directory file naming conventions. This provides an easy way to remap multiple data files in a directory when you only want to change the directory file specification while preserving the original data file names.

设置原库有两个文件：

DB1$:[HRDATA.PAYROLL]tbs5.dbf

DB1$:[HRDATA.PAYROLL]tbs6.dbf

$ cat payroll.par

DIRECTORY=dpump_dir1

FULL=YES

DUMPFILE=db_full.dmp

REMAP_DIRECTORY=”‘DB1$:[HRDATA.PAYROLL]’:’/db1/hrdata/payroll/'”

$ impdp hr PARFILE=payroll.par

REMAP_SCHEMA=source_schema:target_schema

$ expdp system SCHEMAS=hr DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp

$ impdp system DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp REMAP_SCHEMA=hr:scott

使用schema导入时如果dumpfile中的schema在目标库没有则自动创建，但如果导入用户没权限则不会创建schema只会导入原dumpfile中自己schema的对象，即默认不会进行remap

可以指定多remap_schema，但source_schema不能重复，target_schema可以重复

如果target_schema不存在需要满中下面两个条件才会自动创建，否则需要手动提前创建remap schema：

1. 导出dumpfile中包含了source schema的创建语句，即create user，使用system用户以schema或full方式导出的文件即包含此信息
2. 导入时使用system用户因为它有create schema权限

在11g之前版本使用remap_schema导入已创建的schema，在导入结束后还要reset此用户密码：alter user schema_name identified by new_password; 而在11g以后不需要了，密码即原来用户密码; 如果是导入时自动创建的schema，则需要reset新用户的密码。

Restrictions

1. 导入没有权限的话只能remap到当前自己的schema
2. Remap_schema并非100%转化,因为一些schema引用对象找不到。如无法找到模式引用的嵌入式定义的type, view, procedures与packages

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.

1. For triggers, REMAP_SCHEMA affects only the trigger owner.
2. If any table in the schema being remapped contains user-defined object types and that table changes between the time it is exported and the time you attempt to import it, then the import of that table will fail. However, the import operation itself will continue.
3. By default, if schema objects on the source database have object identifiers (OIDs), then they are imported to the target database with those same OIDs. If an object is imported back into the same database from which it was exported, but into a different schema, then the OID of the new (imported) object would be the same as that of the existing object and the import would fail. For the import to succeed you must also specify the TRANSFORM=OID:N parameter on the import. The transform OID:N causes a new OID to be created for the new object, allowing the import to succeed.

REMAP_TABLE=[schema.]old_tablename[.partition]:new_tablename

REMAP_TABLE=[schema.]old_tablename[:partition]:new_tablename

注如果使用上面的语法如REMAP_TABLE=A.B:C，则会让为A为schema，B为table而不是把A当成表名，B当作分区名，因此这时一定要指定schema，即schema.table_name.partition

$ impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expschema.dmp TABLES=hr.employees REMAP_TABLE=hr.employees:emps

REMAP_TABLESPACE=source_tablespace:target_tablespace

$ impdp hr REMAP_TABLESPACE=tbs_1:tbs_6 DIRECTORY=dpump_dir1 DUMPFILE=employees.dmp

Restrictions

1. Data Pump Import can only remap tablespaces for transportable imports in databases where the compatibility level is set to 10.1 or later.
2. Only objects created by the Import will be remapped. In particular, the tablespaces for preexisting tables will not be remapped if TABLE_EXISTS_ACTION is set to SKIP, TRUNCATE, or APPEND.