JL Computer Consultancy

Practical Oracle 8i - Building Efficient Databases

Chapter 1: What is Oracle.


Flashback queries (13th June 2001)

Commit Cleanouts





Flashback Queries

One of the details of Oracle 9i that has recently become public is the facility for the flashback query of data. By wrapping and SQL statement with a pair of calls to a PL/SQL package, you can run your query as at an earlier point in time thus allowing you the option of rebuilding an earlier version of your data to produce a differences report or restore accidental deletions.

As a point of interest, you may be interested to note that this technology is almost present in Oracle 8i, a point I discovered many months ago when experimenting with using locators to return nested tables. The following interesting syntax showed up in one of my trace files when I started using the locator and executing follow-up queries to get the nested table data in a PL/SQL block.

	select * 
	with snapshot (:SYS_SS1) 
	where nested_table_id = sys_op_tosetid(:B1)

A dump of the bind variable :sys_ss1 showed that it was a variable of internal type 23, with a maximum length of 32 and a used length of 24: and the actual value was, indeed, the SCN as at the start of the query.

I've been playing around with trying to make this work outside the confines of the locator environment, but have not had any success yet; I suspect it requires some OCI-level programming.

Top of page


Commit cleanouts

Following the descriptions about the effects of the commit on the changed data blocks that may or may not be in the db_block_buffer, I have written a short note with a few block dumps showing the three states that a block can get into depending on the circumstances surrounding the moment at which its cleanout takes place. This note is for interest only, and need not be taken too seriously.

Top of page


I have stated (p.10) that the acronym SCN stands for system commit number. Yong Huang (yong321@yahoo.com) has pointed out that the term system change number seems to be more prevalent. In fact, the two terms are pretty much interchangeable and a quick search of the documentation shows that the only remaining reference to the SCN as the system commit number appears in the Parallel Server manuals where we find that it is protected by the 'System Commit' (SC) enqueue. However, system change number is technically more appropriate, since the value can increment at times other than commits. Some of these occasions will be due to recursive SQL on the data dictionary, and some will be due to delayed block cleanout (see, for example, my article on Block Cleanout) ; Rama Velpuri also mentions checkpoints as another point in time when the SCN may change.

Top of page

Back to Book Index