-- 得到每个序列的创建语句: eygle@SZTYORA> create sequence t_seq;序列已创建。eygle@SZTYORA> select dbms_metadata.get_ddl('SEQUENCE','T_SEQ') FROM DUAL;DBMS_METADATA.GET_DDL('SEQUENCE','T_SEQ') -------------------------------------------------------------------------------- CREATE SEQUENCE "EYGLE"."T_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE
-- 再根据 user_sequences数据字典,去查看每个序列的下一个值是什么, -- 然后用其下一下值去更新上面得到的创建序列的语句中的 start with 部分!
-- 执行下面的语句,将在C盘根目录下生成sequence_code.txt文件, -- 这个文件就是所有当前用户下创建序列的代码: spool c:\sequence_code.txt;declare v_sequence varchar2(4000); v_nextval number(38,0); begin for i in (select sequence_name from user_sequences) loop select dbms_metadata.get_ddl('SEQUENCE',i.sequence_name,user) into v_sequence from dual; execute immediate 'select '||i.sequence_name||'.'||'nextval from dual' into v_nextval; v_sequence := substr(v_sequence,1,instr(v_sequence,'START WITH',1,1)+length('START WITH'))||' '||to_char(v_nextval)||' '|| substr(v_sequence,instr(v_sequence,'CACHE')); dbms_output.put_line(v_sequence); end loop; end; /spool off;
-- 9楼修正一下( 创建序列语句的末尾加个分号(;) ): spool c:\sequence_code.txt;declare v_sequence varchar2(4000); v_nextval number(38,0); begin for i in (select sequence_name from user_sequences) loop select dbms_metadata.get_ddl('SEQUENCE',i.sequence_name,user) into v_sequence from dual; execute immediate 'select '||i.sequence_name||'.'||'nextval from dual' into v_nextval; v_sequence := substr(v_sequence,1,instr(v_sequence,'START WITH',1,1)+length('START WITH'))||' '||to_char(v_nextval)||' '|| substr(v_sequence,instr(v_sequence,'CACHE'))||';'; dbms_output.put_line(v_sequence); end loop; end; /spool off;
eygle@SZTYORA> create sequence t_seq;序列已创建。eygle@SZTYORA> select dbms_metadata.get_ddl('SEQUENCE','T_SEQ') FROM DUAL;DBMS_METADATA.GET_DDL('SEQUENCE','T_SEQ')
-------------------------------------------------------------------------------- CREATE SEQUENCE "EYGLE"."T_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE
20 NOORDER NOCYCLE
-- 然后用其下一下值去更新上面得到的创建序列的语句中的 start with 部分!
-- 这个文件就是所有当前用户下创建序列的代码:
spool c:\sequence_code.txt;declare
v_sequence varchar2(4000);
v_nextval number(38,0);
begin
for i in (select sequence_name from user_sequences) loop
select dbms_metadata.get_ddl('SEQUENCE',i.sequence_name,user) into v_sequence from dual;
execute immediate 'select '||i.sequence_name||'.'||'nextval from dual' into v_nextval;
v_sequence := substr(v_sequence,1,instr(v_sequence,'START WITH',1,1)+length('START WITH'))||' '||to_char(v_nextval)||' '||
substr(v_sequence,instr(v_sequence,'CACHE'));
dbms_output.put_line(v_sequence);
end loop;
end;
/spool off;
spool c:\sequence_code.txt;declare
v_sequence varchar2(4000);
v_nextval number(38,0);
begin
for i in (select sequence_name from user_sequences) loop
select dbms_metadata.get_ddl('SEQUENCE',i.sequence_name,user) into v_sequence from dual;
execute immediate 'select '||i.sequence_name||'.'||'nextval from dual' into v_nextval;
v_sequence := substr(v_sequence,1,instr(v_sequence,'START WITH',1,1)+length('START WITH'))||' '||to_char(v_nextval)||' '||
substr(v_sequence,instr(v_sequence,'CACHE'))||';';
dbms_output.put_line(v_sequence);
end loop;
end;
/spool off;