We have a log table that we use to track miscellaneous behaviors via some triggers for "audit" purposes. The design is fairly simple... on the occurrence of a "monitoring" behavior,
INSERT a row into the
AUDIT_LOG table. The
INSERT grabs a new sequence value and poof, we're good.
Well, not always. Periodically, the
MAX table value exceeds the sequence value. I don't know why/how it happens, but it causes our applications to start throwing errors [as expected]. I wrote a simple query to get the sequence value back above the table ID maximum.
Note: you may need to establish the "currval" of the session by burning a "nextval".
select -- audit_seq.nextval "Sequence NextVal", audit_seq.currval "Sequence CurrVal", (select max(AUDIT_LOG.SEQ_NO) from AUDIT_LOG) "Table Max ID", case when audit_seq.currval >= (select max(AUDIT_LOG.SEQ_NO) from AUDIT_LOG) then 'Sequence good' when audit_seq.currval < (select max(AUDIT_LOG.SEQ_NO) from AUDIT_LOG) then 'Sequence bad, Table Higher by '||( (select max(AUDIT_LOG.SEQ_NO) from AUDIT_LOG) - audit_seq.nextval) else 'wtf' end "Sequence Status" from dual
You may want to reference: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudocolumns002.htm#sthref806
ORA-02287: sequence number not allowed here