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.

Session 1:

BEGIN
  FOR i IN 1..100 LOOP
    INSERT INTO s ( x ) 
    VALUES ( DBMS_RANDOM.VALUE );
    dbms_lock.sleep( .5 );
  END LOOP;
  COMMIT;
END;
/

I'm inserting 100 records with a wait of half a second between inserts. This should take a little over 50 seconds.

Session 2

Note I've set the timeout to 30 seconds so that it will finish prior to Session 1

DECLARE
  l_bool BOOLEAN;
  l_scn NUMBER;
BEGIN
  l_bool := dbms_utility.wait_on_pending_dml
              ( tables => 'cjustice.s',
                timeout => 30,
                scn => l_scn );

  dbms_output.put_line( 'scn:  ' || l_scn );
  IF l_bool THEN
    dbms_output.put_line( 'true' );
  ELSE
    dbms_output.put_line( 'false' );
  END IF;
END;
/

scn:  924643
false

PL/SQL procedure successfully completed.

Elapsed: 00:00:30.03

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:  924773
true

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.

I'll have more Editioning soon.