Dont't recreate the sequence! You would invalidate all independent objects and l

原文出处：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