tidbit:Oracle:identify when row was changed

Great way to identify when table row was changed (within 3 second precision):

select scn_to_timestamp(ORA_ROWSCN ) from EMP WHERE ROWNUM < 10;

tidbit:Oracle:ITL Deadlock troubleshooting and analysis

How to identify ITL deadlock:

1. Pick slot number (0x40021) at the top

2. Search for it in a deadlock trace file

3. See if wait event is ITL related.

Resolution: Increase INI_TRANS on table or on index.

                       ---------Blocker(s)--------  ---------Waiter(s)---------

Resource Name          process session holds waits  process session holds waits

TX-00040021-00001409        66    2515     X             65    1253           S

TX-0005001b-000014d8        65    1253     X             66    2515           S

    Dumping one waiter:

      inst: 1, sid: 2513, ser: 41

      wait event: 'enq: TX - allocate ITL entry'

        p1: 'name|mode'=0x54580004

        p2: 'usn<<16 | slot'=0x40021

        p3: 'sequence'=0x1409

      row_wait_obj#: 4294967295, block#: 0, row#: 0, file# 0

      min_blocked_time: 0 secs, waiter_cache_ver: 31817

Oracle: Internal structure of "date" data type

Did some investigation on internal date data format:

A date in binary format contains seven bytes, as shown in table below:

BYTE          1    2     3   4    5      6      7
Meaning Century Year Month Day Hour Minute Second
Example     120  110    11  30   16     18      1

The century and year bytes are in an excess–100 notation.

Dates Before Common Era (BCE) are less than 100.

The era begins on 01–JAN–4712 BCE, which is Julian day 1.

For this date, the century byte is 53, and the year byte is 88.

The hour, minute, and second bytes are in excess–1 notation.

The hour byte ranges from 1 to 24, the minute and second bytes from 1 to 60.

If no time was specified when the date was created, the time defaults to midnight (1, 1, 1).

You can use DUMP function to investigate this:

 SQL> create table t ( x date );

Table created.

SQL> insert into t values ( to_date( '03-nov-2010 3:17:01pm', 'dd-mon-yyyy hh:mi:sspm' ) );
1 row created.
SQL> commit; 


SQL> select dump(x) from t;
DUMP(X)
--------------------------------------------------------------------------------
Typ=12 Len=7: 120,110,11,3,16,18,2

Great Oracle Internals book

Oracle8i Internal Services for Waits, Latches, Locks, and Memory
Out of number of great "Oracle internals" book this definitely stands out. Ignore 8i - wealth of information in this book deserves it to be read and reread.

I met author in 2001-2002 and he is a shy and very smart DBA from Australia. His website http://www.ixora.com.au/ is a treasure of Oracle internals information and should be studied and used by every reasonable good Oracle DBA.

This Book explain in a great depth internal work of different latches and locks - knowledge which DBA must have to resolve and identify root causes of very complex contentions.