前言： oracle的序列（sequence）a schema object that generates a serial list of unique numbers for table columns.to use a
前言：
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))