新建序列的语句
declare
v_sql varchar2(200);
v_cnt number(10);
iCount number := 0; --查询结果
begin
for v_tab in (select table_name from user_tables t) loop
--查看表是否有ID字段
v_sql := 'select count(*) as num from USER_TAB_COLUMNS where TABLE_NAME = ''' ||
v_tab.table_name || ''' AND COLUMN_NAME = ''ID''';
Execute Immediate v_sql
into iCount;
IF iCount is not null and iCount > 0 THEN
v_sql := 'select nvl(max(ID),0) from ' || v_tab.table_name;
else
v_sql := 'select count(1) from ' || v_tab.table_name;
END IF;
execute immediate v_sql
into v_cnt;
dbms_output.put_line('drop sequence SEQ_' || v_tab.table_name || ';');
dbms_output.put_line('create sequence SEQ_' || v_tab.table_name ||
' minvalue 1 maxvalue 9999999999999999999999999999 start with ' ||
(v_cnt + 1) || ' increment by 1 cache 20;');
end loop;
end;
导出序列的语句
select 'create sequence '||sequence_name||
' minvalue '||min_value||
' maxvalue '||max_value||
' start with '||last_number||
' increment by '||increment_by||
(case when cache_size=0 then ' nocache' else ' cache '||cache_size end) ||';'
from user_sequences