ITPub博客

首页 > Linux操作系统 > Linux操作系统 > tips: impdp 在oracle的不同版本上的导出差别

tips: impdp 在oracle的不同版本上的导出差别

原创 Linux操作系统 作者:jack22220613 时间:2011-04-08 19:30:59 0 删除 编辑
此处,只比较了oracle 11g的两个小版本,11.2.0.1     11.2.0.2 ,由于只是小版本的不同,导致impdp时出现问题,具体比较过程如下:
 
1、分别在 11.2.0.2 和 11.2.0.1 上用同样的sql各自创建一张表,创建完之后,用metadata取出创建脚本,如下所示:
11.2.0.1
 CREATE TABLE "TAB_1"
   ( "COUNT" NUMBER,
 "TIME" DATE,
 "NE_SYS_ID" VARCHAR2(25),
 "LAC" NUMBER,
 "CI" NUMBER,
 "RXLEV_UP_PC" NUMBER,
 "RXLEV_DOWN_PC" NUMBER,
 "RXLULD" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBS_1"
  PARTITION BY RANGE ("TIME") INTERVAL (NUMTODSINTERVAL(1,'DAY')) STORE IN ("TBS_2")
 (PARTITION "HZ_ABIS_LULD_20100101"  VALUES LESS THAN (TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOLOGGING
  STORAGE(INITIAL 2097152 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBS_2" NOCOMPRESS )
 
 
11.2.0.2
 
  CREATE TABLE "TAB_1"
   ( "COUNT" NUMBER,
 "TIME" DATE,
 "NE_SYS_ID" VARCHAR2(25),
 "LAC" NUMBER,
 "CI" NUMBER,
 "RXLEV_UP_PC" NUMBER,
 "RXLEV_DOWN_PC" NUMBER,
 "RXLULD" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBS_1"
  PARTITION BY RANGE ("TIME") INTERVAL (NUMTODSINTERVAL(1,'DAY')) STORE IN ("TBS_2")
 (PARTITION "HZ_ABIS_LULD_20100101"  VALUES LESS THAN (TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBS_2" )
 
注意,以上两个小版本的不同之处,已经用粗体标注。
 
2、创建完之后,在11.2.0.2中使用expdp 导出这张表,然后用11.2.0.1版本的impdp导入到11.2.0.1数据库中(导入之前,先删除掉该库中的表),在导入的过程中,会发现不能导入,报错信息如“同一个表中,不能同时指定多个 nologging 和 logging “ 等信息。
 
3、分别用 11.2.0.2 和 11.2.0.1 两个版本的impdp工具,将dmp中的建表命令导出到文本(前一篇日志已经提到如何导出),然后比较,发现如下不同之处:
 
11.2.0.1
CREATE TABLE "TAB_1"
   ( "COUNT" NUMBER,
 "TIME" DATE,
 "NE_SYS_ID" VARCHAR2(25 BYTE),
 "LAC" NUMBER,
 "CI" NUMBER,
 "RXLEV_UP_PC" NUMBER,
 "RXLEV_DOWN_PC" NUMBER,
 "RXLULD" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBS_1"
  PARTITION BY RANGE ("TIME") INTERVAL (NUMTODSINTERVAL(1,'DAY')) TRANSITION ("HZ_ABIS_LULD_20100101") STORE IN ("TBS_2")
 (PARTITION "HZ_ABIS_LULD_20100101"  VALUES LESS THAN (TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOLOGGING NOCOMPRESS LOGGING
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBS_2" NOCOMPRESS ,
 PARTITION "SYS_P67"  VALUES LESS THAN (TO_DATE(' 2010-05-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOLOGGING
  STORAGE(INITIAL 2097152 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBS_2" NOCOMPRESS ,
 PARTITION "SYS_P80"  VALUES LESS THAN (TO_DATE(' 2011-03-29 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOLOGGING
  STORAGE(INITIAL 2097152 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBS_2" NOCOMPRESS ,
 PARTITION "SYS_P93"  VALUES LESS THAN (TO_DATE(' 2011-03-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOLOGGING
  STORAGE(INITIAL 2097152 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBS_2" NOCOMPRESS ,
 PARTITION "SYS_P106"  VALUES LESS THAN (TO_DATE(' 2011-03-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOLOGGING
  STORAGE(INITIAL 2097152 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBS_2" NOCOMPRESS ) ;
 
11.2.0.2
CREATE TABLE "TAB_1"
   ( "COUNT" NUMBER,
 "TIME" DATE,
 "NE_SYS_ID" VARCHAR2(25 BYTE),
 "LAC" NUMBER,
 "CI" NUMBER,
 "RXLEV_UP_PC" NUMBER,
 "RXLEV_DOWN_PC" NUMBER,
 "RXLULD" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBS_1"
  PARTITION BY RANGE ("TIME") INTERVAL (NUMTODSINTERVAL(1,'DAY')) TRANSITION ("HZ_ABIS_LULD_20100101") STORE IN ("TBS_2")
 (PARTITION "HZ_ABIS_LULD_20100101"  VALUES LESS THAN (TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBS_2" ,
 PARTITION "SYS_P67"  VALUES LESS THAN (TO_DATE(' 2010-05-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
  STORAGE(INITIAL 2097152 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBS_2" ,
 PARTITION "SYS_P80"  VALUES LESS THAN (TO_DATE(' 2011-03-29 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
  STORAGE(INITIAL 2097152 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBS_2" ,
 PARTITION "SYS_P93"  VALUES LESS THAN (TO_DATE(' 2011-03-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
  STORAGE(INITIAL 2097152 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBS_2" ,
 PARTITION "SYS_P106"  VALUES LESS THAN (TO_DATE(' 2011-03-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
  STORAGE(INITIAL 2097152 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBS_2" ) ;
 
注意到粗体部分的代码,可得知在小版本不一致的情况下,也不能通用,以前没测试过,以为只有大版本上不同会有这个问题。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10037372/viewspace-691980/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-05-13

  • 博文量
    94
  • 访问量
    351312