對序列進行遷移時産生間斷的原因

我們知道建立序列時，會有預設的或者指定的 n個值會cache到記憶體中，當資料庫所在的伺服器down 機或者遷移序列時，會發生序列丢失的問題。現在就第二種進行實驗。這裡并沒有什麼方法可以解決序列 間隔的問題，如果有，也請大家給以事例。

yang@rac1>create sequence yang_seq ;

sequence created.

yang@rac1>select yang_seq.nextval from dual;

   nextval

----------

         1

yang@rac1>/

         2

         3

         4

         5

         6

         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

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

job "yang"."sys_import_full_01" successfully completed at 23:07:21

再次驗證：

        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

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

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 在記憶體中的值 ）。這就是為什麼會出現間斷了。