Sunday, November 15, 2009

DBMS_UTILITY.WAIT_ON_PENDING_DML

I've been reading up on Edition-Based Redefinition (which I can't link to because the docs seem to be missing right now).

In my readings I ran across DBMS_UTILITY.WAIT_ON_PENDING_DML which appears to be new to 11gR2 (there is no entry for it in the 11gR1 docs, which again, I can't link up currently). Based on my reading (I have the docs locally), this function appears to be used specifically for the new editioning feature.

It's listed as a procedure in the docs (don't ask for the link):

and a function if you do a describe on dbms_utility.

This procedure waits until all transactions (other than the caller's own) that have locks on the listed tables and began prior to the specified SCN have either committed or been rolled back.

So I wanted to see how it works.

First, I create the table:

CREATE TABLE s( x NUMBER);

I then open up a second session. In that session, I will run the WAIT_ON_PENDING_DML function which should monitor session 1.

The return value of "false" means that Session 1 is still inserting, which I confirmed visually (I should really create a video for this). Once Session 1 completed, I reran Session 2's anonymous block and received these results:

CJUSTICE@TESTING>/scn: 924773true

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

In regards to editioning, you would poll the database using this utility to see if there is, well, any pending DML. One aspect of Editioning allows you to create temporary triggers that will help you to migrate your application to the latest version without worrying about locking or timeouts. Pretty cool stuff.