签到成功

知道了

CNDBA社区CNDBA社区

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

2017-04-27 11:44 11372 1 原创 Oracle 11G
作者： Expect-乐
本文链接：http://www.cndba.cn/Expect-le/article/1890

1  TRANSFORM参数说明

官方文档：http://docs.oracle.com/database/122/SUTIL/datapump-import-utility.htm#SUTIL939

1.1   TRANSFORM适用场景

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

1.2   语法

TRANSFORM = transform_name:value[:object_type]

http://www.cndba.cn/Expect-le/article/1890

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

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

1.3   transform_name：SEGMENT_ATTRIBUTES

在官方文档中有很详细的介绍，这里我只介绍一个SEGMENT_ATTRIBUTES的参数值。http://www.cndba.cn/Expect-le/article/1890

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表http://www.cndba.cn/Expect-le/article/1890

http://www.cndba.cn/Expect-le/article/1890

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/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/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  删除表和表空间：

http://www.cndba.cn/Expect-le/article/1890

http://www.cndba.cn/Expect-le/article/1890 

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出场了。http://www.cndba.cn/Expect-le/article/1890

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：

表没有导进来。重新导

http://www.cndba.cn/Expect-le/article/1890 

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和一些默认配置。

 

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

http://www.cndba.cn/Expect-le/article/1890

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)

   )

版权声明：本文为博主原创文章，未经博主允许不得转载。

数据泵 TRANSFORM

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
Expect-乐

Expect-乐

关注

Without the continuous bitter cold, there can be no fragrant plum blossom

  • 336
    原创
  • 6
    翻译
  • 100
    转载
  • 41
    评论
  • 访问：1548714次
  • 积分：1957
  • 等级：核心会员
  • 排名：第4名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ