This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Nov 10, 2009 10:22 PM by OracleGuy777 RSS

what does the "row directory" look like?

OracleGuy777 Newbie
Currently Being Moderated
hi,

i am confused as to what the entries in a row directory look like. Oracle docs say:

"This portion of the data block contains information about the actual rows in the block (including addresses for each row piece in the row data area)."

Does the address here mean the DBA or rowid?

Also, what else does this contain?

thanks
  • 1. Re: what does the "row directory" look like?
    Centinul Guru
    Currently Being Moderated
    Although this article is about Oracle forensics it goes into a detailed explanation of block contents.

    [Locating Dropped Objects|http://www.databasesecurity.com/dbsec/Locating-Dropped-Objects.pdf]

    HTH!
  • 2. Re: what does the "row directory" look like?
    OracleGuy777 Newbie
    Currently Being Moderated
    Centinul wrote:
    Although this article is about Oracle forensics it goes into a detailed explanation of block contents.

    [Locating Dropped Objects|http://www.databasesecurity.com/dbsec/Locating-Dropped-Objects.pdf]

    HTH!
    thanks.

    the article says:

    "The row directory contains information about how many rows of data are in the block and for each of these rows there is a two byte entry that acts as a pointer to the actual data of the row. This pointer is added to the offset into the file of the start of the block’s row directory to give an offset to the location of the row of data"

    so it seems to me that the row directory does not store the rowid, but rather just the number of bytes from the beginning of the data block? is this correct or have I misunderstood it?

    thanks
  • 3. Re: what does the "row directory" look like?
    hans forbrich Oracle ACE Director
    Currently Being Moderated
    OracleGuy777 wrote:
    the article says:

    "The row directory contains information about how many rows of data are in the block and for each of these rows there is a two byte entry that acts as a pointer to the actual data of the row. This pointer is added to the offset into the file of the start of the block’s row directory to give an offset to the location of the row of data"

    so it seems to me that the row directory does not store the rowid, but rather just the number of bytes from the beginning of the data block? is this correct or have I misunderstood it?
    Yup.

    So the rowid says "the third row in +this specific+ block. And the directory says "here is where you find the third row in the block". That allows the row to move around in the block if it changes size.
  • 4. Re: what does the "row directory" look like?
    OracleGuy777 Newbie
    Currently Being Moderated
    sorry, I am still confused. I still have some questions!

    1), I thought that the rowid was not stored in the data block, but now it seems as if it is.

    2), there seem to be two different definitions of row header that I am getting.

    At http://www.akadia.com/services/ora_chained_rows.html, it says row overhead | # of columns | cluster key | rowid

    At http://www.tlingua.com/new/articles/Chapter1.html, it says Flag Byte | ITL#, # of columns

    I am assuming the first site is talking about the more generic case of data blocks that can include clusters, and the second is not?

    3) I have read up on row migration. But what happens if after an update row can fit inside the data block if coalescing was to happen inside the block. Would Oracle still row migrate, or will it coalesce and keep the whole row in the block? From Hans answer I believe the latter?

    4)
    The documents define the row id as
    *The data object number of the object
    *The data block in the datafile in which the row resides
    *The position of the row in the data block (first row is 0)
    *The datafile in which the row resides (first file is 1). The file number is relative to the tablespace.

    Now, in the row header, will ALL the above informtaion be stored as part of the row id, or, just the row position in the data block? From Hans answer I again believe the latter.

    And it also seems like row directory will say:

    row 0 - 8000 bytes
    row 1 - 6400 bytes
    row 3 - 6000 bytes
    row 4 - 5176 bytes etc ...

    Is this the right format?

    thanks
  • 5. Re: what does the "row directory" look like?
    418213 Newbie
    Currently Being Moderated
    refer to this
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/logical.htm
  • 6. Re: what does the "row directory" look like?
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    OracleGuy777 wrote:
    hi,

    i am confused as to what the entries in a row directory look like. Oracle docs say:

    "This portion of the data block contains information about the actual rows in the block (including addresses for each row piece in the row data area)."
    Dump a (simple heap table) block and have a look:
    alter system dump datafile NNNNN block MMMMMM;
    If you really want to be sure, that's a much better way than asking - and even if you still end up asking, it's a way of checking which answers are consistent with the evidence.

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    
    "Science is more than a body of knowledge; it is a way of thinking" 
    Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 7. Re: what does the "row directory" look like?
    hans forbrich Oracle ACE Director
    Currently Being Moderated
    OracleGuy777 wrote:
    sorry, I am still confused. I still have some questions!

    1), I thought that the rowid was not stored in the data block, but now it seems as if it is.
    You are definitely getting confused. (Perhaps reading too many articles too fast without time to digest? ;-) )

    Row ID basically points to a block, and a 'row inside that block'. So pointing to that block would be redundant.

    As always, there are exceptions - migration and changing are two significant exceptions that occur when the row (or parts of it) can not be found in the current for any of a number of reasons. At that time, the 'forwarding address' needs to be stored.

    >
    2), there seem to be two different definitions of row header that I am getting.
    There ARE several different kinds of rowid. Most have been introduced since Oracle 7. Unfortunately most people (including myself) are sloppy and do not qualify which one is discussed. (Usually because it is not really important to the day-to-day operation of Oracle.)

    >
    At http://www.akadia.com/services/ora_chained_rows.html, it says row overhead | # of columns | cluster key | rowid

    At http://www.tlingua.com/new/articles/Chapter1.html, it says Flag Byte | ITL#, # of columns

    I am assuming the first site is talking about the more generic case of data blocks that can include clusters, and the second is not?
    'Data' blocks can include a number of things. The exact layout of a block depends on the segment type -each segment type (cluster, index, bitmap index, heap table, IOT) will have some variant to the generic descriptions that have been discussed.

    The stuff being discussed here is specific to the common 'heap table' segment, and does not take into account LOB and other variants.
    3) I have read up on row migration. But what happens if after an update row can fit inside the data block if coalescing was to happen inside the block. Would Oracle still row migrate, or will it coalesce and keep the whole row in the block? From Hans answer I believe the latter?
    Not sure what the actual question is here.

    Within a block, a row can move around. If it grows and can not fit into it's original spot, it can move to another 'byte offset' in the current block without any impact - hence the directory. (The rowid says 'find the 3rd block', the directory says '3rd block is here')

    Migrations occur when the row no longer fits in the current and it is easier to relocate it. However, the rowid is already stored in the index, so the current rowid points to a 'forwarding address' (stored rowid) rather than to a specific row. This supports the assumption that rowids do not normally move and avoids the possibility of updating indexes too often. (Which leads to a whole new set of OralceGuy777 questions - which are interesting, helping me reinforce/enhance my knowledge, but also self-researchable.)

    Chaining happens when the row is larger than the current block - the rowid is again stored to help find the next block i the chain of this row.

    And there are other exceptions (LOBs come to mind).

    >
    4)
    The documents define the row id as
    *The data object number of the object
    *The data block in the datafile in which the row resides
    *The position of the row in the data block (first row is 0)
    *The datafile in which the row resides (first file is 1). The file number is relative to the tablespace.

    Now, in the row header, will ALL the above informtaion be stored as part of the row id, or, just the row position in the data block? From Hans answer I again believe the latter.
    There are several different rowids. Assuming the physical rowid, it is not stored unless needed, but can be (and is automatically) derived when you are positioned at a row.

    If you are performing a table scan, you can say "I found this row as part of segment x in tablespace y block z and it is the nth row in that block (based on the directory, not the scan of the block)". No rowid needs to have been been stored.

    On the other hand, an index needs to store a rowid. That is used to say "for the data you want to see, go to file s, block r and look at row u".

    >
    And it also seems like row directory will say:

    row 0 - 8000 bytes
    row 1 - 6400 bytes
    row 3 - 6000 bytes
    row 4 - 5176 bytes etc ...

    Is this the right format?
    You can do a block dump and see for yourself. XE has this capability, is free and has a smaller footprint than other editions.
  • 8. Re: what does the "row directory" look like?
    OracleGuy777 Newbie
    Currently Being Moderated
    yes, block dumps seem cool but to be honest I still do not feel comfortable with them, I might misinterpret them, hence why I ask here - and no, I am not being lazy, I am reading a lot of articles, but some of this stuff is just not clear to me, sometimes I like to see things pictorially to get a better understanding.

    your reply was very helpful, but there are still a few things I am a little unclear about, I appreciate you have already helped me a lot and I don't want to take up all your time, but if someone could just clarify these points for me, i'd really appreciate it

    thanks
    Hans Forbrich wrote:

    Row ID basically points to a block, and a 'row inside that block'. So pointing to that block would be redundant.
    sorry, that confused me. I was thinking rowid is just an id for a row in the data block and is stored in the row header, and that it does not point to anything!


    >
    Within a block, a row can move around. If it grows and can not fit into it's original spot, it can move to another 'byte offset' in the current block without any impact - hence the directory. (The rowid says 'find the 3rd block', the directory says '3rd block is here')
    so the above means that the row header will contain a byte for rowid, the rowid will state its ij (i.e. 3), and then the row directory will state where row 3 can be found, yes?

    >
    There are several different rowids. Assuming the physical rowid, it is not stored unless needed, but can be (and is automatically) derived when you are positioned at a row.
    Argh!!! Confused again! So when we are talking about the rowids above, I thought they WERE physical rowids (i.e. the 3 we're talking about, I thought that is a physical rowid and is stored in the row header).

    >>
    And it also seems like row directory will say:

    row 0 - 8000 bytes
    row 1 - 6400 bytes
    row 3 - 6000 bytes
    row 4 - 5176 bytes etc ...

    Is this the right format?
    You can do a block dump and see for yourself. XE has this capability, is free and has a smaller footprint than other editions.
    I have Oracle at work, is there a utility or something I can use that is available that kinf of helps decipher data blocks?

    thanks
  • 9. Re: what does the "row directory" look like?
    hans forbrich Oracle ACE Director
    Currently Being Moderated
    OracleGuy777 wrote:
    Hans Forbrich wrote:

    Row ID basically points to a block, and a 'row inside that block'. So pointing to that block would be redundant.
    sorry, that confused me. I was thinking rowid is just an id for a row in the data block and is stored in the row header, and that it does not point to anything!
    << updated --
    RowID contains several parts: object, file, block and row within block. It contains ALL those parts.

    It is a logical construct. WHen you select a row from a heap table, you can get back the rowid. It is generated at run time to show the information above. It says "this row for this object was found in the following file, in the following block. The physical location was determined by looking at the 'row' entry in the directory."

    Since it can be generated, the value of a RowID may also be stored or kept in memory. In this case it may be used to point to a specific file, block and entry in the row directory. Indexes take advantage of this to provide faster access to a specific piece of data.
    -- to here >>

    >
    >>
    Within a block, a row can move around. If it grows and can not fit into it's original spot, it can move to another 'byte offset' in the current block without any impact - hence the directory. (The rowid says 'find the 3rd block', the directory says '3rd block is here')
    so the above means that the row header will contain a byte for rowid, the rowid will state its ij (i.e. 3), and then the row directory will state where row 3 can be found, yes?
    You are again using rowid wrong here. The rowid contains the object number, the file, the block and the location in the row directory. You appear to want to use the term rowid for the location in the directory, which is inside one block.

    >
    >>
    There are several different rowids. Assuming the physical rowid, it is not stored unless needed, but can be (and is automatically) derived when you are positioned at a row.
    Argh!!! Confused again! So when we are talking about the rowids above, I thought they WERE physical rowids (i.e. the 3 we're talking about, I thought that is a physical rowid and is stored in the row header).
    Rowid is a database-wide concept. You want to look at the block level. You are therefore confusing the two concepts - rowid and 'row entry in the directory'. (I am not sure the latter has a formal name, because few people care to get to that level.) That 'row entry in the directory' is a part of the formal rowid. It is commonly called 'the row in the data block' (Ihttp://download.oracle.com/docs/cd/B28359_01/server.111/b28286/sql_elements001.htm#sthref183)

    Edited by: Hans Forbrich on Nov 10, 2009 10:01 AM - clarifications.
  • 10. Re: what does the "row directory" look like?
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    >
    OracleGuy777 wrote:
    yes, block dumps seem cool but to be honest I still do not feel comfortable with them, I might misinterpret them, hence why I ask here - and no, I am not being lazy, I am reading a lot of articles, but some of this stuff is just not clear to me, sometimes I like to see things pictorially to get a better understanding.
    So do a block dump, and have it in front of you while you read an article.

    For example:

    >>
    Within a block, a row can move around. If it grows and can not fit into it's original spot, it can move to another 'byte offset' in the current block without any impact - hence the directory. (The rowid says 'find the 3rd block', the directory says '3rd block is here')
    so the above means that the row header will contain a byte for rowid, the rowid will state its ij (i.e. 3), and then the row directory will state where row 3 can be found, yes?
    Create a table and insert four rows into it.
    Do a block dump.
    Update the third row you inserted to make it bigger
    Do another block dump.
    Then compare what you see with what Hans said.


    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    "Science is more than a body of knowledge; it is a way of thinking" 
    Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • 11. Re: what does the "row directory" look like?
    OracleGuy777 Newbie
    Currently Being Moderated
    TEST_DB > alter system dump datafile 1 block 30097;

    System altered.

    TEST_DB > insert into testing values (1,'hello');

    1 row created.

    TEST_DB > insert into testing values (2,'world');

    1 row created.

    TEST_DB > insert into testing values (3,'goodbye');

    1 row created.

    TEST_DB > update testing set colB='let us increase this value by quite a bit' where colA=2;

    1 row updated.

    TEST_DB > alter system dump datafile 1 block 30097;

    System altered.



    This is what the dump looked like first before:
    Start dump data blocks tsn: 0 file#: 1 minblk 30097 maxblk 30097
    buffer tsn: 0 rdba: 0x00407591 (1/30097)
    scn: 0x0000.005ca414 seq: 0x01 flg: 0x00 tail: 0xa4141001
    frmt: 0x02 chkval: 0x0000 type: 0x10=DATA SEGMENT HEADER - UNLIMITED
    Hex dump of block: st=0, typ_found=1
    Dump of memory from 0x00000000099A9400 to 0x00000000099AB400
    0099A9400 0000A210 00407591 005CA414 00010000  [.....u@...\.....]
    0099A9410 00000000 00000000 00000000 00000000  [................]
    0099A9420 00000000 00000001 00000007 00001020  [............ ...]
    0099A9430 00000000 00000000 00000007 00407592  [.............u@.]
    0099A9440 00000000 00000000 00000000 00000000  [................]
    0099A9450 00000000 00000000 00000000 00000001  [................]
    0099A9460 00000000 0000520D 40000000 00407592  [.....R.....@.u@.]
    0099A9470 00000007 00000000 00000000 00000000  [................]
    0099A9480 00000000 00000000 00000000 00000000  [................]
            Repeat 250 times
    0099AA430 00000000 00010000 00010001 00000000  [................]
    0099AA440 00000000 00000000 00000000 00000000  [................]
            Repeat 250 times
    0099AB3F0 00000000 00000000 00000000 A4141001  [................]
      Extent Control Header
      -----------------------------------------------------------------
      Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 7
                      last map  0x00000000  #maps: 0      offset: 4128
          Highwater::  0x00407592  ext#: 0      blk#: 0      ext size: 7
      #blocks in seg. hdr's freelists: 0
      #blocks below: 0
      mapblk  0x00000000  offset: 0
                       Unlocked
         Map Header:: next  0x00000000  #extents: 1    obj#: 21005  flag: 0x40000000
      Extent Map
      -----------------------------------------------------------------
       0x00407592  length: 7
    
      nfl = 1, nfb = 1 typ = 1 nxf = 0 ccnt = 0
      SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000
    End dump data blocks tsn: 0 file#: 1 minblk 30097 maxblk 30097
    This is what it looks like now:
    Start dump data blocks tsn: 0 file#: 1 minblk 30097 maxblk 30097
    buffer tsn: 0 rdba: 0x00407591 (1/30097)
    scn: 0x0000.005ca552 seq: 0x01 flg: 0x00 tail: 0xa5521001
    frmt: 0x02 chkval: 0x0000 type: 0x10=DATA SEGMENT HEADER - UNLIMITED
    Hex dump of block: st=0, typ_found=1
    Dump of memory from 0x00000000099A9400 to 0x00000000099AB400
    0099A9400 0000A210 00407591 005CA552 00010000  [.....u@.R.\.....]
    0099A9410 00000000 00000000 00000000 00000000  [................]
    0099A9420 00000000 00000001 00000007 00001020  [............ ...]
    0099A9430 00000000 00000001 00000007 00407593  [.............u@.]
    0099A9440 00000000 00000000 00000001 00000001  [................]
    0099A9450 00000000 00000000 00000000 00000001  [................]
    0099A9460 00000000 0000520D 40000000 00407592  [.....R.....@.u@.]
    0099A9470 00000007 00000000 00000000 00000000  [................]
    0099A9480 00000000 00000000 00000000 00000000  [................]
            Repeat 250 times
    0099AA430 00000000 00010000 00010001 00000001  [................]
    0099AA440 00000000 00000001 00407592 00407592  [.........u@..u@.]
    0099AA450 00000000 00000000 00000000 00000000  [................]
            Repeat 249 times
    0099AB3F0 00000000 00000000 00000000 A5521001  [..............R.]
      Extent Control Header
      -----------------------------------------------------------------
      Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 7
                      last map  0x00000000  #maps: 0      offset: 4128
          Highwater::  0x00407593  ext#: 0      blk#: 1      ext size: 7
      #blocks in seg. hdr's freelists: 1
      #blocks below: 1
      mapblk  0x00000000  offset: 0
                       Unlocked
         Map Header:: next  0x00000000  #extents: 1    obj#: 21005  flag: 0x40000000
      Extent Map
      -----------------------------------------------------------------
       0x00407592  length: 7
    
      nfl = 1, nfb = 1 typ = 1 nxf = 0 ccnt = 1
      SEG LST:: flg: USED   lhd: 0x00407592 ltl: 0x00407592
    End dump data blocks tsn: 0 file#: 1 minblk 30097 maxblk 30097
    what should I be looking for here if I want to see row directory information? Any pointers will be appreciated.

    thanks
  • 12. Re: what does the "row directory" look like?
    OracleGuy777 Newbie
    Currently Being Moderated
    Great. To sum up

    1) rowid is really a logical construct that is computed on the fly. It is not stored anywhere (but if we wanted to, we could store it as an additional column as part of the column data).

    2) Each row in a data block has an id to identify it, and this id is stored in the row header (this will just be to say that it is the nth row).

    3) The row directory will contain a mapping table of how many bytes offset each row is from the start of the block.

    If I am still not correct I am going to go jump off a bridge! ;)
  • 13. Re: what does the "row directory" look like?
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    OracleGuy777 wrote:

    what should I be looking for here if I want to see row directory information? Any pointers will be appreciated.
    See the bit which says "type: 0x10=DATA SEGMENT HEADER - UNLIMITED" ?

    How did you pick which block to dump ? If you want to learn the internals of Oracle (or anything, for that matter) it's hard to know what you need to know to get started - but before you start learning about what's inside blocks (which is only slightly documented) shouldn't you first learn about what a data segment is and what it's made from and so on.

    Do you know the large-scale difference between a table which is created using freelist management compared to a table using ASSM ? It's in the manuals, it should have given you some clue about why the block you dumped was not going to help.

    Do you know that a (common type of) rowid gives an object_id, file_id, block_id and row within block ? This is also in the manuals.

    How about querying your table for rowids and picking out the file and block id as a way of dumping the right block ? Do you know about the dbms_rowid package that let's you translate rowids to file ane block ids.


    P.S.+ Out of idle curiosity I did a google search for [ +*row directory oracle*+|http://www.google.co.uk/search?hl=en&source=hp&q=%22row+directory%22+oracle&meta=&aq=f&oq=] The second item on the result set (at the time of writing) was a link to a note on my blog which actually lists an extract from a table block dump to show some of the row directory and some row entries.


    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    "Science is more than a body of knowledge; it is a way of thinking" 
    Carl Sagan
    
    Edited by: Jonathan Lewis on Nov 10, 2009 10:26 PM
    Add google link                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 14. Re: what does the "row directory" look like?
    OracleGuy777 Newbie
    Currently Being Moderated
    Jonathan Lewis wrote:
    OracleGuy777 wrote:

    what should I be looking for here if I want to see row directory information? Any pointers will be appreciated.
    See the bit which says "type: 0x10=DATA SEGMENT HEADER - UNLIMITED" ?

    How did you pick which block to dump ? If you want to learn the internals of Oracle (or anything, for that matter) it's hard to know what you need to know to get started - but before you start learning about what's inside blocks (which is only slightly documented) shouldn't you first learn about what a data segment is and what it's made from and so on.
    yep, I do know the difference! I looked in dba_extents, I did

    select * from dba_extents where segment_name='TESTING'

    one row was returned, and I was careless and just took the block id, without actually thinking that there are 8 blocks in this extent.

    >
    Do you know the large-scale difference between a table which is created using freelist management compared to a table using ASSM ? It's in the manuals, it should have given you some clue about why the block you dumped was not going to help.
    I did look at a slideshow by Tanel Poder on this, where he goes right down into the details of what an ASSM segment header looks like etc. So, yes, I do have some understanding of it, but I still havent yet fully comprehended that stuff yet.

    >
    Do you know that a (common type of) rowid gives an object_id, file_id, block_id and row within block ? This is also in the manuals.

    How about querying your table for rowids and picking out the file and block id as a way of dumping the right block ?
    yes, I should have thought of this. thanks.

    Do you know about the dbms_rowid package that let's you translate rowids to file ane block ids.
    >

    No, I didnt know about this one. thanks
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points