加载中…
个人资料
微博
  • 博客等级：
  • 博客积分：
  • 博客访问：
  • 关注人气：
  • 获赠金笔：0支
  • 赠出金笔：0支
  • 荣誉徽章：
正文 字体大小：大 中 小

[转载]impdp transform=segment_attributes|st

(2014-02-11 10:56:38)
标签：

转载

 分类： oracle-partition
原文地址：impdp transform=segment_attributes|storage|oid|pctspace：Y/N:object_type作者：nowhill

impdp 得到简洁的create 语法

impdp的一个参数 TRANSFORM

这个参数的语法是 transform=segment_attributes|storage|oid|pctspace：Y/N:object_type

--实验

--导出表结构

[oracle@afc2 ~]$ expdp test/test dumpfile=test.dmp directory=dpump_dir  tables=TEST.T_INSTR

Export: Release 10.2.0.1.0 - Production on Friday, 21 January, 2011 11:06:36

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "TEST"."SYS_EXPORT_TABLE_01":  test/******** dumpfile=test.dmp directory=dpump_dir tables=TEST.T_INSTR
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."T_INSTR"                            1.414 MB  100000 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/dbbackup/test.dmp
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 11:11:27


[oracle@afc2 ~]$ impdp test/test DIRECTORY=dpump_dir DUMPFILE=test.dmp sqlfile=test.sql TRANSFORM=segment_attributes:n    --去除段属性

Import: Release 10.2.0.1.0 - Production on Friday, 21 January, 2011 11:13:01

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "TEST"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "TEST"."SYS_SQL_FILE_FULL_01":  test/******** DIRECTORY=dpump_dir DUMPFILE=test.dmp sqlfile=test.sql TRANSFORM=segment_attributes:n
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "TEST"."SYS_SQL_FILE_FULL_01" successfully completed at 11:13:13

--查看test.sql,表和索引后面的storage，pctfree，tablespace等信息都没有了

CREATE TABLE "TEST"."T_INSTR"
   (    "A" NUMBER NOT NULL ENABLE,
        "B" VARCHAR2(200) NOT NULL ENABLE
   ) ;
 
-- new object type path is: TABLE_EXPORT/TABLE/INDEX/INDEX
CREATE INDEX "TEST"."I_T_INSTR_B" ON "TEST"."T_INSTR" ("B")
  ;

  ALTER INDEX "TEST"."I_T_INSTR_B" NOPARALLEL;
 

 

[oracle@afc2 ~]$ impdp test/test DIRECTORY=dpump_dir DUMPFILE=test.dmp sqlfile=test2.sql TRANSFORM=segment_attributes:n:table  --对表去除段属性
Import: Release 10.2.0.1.0 - Production on Friday, 21 January, 2011 11:16:14

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "TEST"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "TEST"."SYS_SQL_FILE_FULL_01":  test/******** DIRECTORY=dpump_dir DUMPFILE=test.dmp sqlfile=test2.sql TRANSFORM=segment_attributes:n:table
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "TEST"."SYS_SQL_FILE_FULL_01" successfully completed at 11:16:23

--查看test2.sql,表的storage，pctfree，tablespace等信息都没有了，索引还有的

CREATE TABLE "TEST"."T_INSTR"
   (    "A" NUMBER NOT NULL ENABLE,
        "B" VARCHAR2(200) NOT NULL ENABLE
   ) ;
 
-- new object type path is: TABLE_EXPORT/TABLE/INDEX/INDEX
CREATE INDEX "TEST"."I_T_INSTR_B" ON "TEST"."T_INSTR" ("B")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" PARALLEL 1 ;

  ALTER INDEX "TEST"."I_T_INSTR_B" NOPARALLEL;
 
[oracle@afc2 ~]$ impdp test/test DIRECTORY=dpump_dir DUMPFILE=test.dmp sqlfile=test3.sql TRANSFORM=storage:n       --去除storage部分

Import: Release 10.2.0.1.0 - Production on Friday, 21 January, 2011 11:23:56

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "TEST"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "TEST"."SYS_SQL_FILE_FULL_01":  test/******** DIRECTORY=dpump_dir DUMPFILE=test.dmp sqlfile=test3.sql TRANSFORM=storage:n
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "TEST"."SYS_SQL_FILE_FULL_01" successfully completed at 11:24:06


--查看test2.sql,表和索引的storage信息没有了，pctfree 等信息还是有的
-- CONNECT TEST
-- new object type path is: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "TEST"."T_INSTR"
   (    "A" NUMBER NOT NULL ENABLE,
        "B" VARCHAR2(200) NOT NULL ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "USERS" ;
 
-- new object type path is: TABLE_EXPORT/TABLE/INDEX/INDEX
CREATE INDEX "TEST"."I_T_INSTR_B" ON "TEST"."T_INSTR" ("B")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  TABLESPACE "USERS" PARALLEL 1 ;

  ALTER INDEX "TEST"."I_T_INSTR_B" NOPARALLEL;

 

--剩余2个参数大家可以实验一下
 

喜欢

0

  

新浪BLOG意见反馈留言板　欢迎批评指正

新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 产品答疑

Copyright © 1996 - 2022 SINA Corporation, All Rights Reserved

新浪公司 版权所有