Today’s Issue:
Trying to compile a PACKAGE BODY and the session was hanging.
It was hanging on the following wait:
SELECT SID, EVENT, P1TEXT, P1, P2TEXT, P2
FROM V$SESSION
WHERE sid = 485;
SID EVENT P1TEXT P1 P2TEXT P2
----- ------------------------------ ------- -- ------- ---
485 read by other session file# 1 block# 828
Normally, I would think that there is some sort of user lock in the database that is preventing my compile from happening. However, in this case, the lock was on an internal object and it was a read by other session (buffer busy wait).
This started to stink of corruption.
There was no problem compiling the package spec. Also, this very same package was modified and compiled in an identical-to-the-block DEV database. This problem was occurring in my TEST copy of the database.
I saw no other sessions actively accessing this object.
I decided to try compiling the object as SYS to see if that made any difference:
alter package
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [ktcdso-1], [], [], [], [], [], [], [], [], [], [], []
Process ID: 12487
Session ID: 251 Serial number: 10663
Well that did not work.
Since it was a block in the buffer cache, I thought, let’s try flushing the buffer cache…this is test after all:
alter system flush buffer_cache;
System altered.
Still, no luck, the compile hung.
I used the ORA-600 lookup tool on Metalink to see what the ORA-00600 was all about. Not really much was returned, there were three issues, none of which really applied to me. There was one that was a little intriguing that had to do with corruption.
I figured I would do a Database Verify against the system tablespace datafile:
$ dbv blocksize=8192 file=/db/oradata/prod/system01.dbf
logfile=system_dbv.out
DBVERIFY: Release 11.2.0.2.0 - Production on Tue Apr 17 11:23:34 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
$ cat system_dbv.out
DBVERIFY: Release 11.2.0.2.0 - Production on Tue Apr 17 11:23:34 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /db/oradata/prod/system01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 185344
Total Pages Processed (Data) : 107060
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 35567
Total Pages Failing (Index): 0
Total Pages Processed (Other): 4087
Total Pages Processed (Seg) : 1
Total Pages Failing (Seg) : 0
Total Pages Empty : 38630
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 3061770519 (16.3061770519)
No obvious corruption.
Next, I decided to use the information I gleaned from V$SESSION_WAIT. Particularly the file# and block# to look up what object my compile was waiting on:
SELECT relative_fno, owner, segment_name, segment_type FROM dba_extents
WHERE file_id = 1
AND 828 BETWEEN block_id AND block_id + blocks - 1;
RELATIVE_FNO OWNER SEGMENT_NAME SEGMENT_TYPE
------------ --------------- ------------- ------------
1 SYS I_PROCEDURE1 INDEX
So, we have an internal index that was not allowing my compile to run. Still no reason why as I said it seems no one else was accessing this procedure. Maybe there was a bit of logical corruption in the index?
Let’s rebuild it online…
Disclaimer: There are all sorts of rules about modifying internal objects. I do not endorse or condone what I am about to do without knowing more about YOUR system. Mine happened to be a TEST system that could easily be refreshed from production. Plus, I figured it was rebuild...not actually changing any structures or anything...
As SYS:
alter index i_procedure1 rebuild online;
Index altered.
Guess what? That fixed the issue. My procedure immediately compiled after rebuilding the index.
