开发者社区> jeanron100> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

关于reset sequence

简介: sequence在工作中使用比较频繁,对于Insert中插入的值,如果只需要它能够自动递增,这个时候sequence就派上用场了。 但是既然sequence的值需要递增就有可能会达到最大值。
+关注继续查看
sequence在工作中使用比较频繁,对于Insert中插入的值,如果只需要它能够自动递增,这个时候sequence就派上用场了。
但是既然sequence的值需要递增就有可能会达到最大值。比如sequence从1开始递增,递增幅度为1,最大值为100,那么很快就会达到最大值。我们可以指定sequence的值。这个时候可以使用删除,重建的方式,但是这种方式相对来说影响范围较大,相关的存储过程,函数,同义词都会失效。可以通过更为灵活的方式来reset sequence.
一种方式相对直接,简洁。就是修改sequence的属性,把increment的值调高点,这样每次递增的幅度就大,然后循环递增,知道递增的值接近目标值,然后修改Increment的值为1.
pl/sql的实现如下,比如我们要修改account_1sq的值,把它修改为10001000,就可以这样来修改。
alter sequence ACCOUNT_1SQ increment by 69 nocache;
alter sequence ACCOUNT_1SQ increment by 1 nocache;
declare
  LastValue integer;
begin
  loop
    select ACCOUNT_1SQ.currval into LastValue from dual;
    exit when LastValue >= 10001000 - 1;
    select ACCOUNT_1SQ.nextval into LastValue from dual;
  end loop;
end;
/
alter sequence ACCOUNT_1SQ increment by 1 cache 20;

当然了这种方式还是存在不少的缺点。
一来是耦合度较高。在代码里面嵌入了太多的细节。pl/sql脚本从头到尾都是account_1sq相关。
reset的值和递增的幅度也得考虑周到。要不就可能出问题,导致reset的值达不到要求。
如果要修改序列的一些属性,在最后需要恢复。所以我们得时刻记得sequence的细节信息。

可以使用下面的改进脚本来修复上面的不足。这个脚本需要几个参数。对象类型(比如sequence或者table),对象名称(sequence的名称),sequence的值(需要修改的值)
WHENEVER SQLERROR EXIT 5
DEFINE OBJTYPE="&1"
DEFINE OBJNAME="&2"
DEFINE SEQVALUE="&3"
SET ECHO OFF
SET FEEDBACK OFF
SET SERVEROUTPUT ON
SET LINESIZE 100
SET PAGESIZE 0
SET TERMOUT ON
SET VERIFY OFF


DECLARE CURSOR C1 IS
 SELECT
        DISTINCT SQ.SEQUENCE_NAME
   FROM
        USER_SEQUENCES    SQ,
        USER_COL_COMMENTS TB
  WHERE 
        TB.COMMENTS = SQ.SEQUENCE_NAME
    AND UPPER('&OBJTYPE') = 'TABLE'
    AND TB.TABLE_NAME = UPPER('&OBJNAME') 
 UNION
 SELECT
       UPPER('&OBJNAME') SEQUENCE_NAME
   FROM
       DUAL
  WHERE
       UPPER('&OBJTYPE') = 'SEQUENCE' ;
sql_tab_name  USER_COL_COMMENTS.TABLE_NAME%TYPE ;
sql_col_name  USER_COL_COMMENTS.COLUMN_NAME%TYPE ;
sql_seq_name  USER_SEQUENCES.SEQUENCE_NAME%TYPE ;
old_val       NUMBER := 0;
new_val       NUMBER := 0;
max_val       NUMBER := 0;
seq_max_val   NUMBER := 0;
old_min_val   NUMBER := 0;
old_inc       NUMBER := 0;
new_inc       NUMBER := 0;
seq_cur       INTEGER ;
seq_fld       INTEGER ;
seq_val       INTEGER ;
seq_cur_rows  INTEGER ;
seq_fld_rows  INTEGER ;
seq_val_rows  INTEGER ;
found_seq_ind CHAR(1) := 'Y' ;
old_new_diff  INTEGER ;
seq_cycle     CHAR(1) := 'N' ;
BEGIN
DBMS_OUTPUT.ENABLE(2000000);
OPEN C1;
LOOP
FETCH C1 INTO
sql_seq_name ;
EXIT WHEN C1%NOTFOUND ;
BEGIN
found_seq_ind := 'Y' ;
SELECT
      MIN_VALUE,
      MAX_VALUE,
      INCREMENT_BY,
      CYCLE_FLAG
  INTO
      old_min_val,
      seq_max_val,
      old_inc,
      seq_cycle
  FROM
      USER_SEQUENCES
 WHERE
      SEQUENCE_NAME = sql_seq_name ;

EXCEPTION
WHEN NO_DATA_FOUND THEN
     DBMS_OUTPUT.PUT_LINE('Sequence: '||sql_seq_name||' is not exists.');
     found_seq_ind := 'N' ;
WHEN OTHERS THEN
     EXIT ;
END ;

------------------------------------------------------------
--Get Max val from all tables that related to this sequence.
------------------------------------------------------------

IF ( found_seq_ind = 'Y' )
THEN
    IF ( UPPER('&OBJTYPE') = 'TABLE' )
    THEN
        seq_fld := DBMS_SQL.OPEN_CURSOR ;
        DBMS_SQL.PARSE (seq_fld, 'SELECT COLUMN_NAME,TABLE_NAME FROM USER_COL_COMMENTS '||
                                 ' WHERE COMMENTS '||' = '||':sql1 ',DBMS_SQL.V7);
        DBMS_SQL.BIND_VARIABLE(seq_fld, 'sql1' ,sql_seq_name);
        DBMS_SQL.DEFINE_COLUMN (seq_fld, 1, sql_col_name, 30 );
        DBMS_SQL.DEFINE_COLUMN (seq_fld, 2, sql_tab_name, 30 );
        seq_fld_rows := DBMS_SQL.EXECUTE (seq_fld);
        max_val := old_min_val ;

        LOOP
        
            IF DBMS_SQL.FETCH_ROWS (seq_fld) > 0
            THEN
                DBMS_SQL.COLUMN_VALUE ( seq_fld, 1, sql_col_name ) ;
                DBMS_SQL.COLUMN_VALUE ( seq_fld, 2, sql_tab_name ) ;
                seq_val := DBMS_SQL.OPEN_CURSOR ;
                DBMS_SQL.PARSE (seq_val, 'SELECT GREATEST(NVL(MAX('||sql_col_name||'),0),'||max_val||') FROM '||
                                         sql_tab_name|| ' WHERE '||sql_col_name||
                                         ' NOT IN (999999999,888888888) ',DBMS_SQL.V7);
                DBMS_SQL.DEFINE_COLUMN (seq_val, 1, max_val);
                seq_val_rows := DBMS_SQL.EXECUTE (seq_val);
        
                IF DBMS_SQL.FETCH_ROWS (seq_val) > 0
                THEN
                    DBMS_SQL.COLUMN_VALUE ( seq_val, 1, max_val ) ;
                END IF ;
                DBMS_SQL.CLOSE_CURSOR (seq_val) ;
            ELSE
                DBMS_SQL.CLOSE_CURSOR (seq_fld) ;
                EXIT ;
            END IF ;
        
        END LOOP ;
ELSE
    max_val := &SEQVALUE ;
END IF ;

------------------------------------------------------------
-- Foreach sequence get the nextvalue.                    --
------------------------------------------------------------

seq_cur := DBMS_SQL.OPEN_CURSOR ;
DBMS_SQL.PARSE (seq_cur, 'SELECT '||sql_seq_name||'.NEXTVAL FROM DUAL',DBMS_SQL.V7);
DBMS_SQL.DEFINE_COLUMN (seq_cur, 1, old_val );
seq_cur_rows := DBMS_SQL.EXECUTE (seq_cur);

IF DBMS_SQL.FETCH_ROWS (seq_cur) > 0
THEN
    DBMS_SQL.COLUMN_VALUE ( seq_cur, 1, old_val );
    DBMS_SQL.CLOSE_CURSOR (seq_cur);
ELSE
    DBMS_SQL.CLOSE_CURSOR (seq_cur);
END IF ;

------------------------------------------------------------
-- Select sequences differences.                          --
------------------------------------------------------------

IF ( max_val > old_val )
THEN
    old_new_diff :=  max_val - old_val ;
ELSE
    old_new_diff :=  max_val - ( old_val - old_min_val  );
END IF;


------------------------------------------------------------
-- Foreach sequence set the nextvalue with the new increment.
------------------------------------------------------------
IF ( old_new_diff 0 ) AND ( (old_new_diff + old_val) THEN
    ---------------------------------------------
    --setting NOCYCLE for the sequence allow us
    --to set sequence on MIN_VAL.
    ---------------------------------------------
    IF ( seq_cycle = 'Y' )
    THEN
        seq_cur := DBMS_SQL.OPEN_CURSOR ;
        DBMS_SQL.PARSE (seq_cur, 'ALTER SEQUENCE '||sql_seq_name||' NOCYCLE ',DBMS_SQL.V7);
        seq_cur_rows := DBMS_SQL.EXECUTE (seq_cur);
        DBMS_SQL.CLOSE_CURSOR (seq_cur);
    END IF;
    ---------------------------------------------
    --set the nextvalue with the new increment.
    ---------------------------------------------
    seq_cur := DBMS_SQL.OPEN_CURSOR ;
    DBMS_SQL.PARSE (seq_cur, 'ALTER SEQUENCE '||sql_seq_name||' INCREMENT BY '||old_new_diff||' ',DBMS_SQL.V7);
    seq_cur_rows := DBMS_SQL.EXECUTE (seq_cur);
    DBMS_SQL.CLOSE_CURSOR (seq_cur);


    ---------------------------------------------
    --Get the nextvalue with the new increment.
    ---------------------------------------------
    seq_cur := DBMS_SQL.OPEN_CURSOR ;
    DBMS_SQL.PARSE (seq_cur, 'SELECT '||sql_seq_name||'.NEXTVAL FROM DUAL',DBMS_SQL.V7);
    DBMS_SQL.DEFINE_COLUMN (seq_cur, 1, new_val );
    seq_cur_rows := DBMS_SQL.EXECUTE (seq_cur);
    IF DBMS_SQL.FETCH_ROWS (seq_cur) > 0
    THEN
        DBMS_SQL.COLUMN_VALUE ( seq_cur, 1, new_val );
        DBMS_SQL.CLOSE_CURSOR (seq_cur);
    ELSE
        DBMS_SQL.CLOSE_CURSOR (seq_cur);
    END IF ;


    DBMS_OUTPUT.PUT_LINE('Resetting sequence: '||sql_seq_name||' to: '||new_val);

    --------------------------------------------------
    --set the nextvalue with the original increment.
    --------------------------------------------------
    seq_cur := DBMS_SQL.OPEN_CURSOR ;
    DBMS_SQL.PARSE (seq_cur, 'ALTER SEQUENCE '||sql_seq_name||' INCREMENT BY '||old_inc||' ',DBMS_SQL.V7);
    seq_cur_rows := DBMS_SQL.EXECUTE (seq_cur);
    DBMS_SQL.CLOSE_CURSOR (seq_cur);

    ---------------------------------------------
    --setting original CYCLE for the sequence  --
    ---------------------------------------------


    IF ( seq_cycle = 'Y' )
    THEN
        seq_cur := DBMS_SQL.OPEN_CURSOR ;
        DBMS_SQL.PARSE (seq_cur, 'ALTER SEQUENCE '||sql_seq_name||' CYCLE ',DBMS_SQL.V7);
        seq_cur_rows := DBMS_SQL.EXECUTE (seq_cur);
        DBMS_SQL.CLOSE_CURSOR (seq_cur);
    END IF;

END IF ;

END IF;

END LOOP ;

CLOSE C1 ;

END;
/


执行脚本的时候,比如脚本名称为reset_seq.sql
可以这样执行。
@reset_seq.sql sequence account_1sq 10000010

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Sequence to Sequence学习资料
Sequence to Sequence学习资料
0 0
Long Story of Block - DISCARD
## Concept ### introduction to DISCARD DISCARD 的概念其实来自 SSD 设备。我们知道由于 flash 存储介质的特性,SSD 设备中的一个 block 只支持 write、erase 操作,而不支持 overwrite 操作。对于一个已经被 write 过的 block,如果需要向这个 block 写入新的数据,就必须先对该 block
0 0
1140. Look-and-say Sequence (20)
#include #include #include using namespace std; string get_num(string &s){ string sa = ""; for(int i = 0; i < s.
662 0
[20160501]dg参数STANDBY_MAX_DATA_DELAY
[20160501]dg参数STANDBY_MAX_DATA_DELAY.txt --11G dg 支持Active Data Guard应用,就是在dg上只读模式下应用日志.
722 0
[ACMcoder] Number Sequence
Problem Description A number sequence is defined as follows: f(1) = 1, f(2) = 1, f(n) = (A * f(n - 1) + B * f(n - 2)) mod 7. Given A, B, and n, you are to calculate the value of f(n). I
834 0
sequence的MAXVALUE、NOMAXVALUE和CYCLE、NOCYCLE参数
Oracle的sequence通常可以用来作自增列,例如主键,因为他可以自动累加并且唯一。创建sequence时有几个参数,MAXVALUE、MINVALUE、CYCLE、NOCYCLE、NOMAXVALUE等,这几个参数我们日常使用的时候可能不会太在意,但有些细节问题碰到的时候就需要明确下。
488 0
+关注
jeanron100
Oracle ACE,《Oracle DBA工作笔记》作者 现就职于国内某互联网公司,擅长数据管理,数据迁移,性能优化,目前专注于开源技术,运维自动化和性能优化。
文章
问答
文章排行榜
最热
最新
相关电子书
更多
Tracking-Ransomware-End-To-End
立即下载
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载