Tip#20 Session Marked for Kill Forever

I had a situation recently where our junior DBA accidentally killed an MV refresh job session and session killed was 'marked for kill' forever i.e. it wont release the resource (in this case lock) hence any subsequent refresh were also failing.

Lock holding session can be determined by,

select SID from v$lock where ID1 = (select object_id from dba_objects where object_name = *Object name* and OWNER = *Owner*)

Also any subsequent try to kill/disconnect session using alter session will result in ORA-00031: session marked for kill. So only way to release resources is to kill the process at OS level and let the PMON do the cleanup.

Now since sessoin is already killed and we are not yet using 11g (which gives you OS PID after killing as well), I tried getting OS PID using following SQL (Also suggest to read Metalink Doc ID 1020720.102) i.e.

SQL> SELECT spid FROM v$process WHERE NOT EXISTS ( SELECT 1 FROM v$session WHERE paddr = addr) AND spid IS NOT NULL;

unfortunately this didnt help as PID returned was not JOB process, you can check on Linux using,

% ps -ef | grep spid

The process I was expecting to be something like ora_j00x_sid but didnt found any so search for OS PID was still on.

Since we still have an entry in v$session with 'KILLED' status, we decided to get a 'Logon time' and see if we can match that with OS PID for same time and with command format 'ora_j00x_sid'. And fortunately we had hit the bulls eye we had only one OS PID with same time and expected command so we could just easily nailed it. So we killed the process at OS level

% kill spid

and after few moments we could see the lock was released!

Tip#19 Oracle Date Insight

Note : Partial text from Metalink Note:69028.1

Oracle DATE values are always stored in 7 bytes, excluding the length byte, within a datafile. These bytes store the century, year, month, day, hour, minute, and second details respectively. The following is the definition of Oracle's internal DATE storage structure:

BYTE Meaning
---- -------
1 Century -- stored in excess-100 notation
2 Year -- " "
3 Month -- stored in 0 base notation
4 Day -- " "
5 Hour -- stored in excess-1 notation
6 Minute -- " "
7 Second -- " "

Note that the century and year components are stored in 'excess 100 format', which means that 100 must be deducted from the byte's value. If a negative number results, then we've got a BC date at which point we take the absolute number. Also, to avoid ever having a zero byte, 1 is added to the hour, minute and second bytes. Therefore, 1 must be detected to get the correct value.

For example, take the following date:
11-MAR-2009 13:08:00

we would expect this date to be stored internally as follows:
120,109,3,11,14,9,1

Let's confirm this,

SQL> create table test1 as select sysdate sd from dual;

SQL> select to_char(sd, 'DD-MON-YYYY HH24:MI:SS'), dump(sd) from test1;

Result:

TO_CHAR(SD,'DD-MON-YYYYHH24:MI:SS') : 11-MAR-2009 13:08:00

DUMP(SD) : Typ=12 Len=7: 120,109,3,11,14,9,1

Let's try using the DUMP() function to do the same thing with TO_DATE now. Issue the following statement:

SQL> SELECT dump(to_date('11-MAR-2009 13:08:00', 'DD-MON-YYYY HH24:MI:SS'))
FROM dual;

Result: Typ=13 Len=8: 217,7,3,11,13,8,0,0

Note the different "Typ=" values to understand why we are seeing these results. The datatype returned is 13 and not 12, the external DATE datatype. This occurs because we rely on the TO_DATE function! External datatype 13 is an internal c-structure whose length varies depending on how the c-compiler represents the structure. Note that the "Len=" value is 8 and not 7. Type 13 is not a part of the published 3GL interfaces for Oracle and is used for date calculations mainly within PL/SQL operations.

Note that the same result can be seen when DUMPing the value SYSDATE.

Using deductive logic, we can derive the following storage format for type 13 data:

Byte 1 - Base 256 year modifier
2 - Base 256 year
3 - Month
4 - Day
5 - Hours
6 - Minutes
7 - Seconds
8 - Unused

For AD dates, the year and base 256 modifier are stored in base 0 notation and we must add the modifier to the year to obtain the true year. For BC dates, the year and base 256 modifier are stored in excess-255 notation. We must subtract the modifier from the year to obtain the true year.

For our year 2009, we could read this to be, Byte 1 + Byte 2 * 256. In other words, 217 + 7 * 256 = 2009.

Oracle is capable of handling dates from 01-JAN-4712 BC 00:00:00 TO 31-DEC-9999 AD 23:59:59 AD OR in terms of Julian Day: 1 through Julian Day: 5373484

The Julian Day number is a count of days elapsed since Greenwich mean noon on 1 January 4712 B.C. in the Julian proleptic calendar. The Julian Date is the Julian Day number followed by the fraction of the day elapsed since the preceding noon.