如何批量重置序列的最大值

–创建临时表
create table TAB_MAX(table_name varchar(200),column_name varchar(200),max_id int)
–将所有表对应的id的最大的值写入临时表
declare
v_clounmname varchar2(50);
v_tablename varchar2(100);
v_count int;
V_SQL VARCHAR2(200);
cursor v_tab_count
is
select
tab_name ,column_name ,‘select max(’||column_name||’) from '||tab_name
from
(
select
replace(sequence_name, ‘_seq’) TAB_NAME
from
USER_SEQUENCES
where
sequence_name like ‘kk%’
)
A join (select * from ALL_CONS_COLUMNS where owner = ‘KK’) b on a.tab_name = b.table_name
;
begin
OPEN v_tab_count;
LOOP
EXIT
WHEN v_tab_count%NOTFOUND;
FETCH v_tab_count INTO v_tablename, v_clounmname, V_SQL;
execute immediate v_sql into v_count;
insert into TAB_MAX values
(v_tablename, v_clounmname, v_count
);
commit;
END LOOP;
close v_tab_count;
end;

–拼接成修改序列号的sql语句
select ‘ALTER SEQUENCE ‘|| table_name||’_seq CURRENT VALUE ‘||max_id||’;’ from TAB_MAX where max_id is not null;

注：表名和序列名需要由一定的关联关系才能够进行处理，要根据实际情况来！！！