根据当前值重建sequence

大家知道oracle中的序列与表之前没有必然的关系，一个序列可以给一个表使用也可以给另一个一使用。

所以最好增加字典表及文档来保存序列与表之间的对应关系。

那最好这些都没有怎么办呢？

有一个系统中是根据触发器来调用序列，通过触发器我们可以找到表与序列之间的对应关系

SELECT NAME,
       MAX(CASE referenced_type
             WHEN 'TABLE' THEN
              referenced_name
           END) AS tbl,
       MAX(CASE referenced_type
             WHEN 'SEQUENCE' THEN
              referenced_name
           END) AS seq
  FROM user_dependencies seq
 WHERE TYPE = 'TRIGGER'
   AND NAME IN (SELECT NAME
                  FROM user_source
                 WHERE TYPE = 'TRIGGER'
                   AND upper(text) LIKE upper('%.nextval%'))
   AND referenced_type IN ('TABLE', 'SEQUENCE')
 GROUP BY NAME
HAVING COUNT(*) = 2
/

NAME       TBL        SEQ
---------- ---------- ----------
TRG_EMP2   EMP2       SEQ_EMP2

有了这个对应关系后就可以查找表对应的主键列

    WITH seq AS
     (SELECT /*+ materialize */
       NAME,
       MAX(CASE referenced_type
             WHEN 'TABLE' THEN
              referenced_name
           END) AS tbl,
       MAX(CASE referenced_type
             WHEN 'SEQUENCE' THEN
              referenced_name
           END) AS seq
        FROM user_dependencies seq
       WHERE TYPE = 'TRIGGER'
         AND NAME IN (SELECT NAME
                        FROM user_source
                       WHERE TYPE = 'TRIGGER'
                         AND upper(text) LIKE upper('%.nextval%'))
         AND referenced_type IN ('TABLE', 'SEQUENCE')
       GROUP BY NAME
      HAVING COUNT(*) = 2)
    SELECT trg,seq, tbl, column_name
      FROM (SELECT seq.seq,
                   seq.tbl,
                   seq.NAME AS trg,
                   ic.column_name,
                   COUNT(*) over(PARTITION BY ic.table_name) AS cnt
              FROM seq
             INNER JOIN user_constraints c
                ON c.table_name = seq.tbl
             INNER JOIN user_ind_columns ic
                ON ic.index_name = c.index_name
             INNER JOIN user_tab_cols col
                ON col.table_name = ic.table_name
               AND col.column_name = ic.column_name
             WHERE constraint_type = 'P')
     WHERE cnt = 1;

TRG        SEQ        TBL        COLUMN_NAME
---------- ---------- ---------- ---------------
TRG_EMP2   SEQ_EMP2   EMP2       EMPNO

因为使用序列的表一般都是单列主键，所以这儿没有判断主键列的个数。

对应信息都得到后就可以重建seq了

DECLARE
  CURSOR cur_seq IS
    WITH seq AS
     (SELECT /*+ materialize */
       NAME,
       MAX(CASE referenced_type
             WHEN 'TABLE' THEN
              referenced_name
           END) AS tbl,
       MAX(CASE referenced_type
             WHEN 'SEQUENCE' THEN
              referenced_name
           END) AS seq
        FROM user_dependencies seq
       WHERE TYPE = 'TRIGGER'
         AND NAME IN (SELECT NAME
                        FROM user_source
                       WHERE TYPE = 'TRIGGER'
                         AND upper(text) LIKE upper('%.nextval%'))
         AND referenced_type IN ('TABLE', 'SEQUENCE')
       GROUP BY NAME
      HAVING COUNT(*) = 2)
    SELECT trg,seq, tbl, column_name
      FROM (SELECT seq.seq,
                   seq.tbl,
                   seq.NAME AS trg,
                   ic.column_name,
                   COUNT(*) over(PARTITION BY ic.table_name) AS cnt
              FROM seq
             INNER JOIN user_constraints c
                ON c.table_name = seq.tbl
             INNER JOIN user_ind_columns ic
                ON ic.index_name = c.index_name
             INNER JOIN user_tab_cols col
                ON col.table_name = ic.table_name
               AND col.column_name = ic.column_name
             WHERE constraint_type = 'P')
     WHERE cnt = 1;
  v_trg   VARCHAR2(50);
  v_seq   VARCHAR2(50);
  v_tbl   VARCHAR2(50);
  v_col   VARCHAR2(50);
  v_sql   VARCHAR2(4000);
  v_maxid NUMBER;
  v_nextval NUMBER;
BEGIN
  OPEN cur_seq;
  LOOP
    FETCH cur_seq
      INTO v_trg,v_seq, v_tbl, v_col;
    EXIT WHEN cur_seq%NOTFOUND;  
    v_sql := 'select max(' || v_col || ') from ' || v_tbl;
    --dbms_output.put_line(v_sql);
    EXECUTE IMMEDIATE v_sql INTO v_maxid;
    v_maxid := NVL(v_maxid,0) + 1;
    v_sql := 'drop sequence ' || v_seq;
    --dbms_output.put_line(v_sql);
    EXECUTE IMMEDIATE v_sql;
    v_sql := 'create sequence ' || v_seq || ' start with ' || to_char(v_maxid);
    --dbms_output.put_line(v_sql);
    EXECUTE IMMEDIATE v_sql;
    v_sql := 'alter trigger ' || v_trg || ' compile';
    --dbms_output.put_line(v_sql);
    EXECUTE IMMEDIATE v_sql;
    NULL;
  END LOOP;
  CLOSE cur_seq;
END;