Hi TOMs,

A little back story:

Our application incurs many "cursor: pin s wait on x" and "library cache lock" waits during our nightly partition maintenance job. It has been expected behavior and one that we've been living happily with for quite some time. Enter the 12.1.0.2 upgrade. Now, all these sessions that were waiting for their turn to re-validate their cursors are now actually affecting the partition maintenance job and slowing it down to an unhappy pace. We've opened some SRs, patched some things, disabled some things (i.e. optimizer_adaptive_features & heatmap) and Support has finally said that we are to live with it and to change the application logic to remove this contention by forcing the partition maintenance sessions and the application sessions to play nice and wait their turns. Meaning, pause the application processing for a bit, do some DDL, turn on processing for a bit then pause, do some DDL, repeat until done.

Question:

Coding this "pause" will be a little difficult in the application since it is HUGE and there are so many pieces to pause, so in my head I thought, well, let's force a pause by LOCKing the table we will be doing partition ADD/DROPs to. For example,

session 1:
  
SQL> create table mylist (id number, n number)
  partition by list (n)
  (
    partition one values (1),
    partition two values (2)
  );    

Table created.

SQL> lock table mylist in exclusive mode;

Table(s) Locked.

Session 2:

SQL> insert into mylist values (1,2); <<< hangs while waiting for the TM lock to go away

Session 1: 

SQL> alter table mylist drop partition two;
alter table mylist drop partition two
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired <<< BLARG!

Session 2: 

1 row created. <<< wins the battle!

Maybe I used to know that this was expected, but as of now I'm sort of at a loss as how best to approach this. Is there a way that I can lock a table forcing all sessions to wait until I am done doing some (and actually quite a bit) DDL?

As always, thanks for your assistance and expertise!

create or replace procedure get_lock is
  v_lockhandle varchar2(200);
  v_result     number;
begin
  dbms_lock.allocate_unique('DDL_lock', v_lockhandle);

  v_result := dbms_lock.request(v_lockhandle, dbms_lock.x_mode);

  if v_result <> 0 then
    raise_application_error(-20001, 'Unable to get lock');
  end if; 
end get_lock;
/

create or replace procedure release_lock is
  v_lockhandle varchar2(200);
  v_result     number;
begin

  dbms_lock.allocate_unique('DDL_lock', v_lockhandle);

  v_result := dbms_lock.release(v_lockhandle);

  if v_result <> 0 then
    raise_application_error(-20001, 'Unable to release lock');
  end if; 

end release_lock;
/

exec get_lock;

alter table mylist drop partition two;

exec release_lock;

exec get_lock;

insert into mylist values (1,2);

exec release_lock;