Triggers and constraints: diagnostic mechanism, part I

Throughout the prevoius topic the essential question is - what is diagnostic area, where it is located and what mechanism Oracle uses to handle it.
Let's check what is temp segment usage in our case. Maybe we'll see something interested.

SQL> create table parent_t (x int);

Table created.

SQL> create table child_t (x int, y int);

Table created.

SQL> alter table parent_t add constraint parent_t_pk primary key (x);

Table altered.

SQL> alter table child_t add constraint child_t_fk

2 foreign key (y) references parent_t (x) deferrable;

Table altered.

At the beginning we will execute INSERT statement which should not generate any constraint violations for not-deferred constraint mode and check temp segment usage.

SQL> insert into child_t select rownum, null from dual connect by level <= 10000;  10000 rows created.  SQL> select sid from v$mystat where rownum = 1;

       SID
----------
       127

SQL> select x.segtype, x.extents, x.blocks, x.segfile#, x.segblk#
  2  from v$tempseg_usage x, v$session y
  3  where x.session_addr = y.saddr and y.sid = 127
  4  /

no rows selected

Well, nothing unexpected.

Now let's set DEFERRED MODE and insert next 10000 rows:

SQL> set constraint all deferred;

Constraint set.

SQL> insert into child_t select rownum, null from dual connect by level <= 10000;  10000 rows created.  SQL> select x.segtype, x.extents, x.blocks, x.segfile#, x.segblk#
  2  from v$tempseg_usage x, v$session y
  3  where x.session_addr = y.saddr and y.sid = 127
  4  /

no rows selected

And finally let's insert rows what force constraint violation - Y column value
will contain values from 0 to 3 inclusively and no one has parent key:

SQL> insert into child_t select rownum, mod(rownum,4) from dual connect by level <= 10000;  

10000 rows created.  

SQL> select x.segtype, x.extents, x.blocks, x.segfile#, x.segblk#, x.segrfno#
  2  from v$tempseg_usage x, v$session y
  3  where x.session_addr = y.saddr and y.sid = 127
  4  /

SEGTYPE      EXTENTS     BLOCKS   SEGFILE#    SEGBLK#   SEGRFNO#
--------- ---------- ---------- ---------- ---------- ----------
INDEX              1        128        202     255753          2

Now we see temporary segment usage and it's type is INDEX.

Let's dump sereval blocks from this structure.

SQL> alter system dump tempfile 2 block min 255753 block max 255760;

System altered.

We will see segment header and index leaf block:

Start dump data blocks tsn: 3 file#: 2 minblk 255753 maxblk 255880
buffer tsn: 3 rdba: 0x0083e709 (2/255753)
scn: 0x0875.7bd4b765 seq: 0x00 flg: 0x08 tail: 0xb7651000
frmt: 0x02 chkval: 0x0000 type: 0x10=DATA SEGMENT HEADER - UNLIMITED
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 127
                  last map  0x00000000  #maps: 0      offset: 4128
      Highwater::  0x0083e70b  ext#: 0      blk#: 1      ext size: 127
  #blocks in seg. hdr's freelists: 0
  #blocks below: 1
  mapblk  0x00000000  offset: 0
                   Unlocked
     Map Header:: next  0x00000000  #extents: 1    obj#: 8644361 flag: 0x40000000
  Extent Map
  -----------------------------------------------------------------
   0x0083e70a  length: 127

  nfl = 1, nfb = 1 typ = 2 nxf = 0 ccnt = 0
  SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000 

buffer tsn: 3 rdba: 0x0083e70a (2/255754)
scn: 0x0875.7bd4b7d3 seq: 0x00 flg: 0x08 tail: 0xb7d30600
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump:  0x0083e70a
 Object id on Block? Y
 seg/obj: 0x83e709  csc: 0x875.7bd4b764  itc: 2  flg: -  typ: 2 - INDEX
     fsl: 0  fnx: 0x0 ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x0006.02b.000050aa  0x00873a96.165c.02  ----    4  fsc 0x0000.00000000

Leaf block dump
===============
header address 242391644=0xe729a5c
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 4
kdxcofbo 44=0x2c
kdxcofeo 7997=0x1f3d
kdxcoavs 7953
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 4
kdxlebksz 8032
row#0[8024] flag: ------, lock: 2, len=8, data:(4):  c4 09 00 00
col 0; len 1; (1):  80
row#1[8015] flag: ------, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 02
row#2[8006] flag: ------, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 03
row#3[7997] flag: ------, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 04

Let's now delete from the CHILD_T table all rows with y = 2 and check
if there will be any changes in this segment:

SQL> delete from child_t where y = 1;

2500 rows deleted.

row#0[8024] flag: ------, lock: 2, len=8, data:(4):  c4 09 00 00
col 0; len 1; (1):  80
row#1[8015] flag: ---D--, lock: 2, len=9, data:(4):  01 00 00 00
col 0; len 2; (2):  c1 02
row#2[8006] flag: ------, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 03
row#3[7997] flag: ------, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 04

OK, the record for key value = 1 has been marked as deleted.

Now go forward and create parent row for Y=2:

SQL> insert into parent_t values(2);

1 row created.

As we can see, now the row for key value = 2 has been marked as DELETED too:

row#0[8024] flag: ------, lock: 2, len=8, data:(4):  c4 09 00 00
col 0; len 1; (1):  80
row#1[8015] flag: ---D--, lock: 2, len=9, data:(4):  01 00 00 00
col 0; len 2; (2):  c1 02
row#2[8006] flag: ---D--, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 03
row#3[7997] flag: ------, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 04

And finally we can create parent row for Y=0 in AUTONOMOUS transaction:

SQL> declare
  2   pragma autonomous_transaction;
  3  begin
  4   insert into parent_t values(0);
  5   commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

row#0[8024] flag: ------, lock: 2, len=8, data:(4):  c4 09 00 00
col 0; len 1; (1):  80
row#1[8015] flag: ---D--, lock: 2, len=9, data:(4):  01 00 00 00
col 0; len 2; (2):  c1 02
row#2[8006] flag: ---D--, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 03
row#3[7997] flag: ------, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 04

No change - as we could reasonably expect.

Now touch rows in the table with Y=0, but don't touch Y value itself:

SQL> update child_t set x = x where y = 0;

2500 rows updated.

No changes in the leaf block:

row#0[8024] flag: ------, lock: 2, len=8, data:(4):  c4 09 00 00
col 0; len 1; (1):  80
row#1[8015] flag: ---D--, lock: 2, len=9, data:(4):  01 00 00 00
col 0; len 2; (2):  c1 02
row#2[8006] flag: ---D--, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 03
row#3[7997] flag: ------, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 04

But if we touch Y column in UPDATE statement

SQL> update child_t set y = y where y = 0;

2500 rows updated.

we can see what the row with key value = 0 is marked as deleted - because
now the parent key exists:

row#0[8024] flag: ---D--, lock: 2, len=8, data:(4):  01 00 00 00
col 0; len 1; (1):  80
row#1[8015] flag: ---D--, lock: 2, len=9, data:(4):  01 00 00 00
col 0; len 2; (2):  c1 02
row#2[8006] flag: ---D--, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 03
row#3[7997] flag: ------, lock: 2, len=9, data:(4):  c4 09 00 00
col 0; len 2; (2):  c1 04