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

CREATE SEQUENCE MYTEST.S_TESTSTARTWITH 1INCREMENTBY 1NOMINVALUEMAXVALUE10000NOCYCLECACHE 20NOORDERSQL> select S_TEST.nextval from dual;NEXTVAL----------1SQL> select S_TEST.nextval from dual;NEXTVAL----------2SQL> select S_TEST.nextval from dual;NEXTVAL----------3

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

SQL> select S_TEST.nextval from dual;NEXTVAL----------21

SQL> desc dbms_shared_pool;ERROR:ORA-04043: 对象 dbms_shared_pool 不存在

SQL> @E:\oracle\RDBMS\ADMIN\dbmspool.sql

SQL> desc dbms_shared_pool;

SQL> grant execute on dbms_shared_pool to MYtest;

SQL> create synonym MYtest.dbms_shared_pool for dbms_shared_pool;

procedure keep(name varchar2, flag char DEFAULT 'P')name-- The name of the object to keep. There are twokinds of objects:-- PL/SQL objects, triggers, sequences, types andJava objects,-- which are specified by name, and-- SQL cursor objects which are specified by a two-part number-- (indicating a location in the shared pool). Forexample:-- dbms_shared_pool.keep('scott.hispackage')-- will keep package HISPACKAGE, owned by SCOTT. Thenames for-- PL/SQL objects follows SQL rules for naming objects (i.e.,-- delimited identifiers, multi-byte names, etc. are allowed).-- A cursor can be keeped by-- dbms_shared_pool.keep('0034CDFF, 20348871', 'C')-- The complete hexadecimal address must be in the first 8characters.-- The value for this identifier is the concatenation of the-- 'address' and 'hash_value' columns from the v$sqlareaview. This-- is displayed by the 'sizes' call above.-- Currently 'TABLE' and 'VIEW' objects may not be keeped.flag-- This is an optional parameter. If the parameteris not specified,-- the package assumes that the first parameter is the name of a-- package/procedure/function and will resolve thename. Otherwise,-- the parameter is a character string indicating what kind ofobject-- to keep the name identifies. The string is caseinsensitive.-- The possible values and the kinds of objects they indicate are-- given in the following table:-- Value Kind of Object to keep-- ----- ------------------------ P package/procedure/function-- Q sequence-- R trigger-- T type-- JS java source-- JC java class-- JR java resource-- JD java shared data-- C cursor

SQL> select S_TEST.nextval from dual;NEXTVAL----------24SQL> select S_TEST.nextval from dual;NEXTVAL----------25SQL> select S_TEST2.nextval from dual;NEXTVAL----------1SQL> exec dbms_shared_pool.keep('s_test','q');

SQL> select S_TEST2.nextval from dual;NEXTVAL----------21SQL> select S_TEST.nextval from dual;NEXTVAL----------26

SQL> execdbms_shared_pool.unkeep('s_test','q');

SQL> select S_TEST.nextval from dual;NEXTVAL----------27SQL> alter system flush shared_pool;

SQL> select S_TEST.nextval from dual;NEXTVAL----------41