Thursday, June 09, 2011

Burning an Oracle Sequence value to raise it above Table Max

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

No comments: