【impdp】IMPDP中的TRANSFORM引數--【資料泵】EXPDP匯出表結構(真實案例)後傳

lhrbest發表於2015-05-19
ORM

        在上一篇文章【資料泵】EXPDP匯出表結構(真實案例) 中：http://blog.itpub.net/26736162/viewspace-1657828/  ，由於表的storage引數儲存很大，導致不能匯入到測試庫，我提出了2種辦法，但是今天經過網友 (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

。。。。。。。







官網地址：


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版：  提取碼：af2d
QQ：642808185 若加QQ請註明你所正在讀的文章標題
創作時間地點：2015-05-19 09:00~ 2015-05-19 11:20 於外匯交易中心

........................................................................................................................................................................................... 

                                                    

                                        

                    

                                                    來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-1662344/，如需轉載，請註明出處，否則將追究法律責任。
                                            


                




相關文章