【impdp】IMPDP中的TRANSFORM参数--【数据泵】EXPDP导出表结构(真实案例)后传

简介:         在上一篇文章【数据泵】EXPDP导出表结构(真实案例) 中:http://blog.itpub.net/26736162/viewspace-1657828/  ,由于表的storage参数存储很大,导致不能导入到测试库,我提出了2种办法,但是今天经过网友selectshen (http://blog.

        在上一篇文章【数据泵】EXPDP导出表结构(真实案例) 中:http://blog.itpub.net/26736162/viewspace-1657828/  ,由于表的storage参数存储很大,导致不能导入到测试库,我提出了2种办法,但是今天经过网友selectshen (http://blog.itpub.net/28539951/)的提醒说是有一个参数TRANSFORM可以解决这个问题,于是就研究了一下这个参数,发现大牛的一篇文章,http://blog.itpub.net/26736162/viewspace-1662276/ ,然后自己又去官网查看了这个参数的详细解释,颇有收获,现分享给大家。

[oracle@rhel6_lhr dpdump]$ impdp  lhr/lhr  directory=DATA_PUMP_DIR dumpfile=lhrsql20150515.dmp  logfile=imp_exptest.log TRANSFORM=storage:n TRANSFORM=SEGMENT_CREATION:n

 

Import: Release 11.2.0.3.0 - Production on 星期二 5月 19 15:51:50 2015

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

已成功加载/卸载了主表 "LHR"."SYS_IMPORT_FULL_01" 

启动 "LHR"."SYS_IMPORT_FULL_01":  lhr/******** directory=DATA_PUMP_DIR dumpfile=lhrsql20150515.dmp logfile=imp_exptest.log TRANSFORM=storage:n TRANSFORM=SEGMENT_CREATION:n 

处理对象类型 SCHEMA_EXPORT/USER

ORA-31684: 对象类型 USER:"LHR" 已存在

处理对象类型 SCHEMA_EXPORT/SYSTEM_GRANT

处理对象类型 SCHEMA_EXPORT/ROLE_GRANT

处理对象类型 SCHEMA_EXPORT/DEFAULT_ROLE

处理对象类型 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

处理对象类型 SCHEMA_EXPORT/DB_LINK

处理对象类型 SCHEMA_EXPORT/SEQUENCE/SEQUENCE

处理对象类型 SCHEMA_EXPORT/TABLE/TABLE

处理对象类型 SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/INDEX

处理对象类型 SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

处理对象类型 SCHEMA_EXPORT/TABLE/COMMENT

处理对象类型 SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC

处理对象类型 SCHEMA_EXPORT/FUNCTION/FUNCTION

处理对象类型 SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT

处理对象类型 SCHEMA_EXPORT/PROCEDURE/PROCEDURE

处理对象类型 SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT

处理对象类型 SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC

处理对象类型 SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION

处理对象类型 SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

ORA-39082: 对象类型 ALTER_PROCEDURE:"FXDM"."SP_STAT_MAKER_AWARD_FX" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"FXDM"."SP_FXDM_FX_MBR_MMKNG_FX_F" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"DPA"."TEST" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"DPA"."SP_FX_SF_CP_RL_D_SUM" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"FXDM"."SP_FXDM_FX_MBR_MMKNG_AMNT_F_T" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"FXDM"."SP_STAT_MAKER" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"FXDM"."SP_FXDM_FX_MBR_MMKNG_F" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"FXDM"."SP_FX_MBR_MMKNG_ROLE_MTH_H_N" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"TEST"."GBP_ZUOSHISHANG" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"DPA"."SP_GOLD_MBR_MMKNG_NGTN_F" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"DPA"."I_DW_SP_RPT_MMKT_NET_INFO_CNY" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"DPA"."I_DW_SP_RPT_MMKT_NET_INFO_FX" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"TEST"."NZD_ZUOSHISHANG" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"FXDM"."SP_GOLD_MBR_MMKNG_NGTN_F_OLD" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"FXDM"."SP_GOLD_MBR_MMKNG_NGTN_F_BK" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"FXDM"."SP_STAT_MAKER_OPTION" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"FXDM"."SP_STAT_MAKER_OPTION_N" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"FXDM"."SP_STAT_MAKER_N" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"SOR"."BST_QT_ARCHIVE" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"SOR"."FX_BST_QT_ARCHIVE" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"FXDM"."SP_HANDREPORT_MAKE_JPYAUD" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"TEST"."USD_ZUOSHISHANG" 已创建, 但带有编译警告

ORA-39082: 对象类型 ALTER_PROCEDURE:"FXDM"."SP_GOLD_MBR_MMKNG_NGTN_F" 已创建, 但带有编译警告

处理对象类型 SCHEMA_EXPORT/VIEW/VIEW

处理对象类型 SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT

ORA-39082: 对象类型 VIEW:"DPA"."V_IRS_OFST_DTLS_F" 已创建, 但带有编译警告

处理对象类型 SCHEMA_EXPORT/VIEW/COMMENT

处理对象类型 SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

处理对象类型 SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

作业 "LHR"."SYS_IMPORT_FULL_01" 已经完成, 但是有 25 个错误 (于 15:59:01 完成)

 

[oracle@rhel6_lhr ~]$ ORACLE_SID=ora11g

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on 星期二 5月 19 15:45:48 2015

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

已连接到空闲例程。

 

15:45:48 SQL> startup

ORACLE 例程已经启动。

 

Total System Global Area  409194496 bytes

Fixed Size                  2228864 bytes

Variable Size             301993344 bytes

Database Buffers           96468992 bytes

Redo Buffers                8503296 bytes

数据库装载完毕。

数据库已经打开。

15:46:24 SQL> select name from v$datafile;

 

NAME

/u01/app/oracle/oradata/ora11g/system01.dbf

/u01/app/oracle/oradata/ora11g/sysaux01.dbf

/u01/app/oracle/oradata/ora11g/undotbs01.dbf

/u01/app/oracle/oradata/ora11g/users01.dbf

/u01/app/oracle/oradata/ora11g/example01.dbf

/u01/app/oracle/oradata/ora11g/aa.dbf

 

已选择6行。

 

已用时间:  00: 00: 00.00

15:47:13 SQL> create tablespace  DWII_CNY_BK_F_01 datafile '/u01/app/oracle/oradata/ora11g/DWII_CNY_BK_F_01.dbf' size 10M;

create tablespace  DWII_DPA_F_01 datafile '/u01/app/oracle/oradata/ora11g/DWII_DPA_F_01.dbf' size 10M;

create tablespace  DWII_DPA_I_01 datafile '/u01/app/oracle/oradata/ora11g/DWII_DPA_I_01.dbf' size 10M;

create tablespace  DWII_DPA_S_01 datafile '/u01/app/oracle/oradata/ora11g/DWII_DPA_S_01.dbf' size 10M;

create tablespace  DWII_SOR_F_01 datafile '/u01/app/oracle/oradata/ora11g/DWII_SOR_F_01.dbf' size 10M;

create tablespace  DWII_SOR_I_01 datafile '/u01/app/oracle/oradata/ora11g/DWII_SOR_I_01.dbf' size 10M;

create tablespace  DW_USER datafile '/u01/app/oracle/oradata/ora11g/DW_USER.dbf' size 10M;

create tablespace  SQCHECK datafile '/u01/app/oracle/oradata/ora11g/SQCHECK.dbf' size 10M;

create tablespace  SD_CNY_D_01 datafile '/u01/app/oracle/oradata/ora11g/SD_CNY_D_01.dbf' size 10M;

create tablespace  SD_CNY_F_01 datafile '/u01/app/oracle/oradata/ora11g/SD_CNY_F_01.dbf' size 10M;

create tablespace  SD_DPA_D_01 datafile '/u01/app/oracle/oradata/ora11g/SD_DPA_D_01.dbf' size 10M;

create tablespace  SD_DPA_F_01 datafile '/u01/app/oracle/oradata/ora11g/SD_DPA_F_01.dbf' size 10M;

create tablespace  SD_SORT_T_01 datafile '/u01/app/oracle/oradata/ora11g/SD_SORT_T_01.dbf' size 10M;

create tablespace  DWII_FXDM_F_01 datafile '/u01/app/oracle/oradata/ora11g/DWII_FXDM_F_01.dbf' size 10M;

 

表空间已创建。

 

已用时间:  00: 00: 01.01

15:49:42 SQL> create tablespace  SD_SOR_T_01 datafile '/u01/app/oracle/oradata/ora11g/SD_SOR_T_01.dbf' size 10M;

 

表空间已创建。

 

已用时间:  00: 00: 00.60

15:49:42 SQL> 

表空间已创建。

 

已用时间:  00: 00: 00.45

15:49:43 SQL> 

表空间已创建。

 

已用时间:  00: 00: 00.35

15:49:43 SQL> 

表空间已创建。

 

已用时间:  00: 00: 00.47

15:49:44 SQL> 

表空间已创建。

 

已用时间:  00: 00: 00.62

15:49:44 SQL> 

表空间已创建。

 

已用时间:  00: 00: 00.55

15:49:45 SQL> 

表空间已创建。

 

已用时间:  00: 00: 00.61

15:49:45 SQL> 

表空间已创建。

 

已用时间:  00: 00: 01.76

15:49:47 SQL> 

表空间已创建。

 

已用时间:  00: 00: 00.59

15:49:48 SQL> 

表空间已创建。

 

已用时间:  00: 00: 00.66

15:49:48 SQL> 

表空间已创建。

 

已用时间:  00: 00: 00.60

15:49:49 SQL> 

表空间已创建。

 

已用时间:  00: 00: 00.51

15:49:50 SQL> 

表空间已创建。

 

已用时间:  00: 00: 00.49

15:49:50 SQL> 

表空间已创建。

 

已用时间:  00: 00: 00.59

15:49:51 SQL> 

15:49:59 SQL> 

15:50:00 SQL> 

15:50:00 SQL> show parameter DEFERRED_SEGMENT_CREATION

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

deferred_segment_creation            boolean     TRUE

15:55:06 SQL> SET TERMOUT OFF;

16:04:10 SQL> COLUMN current_instance NEW_VALUE current_instance NOPRINT;

16:04:10 SQL> SELECT rpad(instance_name, 17) current_instance FROM v$instance;

 

 

 

 

已用时间:  00: 00: 00.00

16:04:10 SQL> SET TERMOUT ON;

16:04:10 SQL> 

16:04:10 SQL> PROMPT 

 

16:04:10 SQL> PROMPT +------------------------------------------------------------------------+

+------------------------------------------------------------------------+

16:04:10 SQL> PROMPT | Report   : Tablespaces                                                 |

| Report   : Tablespaces                                                 |

16:04:10 SQL> PROMPT | Instance : ¤t_instance                                           |

| Instance : ora11g                                                      |

16:04:11 SQL> PROMPT +------------------------------------------------------------------------+

+------------------------------------------------------------------------+

16:04:11 SQL> 

16:04:11 SQL> SET ECHO        OFF

16:04:11 SQL> SET FEEDBACK    6

16:04:11 SQL> SET HEADING     ON

16:04:11 SQL> SET LINESIZE    180

16:04:11 SQL> SET PAGESIZE    50000

16:04:11 SQL> SET TERMOUT     ON

16:04:11 SQL> SET TIMING      OFF

16:04:11 SQL> SET TRIMOUT     ON

16:04:11 SQL> SET TRIMSPOOL   ON

16:04:11 SQL> SET VERIFY      OFF

16:04:11 SQL> 

16:04:11 SQL> CLEAR COLUMNS

columns 已清除

16:04:11 SQL> CLEAR BREAKS

breaks 已清除

16:04:11 SQL> CLEAR COMPUTES

computes 已清除

16:04:11 SQL> 

16:04:11 SQL> COLUMN status     FORMAT a9                 HEADING 'Status'

16:04:11 SQL> COLUMN name       FORMAT a30                HEADING 'Tablespace Name'

16:04:11 SQL> COLUMN type       FORMAT a15                HEADING 'TS Type'

16:04:11 SQL> COLUMN extent_mgt FORMAT a11                HEADING 'Extent Mgt.'

16:04:11 SQL> COLUMN ts_size    FORMAT 9,999,999,999,999  HEADING 'Tablespace Size'

16:04:11 SQL> COLUMN used       FORMAT 9,999,999,999,999  HEADING 'Used (in bytes)'

16:04:11 SQL> COLUMN free       FORMAT 9,999,999,999,999  HEADING 'Free (in bytes)'

16:04:11 SQL> COLUMN pct_used   FORMAT 999                HEADING 'Pct. Used'

16:04:11 SQL> 

16:04:11 SQL> BREAK ON report

16:04:11 SQL> 

16:04:11 SQL> COMPUTE sum OF ts_size  ON report

16:04:11 SQL> COMPUTE sum OF used     ON report

16:04:11 SQL> COMPUTE sum OF free     ON report

16:04:11 SQL> COMPUTE avg OF pct_used ON report

16:04:11 SQL> 

16:04:11 SQL> SELECT

16:04:11   2      d.status                                            status

16:04:11   3    , d.tablespace_name                                   name

16:04:11   4    , d.contents                                          type

16:04:11   5    , d.extent_management                                 extent_mgt

16:04:11   6    , NVL(a.bytes, 0)                                     ts_size

16:04:11   7    , NVL(a.bytes - NVL(f.bytes, 0), 0)                   used

16:04:11   8    , NVL(f.bytes, 0)                                     free

16:04:11   9    , NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0) pct_used

16:04:11  10  FROM 

16:04:11  11      sys.dba_tablespaces d

16:04:11  12    , ( select tablespace_name, sum(bytes) bytes

16:04:11  13        from dba_data_files

16:04:11  14        group by tablespace_name

16:04:11  15      ) a

16:04:11  16    , ( select tablespace_name, sum(bytes) bytes

16:04:11  17        from dba_free_space

16:04:11  18        group by tablespace_name

16:04:11  19      ) f

16:04:11  20  WHERE

16:04:11  21        d.tablespace_name = a.tablespace_name(+)

16:04:11  22    AND d.tablespace_name = f.tablespace_name(+)

16:04:11  23    AND NOT (

16:04:11  24      d.extent_management like 'LOCAL'

16:04:11  25      AND

16:04:11  26      d.contents like 'TEMPORARY'

16:04:11  27    )

16:04:11  28  UNION ALL 

16:04:11  29  SELECT

16:04:11  30      d.status                         status

16:04:11  31    , d.tablespace_name                name

16:04:12  32    , d.contents                       type

16:04:12  33    , d.extent_management              extent_mg

16:04:12  34    , NVL(a.bytes, 0)                  ts_size

16:04:12  35    , NVL(t.bytes, 0)                  used

16:04:12  36    , NVL(a.bytes - NVL(t.bytes,0), 0) free

16:04:12  37    , NVL(t.bytes / a.bytes * 100, 0)  pct_used

16:04:12  38  FROM

16:04:12  39      sys.dba_tablespaces d

16:04:12  40    , ( select tablespace_name, sum(bytes) bytes

16:04:12  41        from dba_temp_files

16:04:12  42        group by tablespace_name

16:04:12  43      ) a

16:04:12  44    , ( select tablespace_name, sum(bytes_cached) bytes

16:04:12  45        from v$temp_extent_pool

16:04:12  46        group by tablespace_name

16:04:12  47      ) t

16:04:12  48  WHERE

16:04:12  49        d.tablespace_name = a.tablespace_name(+)

16:04:12  50    AND d.tablespace_name = t.tablespace_name(+)

16:04:12  51    AND d.extent_management like 'LOCAL'

16:04:12  52    AND d.contents like 'TEMPORARY'

16:04:12  53  /

 

Status    Tablespace Name                TS Type         Extent Mgt.    Tablespace Size    Used (in bytes)    Free (in bytes) Pct. Used

--------- ------------------------------ --------------- ----------- ------------------ ------------------ ------------------ ---------

ONLINE    AA                             PERMANENT       LOCAL                5,242,880          1,114,112          4,128,768        21

ONLINE    DWII_DPA_S_01                  PERMANENT       LOCAL               10,485,760          1,048,576          9,437,184        10

ONLINE    SD_DPA_F_01                    PERMANENT       LOCAL               10,485,760          1,048,576          9,437,184        10

ONLINE    SYSAUX                         PERMANENT       LOCAL              587,202,560        562,298,880         24,903,680        96

ONLINE    DW_USER                        PERMANENT       LOCAL               10,485,760          1,048,576          9,437,184        10

ONLINE    SD_CNY_D_01                    PERMANENT       LOCAL               10,485,760          1,048,576          9,437,184        10

ONLINE    SD_DPA_D_01                    PERMANENT       LOCAL               10,485,760          1,048,576          9,437,184        10

ONLINE    USERS                          PERMANENT       LOCAL              100,925,440         13,697,024         87,228,416        14

ONLINE    DWII_CNY_BK_F_01               PERMANENT       LOCAL               10,485,760          1,048,576          9,437,184        10

ONLINE    DWII_SOR_I_01                  PERMANENT       LOCAL               10,485,760          1,048,576          9,437,184        10

ONLINE    SYSTEM                         PERMANENT       LOCAL              807,403,520        803,733,504          3,670,016       100

ONLINE    EXAMPLE                        PERMANENT       LOCAL              362,414,080        325,189,632         37,224,448        90

ONLINE    DWII_SOR_F_01                  PERMANENT       LOCAL               10,485,760          1,048,576          9,437,184        10

ONLINE    SQCHECK                        PERMANENT       LOCAL               10,485,760          1,048,576          9,437,184        10

ONLINE    SD_SORT_T_01                   PERMANENT       LOCAL               10,485,760          1,048,576          9,437,184        10

ONLINE    SD_SOR_T_01                    PERMANENT       LOCAL               10,485,760          1,048,576          9,437,184        10

ONLINE    DWII_DPA_I_01                  PERMANENT       LOCAL               10,485,760          1,048,576          9,437,184        10

ONLINE    SD_CNY_F_01                    PERMANENT       LOCAL               10,485,760          1,048,576          9,437,184        10

ONLINE    DWII_FXDM_F_01                 PERMANENT       LOCAL               10,485,760          1,048,576          9,437,184        10

ONLINE    DWII_DPA_F_01                  PERMANENT       LOCAL               10,485,760          1,048,576          9,437,184        10

ONLINE    UNDOTBS1                       UNDO            LOCAL              298,844,160        298,844,160                  0       100

ONLINE    TEMP                           TEMPORARY       LOCAL               87,031,808         85,983,232          1,048,576        99

                                                                     ------------------ ------------------ ------------------ ---------

avg                                                                                                                                  30

sum                                                                       2,406,350,848      2,106,589,184        299,761,664

 

已选择22行。

 

16:04:12 SQL> 






     这里尤其指出的是在 11.2.0.2以上有个新增的参数,SEGMENT_CREATION,如果设置其为n的话,ddl语句就不包含SEGMENT CREATION IMMEDIATE字段。如下:
。。。。
"ISSUE_TYPE" NUMBER(6,0) DEFAULT 0,
"CSTDTN_BANK_ID" NUMBER(6,0)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
。。。。。。。


。。。。。。。
        "ISSUE_TYPE" NUMBER(6,0) DEFAULT 0, 
        "CSTDTN_BANK_ID" NUMBER(6,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 3145728 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

。。。。。。。







官网地址:

http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#SUTIL300


TRANSFORM

Default: There is no default

Purpose

Enables you to alter object creation DDL for objects being imported.

Syntax and Description

TRANSFORM = transform_name:value[:object_type]

The transform_name specifies the name of the transform. The possible options are as follows:

  • SEGMENT_ATTRIBUTES - If the value is specified as y, then segment attributes (physical attributes, storage attributes, tablespaces, and logging) are included, with appropriate DDL. The default is y.

  • STORAGE - If the value is specified as y, then the storage clauses are included, with appropriate DDL. The default is y. This parameter is ignored ifSEGMENT_ATTRIBUTES=n.

  • OID - If the value is specified as n, then the assignment of the exported OID during the creation of object tables and types is inhibited. Instead, a new OID is assigned. This can be useful for cloning schemas, but does not affect referenced objects. The default value is y.

  • PCTSPACE - The value supplied for this transform must be a number greater than zero. It represents the percentage multiplier used to alter extent allocations and the size of data files.

    Note that you can use the PCTSPACE transform with the Data Pump Export SAMPLE parameter so that the size of storage allocations matches the sampled data subset. (See "SAMPLE".)

  • SEGMENT_CREATION - If set to y (the default), then this transform causes the SQL SEGMENT CREATION clause to be added to the CREATE TABLE statement. That is, the CREATE TABLE statement will explicitly say either SEGMENT CREATION DEFERRED or SEGMENT CREATION IMMEDIATE. If the value is n, then the SEGMENT CREATION clause is omitted from the CREATE TABLE statement. Set this parameter to n to use the default segment creation attributes for the table(s) being loaded. (This functionality is available starting with Oracle Database 11g release 2 (11.2.0.2).)

The type of value specified depends on the transform used. Boolean values (y/n) are required for the SEGMENT_ATTRIBUTES, STORAGE, and OID transforms. Integer values are required for the PCTSPACE transform.

The object_type is optional. If supplied, it designates the object type to which the transform will be applied. If no object type is specified, then the transform applies to all valid object types. The valid object types for each transform are shown in Table 3-1.

Table 3-1 Valid Object Types For the Data Pump Import TRANSFORM Parameter


SEGMENT_ATTRIBUTES STORAGE OID PCTSPACE SEGMENT_CREATION

CLUSTER

X

X

 

X

 

CONSTRAINT

X

X

 

X

 

INC_TYPE

   

X

   

INDEX

X

X

 

X

 

ROLLBACK_SEGMENT

X

X

 

X

 

TABLE

X

X

X

X

X

TABLESPACE

X

   

X

 

TYPE

   

X

   

Example

For the following example, assume that you have exported the employees table in the hr schema. The SQL CREATE TABLE statement that results when you then import the table is similar to the following:

CREATE TABLE "HR"."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)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 10240 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 121
  PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM" ;

If you do not want to retain the STORAGE clause or TABLESPACE clause, then you can remove them from the CREATE STATEMENT by using the Import TRANSFORMparameter. Specify the value of SEGMENT_ATTRIBUTES as n. This results in the exclusion of segment attributes (both storage and tablespace) from the table.

> impdp hr TABLES=hr.employees DIRECTORY=dpump_dir1 DUMPFILE=hr_emp.dmp
  TRANSFORM=SEGMENT_ATTRIBUTES:n:table

The resulting CREATE TABLE statement for the employees table would then look similar to the following. It does not contain a STORAGE or TABLESPACE clause; the attributes for the default tablespace for the HR schema will be used instead.

CREATE TABLE "HR"."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)
   );

As shown in the previous example, the SEGMENT_ATTRIBUTES transform applies to both storage and tablespace attributes. To omit only the STORAGE clause and retain the TABLESPACE clause, you can use the STORAGE transform, as follows:

> impdp hr TABLES=hr.employees DIRECTORY=dpump_dir1 DUMPFILE=hr_emp.dmp
  TRANSFORM=STORAGE:n:table

The SEGMENT_ATTRIBUTES and STORAGE transforms can be applied to all applicable table and index objects by not specifying the object type on the TRANSFORMparameter, as shown in the following command:

> impdp hr DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp SCHEMAS=hr TRANSFORM=SEGMENT_ATTRIBUTES:n
 
 
 
...........................................................................................................................................................................................
本文作者:小麦苗,只专注于数据库的技术,更注重技术的运用
ITPUB BLOG:http://blog.itpub.net/26736162 
本文地址:http://blog.itpub.net/26736162/viewspace-1662344/ 
本文pdf版:http://yunpan.cn/QCwUAI9bn7g7w  提取码:af2d
QQ:642808185 若加QQ请注明你所正在读的文章标题
创作时间地点:2015-05-19 09:00~ 2015-05-19 11:20 于外汇交易中心

........................................................................................................................................................................................... 
目录
相关文章
|
Oracle 关系型数据库
PLSQL_数据泵导入进度查看Impdp/Expdp Status(案例)
20150701 Created By BaoXinjian 一、摘要 查看EXPDP/IMPDP的进度,当你当如导出的时候,如果数据量比较大,中途有些人会着急,不免想看看进度如何 1. 两个视图 DBA_DATAPUMP_JOBS; DBA_DATAPUMP_SESSIONS; 2.
3317 0
|
Oracle 关系型数据库 数据库
12 impdp 导入更好用户和表空间
12 impdp 导入更好用户和表空间
141 0
|
SQL Oracle 关系型数据库
实战篇:LogMiner 分析数据泵导入参数 TABLE_EXISTS_ACTION 的秘密
前几天,技术交流群里看到大家讨论 Oracle 数据泵导入时使用 table_exists_action 参数,存在一些疑惑。于是,我打算通过 LogMiner 来分析一下在线重做日志,看看到底是怎么玩的。
实战篇:LogMiner 分析数据泵导入参数 TABLE_EXISTS_ACTION 的秘密
|
机器学习/深度学习 Oracle 关系型数据库
【expdp】10g数据泵expdp工具选项详解及应用示例
理解expdp各个选项的含义最好的途径就是逐一的进行测试,这样可以在感性上有一个真实的体验。 1.数据泵expdp导出工具与传统的exp导出工具的区别 1)exp是客户端程序,既可以在客户端使用,也可以在服务器端使用; 2)expdp是服务器端工具,只能在ORACLE服务器端使用,不能在客户端使用; 3)这两个工具生成的备份文件不能被对方与之对应的导入工具使用; 4)expdp在灵活性和功能性上与exp相比,有质上的飞跃。 2.expdp命令行选项列表 使用“-help”选项获得expdp命令可用的选项列表和简单的注释信息。 ora10g@linux5 /expdp$ expdp help
347 0
|
Oracle 关系型数据库