Hi all,
I'd appreciate your help with a problem that I've got at the moment.
I'm running a script that applies the delta DDL that is required to get
the database from one version of my app to the next. The script also
does some DML after applying the schema changes. There are no other
sessions running. I'm getting this error in the script's logfile:
Sequence dropped.
Sequence dropped.
rename TEMP_MULTWIRE_SEQ to MULTTERM_SEQ
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
This is happening at a point in the script where I'm dropping two
existing sequences, and renaming two other sequences (which are created
earlier in the script) so that they have the same names as the
sequences that just got dropped:
drop sequence MULTTERM_SEQ;
drop sequence WELDWIRESPEC_SEQ;
rename TEMP_MULTWIRE_SEQ to MULTTERM_SEQ;
rename TEMP_UWELD_SEQ to WELDWIRESPEC_SEQ;
Now, what's confusing me is that the trace file generated at the time
of this error doesn't mention the sequences but it does complain about
a deadlock on a table that has just had several DDL operations applied
to it. Here's the trace file:
[start of trace file]
Dump file d:\oracle\admin\chs\udump\chs_ora_2220.trc
Thu Oct 19 09:47:47 2006
ORACLE V9.2.0.1.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.2 Service Pack 1, CPU type 586
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Windows 2000 Version 5.2 Service Pack 1, CPU type 586
Instance name: chs
Redo thread mounted by this instance: 1
Oracle process number: 8
Windows thread id: 2220, image: ORACLE.EXE
- 2006-10-19 09:47:47.000
- SESSION ID:(7.10) 2006-10-19 09:47:47.000
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 CHS.HARNLOC1
object waiting waiting blocking blocking
handle session lock mode session lock mode
-------- -------- -------- ---- -------- -------- ----
668671A0 6820F5A8 670AEF34 X 6820F5A8 670AF054 X
- DUMP OF WAITING AND BLOCKING LOCKS ----------
- WAITING LOCK -------------
SO: 670AEF34, type: 51, owner: 6745F784, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=670aef34 handle=668671a0 request=X
call pin=00000000 session pin=00000000
user=6820f5a8 session=6820ff18 count=0 flags=[00] savepoint=1493
LIBRARY OBJECT HANDLE: handle=668671a0
name=CHS.HARNLOC1
hash=676eca29 timestamp=10-19-2006 09:47:41
namespace=TABL/PRCD/TYPE flags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-070d-070d lock=X pin=X latch#=7
lwt=668671B8[670AEF44,670AEF44] ltm=668671C0[668671C0,668671C0]
pwt=668671D0[668671D0,668671D0] ptm=66867228[66867228,66867228]
ref=668671A8[668671A8, 668671A8] lnd=66867234[6764A910,65DE9FC4]
DEPENDENCY REFERENCES:
reference latch flags
- ----- -------------------
66508490 4 DEP/INV[05]
65f47718 2 DEP[01]
LOCK OWNERS:
lock user session count mode flags
- -------- -------- ----- ---- ------------------------
670af054 6820f5a8 6820f5a8 1 X [00]
670aef7c 6820f5a8 6820ff18 1 N PNC/[04]
LOCK WAITERS:
lock user session count mode
- -------- -------- ----- ----
670aef34 6820f5a8 6820ff18 0 X
PIN OWNERS:
pin user session lock count mode mask
- -------- -------- -------- ----- ---- ----
670aff84 6820f5a8 6820f5a8 0 1 X 0701
670b00a4 6820f5a8 6820ff18 670aef7c 2 N 0509
LIBRARY OBJECT: object=661a54fc
type=TABL flags=EXS/LOC/UPD[0905] pflags= [00] status=VALD load=0
DATA BLOCKS:
data# heap pointer status pins change
- -------- -------- ------ ---- ------
0 66287b98 661a5584 I/P/A 0 NONE
2 66158594 0 -/P/- 0 NONE
3 66158530 665081f0 I/P/A 1 NONE
8 664c42b0 65f84160 I/P/A 2 UPDATE
9 664c4314 6615830c I/P/A 1 NONE
10 664c4378 66b8c4f4 I/P/A 2 NONE
- BLOCKING LOCK ------------
SO: 670AF054, type: 51, owner: 6745F3A8, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=670af054 handle=668671a0 mode=X
call pin=00000000 session pin=00000000
user=6820f5a8 session=6820f5a8 count=1 flags=[00] savepoint=7
LIBRARY OBJECT HANDLE: handle=668671a0
name=CHS.HARNLOC1
hash=676eca29 timestamp=10-19-2006 09:47:41
namespace=TABL/PRCD/TYPE flags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-070d-070d lock=X pin=X latch#=7
lwt=668671B8[670AEF44,670AEF44] ltm=668671C0[668671C0,668671C0]
pwt=668671D0[668671D0,668671D0] ptm=66867228[66867228,66867228]
ref=668671A8[668671A8, 668671A8] lnd=66867234[6764A910,65DE9FC4]
DEPENDENCY REFERENCES:
reference latch flags
- ----- -------------------
66508490 4 DEP/INV[05]
65f47718 2 DEP[01]
LOCK OWNERS:
lock user session count mode flags
- -------- -------- ----- ---- ------------------------
670af054 6820f5a8 6820f5a8 1 X [00]
670aef7c 6820f5a8 6820ff18 1 N PNC/[04]
LOCK WAITERS:
lock user session count mode
- -------- -------- ----- ----
670aef34 6820f5a8 6820ff18 0 X
PIN OWNERS:
pin user session lock count mode mask
- -------- -------- -------- ----- ---- ----
670aff84 6820f5a8 6820f5a8 0 1 X 0701
670b00a4 6820f5a8 6820ff18 670aef7c 2 N 0509
LIBRARY OBJECT: object=661a54fc
type=TABL flags=EXS/LOC/UPD[0905] pflags= [00] status=VALD load=0
DATA BLOCKS:
data# heap pointer status pins change
- -------- -------- ------ ---- ------
0 66287b98 661a5584 I/P/A 0 NONE
2 66158594 0 -/P/- 0 NONE
3 66158530 665081f0 I/P/A 1 NONE
8 664c42b0 65f84160 I/P/A 2 UPDATE
9 664c4314 6615830c I/P/A 1 NONE
10 664c4378 66b8c4f4 I/P/A 2 NONE
This lock request was aborted.
[end of trace file]
So... I'm confused. Is the problem with the sequences or with the
table? From what I can find about ORA-04020, it seems to be something
to do with the library cache, and people recommend issuing a commit to
free up a lock, but I don't see how that would help in this situation.
FWIW, the DDL operations on the harnloc1 table before the sequences get
dropped / renamed are:
- Drop the only FK constraint
- Modify the width of a numeric column
- Add some new columns
- Re-add the FK constraint, disabled
- Enable the FK constraint
TIA
Tarby
Received on Thu Oct 19 2006 - 06:04:25 CDT
