Oracle Database Discussions

Hi,
trying to exchange a partition sometimes getting me into a deadlocks

command:
ALTER TABLE my_table EXCHANGE PARTITION part_01 WITH TABLE PD_EXCHANGE INCLUDING INDEXES WITHOUT VALIDATION;

deadlock trace file:

/home/oracle/product/10.2.0/db_1/admin/orcl/udump/orcl_ora_16441.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /home/oracle/product/10.2.0/db_1
System name: Linux
Node name: xxxxxxxxxxx
Release: 2.6.9-55.ELsmp
Version: #1 SMP Fri Apr 20 16:36:54 EDT 2007
Machine: x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 29
Unix process pid: 16441, image: oracle@xxxxxxxxxxxx

*** 2011-03-06 17:16:07.807
*** ACTION NAME:(.......) 2011-03-06 17:16:07.778
*** MODULE NAME:(.......) 2011-03-06 17:16:07.778
*** SERVICE NAME:(SYS$USERS) 2011-03-06 17:16:07.778
*** SESSION ID:(499.6) 2011-03-06 17:16:07.777
A deadlock among DDL and parse locks is detected.
This deadlock is usually due to user errors in
the design of an application or from issuing a set
of concurrent statements which can cause a deadlock.
This should not be reported to Oracle Support.
The following information may aid in finding
the errors which cause the deadlock:
ORA-04020: deadlock detected while trying to lock object PD_EXCHANGE
--------------------------------------------------------
object waiting waiting blocking blocking
handle session lock mode session lock mode
-------- -------- -------- ---- -------- -------- ----
0x15c783698 0x15a54bf48 0x155aea768 S 0x15d56a8a0 0x1461f7ab8 X
0x142be8950 0x15d56a8a0 0x14677aee8 X 0x15a54bf48 0x155694628 S
--------------------------------------------------------
---------- DUMP OF WAITING AND BLOCKING LOCKS ----------
--------------------------------------------------------
------------- WAITING LOCK -------------
----------------------------------------
SO: 0x155aea768, type: 53, owner: 0x155b47490, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=155aea768 handle=15c783698 request=S
call pin=(nil) session pin=(nil) hpc=099f hlc=0000
htl=0x155aea7e8[0x155a36f78,0x155b45908] htb=0x155b45908 ssga=0x155b44f70
user=15a54bf48 session=15a54bf48 count=0 flags=RES/[0010] savepoint=0x1abc24
LIBRARY OBJECT HANDLE: handle=15c783698 mtx=0x15c7837c8(0) lct=16166 pct=23316 cdp=0
name=MY_TABLE
hash=d4b7630ea6718c50d8d20786d0c83388 timestamp=03-06-2011 17:16:01
namespace=TABL flags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-0749-0749 lock=X pin=X latch#=8 hpc=09a0 hlc=099e
lwt=0x15c783740[0x1460b6950,0x155aea798] ltm=0x15c783750[0x15c783750,0x15c783750]
pwt=0x15c783708[0x15c783708,0x15c783708] ptm=0x15c783718[0x15c783718,0x15c783718]
ref=0x15c783770[0x15c783770,0x15c783770] lnd=0x15c783788[0x15c782de0,0x15c7ac860]
LIBRARY OBJECT: object=152fa8ec8
type=TABL flags=EXS/LOC[0005] pflags=[0000] status=VALD load=0
DATA BLOCKS:
data# heap pointer status pins change whr
----- -------- -------- --------- ---- ------ ---
0 14f77fc38 152fa8fe0 I/P/A/-/- 0 NONE 00
3 152fa8d80 151a4c8b0 I/-/A/-/- 0 NONE 00
6 152fa8b28 1515c81c8 I/P/A/-/- 1 NONE 00
8 152fa91c8 1374d3af0 I/P/A/-/- 1 NONE 00
9 152fa8bf0 108ef3398 I/-/A/-/- 0 NONE 00
10 152fa8cb8 1331b7ad0 I/P/A/-/- 1 NONE 00
------------- BLOCKING LOCK ------------
----------------------------------------
SO: 0x1461f7ab8, type: 53, owner: 0x15946e010, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=1461f7ab8 handle=15c783698 mode=X
call pin=(nil) session pin=(nil) hpc=0000 hlc=0000
htl=0x1461f7b38[0x1558a8408,0x1558a8408] htb=0x1558a8408 ssga=0x1558a7a70
user=15d56a8a0 session=15d56a8a0 count=1 flags=[0000] savepoint=0xa67222
LIBRARY OBJECT HANDLE: handle=15c783698 mtx=0x15c7837c8(0) lct=16166 pct=23316 cdp=0
name=MY_TABLE
hash=d4b7630ea6718c50d8d20786d0c83388 timestamp=03-06-2011 17:16:01
namespace=TABL flags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-0749-0749 lock=X pin=X latch#=8 hpc=09a0 hlc=099e
lwt=0x15c783740[0x1460b6950,0x155aea798] ltm=0x15c783750[0x15c783750,0x15c783750]
pwt=0x15c783708[0x15c783708,0x15c783708] ptm=0x15c783718[0x15c783718,0x15c783718]
ref=0x15c783770[0x15c783770,0x15c783770] lnd=0x15c783788[0x15c782de0,0x15c7ac860]
LIBRARY OBJECT: object=152fa8ec8
type=TABL flags=EXS/LOC[0005] pflags=[0000] status=VALD load=0
DATA BLOCKS:
data# heap pointer status pins change whr
----- -------- -------- --------- ---- ------ ---
0 14f77fc38 152fa8fe0 I/P/A/-/- 0 NONE 00
3 152fa8d80 151a4c8b0 I/-/A/-/- 0 NONE 00
6 152fa8b28 1515c81c8 I/P/A/-/- 1 NONE 00
8 152fa91c8 1374d3af0 I/P/A/-/- 1 NONE 00
9 152fa8bf0 108ef3398 I/-/A/-/- 0 NONE 00
10 152fa8cb8 1331b7ad0 I/P/A/-/- 1 NONE 00
------------- WAITING LOCK -------------
----------------------------------------
SO: 0x14677aee8, type: 53, owner: 0x15946e010, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=14677aee8 handle=142be8950 request=X
call pin=(nil) session pin=(nil) hpc=0000 hlc=0000
htl=0x14677af68[0x156e54118,0x1558a7c58] htb=0x1558a7c58 ssga=0x1558a7a70
user=15d56a8a0 session=15d56a8a0 count=0 flags=[0000] savepoint=0xa67222
LIBRARY OBJECT HANDLE: handle=142be8950 mtx=0x142be8a80(0) lct=8066 pct=11522 cdp=0
name=PD_EXCHANGE
hash=01b6ee057f93d7ccd16112f0d6dcc70d timestamp=12-08-2010 16:51:08
namespace=TABL flags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-0701-0701 lock=S pin=S latch#=6 hpc=0908 hlc=0908
lwt=0x142be89f8[0x14677af18,0x14677af18] ltm=0x142be8a08[0x142be8a08,0x142be8a08]
pwt=0x142be89c0[0x142be89c0,0x142be89c0] ptm=0x142be89d0[0x142be89d0,0x142be89d0]
ref=0x142be8a28[0x142be8a28,0x142be8a28] lnd=0x142be8a40[0x15c5273a8,0x142bdb568]
LIBRARY OBJECT: object=152ba59e0
type=TABL flags=EXS/LOC[0005] pflags=[0000] status=VALD load=0
DATA BLOCKS:
data# heap pointer status pins change whr
----- -------- -------- --------- ---- ------ ---
0 14fe33568 152ba5b78 I/P/A/-/- 0 NONE 00
8 152ba5d20 133a30db8 I/P/A/-/- 1 NONE 00
9 15244ce20 151499460 I/-/A/-/- 0 NONE 00
10 15244cd58 133a2b6a0 I/P/A/-/- 1 NONE 00
------------- BLOCKING LOCK ------------
----------------------------------------
SO: 0x155694628, type: 53, owner: 0x155b47490, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=155694628 handle=142be8950 mode=S
call pin=0x156e0e240 session pin=(nil) hpc=0000 hlc=0000
htl=0x1556946a8[0x155b45158,0x155b45158] htb=0x155b45158 ssga=0x155b44f70
user=15a54bf48 session=15a54bf48 count=1 flags=PNC/[0400] savepoint=0x4b980
LIBRARY OBJECT HANDLE: handle=142be8950 mtx=0x142be8a80(0) lct=8066 pct=11522 cdp=0
name=PD_EXCHANGE
hash=01b6ee057f93d7ccd16112f0d6dcc70d timestamp=12-08-2010 16:51:08
namespace=TABL flags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-0701-0701 lock=S pin=S latch#=6 hpc=0908 hlc=0908
lwt=0x142be89f8[0x14677af18,0x14677af18] ltm=0x142be8a08[0x142be8a08,0x142be8a08]
pwt=0x142be89c0[0x142be89c0,0x142be89c0] ptm=0x142be89d0[0x142be89d0,0x142be89d0]
ref=0x142be8a28[0x142be8a28,0x142be8a28] lnd=0x142be8a40[0x15c5273a8,0x142bdb568]
LIBRARY OBJECT: object=152ba59e0
type=TABL flags=EXS/LOC[0005] pflags=[0000] status=VALD load=0
DATA BLOCKS:
data# heap pointer status pins change whr
----- -------- -------- --------- ---- ------ ---
0 14fe33568 152ba5b78 I/P/A/-/- 0 NONE 00
8 152ba5d20 133a30db8 I/P/A/-/- 1 NONE 00
9 15244ce20 151499460 I/-/A/-/- 0 NONE 00
10 15244cd58 133a2b6a0 I/P/A/-/- 1 NONE 00
--------------------------------------------------------
This lock request was aborted.





any idea how to solve this issue ?

Edited by: 843945 on Mar 13, 2011 6:07 AM