ORACLENERD

Things I've Learned This Week

You can in fact DELETE from DBA_JOBS. Not that I had ever tried before this weekend, I just assumed it was a view (still might be with an INSTEAD OF trigger).

I also learned how to reset a sequence without dropping and recreating it. This was courtesy of my crazy DBA, oraclue.

Example:

SQL> CREATE SEQUENCE TEST_SEQ
  2    START WITH 10
  3    INCREMENT BY 10
  4    MINVALUE -1000;

Sequence created.

SQL> SELECT test_seq.nextval FROM dual CONNECT BY LEVEL <= 10;

   NEXTVAL
----------
        10
        20
        30
        40
        50
        60
        70
        80
        90
       100

10 rows selected.

SQL> SELECT * FROM user_sequences WHERE sequence_name = 'TEST_SEQ';

SEQUENCE  MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
-------- ---------- ---------- ------------ - - ---------- -----------
TEST_SEQ      -1000 1.0000E+27           10 N N         20         210

SQL> ALTER SEQUENCE TEST_SEQ INCREMENT BY -10;

Sequence altered.

SQL> SELECT * FROM user_sequences WHERE sequence_name = 'TEST_SEQ';

SEQUENCE  MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
-------- ---------- ---------- ------------ - - ---------- -----------
TEST_SEQ      -1000 1.0000E+27          -10 N N         20          90

SQL> SELECT test_seq.nextval FROM dual CONNECT BY LEVEL <= 10;

   NEXTVAL
----------
        90
        80
        70
        60
        50
        40
        30
        20
        10
         0

10 rows selected.

SQL> SELECT * FROM user_sequences WHERE sequence_name = 'TEST_SEQ';

SEQUENCE  MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
-------- ---------- ---------- ------------ - - ---------- -----------
TEST_SEQ      -1000 1.0000E+27          -10 N N         20        -110

SQL> ALTER SEQUENCE TEST_SEQ INCREMENT BY 10;

Sequence altered.

SQL> SELECT * FROM user_sequences WHERE sequence_name = 'TEST_SEQ';

SEQUENCE  MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
-------- ---------- ---------- ------------ - - ---------- -----------
TEST_SEQ      -1000 1.0000E+27           10 N N         20          10

SQL> SELECT test_seq.nextval FROM dual CONNECT BY LEVEL <= 10;

   NEXTVAL
----------
        10
        20
        30
        40
        50
        60
        70
        80
        90
       100

10 rows selected.