JL Computer Consultancy

Try the following experiment:

               create table t (n1 number);

               insert into t values (userenv('commitscn'));

               select n1 from t;

                     N1

               --------

               43526438

               rem     Wait a few seconds if there are other people working

               rem     on your system, or start a second session execute a

               rem     couple of small (but real) transactions and commits then

               commit;

               select n1 from t;

                     N1

               --------

               43526441

Obviously your values for N1 will not match the values above, but you should see that somehow the data you inserted into your table was not the value that was finally committed, so what's going on ?

The userenv('commitscn') function has to be one of the most quirky little undocumented features of Oracle. You can only use it in a very restricted fashion, but if you follow the rules the value that hits the database is the current value of the SCN (System Commit Number), but when you commit your transaction the number changes to the latest value of the SCN which is always just one less than the commit SCN used by your transaction.

Why on earth, you say, would Oracle produce such a wierd function - and how on earth do they stop it from costing a fortune in processing time.

To answer the first question think replication. Back to the days of 7.0.9, when a client asked me to build a system which used asynchronous replication between London and New York; eventually I persuaded him this was not a good idea, especially on early release software when the cost to the business of an error would be around $250,000 per shot; nevertheless I did have to demonstrate that in principal it was possible. The biggest problem, though, was guaranteeing that transactions were applied at the remote site in exactly the same order that they had been committed at the local site; and this is precisely where Oracle uses userenv('commitscn').

Each time a commit hits the database, the SCN is incremented, so each transaction is 'owned' by an SCN and no two transactions can belong to a single SCN - ultimately the SCN generator is the single-thread through which all the database must pass and be serialised. Although there is a small arithmetical quirk that the value of the userenv('commitscn') is changed to one less than the actual SCN used to commit the transaction, nevertheless each transaction gets a unique, correctly ordered value for the function. If you have two transactions, the one with the lower value of userenv('commitscn') is guaranteeably the one that committed first.

So how does Oracle ensure that the cost of using this function is not prohbitive. Well you need to examine Oracle errors 1735 and 1721 in the $ORACLE_HOME/rdbms/admin/mesg/oraus.msg file.

        ORA-01721: USERENV(COMMITSCN) invoked more than once in a transaction

        ORA-01735: "USERENV('COMMITSCN') not allowed here

You may only use userenv('commitscn') to update exactly one column of one row in a transaction, or insert exactly one value for one row in a transaction, and (just to add that final touch of peculiarity) the column type has to be an unconstrained number type otherwise the subsequent change does not take place.

Build Your Own Replication:

Given this strange function, here's the basis of what you have to do to write your own replication code:

create table control_table(sequence_id number, commit_id number);

begin transaction

        insert into control_table (sequence_id,commit_id)

        select meaningless_sequence.nextval, null 

        from dual;

        --  save the value of meaningless_sequence

        --  left as a language-specific exercise

        update control_table

        set commit_id = userenv('commitscn')

        where sequence_id = {saved value of meaningless_sequence};

        -- now do all the rest of the work, and include the saved

        -- meaningless_sequence.currval in every row of every table

        commit;

end transaction

If you now transport the changed data to the remote site, using the commit_id to send the transactions in the correct order, and the sequence_id to find the correct items of data, most of your problems are over. (Although you still have some messy details which are again left as an exercise.)