调整sequence大小之脚本

set feedback off
set define off
set head off
set timing off

--删除seq脚本
SELECT 'DROP SEQUENCE '||F.SEQUENCE_NAME ||';' FROM user_sequences f;

--获取创建seq脚本
select 'select ''create sequence SEQ_'|| t.TABLE_NAME||'_ID start with ''||'||
'decode((select max('||t.COLUMN_NAME||') from '||t.TABLE_NAME||'),null,1,(select max('||t.COLUMN_NAME||') from '|| t.TABLE_NAME||')+1)||'';'' from dual;'
from cols t where t.COLUMN_ID = 1 and
t.TABLE_NAME in (
select REPLACE(replace(f.sequence_name,'SEQ_',''),'_ID','') from user_sequences f
);

--执行上面跑出来的脚本
...............
...............

--找出不符合 seq_table_name_id 方式创建的序列
SELECT 'DROP SEQUENCE '||F.SEQUENCE_NAME ||';' FROM user_sequences f
WHERE F.SEQUENCE_NAME NOT IN (SELECT 'SEQ_'||TNAME||'_ID' FROM TAB);

--统计表数据量总数的脚本
select 'select '''||tname||''', count(*) from '||tname||';' from tab where tname not like 'TMP%'
AND TNAME NOT LIKE 'BIN%' ORDER BY tname;

--保存以备用