DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 28

Thread: Column data in Oracle block

  1. #1
    Join Date
    May 2006
    Posts
    41

    Column data in Oracle block

    Consider a table having 3 columns,namely EMP(c1,c2,c3)

    Using data block header,Oracle identifies that a block containing rows
    for EMP table.My doubt in this is,

    In the row data of a block,how oracle identifies that this piece of data belong to a particular column(i.e.,column data) of EMP table i.e,how it identifies this portion of data belongs to C1,C2 & C3? Is oracle following any offset mechanism for this classification?Please suggest your answers & views on this question.

  2. #2
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    I don't think Oracle explained in that level.

    http://www.stanford.edu/dept/itss/do...43/logical.htm

    Explaing about, how the Blocks are confiured.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  3. #3
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Code:
    data_block_dump,data header at 0xd603864
    ===============
    tsiz: 0x1f98
    hsiz: 0xa2
    pbl: 0x0d603864
    bdba: 0x01008398
         76543210
    flag=--------
    ntab=1
    nrow=72
    frre=-1
    fsbo=0xa2
    fseo=0x1cc8
    avsp=0x1c26
    tosp=0x1c26
    0xe:pti[0]	nrow=72	offs=0
    0x12:pri[0]	offs=0x1f8e
    0x14:pri[1]	offs=0x1f84
    0x16:pri[2]	offs=0x1f7a
    0x18:pri[3]	offs=0x1f70
    0x1a:pri[4]	offs=0x1f66
    0x1c:pri[5]	offs=0x1f5c
    0x1e:pri[6]	offs=0x1f52
    0x20:pri[7]	offs=0x1f48
    0x22:pri[8]	offs=0x1f3e
    0x24:pri[9]	offs=0x1f34
    0x26:pri[10]	offs=0x1f2a
    0x28:pri[11]	offs=0x1f20
    0x2a:pri[12]	offs=0x1f16
    0x2c:pri[13]	offs=0x1f0c
    0x2e:pri[14]	offs=0x1f02
    0x30:pri[15]	offs=0x1ef8
    0x32:pri[16]	offs=0x1eee
    0x34:pri[17]	offs=0x1ee4
    0x36:pri[18]	offs=0x1eda
    0x38:pri[19]	offs=0x1ed0
    0x3a:pri[20]	offs=0x1ec6
    0x3c:pri[21]	offs=0x1ebc
    0x3e:pri[22]	offs=0x1eb2
    0x40:pri[23]	offs=0x1ea8
    0x42:pri[24]	offs=0x1e9e
    0x44:pri[25]	offs=0x1e94
    0x46:pri[26]	offs=0x1e8a
    0x48:pri[27]	offs=0x1e80
    0x4a:pri[28]	offs=0x1e76
    0x4c:pri[29]	offs=0x1e6c
    0x4e:pri[30]	offs=0x1e62
    0x50:pri[31]	offs=0x1e58
    0x52:pri[32]	offs=0x1e4e
    0x54:pri[33]	offs=0x1e44
    0x56:pri[34]	offs=0x1e3a
    0x58:pri[35]	offs=0x1e30
    0x5a:pri[36]	offs=0x1e26
    0x5c:pri[37]	offs=0x1e1c
    0x5e:pri[38]	offs=0x1e12
    0x60:pri[39]	offs=0x1e08
    0x62:pri[40]	offs=0x1dfe
    0x64:pri[41]	offs=0x1df4
    0x66:pri[42]	offs=0x1dea
    0x68:pri[43]	offs=0x1de0
    0x6a:pri[44]	offs=0x1dd6
    0x6c:pri[45]	offs=0x1dcc
    0x6e:pri[46]	offs=0x1dc2
    0x70:pri[47]	offs=0x1db8
    0x72:pri[48]	offs=0x1dae
    0x74:pri[49]	offs=0x1da4
    0x76:pri[50]	offs=0x1d9a
    0x78:pri[51]	offs=0x1d90
    0x7a:pri[52]	offs=0x1d86
    0x7c:pri[53]	offs=0x1d7c
    0x7e:pri[54]	offs=0x1d72
    0x80:pri[55]	offs=0x1d68
    0x82:pri[56]	offs=0x1d5e
    0x84:pri[57]	offs=0x1d54
    0x86:pri[58]	offs=0x1d4a
    0x88:pri[59]	offs=0x1d40
    0x8a:pri[60]	offs=0x1d36
    0x8c:pri[61]	offs=0x1d2c
    0x8e:pri[62]	offs=0x1d22
    0x90:pri[63]	offs=0x1d18
    0x92:pri[64]	offs=0x1d0e
    0x94:pri[65]	offs=0x1d04
    0x96:pri[66]	offs=0x1cfa
    0x98:pri[67]	offs=0x1cf0
    0x9a:pri[68]	offs=0x1ce6
    0x9c:pri[69]	offs=0x1cdc
    0x9e:pri[70]	offs=0x1cd2
    0xa0:pri[71]	offs=0x1cc8
    block_row_dump:
    tab 0, row 0, @0x1f8e
    tl: 10 fb: --H-FL-- lb: 0x1  cc: 2
    col  0: [ 2]  c1 02
    col  1: [ 3]  58 58 58
    tab 0, row 1, @0x1f84
    tl: 10 fb: --H-FL-- lb: 0x1  cc: 2
    col  0: [ 2]  c1 03
    col  1: [ 3]  58 58 58
    tab 0, row 2, @0x1f7a
    tl: 10 fb: --H-FL-- lb: 0x1  cc: 2
    col  0: [ 2]  c1 04
    col  1: [ 3]  58 58 58
    tab 0, row 3, @0x1f70
    tl: 10 fb: --H-FL-- lb: 0x1  cc: 2
    col  0: [ 2]  c1 02
    col  1: [ 3]  59 59 59
    tab 0, row 4, @0x1f66
    tl: 10 fb: --H-FL-- lb: 0x1  cc: 2
    col  0: [ 2]  c1 03
    col  1: [ 3]  59 59 59
    tab 0, row 5, @0x1f5c
    tl: 10 fb: --H-FL-- lb: 0x1  cc: 2
    col  0: [ 2]  c1 04
    col  1: [ 3]  59 59 59
    tab 0, row 6, @0x1f52
    tl: 10 fb: --H-FL-- lb: 0x1  cc: 2
    col  0: [ 2]  c1 02
    col  1: [ 3]  5a 5a 5a
    tab 0, row 7, @0x1f48
    tl: 10 fb: --H-FL-- lb: 0x1  cc: 2
    col  0: [ 2]  c1 03
    col  1: [ 3]  5a 5a 5a
    tab 0, row 8, @0x1f3e
    tl: 10 fb: --H-FL-- lb: 0x1  cc: 2
    col  0: [ 2]  c1 04
    col  1: [ 3]  5a 5a 5a
    tab 0, row 9, @0x1f34
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 02
    col  1: [ 3]  58 58 58
    tab 0, row 10, @0x1f2a
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 03
    col  1: [ 3]  58 58 58
    tab 0, row 11, @0x1f20
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 04
    col  1: [ 3]  58 58 58
    tab 0, row 12, @0x1f16
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 02
    col  1: [ 3]  59 59 59
    tab 0, row 13, @0x1f0c
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 03
    col  1: [ 3]  59 59 59
    tab 0, row 14, @0x1f02
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 04
    col  1: [ 3]  59 59 59
    tab 0, row 15, @0x1ef8
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 02
    col  1: [ 3]  5a 5a 5a
    tab 0, row 16, @0x1eee
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 03
    col  1: [ 3]  5a 5a 5a
    tab 0, row 17, @0x1ee4
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 04
    col  1: [ 3]  5a 5a 5a
    tab 0, row 18, @0x1eda
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 02
    col  1: [ 3]  58 58 58
    tab 0, row 19, @0x1ed0
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 03
    col  1: [ 3]  58 58 58
    tab 0, row 20, @0x1ec6
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 04
    col  1: [ 3]  58 58 58
    tab 0, row 21, @0x1ebc
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 02
    col  1: [ 3]  59 59 59
    tab 0, row 22, @0x1eb2
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 03
    col  1: [ 3]  59 59 59
    tab 0, row 23, @0x1ea8
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 04
    col  1: [ 3]  59 59 59
    tab 0, row 24, @0x1e9e
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 02
    col  1: [ 3]  5a 5a 5a
    tab 0, row 25, @0x1e94
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 03
    col  1: [ 3]  5a 5a 5a
    tab 0, row 26, @0x1e8a
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 04
    col  1: [ 3]  5a 5a 5a
    tab 0, row 27, @0x1e80
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 02
    col  1: [ 3]  58 58 58
    tab 0, row 28, @0x1e76
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 03
    col  1: [ 3]  58 58 58
    tab 0, row 29, @0x1e6c
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 04
    col  1: [ 3]  58 58 58
    tab 0, row 30, @0x1e62
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 02
    col  1: [ 3]  59 59 59
    tab 0, row 31, @0x1e58
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 03
    col  1: [ 3]  59 59 59
    tab 0, row 32, @0x1e4e
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 04
    col  1: [ 3]  59 59 59
    tab 0, row 33, @0x1e44
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 02
    col  1: [ 3]  5a 5a 5a
    tab 0, row 34, @0x1e3a
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 03
    col  1: [ 3]  5a 5a 5a
    tab 0, row 35, @0x1e30
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 04
    col  1: [ 3]  5a 5a 5a
    tab 0, row 36, @0x1e26
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 02
    col  1: [ 3]  58 58 58
    tab 0, row 37, @0x1e1c
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 03
    col  1: [ 3]  58 58 58
    tab 0, row 38, @0x1e12
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 04
    col  1: [ 3]  58 58 58
    tab 0, row 39, @0x1e08
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 02
    col  1: [ 3]  59 59 59
    tab 0, row 40, @0x1dfe
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 03
    col  1: [ 3]  59 59 59
    tab 0, row 41, @0x1df4
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 04
    col  1: [ 3]  59 59 59
    tab 0, row 42, @0x1dea
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 02
    col  1: [ 3]  5a 5a 5a
    tab 0, row 43, @0x1de0
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 03
    col  1: [ 3]  5a 5a 5a
    tab 0, row 44, @0x1dd6
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 04
    col  1: [ 3]  5a 5a 5a
    tab 0, row 45, @0x1dcc
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 02
    col  1: [ 3]  58 58 58
    tab 0, row 46, @0x1dc2
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 03
    col  1: [ 3]  58 58 58
    tab 0, row 47, @0x1db8
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 04
    col  1: [ 3]  58 58 58
    tab 0, row 48, @0x1dae
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 02
    col  1: [ 3]  59 59 59
    tab 0, row 49, @0x1da4
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 03
    col  1: [ 3]  59 59 59
    tab 0, row 50, @0x1d9a
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 04
    col  1: [ 3]  59 59 59
    tab 0, row 51, @0x1d90
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 02
    col  1: [ 3]  5a 5a 5a
    tab 0, row 52, @0x1d86
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 03
    col  1: [ 3]  5a 5a 5a
    tab 0, row 53, @0x1d7c
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 04
    col  1: [ 3]  5a 5a 5a
    tab 0, row 54, @0x1d72
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 02
    col  1: [ 3]  58 58 58
    tab 0, row 55, @0x1d68
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 03
    col  1: [ 3]  58 58 58
    tab 0, row 56, @0x1d5e
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 04
    col  1: [ 3]  58 58 58
    tab 0, row 57, @0x1d54
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 02
    col  1: [ 3]  59 59 59
    tab 0, row 58, @0x1d4a
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 03
    col  1: [ 3]  59 59 59
    tab 0, row 59, @0x1d40
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 04
    col  1: [ 3]  59 59 59
    tab 0, row 60, @0x1d36
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 02
    col  1: [ 3]  5a 5a 5a
    tab 0, row 61, @0x1d2c
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 03
    col  1: [ 3]  5a 5a 5a
    tab 0, row 62, @0x1d22
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 04
    col  1: [ 3]  5a 5a 5a
    tab 0, row 63, @0x1d18
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 02
    col  1: [ 3]  58 58 58
    tab 0, row 64, @0x1d0e
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 03
    col  1: [ 3]  58 58 58
    tab 0, row 65, @0x1d04
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 04
    col  1: [ 3]  58 58 58
    tab 0, row 66, @0x1cfa
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 02
    col  1: [ 3]  59 59 59
    tab 0, row 67, @0x1cf0
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 03
    col  1: [ 3]  59 59 59
    tab 0, row 68, @0x1ce6
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 04
    col  1: [ 3]  59 59 59
    tab 0, row 69, @0x1cdc
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 02
    col  1: [ 3]  5a 5a 5a
    tab 0, row 70, @0x1cd2
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 03
    col  1: [ 3]  5a 5a 5a
    tab 0, row 71, @0x1cc8
    tl: 10 fb: --H-FL-- lb: 0x2  cc: 2
    col  0: [ 2]  c1 04
    col  1: [ 3]  5a 5a 5a
    end_of_block_dump
    The block dump shows ROW DIRECTORY (72 rows) and each column is separated by column number. Definitely Oracle is using certain internal algorithm to track this !!
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    I would assume there will be some number appended to the data in each column for each row or in other words some number (number corresponding to the col number) will be appened along with data while insertion.. so that it can decode while retrival.

    - Abhay.
    Last edited by abhaysk; 05-22-2006 at 07:37 AM.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    excuse my ignorance but knowing this little tid bit of information is going to help me in my daily DBA duties how?
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    not at all... this info may be for curiosity.. or may be for some research?
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  7. #7
    Join Date
    Feb 2006
    Posts
    162
    Thanks Thomasps,

    Using which tool,you've generated that,"data_block_dump".

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    logical block dump:

    alter system dump datafile 10 block 104;

    physical dump

    dd if=yourdatafile bs=blocksize skip=104 count=1 (nuber of blocks to dump)

  9. #9
    Join Date
    Jun 2006
    Posts
    259
    Quote Originally Posted by dba127
    Consider a table having 3 columns,namely EMP(c1,c2,c3)

    Using data block header,Oracle identifies that a block containing rows
    for EMP table.My doubt in this is,

    In the row data of a block,how oracle identifies that this piece of data belong to a particular column(i.e.,column data) of EMP table i.e,how it identifies this portion of data belongs to C1,C2 & C3? Is oracle following any offset mechanism for this classification?Please suggest your answers & views on this question.
    Yes it certainly is.

    The block contains a table directory (offsets for clustered table ).. And Row directory an offset list for the starting address for row data that is all relative to the blocks header structures.

    Each row has some flag fields as well as a count of the columns for the row. Combine that whith some basic info from sys.col$ to generate a mapping between the internal column and the external column position.

  10. #10
    Join Date
    Jun 2006
    Posts
    259
    Quote Originally Posted by abhaysk
    I would assume there will be some number appended to the data in each column for each row or in other words some number (number corresponding to the col number) will be appened along with data while insertion.. so that it can decode while retrival.

    - Abhay.
    - Abhay,

    There is no column number appended to each column during insertion.

    Each column is in a physical position that is determined by the sys.col$ table.

    Writing a "logical" column number into the bock would waste a lot of space. Oracle is very space efficient. For instance, the length of the column is stored along with the column data. If it < 255 bytes in lenght then a single byte is used to store the length. But if it longer then 3 bytes are used to store the length.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width