Re: Deadlock in single session

Well, it's getting interesting now. By splitting the script up into several smaller ones, I've been able to isolate the problem - in fact, I've found that there are two problems.

The first one is the one that causes the trace file to be created, and it's caused by a couple of DDL statements for a certain table. Originally, I had this:

ALTER TABLE harnloc1 MODIFY ( mlctype NUMBER (3) );

ALTER TABLE harnloc1 ADD

  ( symbol_id        VARCHAR2 (64 BYTE)                  NULL,
    time_modified    VARCHAR2 (255 BYTE)                 DEFAULT 0 NULL

Executing those statements in that order causes the trace file to be generated. Executing the second statement without first having executed the first one does NOT cause the trace file to be generated. Breaking down the DDL for "time_modified" so that it is created with just the "null" qualifier and then using ALTER TABLE to apply the "default 0" also does NOT cause the trace file to be generated. There's just something about the original DDL that Oracle doesn't like, and breaking it down into several smaller chunks seems to fix that particular problem.

The second problem is the one related to dropping and renaming some sequences, resulting in the "end of file on communication channel" message, and it actually causes the instance to shut down. So far, I'm not getting that problem on patched 9ir1, but I AM getting it on unpatched 9ir2 and I'm hoping that applying a patch might fix it.

Regards,
Tarby Received on Sun Oct 22 2006 - 02:19:53 CDT