RE: Query on exchanged partition
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- To: "paul.baumgartel@xxxxxxx" <paul.baumgartel@xxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 5 Nov 2010 15:30:32 -0400
Hi Paul,
I don't think you mentioned your version. If it's 11.2, you could do:
Alter session set ddl_lock_timeout = <some_appropriate_number_of_seconds>;
Drop table <exchanged_table>;
The problem with:
Lock table <exchanged_table> in exclusive mode wait xxx;
Drop table <exchanged_table>;
Is that the drop table does an implicit commit before and after the recursive
SQL generated by the drop. The commit preceding the drop releases the lock and
creates a race condition, meaning that the drop will very likely hit ORA-00054,
even on a lightly loaded system.
Try this test:
Open three sessions:
Session one:
create table test_lock as select * from dba_objects;
update test_lock set object_name='MINE' where rownum=1;
session two:
lock table test_lock in exclusive mode wait 300;
dorp table test_lock;
session three:
update test_lock set object_name='MINE' where rownum=1 and object_name!='MINE';
Now, session two and three are both blocked.
Note that you executed the lock table command *before* the second 'update'
command, and that session two is blocking on the lock table command.
Now, commit in session one.
What happened?
Session two got the lock, then the implicit commit preceding the drop released
the lock, the second update got the lock, and the drop failed with ORA-00054.
I'm pretty sure this is why Oracle invented the 'ddl_lock_timeout' mechanism.
Hope that helps,
-Mark
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of paul.baumgartel@xxxxxxx
Sent: Friday, November 05, 2010 3:05 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Query on exchanged partition
Thanks to Tim Gorman, I've found that the answer to the question below is that
the second statement is true.
Now, one more question. I want to drop the exchanged table as soon as I can.
Does the querying session's parse lock on the exchanged partition move to the
exchanged table after the exchange? If so, we could issue a LOCK TABLE
<exchanged table> IN EXCLUSIVE MODE WAIT, followed by DROP TABLE <exchanged
table>. The lock could be obtained as soon as the query completed, and the drop
right after that.
Paul Baumgartel
UBS AG
IB Accounting Solutions
400 Atlantic Street
Stamford, CT 06904
203.719.4368
paul.baumgartel@xxxxxxx
www.ubs.com<file:///\\www.ubs.com>
_____________________________________________
From: Baumgartel, Paul
Sent: Friday, November 05, 2010 2:42 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Query on exchanged partition
If a query on a partitioned table is in flight, and alter table...exchange
partition is issued against it, which, if any, of the following is (are) true?
* Partition exchange waits for query completion, since it requires a DDL lock
* Partition exchange occurs immediately, query uses blocks formerly in the
exchanged partition but now in the table that was exchanged
* Query fails when it reaches the partition, throwing "no longer exists"
error
* Something else altogether
Paul Baumgartel
UBS AG
IB Accounting Solutions
400 Atlantic Street
Stamford, CT 06904
203.719.4368
paul.baumgartel@xxxxxxx
www.ubs.com<file:///\\www.ubs.com>
Other related posts:
