关于导入导出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;

总之办法总比困难多，还是有很多的途径来实现一些没有的功能。