OracleBlog

Proactively maintaining your database ... something some people do only AFTER a problem of some kind.

Case in point, a customer recently asked us what sequences are being used as primary keys, what are their maximum values, and are any in danger of running out. Guess what motivated that investigation?

Most of this is rather trivial using only the ALL_SEQUENCES table.

1. What sequences are being used, and do they roll over or run out?

SELECT cycle_flag, sequence_name FROM ALL_SEQUENCES;

2. Which sequences are being used as primary keys?

That's trickier. See, even if you're using a sequence as a primary key, there's no way to tell by just looking at a table somewhere. The INSERT commands could be calling that sequence directly. Edit Or, it could be inserted by a trigger. I'm not aware of any easy, reliable way to determine if sequences are being used as primary keys without looking table by table.

However, generally that is what people like to use sequences for, so there's a good chance that all the sequences are being used as keys somewhere.

3. Which sequences are in danger of running out?

SELECT sequence_name, (max_value - last_number)/increment_by sequences_left
FROM ALL_SEQUENCES
ORDER BY sequences_left;

4. What happens when a sequence runs out?

Well that depends if its a roll-over or not.

CREATE SEQUENCE rollover_seq MINVALUE 1 START WITH 1 INCREMENT BY 1 MAXVALUE 3 CYCLE NOCACHE;
CREATE SEQUENCE runout_seq MINVALUE 1 START WITH 1 INCREMENT BY 1 MAXVALUE 3 NOCYCLE NOCACHE;

CREATE TABLE sequence_table (roll NUMBER(1), runout NUMBER(1));

Run this three times:
INSERT INTO sequence_table (roll, runout) VALUES (rollover_seq.NEXTVAL, runout_seq.NEXTVAL);

On the fourth time:
ORA-08004: sequence RUNOUT_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated

But the other one rolls over:
INSERT INTO sequence_table (roll, runout) VALUES (rollover_seq.NEXTVAL, 4);

scott@Robert> SELECT * FROM sequence_table;

      ROLL     RUNOUT
---------- ----------
         1          1
         2          2
         3          3
         2          4

Why is it 2,4 instead of 1,4? Well we "used up" the 1 on the misfire, so now we have a "gap." Every time NEXTVAL is "called", the sequence increments. CURRVAL doesn't. Of course, CURRVAL gives you last_number, which is one that's presumably already in use.

By the way, as usual, you can get some better examples, and a better reference, from Dan Morgan's library.

Those looking for an equivalent to AUTONUMBER in other RDBMS may find that using an Oracle sequence is close to the same functionality. Edit Just use the sequence's NEXTVAL as your insert for your key, or in a trigger, like so:

-- Won't work:
CREATE TABLE MyTable (seq_id NUMBER(1) DEFAULT rollover_seq.NEXTVAL);
ORA-00984: column not allowed here

-- Will work:
CREATE TABLE MyTable (seq_id NUMBER(1));

CREATE OR REPLACE TRIGGER trig_seq BEFORE INSERT ON MyTable
FOR EACH ROW
BEGIN
SELECT rollover_seq.NEXTVAL into :new.seq_id FROM dual;
END;

To prevent this post from getting longer than it needs to be, I'll just send you to AskTom to read a discussion about the performance of using Oracle sequences as primary keys.

One final word, about the NOCACHE command I used up there. The default CACHE value is 20, so I had to use NOCACHE because it would try to CACHE more values than the sequence had. CACHE actually CACHEs the next few values for quicker access. But if ever there is a system failure, those previously-retrieved sequence numbers are gone. You'll be left with a "gap."

Of course, gaps may not be the end of the world for you. If they are, you'll need to use ALTER SEQUENCE to fix them. I've already mentioned two ways to get gaps with sequences, here's one more. If you do a transaction with NEXTVAL and then rollback, the sequence doesn't roll back to where you started. That'll create a gap, too.