原文出处：https://community.oracle.com/message/4016489#4016489
Dont't recreate the sequence! You would invalidate all independent objects and lose all privileges granted for the sequences.
Instead try this:
SQL>CREATE SEQUENCE SEQ_TEST
2 START WITH 1
3 INCREMENT BY 1
4 MINVALUE 1
5 MAXVALUE 9999999;
Sequence created.
SQL>
SQL>SELECT SEQ_TEST.NEXTVAL
2 FROM all_objects WHERE ROWNUM < =10;
NEXTVAL
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
SQL>
SQL>DECLARE
2 Val NUMBER;
3 BEGIN
4 SELECT SEQ_TEST.CURRVAL
5 INTO Val
6 FROM DUAL;
7
8 EXECUTE IMMEDIATE 'ALTER SEQUENCE SEQ_TEST INCREMENT BY ' || TO_CHAR(1 - Val);
9
10 SELECT SEQ_TEST.NEXTVAL
11 INTO Val
12 FROM DUAL;
13
14 EXECUTE IMMEDIATE 'ALTER SEQUENCE SEQ_TEST INCREMENT BY 1';
15
16 DBMS_OUTPUT.put_line('New value of SEQ_TEST is ' || TO_CHAR(Val));
17 END;
18 /
New value of SEQ_TEST is 1
CREATE OR REPLACE PROCEDURE do_job IS
BEGIN
drop sequence Srno;
create sequence SrNo
minvalue 1
maxvalue 9999999
start with 1
increment by 1
Order;
COMMIT;
END do_job;
Declare
v_job number;
begin
dbms_job_submit
(v_job,'do_job',
last_day(sysdate)+1,
to_char(trunc(last_day(sysdate)+1)+(6/24)));
hth, Urs
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
