oracle自增序列跳号,sequence跳号问题及解决办法丨本际云推荐

oracle自增序列跳号,sequence跳号问题及解决办法

小本本的序列跳号问题解决方案

作为一名际云服务器推荐网的小编小本本，我们今天来谈一下Oracle自增序列出现跳号现象的问题，并分享如何解决它。

oracle自增序列跳号,sequence跳号问题及解决办法

问题描述

在数据库重启、刷新、奔溃等原因导致sequence缓存数据丢失的情况下，查询时会出现跳号现象，下面我们来看一个样例：

-- 创建序列
CREATE SEQUENCE MYTEST.S_TEST
START WITH 1
INCREMENT BY 1
NO MINVALUE
MAXVALUE 10000
NO CYCLE
CACHE 20
NO ORDER;
-- 查询序列
SQL> select S_TEST.nextval from dual;
NEXTVAL
----------
1
SQL> select S_TEST.nextval from dual;
NEXTVAL
----------
2
SQL> select S_TEST.nextval from dual;
NEXTVAL
----------
3

共享池刚刚刷新的时候，会清除所有的没有使用DBMS_SHARED_POOL.KEEP固定的对象，所以下一次再取序列的nextval值就不再从之前的值开始，而是从下一个号开始，从而出现跳号现象。

解决方案

为了避免上述情况，Oracle提供了把SEQUENCE KEEP到SHARED POOL中的方法，从而避免SEQUENCE的CACHE被交换出去。根据Metalink的注意事项61760.1，如果需要访问DBMS_SHARED_POOL包，任何用户都必须由SYS授予执行权限。默认情况下dbms_shared_pool包是不在系统中的，需要运行$ORACLE_HOME/rdbms/admin/dbmspool.sql进行创建。步骤如下：

运行$ORACLE_HOME/rdbms/admin/dbmspool.sql创建dbms_shared_pool包。
授权用户访问dbms_shared_pool包：grant execute on dbms_shared_pool to MYtest; createsynonym MYtest.dbms_shared_pool for dbms_shared_pool;
将SEQUENCE KEEP到SHARED POOL中：exec dbms_shared_pool.keep(数据库用户名.sequence名称, Q);
为了取消已经KEEP在SHARED POOL中的对象，使用dbms_shared_pool.unkeep(s_test, q)。

下面是一个测试结果的样例：

-- 设置cache并测试序列
CREATE SEQUENCE S_TEST2
START WITH 1
INCREMENT BY 1
NO MINVALUE
MAXVALUE 10000
NO CYCLE
CACHE 20
NO ORDER;
-- 将s_test序列keep到sharedpool中
SQL> exec dbms_shared_pool.keep(s_test, q);
-- 测试结果
SQL> select S_TEST.nextval from dual;
NEXTVAL
----------
24
SQL> select S_TEST.nextval from dual;
NEXTVAL
----------
25
SQL> select S_TEST2.nextval from dual;
NEXTVAL
----------
1
SQL> select S_TEST.nextval from dual;
NEXTVAL
----------
26
-- 取消keep
SQL> exec dbms_shared_pool.unkeep(s_test, q);
SQL> select S_TEST.nextval from dual;
NEXTVAL
----------
27
SQL> alter system flush shared_pool;
SQL> select S_TEST.nextval from dual;
NEXTVAL
----------
41

本文原创来源：IT那活儿微信公众号（上海新炬王翦团队）

原创文章，作者：小编小本本，如若转载，请注明出处：https://www.benjiyun.com/yunzhujiyunwei/vps-yunwei/6194.html