欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

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

程序员文章站 2024-03-24 23:10:04
...

1 TRANSFORM参数说明

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

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.

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

  • 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"."YS_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 删除表和表空间：

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" ARCHAR2(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)
   )
相关标签： TRANSFORM

上一篇： pytorch LSTM

下一篇： JS 原型和原型链

推荐阅读