ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 对序列进行迁移时产生间断的原因

对序列进行迁移时产生间断的原因

原创 Linux操作系统 作者:杨奇龙 时间:2011-04-01 23:19:08 0 删除 编辑
我们知道创建序列时,会有默认的或者指定的 n个值会cache到内存中,当数据库所在的服务器down 机或者迁移序列时,会发生序列丢失的问题。现在就第二种进行实验。这里并没有什么方法可以解决序列 间隔的问题,如果有,也请大家给以事例。
yang@rac1>create sequence yang_seq ;
Sequence created.
yang@rac1>select yang_seq.nextval from dual;
   NEXTVAL
----------
         1
yang@rac1>/
   NEXTVAL
----------
         2
yang@rac1>/
   NEXTVAL
----------
         3
yang@rac1>/
   NEXTVAL
----------
         4
yang@rac1>/
   NEXTVAL
----------
         5
yang@rac1>/
   NEXTVAL
----------
         6
yang@rac1>/
   NEXTVAL
----------
         7
导出序列:
oracle@rac1:rac1 /tmp>expdp yang/yang  directory=dumpdir dumpfile=sequence.dmp  include=sequence  
Export: Release 11.2.0.1.0 - Production on Fri Apr 1 22:59:31 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "YANG"."SYS_EXPORT_SCHEMA_01":  yang/******** directory=dumpdir dumpfile=sequence.dmp include=sequence
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Master table "YANG"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for YANG.SYS_EXPORT_SCHEMA_01 is:
  /tmp/dump/sequence.dmp
Job "YANG"."SYS_EXPORT_SCHEMA_01" successfully completed at 23:01:06
删除序列
yang@rac1>drop sequence yang_seq;
Sequence dropped.
导入序列:
oracle@rac1:rac1 /tmp>impdp yang/yang  directory=dumpdir dumpfile=sequence.dmp  table_exists_action=skip include=sequence  
Import: Release 11.2.0.1.0 - Production on Fri Apr 1 23:06:21 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "YANG"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "YANG"."SYS_IMPORT_FULL_01":  yang/******** directory=dumpdir dumpfile=sequence.dmp table_exists_action=skip include=sequence
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Job "YANG"."SYS_IMPORT_FULL_01" successfully completed at 23:07:21
再次验证
yang@rac1>select yang_seq.nextval from dual;

   NEXTVAL
----------
        21
从7 间断了13个 (注意:默认的是20个)
从下面的实验中可以看出 我们导入sequence时 数据库都做了什么:
oracle@rac1:rac1 /tmp>impdp yang/yang  directory=dumpdir dumpfile=sequence.dmp   SQLFILE=seq.sql                                               
Import: Release 11.2.0.1.0 - Production on Fri Apr 1 23:15:16 2011
Data Mining and Real Application Testing options
Master table "YANG"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "YANG"."SYS_SQL_FILE_FULL_01":  yang/******** directory=dumpdir dumpfile=sequence.dmp SQLFILE=seq.sql
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Job "YANG"."SYS_SQL_FILE_FULL_01" successfully completed at 23:16:13

oracle@rac1:rac1 /tmp>cd dump
oracle@rac1:rac1 /tmp/dump>ls
export.log  import.log  seq.sql  sequence.dmp  t.dmp
oracle@rac1:rac1 /tmp/dump>cat seq.sql
-- CONNECT YANG
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/SEQUENCE/SEQUENCE
 CREATE SEQUENCE  "YANG"."YANG_SEQ"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER  NOCYCLE ;
每次导入队列时,会从cache 的 N+1 开始(N 为创建序列时cache 在内存中的值 )。这就是为什么会出现间断了。
 

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

上一篇: iozone 测试实例
请登录后发表评论 登录
全部评论
MySQL DBA NoSQL DEVOPS

注册时间:2009-10-07

  • 博文量
    1026
  • 访问量
    7823878