前言:
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次方 思路: SEQUENCE和表名长度最大限制是30 MAX_ID NUMBER(12 ); T1.COLUMN_NAME COLUMN_NAME, T1.SEQUENCE_NAME1 SEQUENCE_NAME FROM ((SELECT C.TABLE_NAME, ELSE FROM (SELECT C.TABLE_NAME, 'SEQ_' || C.TABLE_NAME SEQUENCE_NAME1 FROM DBA_CONS_COLUMNS C --用户的约束对应的表列信息 FROM DBA_CONSTRAINTS S --用户的对象约束信息 UNION 'SEQ_ETL_CS_CUST_INFO_MID' WHERE C1.OWNER = UPPER (USERNAME) AND C1.TABLE_NAME = C.TABLE_NAME) /** FROM DBA_CONSTRAINTS S FROM DUAL)) M2 WHERE AA > 26 )) T1, WHERE SQ.SEQUENCE_NAME = --EXECUTE IMMEDIATE XX; --执行XX的查询 --开始 SEQUENCE.nextval和主键最大值 做比较.. P_COLUMN := P_C_CONS.COLUMN_NAME; P_SEQUENCE := EXECUTE IMMEDIATE 'DROP SEQUENCE ' || P_SEQUENCE; /*DBMS_OUTPUT.PUT_LINE('CREATE SEQUENCE ' || P_SEQUENCE DBMS_OUTPUT.PUT_LINE( '错误序列对应的表:' || P_TABLE_NAME || ' |
--使用步骤:
-- 编辑存储..-->调用存储(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;