Oracle -- 批量更新sequence的存储

前言：

ORACLE的序列（SEQUENCE）A SCHEMA OBJECT THAT GENERATES A SERIAL LIST OF UNIQUE NUMBERS FOR TABLE COLUMNS. TO USE A SEQUENCE ,YOUR SCHEMA MUST CONTAIN THE SEQUENCE OR YOU MUST HAVE BEEN GRANTED THE SELECT OBJECT PRIVILEGE FOR ANOTHER USER IS SEQUENCE. ONCE A   SEQUENCE   IS   DEFINED,IT CAN BE   ACCESSED   AND   INCREMENTED   BY   MULTIPLE   USERS (WHO HAVE   SELECT   OBJECT   PRIVILEGE   FOR   THE   SEQUENCE   CONTAINING   THE   SEQUENCE ) WITH   NO   WAITING. THE   DATABASE   DOES   NOT   WAIT   FOR   A   TRANSACTION   THAT HAS INCREMENTED A   SEQUENCE   TO   COMPLETE   BEFORE   THAT   SEQUENCE   CAN BE INCREMENTED AGAIN.

简单说：序列一般用于自动递增生成主键值 ..

但是否有一些情况会导致调用SEQ_....NEXTVAL时大于主键最大值呢？

场景：

  主键表 -> T表 '100W'数据同步到T1表（同步：TRUNCATE/DELETE或IMPDP...等方式[手动改数据]）

  例如： T表对应SEQ_T.NEXTVAL= 100W；

        T1表对应SEQ_T.NEXTVAL= 10W；

        TRUNCATE TABLE T1;

        INSERT TABLE T1 SELECT * FROM T;

        数据同步但T1表对应SEQ_T.NEXTVAL= 10W序列不变；

        此时T1调用序列INSERT到T1表时就会报错 ( ORA-00001 : 违反唯一约束条件 (LOTTERY.PK_T1))

        (若批量同步很多表就可能会引起很多表序列需要更新成主键最大值+ 1才能保证不报错

        (可以用 把源库的SEQUENCE同步过来①或者如下存储解决② ))

 

   ① PLSQL 工具的COMPARE   USER   OBJECTS可以解决SEQUENCE序列对象同步到其他环境...在此就不细说了

 

   ②我们环境序列一般都是由SEQ_表名字组成.所以写批量更新存储的时候比较方便~

  如下存储针对常用于以上场景的环境,,生产环境不批量导新数据/同步用户数据/表数据 就很少用到... 也可只提供参考...

--批量更新序列存储-- CREATE OR REPLACE PROCEDURE P_SYNCSEQ(USERNAME VARCHAR2 /*传入要检查/更新序列的用户*/ ) IS   /*   **@AUTHOR 毛海晴   ORACLE 批量更新SEQUENCE   注释：     批量更新SEQUENCE,     更新序列下一个值 = 主键最大值+1      ---序列创建时，属性NOMAXVALUE=最大值是10的28次方   思路：     1、找到每个表主键列 且在该表主键最大值是什么？     2、找到表对应SEQUENCE值 与 表主键最大值去对比。     如果SEQUENCE 下一个值大于表主键最大值就不做更新；     否则需要进行更新(2中更新方式)     1）删除SEQUENCE ，创建新序列开始值为表主键最大值+1；   --本文选择此方案...嘿嘿~     (坏处：赶好在DROP SEQUENCE..而程序也恰巧调用依赖它的函数和存储过程将失效      但 后续CREATE SEQUENCE了，再调用了会重新编译 调用..不会报错....有实验过哦~)     2）ALTER SEQUENCE .. INCREMENT BY .. NOCACHE;        SELECT ...NEXTVAL FROM DUAL;        ALTER SEQUENCE .. INCREMENT BY 1 CACHE 20;     .... sequence.nextval其实也可以用user_sequences.last_number字段实现..由于最早存储就这样的写就没改~...谅解~     SEQUENCE和表名长度最大限制是30     SEQUENCE规范的名字SEQ_+表名字    -- 此处规范只是管理维护方便而已 并不是非要这样要求     如果表名长度大小大于26 加上"SEQ_"就大于了SEQUENCE长度限制的30     若表名长度大于26，那对应序列肯定不是规范命名(SEQ_表名字),再由于这样的序列并不多,所以将这些一一处理     在更新前可先注释掉EXECUTE IMMEDIATE，先作下测试看下效果，免得EXECUTE IMMEDIATE DROP .后创建报错，导致在调用 序列不会创建,也校验不到序列       所需权限：     -- 创建序列权限 --     -- Grant/Revoke system privileges      grant create sequence to LOTTERY;     --查询权限--     -- Grant/Revoke object privileges      grant select on DBA_CONSTRAINTS to LOTTERY;     grant select on DBA_CONS_COLUMNS to LOTTERY;     grant select on DBA_SEQUENCES to LOTTERY;     grant select on DBA_TABLES to LOTTERY;     grant select on DBA_TAB_COLUMNS to LOTTERY;       --或者--       -- Grant/Revoke system privileges        grant select any dictionary to LOTTERY;   */   -- 变量   MAX_ID             NUMBER(12 );    P_SEQ_NUM          NUMBER(12 );   P_TABLE_NAME       VARCHAR2(50 );   P_COLUMN           VARCHAR2(50 );   P_SEQUENCE         VARCHAR2(50 );   P_SQL              VARCHAR2(500 );   P_SEQ_SQL          VARCHAR2(5000 );   P_SQL_SEQ          VARCHAR2(30000 );   P_NEW_COUNT        NUMBER(12 );   --查询表长度小于26 的表/序列   --游标   CURSOR C_CONS IS -- 查询表长度小于26 的表/序列     SELECT T1.TABLE_NAME     TABLE_NAME,            T1.COLUMN_NAME    COLUMN_NAME,            T1.SEQUENCE_NAME1 SEQUENCE_NAME       FROM ((SELECT C.TABLE_NAME,                     CASE                       WHEN C1.DATA_TYPE = 'NUMBER' THEN                        C.COLUMN_NAME                       ELSE                        'TO_NUMBER(' || C.COLUMN_NAME || ')'                     END COLUMN_NAME,                     C.SEQUENCE_NAME1                FROM (SELECT C.TABLE_NAME,                             C.COLUMN_NAME,                             'SEQ_' || C.TABLE_NAME SEQUENCE_NAME1                        FROM DBA_CONS_COLUMNS C --用户的约束对应的表列信息                       WHERE C.OWNER = UPPER (USERNAME)                         AND (C.CONSTRAINT_NAME, C.TABLE_NAME) IN                             ( SELECT S.CONSTRAINT_NAME, S.TABLE_NAME                                FROM DBA_CONSTRAINTS S --用户的对象约束信息                               WHERE S.OWNER = (UPPER (USERNAME))                                 AND S.CONSTRAINT_TYPE = 'P' /*CONSTRAINT_TYPE: P：主键,R：外键,C：非空约束/CHECK;*/                              ---若主键是由多字段'ID1,ID2',该查询会显示成2行分别为(T.ID1 SEQ_T和T.ID2 SEQ_T)                              )                      --..一个序列被2个表/2字段共用...可以用如下方式进行                      UNION                      SELECT 'ETL_CS_CUST_INFO_MID' ,                             'BATCH_NO', --若数据为VARCHAR类型需要TO_NUMBER转换来取MAX(字段)                             'SEQ_ETL_CS_CUST_INFO_MID'                        FROM DUAL) C,                     DBA_TAB_COLUMNS C1               WHERE C1.OWNER = UPPER (USERNAME)                 AND C1.COLUMN_NAME = C.COLUMN_NAME                 AND C1.TABLE_NAME = C.TABLE_NAME)            /**            ---提供表长度大于26 的表名字/序列  ..再关联DBA_CONS_COLUMNS找到对应的主键字段..和表长度小于26部分的查询进行UNION ALL            CS_BEAR_ALLOWANCE_AND_INJ_DET ---&gt SEQ_CS_BEAR_ALLOWANCE_INJ_DET            CS_BEAR_ALLOWANCE_AND_INJ_DETS ---&gt SEQ_CS_BEAR_ALLOWANCE_INJ_DETS...等            */             UNION ALL (SELECT M1.TABLE_NAME, COLUMN_NAME, M2.SEQUENCE_NAME                          FROM (SELECT LENGTH(C.TABLE_NAME) AA,                                       C.TABLE_NAME,                                       C.COLUMN_NAME                                  FROM DBA_CONS_COLUMNS C                                 WHERE C.OWNER = UPPER (USERNAME)                                   AND (C.CONSTRAINT_NAME, C.TABLE_NAME) IN                                       ( SELECT S.CONSTRAINT_NAME, S.TABLE_NAME                                          FROM DBA_CONSTRAINTS S                                         WHERE S.OWNER = UPPER (USERNAME)                                           AND S.CONSTRAINT_TYPE = 'P' )) M1 --如果不限制主键 可能找到NOT NULL的列                          JOIN (SELECT TABLE_NAME, SEQUENCE_NAME                                 FROM (SELECT 'CS_BEAR_ALLOWANCE_AND_INJ_DET' TABLE_NAME,                                              'SEQ_CS_BEAR_ALLOWANCE_INJ_DET' SEQUENCE_NAME                                         FROM DUAL                                       UNION ALL                                       SELECT 'CS_BEAR_ALLOWANCE_AND_INJ_DETS' ,                                              'SEQ_CS_BEAR_ALLOWANCE_INJ_DETS'                                         FROM DUAL)) M2                            ON M1.TABLE_NAME = M2.TABLE_NAME                         WHERE AA > 26 )) T1,            DBA_SEQUENCES SQ, --(列出的序列是否在库中存在)            DBA_TABLES T --(列出的表是否在库中存在)..由于环境不同用到的序列可能也是不同的.若不加可能会报错      WHERE SQ.SEQUENCE_NAME = T1.SEQUENCE_NAME1        AND T.TABLE_NAME = T1.TABLE_NAME        AND SQ.SEQUENCE_OWNER = UPPER (USERNAME)        AND T.OWNER = UPPER (USERNAME);   ----------------------以上查询表/对应序列/主键字段 -------------   ----------------------以下开始判断序列是否需要更新 ------------- BEGIN   ----------------------SEQUENCE判断更新语句 -----------------------------   --~~注释：DBMS_OUTPUT.PUT_LINE(XX)是将这个结果或者查询显示出来   --EXECUTE IMMEDIATE XX; --执行XX的查询   --开始 SEQUENCE.nextval和主键最大值 做比较..   FOR P_C_CONS IN C_CONS LOOP     --利用C_CONS游标对应列值     P_TABLE_NAME := P_C_CONS.TABLE_NAME;     P_COLUMN     := P_C_CONS.COLUMN_NAME;     P_SEQUENCE   := P_C_CONS.SEQUENCE_NAME;       ---每次循环都赋值0 ..     MAX_ID := 0;     --查询表主键中最大值     P_SQL := 'SELECT MAX(' || P_COLUMN || ')  FROM  ' || P_TABLE_NAME;     --USING MAX_ID     EXECUTE IMMEDIATE P_SQL       INTO MAX_ID;       -- 查询序列.nextval值     P_SEQ_SQL := 'SELECT ' || P_SEQUENCE || '.NEXTVAL FROM DUAL' ;     --USING P_SEQ_SQL       EXECUTE IMMEDIATE P_SEQ_SQL       INTO P_SEQ_NUM;       ---SEQUENCE.nextval和主键最大值 做比较..（如果SEQUENCE.nextval     IF P_SEQ_NUM < MAX_ID THEN               /* DBMS_OUTPUT.PUT_LINE( 'DROP SEQUENCE ' || P_SEQUENCE); */           --删除原来不正确的SEQUENCE       EXECUTE IMMEDIATE 'DROP SEQUENCE ' || P_SEQUENCE;       P_NEW_COUNT := 0;       P_NEW_COUNT := MAX_ID + 1; --&gt当前主键最大值+1 才是SEQUENCE要更新值，才保证主键值再加入的时候不冲突；       P_SQL_SEQ   := 'CREATE SEQUENCE ' || P_SEQUENCE ||                      ' MINVALUE 1 NOMAXVALUE START WITH ' || P_NEW_COUNT ||                      '  INCREMENT BY 1 CACHE 20'; --创建正确的SEQUENCE语句             /* 打印序列创建语句 */       /*DBMS_OUTPUT.PUT_LINE('CREATE SEQUENCE ' || P_SEQUENCE ||       ' MINVALUE 1 NOMAXVALUE START WITH ' ||       P_NEW_COUNT || '  INCREMENT BY 1 CACHE 20');*/           --执行创建序列语句       EXECUTE IMMEDIATE P_SQL_SEQ;             --打印错 错误序列对应的表、序列由之前值更新到现在的值；       DBMS_OUTPUT.PUT_LINE( '错误序列对应的表：' || P_TABLE_NAME || '              ' || P_SEQUENCE || ' 由' ||                            P_SEQ_NUM || '更新到' || P_NEW_COUNT || '；' );     END IF ;   END LOOP; END P_SYNCSEQ;

--使用步骤：

--  编辑存储..--&gt调用存储（Call change_varchar2(username => 'u1' );或者begin..传值.等）

--输出结果：

--DROP SEQUENCE SEQ_T1    --本文中存储打印部分注释掉了.若想看其效果将注释/**/打开.

--CREATE SEQUENCE SEQ_T1 MINVALUE 1 NOMAXVALUE START WITH 1004   INCREMENT BY 1 CACHE 20   --本文中存储打印部分注释掉了.若想看其效果将注释/**/打开.

--错误序列对应的表：T1

             SEQ_T1 由1000更新到1004；

祝好~

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28602568/viewspace-1266101/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28602568/viewspace-1266101/