Systems Engineering and RDBMS

Missing Sequence Numbers and Audits

Posted by decipherinfosys on July 7, 2007

At one of the client firms, they were using Oracle as their back-end RDBMS and were using Sequences for their surrogate key generation. As you might already know, the sequence values get used as soon as they are referenced i.e. in a SQL statement, as soon as a sequence value is referenced, it gets used up regardless of whether a commit or a rollback took place in that transaction. Hence these sequence numbers are never “gap-less”…the numbers that are discarded from the cache because of not using them will never show up in the tables.

An auditor asked this question to one of our clients: “You guys are using Sequences…hmmm, why are their gaps in the table values – how can you prove that your software has not deleted those records because of a bug in the system?” Well, that is what audit trails are there for…using the audit trail system, it was very easy to prove to the auditor that since there is no audit trail for a creation of a record for that value, thus there is no record of that value in the system at all and since it never existed, how can it even be deleted. That, in itself is proof that the system was doing fine and the auditor was confusing the functionality of the sequences with the end user’s data in the system.

Many people do not understand how Sequences operate in Oracle. Sequences will never provide gap free numbers – and in a majority of the scenarios, it is not even required to do so. So, if someone calls say my_seq.nextval and then does not use it or uses it and then does a rollback on that transaction, then that number is gone. This is a great way to build scalable multi-user applications for generating surrogate keys for tables in Oracle. They also remove the concurrency related issues that are associated with unique key generations. The fact that the auditor was actually looking at this level was pretty neat but then again using the right audit trail records in the system, we were able to provide the right answers as well as explain why there were gaps in the values.

One Response to “Missing Sequence Numbers and Audits”

  1. […] auto generate column IDs. You can read more about Sequences and their usage in our previous post – here.  In today’s post, we are going to look at how we can re-set the sequence value. Re-setting […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: