数据泵的TRANSFORM参数说明及使用

郑全

1   TRANSFORM参数说明

1.1 TRANSFORM适用场景

导入和导出的时候，有些表空间不一样。比如导出的时候该对象是在A表空间，导入到另一个库没有表空间A就会报错。但是使用这个参数就会正常导入，该对象会存储在该用户下的默认表空间中。

1.2    语法
TRANSFORM = transform_name:value[:object_type]

object_type是可选的，如果不指定object_type，那么则指定导入的所有有效的对象。

TRANSFORM对下面对象有效：CLUSTER, CONSTRAINT, INDEX, ROLLBACK_SEGMENT, TABLE, and TABLESPACE.

1.3    transform_name：SEGMENT_ATTRIBUTES

在官方文档中有很详细的介绍，这里我只介绍一个SEGMENT_ATTRIBUTES的参数值。

1.4    SEGMENT_ATTRIBUTES：value值

值：Y|N

Y：默认值，表示这个段的属性(物理属性，存储属性，表空间和logging)都将被包含在DDL的语句中。

N:表示这些属性都不会在DDL语句中。意思是导入该对象的时候，不会指定表空间等属性，只是简单的创建一个对象。

2   具体例子
   2.1    创建一个新的表空间和表

表空间：

SQL> create tablespace test datafile 'D:/app/zhixin/oradata/orcl/test.dbf' size 50M; Tablespace created.

表：

在SCOTT用户下创建EMPLOYEES表

SQL> CREATE TABLE "SCOTT"."EMPLOYEES"  
2     ( "EMPLOYEE_ID" NUMBER(6,0),  
3       "FIRST_NAME" VARCHAR2(20),  
4       "LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE,  
5       "EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE,  
6       "PHONE_NUMBER" VARCHAR2(20),  
7       "HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE,  
8       "JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE,  
9       "SALARY" NUMBER(8,2),
10       "COMMISSION_PCT" NUMBER(2,2),
11       "MANAGER_ID" NUMBER(6,0),
12       "DEPARTMENT_ID" NUMBER(4,0)
13     ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
14    STORAGE(INITIAL 10240 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 121
15    PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
16    TABLESPACE "TEST" ; Table created.

  2.2    查看该表的DDL语句

SQL > SELECT dbms_metadata.get_ddl('TABLE','EMPLOYEES','SCOTT') FROM dual;  
CREATE TABLE "SCOTT"."EMPLOYEES"   
(        
"EMPLOYEE_ID" NUMBER(6,0),
"FIRST_NAME" VARCHAR2(20),
"LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE,
"EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE,
"PHONE_NUMBER" VARCHAR2(20),
"HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE,
"JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE,
"SALARY" NUMBER(8,2),
"COMMISSION_PCT" NUMBER(2,2),
"MANAGER_ID" NUMBER(6,0),
"DEPARTMENT_ID" NUMBER(4,0)   
) SEGMENT CREATION DEFERRED  
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  
STORAGE( INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TEST"

  2.3    导出SCOTT用户

D:/windows 2.7>expdp system/oracle owner=scott dumpfile=scott.dmp logfile=exp_scott.log directory=zhixin
Export: Release 11.2.0.4.0 - Production on Thu Apr 27 10:57:08 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  
All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "owner=scott" Location: Command Line, Replaced with: "schemas=scott"
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** schemas=scott dumpfile=scott.dmp logfile=exp_scott.log directory=zhixin reuse_dumpfiles=true
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1024 KB
Processing object type SCHEMA_EXPORT/USERP
rocessing 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/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. .
exported "SCOTT"."ZX"                                704.2 KB    2814 rows. .
exported "SCOTT"."DEPT"                              5.929 KB       4 rows. .
exported "SCOTT"."EMP"                               8.562 KB      14 rows. .
exported "SCOTT"."SALGRADE"                          5.859 KB       5 rows. .
exported "SCOTT"."BONUS"                                 0 KB       0 rows. .
exported "SCOTT"."EMPLOYEES"                             0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:  D:/BACKUP/SCOTT.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Apr 27 10:57:28 2017
elapsed 0 00:00:18  

D:/windows 2.7>

  2.4    删除该表，重新导入
  2.4.1   删除表和表空间：

SQL> drop table scott.employees; Table dropped.
SQL> drop tablespace test including contents;
Tablespace dropped.
SQL> desc scott.employees;
ERROR:ORA-04043: object scott.employees does not exist

  2.4.2   重新导入EMPLOYEES表：
   2.4.2.1   不加TRANSFORM=SEGMENT_ATTRIBUTES:N:table：

看到了报错：提示TEST表空间不存在，无法导入。那么这个时候就需要TRANSFORM出场了。

D:/windows 2.7>impdp scott/tiger DIRECTORY=ZHIXIN DUMPFILE=SCOTT.DMP LOGFILE=imp_scott.log TABLES=EMPLOYEES
Import: Release 11.2.0.4.0 - Production on Thu Apr 27 11:27:07 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  
All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** DIRECTORY=ZHIXIN DUMPFILE=SCOTT.DMP LOGFILE=imp_scott.log TABLES=EMPLOYEES
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"SCOTT"."EMPLOYEES" failed to create with error: ORA-00959: tablespace 'TEST' does not exist
Failing sql is:
CREATE TABLE "SCOTT"."EMPLOYEES"
("EMPLOYEE_ID" NUMBER(6,0),
"FIRST_NAME" VARCHAR2(20 BYTE),
"LAST_NAME" VARCHAR2(25 BYTE) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE,
"EMAIL" VARCHAR2(25 BYTE) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE,
"PHONE_NUMBER" VARCHAR2(20 BYTE),
"HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE,
"JOB_ID" VARCHAR2(10 BYTE) CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Job "SCOTT"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Thu Apr 27 11:27:09 2017 elapsed 0 00:00:01

  2.4.2.2   加TRANSFORM=SEGMENT_ATTRIBUTES:N:table：

表没有导进来。重新导

D:/windows 2.7>impdp scott/tiger DIRECTORY=ZHIXIN DUMPFILE=SCOTT.DMP LOGFILE=imp_scott.log TABLES=EMPLOYEES TRANSFORM=SEGMENT_ATTRIBUTES:N:table
Import: Release 11.2.0.4.0 - Production on Thu Apr 27 11:29:32 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  
All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** DIRECTORY=ZHIXIN DUMPFILE=SCOTT.DMP LOGFILE=imp_scott.log TABLES=EMPLOYEES TRANSFORM=SEGMENT_ATTRIBUTES:N:table
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA. . imported "SCOTT"."EMPLOYEES"                             0 KB       0 rows
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at Thu Apr 27 11:29:34 2017 elapsed 0 00:00:01

可以看到成功导入了。。。

  2.5    再次查看该表DDL

SQL >SELECT dbms_metadata.get_ddl('TABLE','EMPLOYEES','SCOTT') FROM dual;   
CREATE TABLE "SCOTT"."EMPLOYEES"   (        
"EMPLOYEE_ID" NUMBER(6,0),"FIRST_NAME" VARCHAR2(20),"LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE,"EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE,"PHONE_NUMBER" VARCHAR2(20),"HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE,"JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE,"SALARY" NUMBER(8,2),"COMMISSION_PCT" NUMBER(2,2),"MANAGER_ID" NUMBER(6,0),"DEPARTMENT_ID" NUMBER(4,0)   )
SEGMENT CREATION DEFERRED  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  
TABLESPACE "USERS"

--可以看到，创建的DDL语句已经变化。现在使用操作系统默认的表空间USERS和一些默认配置。

其实就是相当于执行了这个语句：

CREATE TABLE "SCOTT"."EMPLOYEES"   (        
"EMPLOYEE_ID" NUMBER(6,0),"FIRST_NAME" VARCHAR2(20),
"LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE,
"EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE,"PHONE_NUMBER" VARCHAR2(20),
"HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE,
"JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE,
"SALARY" NUMBER(8,2),
"COMMISSION_PCT" NUMBER(2,2),
"MANAGER_ID" NUMBER(6,0),
"DEPARTMENT_ID" NUMBER(4,0)   
)