關于導入導出sequence

sequence在平時的工作中是一個默默無聞的角色。可能建立好之後很少會去修改它，它就在默默地自增長。直到一些特殊的原因導緻sequence出現問題，比如提供了一個腳本，需要使用insert語句修複一些問題，

修複的語句類似insert into test values(100,xxxxxx,xxxx);

正确的寫法應該是insert into test values(test_seq.nextval,xxxxxx,xxxx);

但是測試的時候也沒有發現問題，就這樣部署到生産中就出現問題了。這個時候就是比較典型的sequence不一緻問題，可能sequence的nextval是100，但是已經手工插入了一些100,101的資料，這樣sequence遞增的時候就會出現問題。

是以說sequence的問題發生時，情況還是比較嚴重的。

在各個環境之間導入導出資料的時候，sequence也是一個不可忽視的環節。資料的導入導出不會預設調用sequence，是以如果不能合理的處理sequence問題，就很可能影響到imp/impdp的進度，甚至導緻很多資料問題。

但是在oracle中關于sequence的處理還是一個比較糾結的部分。

oracle沒有顯示提供工具來做sequence的導入導出，但是工具是死的，人是活的還是有一些途徑來完成sequence的導入導出。

有兩種主流的處理方法，一種是使用dbms_metadata來導出建立語句，另外一種是直接通路資料字典表，直接生成建立語句。

還有一種方法可以彌補以上兩種方法的不足。我都一一做解釋。

使用dbms_metadata導出sequence

這種方法也是比較正統的方法。在資料導出的時候可以同時導出一份sequence的腳本。

可以采用如下的腳本來實作。

set linesize 200

col create_ddl format a200

set long 9999

set pages 0

select dbms_metadata.get_ddl('SEQUENCE',u.object_name)||';' create_ddl from user_objects u where object_type='SEQUENCE'

腳本運作情況如下：

SQL> select dbms_metadata.get_ddl('SEQUENCE',u.object_name)||';' create_ddl from user_objects u where object_type='SEQUENCE';

   CREATE SEQUENCE  "N1"."TEST_SEQ2"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  ORDER  CYCLE ;

   CREATE SEQUENCE  "N1"."TEST_SEQ"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE  ORDER  NOCYCLE ;

使用資料字典生成動态建立語句

可以使用user_sequences來建構動态的建立語句。

可以使用如下的腳本來實作。

select 'create sequence '||sequence_name||   

       ' minvalue '||min_value||   

       ' maxvalue '||max_value||   

       ' start with '||last_number||   

       ' increment by '||increment_by||   

        (case when cache_size=0 then ' nocache' else ' cache '||cache_size end) ||';'  

from user_sequences ; 

create sequence TEST_SEQ minvalue 1 maxvalue 9999999999999999999999999999 start with 1 increment by 1 nocache;

create sequence TEST_SEQ2 minvalue 1 maxvalue 9999999999999999999999999999 start with 1 increment by 1 nocache;

以上兩種方法如果可以通路遠環境的情況下是不錯的選擇。

如果當我們拿到一個dump的時候，沒有權限通路源環境的時候，也是可以做點工作得到sequence的語句的。這是第三種方法。

可以使用strings來解析dump檔案，然後簡單的過濾就能生成sequence的語句。

一般來說我們使用exp做schema級别的資料導出的時候可以看到下面的日志。預設是會導出sequence的值的。

About to export specified users ...

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user N1 

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user N1 

About to export N1's objects ...

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export N1's tables via Conventional Path ...

我們可以嘗試解析dump檔案，使用如下的方式，假設dump檔案為a.dmp,就能夠很輕松的得到sequence的值。

[ora11g@rac1 ~]$ strings a.dmp|grep "CREATE SEQUENCE"|awk '{print $0";"}'

CREATE SEQUENCE "TEST_SEQ2" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE ORDER CYCLE;

CREATE SEQUENCE "TEST_SEQ" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE;

總之辦法總比困難多，還是有很多的途徑來實作一些沒有的功能。