Sundaramurthy Blog

June 18, 2010

Oracle Data Block Internals (DBA 4,31)

Filed under: Oracle internals — sundar5 @ 2:22 pm

When you create a table object on a tablespace, oracle creates the segment header block, BMB (Bit Map Block) block and data blocks in the data file.  Below is the data block internal structure  where the input data gets inserted.

For example, I have created the  following table structure and inserted the record.  See the record in the data block.

SQL> CREATE TABLE T1 (N1 number, desc1 varchar2(10))  TABLESPACE POCTABLESPACE;

SQL> SELECT OBJECT_NAME,OBJECT_ID,OBJECT_TYPE FROM DBA_OBJECTS

WHERE OBJECT_NAME=’T1′ AND OWNER=’SYS’

OBJECT_NAM  OBJECT_ID OBJECT_TYPE

———- ———- ————-

T1               9858 TABLE

SQL> INSERT INTO T1 VALUES (100,’TESTDATA’);

1 row created.
SQL> COMMIT;
SQL> select * from t1;
N1 DESC1
---------- ----------
100 TESTDATA

Let see how oracle internally store the data in hex format:

SQL> select dump(100,16),dump(‘TESTDATA’,16) FROM DUAL;

—————– ————————————-

Typ=2 Len=2: c2,2 Typ=96 Len=8: 54,45,53,54,44,41,54,41

Transaction data Block Internal Structure:

Block header
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 0x06
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x0100001f
ub4 bas_kcbh @8 0x00054f17
ub2 wrp_kcbh @12 0x0000
ub1 seq_kcbh @14 0x01
ub1 flg_kcbh @15 0x04 (KCBHFCKV)
ub2 chkval_kcbh @16 0x6fc8
ub2 spare3_kcbh @18 0x0000
Transaction Layer
struct ktbbh, 72 bytes @20
ub1 ktbbhtyp @20 0x01 (KDDBTDATA)
union ktbbhsid, 4 bytes @24
ub4 ktbbhsg1 @24 0x00002682
ub4 ktbbhod1 @24 0x00002682
struct ktbbhcsc, 8 bytes @28
ub4 kscnbas @28 0x00054f17
ub2 kscnwrp @32 0x0000
b2 ktbbhict @36 7938
ub1 ktbbhflg @38 0x32 (NONE)
ub1 ktbbhfsl @39 0x00
ub4 ktbbhfnx @40 0x01000009
struct ktbbhitl[0], 24 bytes @44
struct ktbitxid, 8 bytes @44
ub2 kxidusn @44 0x000a
ub2 kxidslt @46 0x000f
ub4 kxidsqn @48 0x000000ef
struct ktbituba, 8 bytes @52
ub4 kubadba @52 0x00800318
ub2 kubaseq @56 0x0041
ub1 kubarec @58 0x22
ub2 ktbitflg @60 0x8000 (KTBFCOM)
union _ktbitun, 2 bytes @62
b2 _ktbitfsc @62 0
ub2 _ktbitwrp @62 0x0000
ub4 ktbitbas @64 0x00054ec3
struct ktbbhitl[1], 24 bytes @68
struct ktbitxid, 8 bytes @68
ub2 kxidusn @68 0x0000
ub2 kxidslt @70 0x0000
ub4 kxidsqn @72 0x00000000
struct ktbituba, 8 bytes @76
ub4 kubadba @76 0x00000000
ub2 kubaseq @80 0x0000
ub1 kubarec @82 0x00
ub2 ktbitflg @84 0x0000 (NONE)
union _ktbitun, 2 bytes @86
b2 _ktbitfsc @86 0
ub2 _ktbitwrp @86 0x0000
ub4 ktbitbas @88 0x00000000
Table Directory
struct kdbh, 14 bytes @100
ub1 kdbhflag @100 0x00 (NONE)
b1 kdbhntab @101 1
b2 kdbhnrow @102 1
Free List
sb2 kdbhfrre @104 -1
sb2 kdbhfsbo @106 20
sb2 kdbhfseo @108 8073
b2 kdbhavsp @110 8053
b2 kdbhtosp @112 8053
Row Directory
sb2 kdbr[0] @118 8073
rowdata[0]
———-
ub1 rowdata[0] @8173 0x2c
Row
rowdata[0] @8173
———-
flag@8173: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8174: 0x00
cols@8175:    2
col    0[2] @8176: 100
col    1[8] @8179: TESTDATA

Transaction Data block:

Legend:

Advertisements

June 15, 2010

Data file first block structure (DBA 4,1)

Filed under: Oracle Block,Oracle internals — sundar5 @ 6:32 pm

When you create any tablespace in the oracle environment, it reserves the first block of a data file for an  internal purpose. All information related to checkpoint, recovery status, SCN etc. are kept in the first block.  When you open the database, oracle always validates  the control file information with the first block of data files. Below is the structure of the first block.

Data file internal structure dba 4,1
UNIT       NAME OFFSET VALUE
struct kcvfh, 676 bytes @0
struct kcvfhbfh, 20 bytes @0
ub1 type_kcbh @0 0x0b
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x01000001
ub4 bas_kcbh @8 0x00000000
ub2 wrp_kcbh @12 0x0000
ub1 seq_kcbh @14 0x01
ub1 flg_kcbh @15 0x04 (KCBHFCKV)
ub2 chkval_kcbh @16 0x8412
ub2 spare3_kcbh @18 0x0000
struct kcvfhhdr, 76 bytes @20
ub4 kccfhswv @20 0x00000000
ub4 kccfhcvn @24 0x0a200000
ub4 kccfhdbi @28 0x43f4c8dc
text kccfhdbn[0] @32 E
text kccfhdbn[1] @33 T
text kccfhdbn[2] @34 L
text kccfhdbn[3] @35 T
text kccfhdbn[4] @36 E
text kccfhdbn[5] @37 S
text kccfhdbn[6] @38 T
text kccfhdbn[7] @39
ub4 kccfhcsq @40 0x00000192
ub4 kccfhfsz @44 0x00006400
s_blkz kccfhbsz @48 0x00
ub2 kccfhfno @52 0x0004
ub2 kccfhtyp @54 0x0003
ub4 kccfhacid @56 0x00000000
ub4 kccfhcks @60 0x00000000
text kccfhtag[0] @64
text kccfhtag[1] @65
text kccfhtag[2] @66
text kccfhtag[3] @67
text kccfhtag[4] @68
text kccfhtag[5] @69
text kccfhtag[6] @70
text kccfhtag[7] @71
text kccfhtag[8] @72
text kccfhtag[9] @73
text kccfhtag[10] @74
text kccfhtag[11] @75
text kccfhtag[12] @76
text kccfhtag[13] @77
text kccfhtag[14] @78
text kccfhtag[15] @79
text kccfhtag[16] @80
text kccfhtag[17] @81
text kccfhtag[18] @82
text kccfhtag[19] @83
text kccfhtag[20] @84
text kccfhtag[21] @85
text kccfhtag[22] @86
text kccfhtag[23] @87
text kccfhtag[24] @88
text kccfhtag[25] @89
text kccfhtag[26] @90
text kccfhtag[27] @91
text kccfhtag[28] @92
text kccfhtag[29] @93
text kccfhtag[30] @94
text kccfhtag[31] @95
ub4 kcvfhrdb @96 0x00000000
struct kcvfhcrs, 8 bytes @100
ub4 kscnbas @100 0x0002a4da
ub2 kscnwrp @104 0x0000
ub4 kcvfhcrt @108 0x2ae967a3
ub4 kcvfhrlc @112 0x2ae9645c
struct kcvfhrls, 8 bytes @116
ub4 kscnbas @116 0x00000001
ub2 kscnwrp @120 0x0000
ub4 kcvfhbti @124 0x00000000
struct kcvfhbsc, 8 bytes @128
ub4 kscnbas @128 0x00000000
ub2 kscnwrp @132 0x0000
ub2 kcvfhbth @136 0x0000
ub2 kcvfhsta @138 0x0004 (KCVFHOFZ)
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x000cb354
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x2b0044ee
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x0000000a
ub4 kcrbabno @504 0x00000002
ub2 kcrbabof @508 0x0010
ub1 kcvcpetb[0] @512 0x02
ub1 kcvcpetb[1] @513 0x00
ub1 kcvcpetb[2] @514 0x00
ub1 kcvcpetb[3] @515 0x00
ub1 kcvcpetb[4] @516 0x00
ub1 kcvcpetb[5] @517 0x00
ub1 kcvcpetb[6] @518 0x00
ub1 kcvcpetb[7] @519 0x00
ub4 kcvfhcpc @140 0x00000010
ub4 kcvfhrts @144 0x00000000
ub4 kcvfhccc @148 0x0000000f
struct kcvfhbcp, 36 bytes @152
struct kcvcpscn, 8 bytes @152
ub4 kscnbas @152 0x00000000
ub2 kscnwrp @156 0x0000
ub4 kcvcptim @160 0x00000000
ub2 kcvcpthr @164 0x0000
union u, 12 bytes @168
struct kcvcprba, 12 bytes @168
ub4 kcrbaseq @168 0x00000000
ub4 kcrbabno @172 0x00000000
ub2 kcrbabof @176 0x0000
ub1 kcvcpetb[0] @180 0x00
ub1 kcvcpetb[1] @181 0x00
ub1 kcvcpetb[2] @182 0x00
ub1 kcvcpetb[3] @183 0x00
ub1 kcvcpetb[4] @184 0x00
ub1 kcvcpetb[5] @185 0x00
ub1 kcvcpetb[6] @186 0x00
ub1 kcvcpetb[7] @187 0x00
ub4 kcvfhbhz @312 0x00000000
struct kcvfhxcd, 16 bytes @316
ub4 space_kcvmxcd[0] @316 0x00000000
ub4 space_kcvmxcd[1] @320 0x00000000
ub4 space_kcvmxcd[2] @324 0x00000000
ub4 space_kcvmxcd[3] @328 0x00000000
word kcvfhtsn @332 4
ub2 kcvfhtln @336 0x000d
text kcvfhtnm[0] @338 P
text kcvfhtnm[1] @339 O
text kcvfhtnm[2] @340 C
text kcvfhtnm[3] @341 T
text kcvfhtnm[4] @342 A
text kcvfhtnm[5] @343 B
text kcvfhtnm[6] @344 L
text kcvfhtnm[7] @345 E
text kcvfhtnm[8] @346 S
text kcvfhtnm[9] @347 P
text kcvfhtnm[10] @348 A
text kcvfhtnm[11] @349 C
text kcvfhtnm[12] @350 E
text kcvfhtnm[13] @351
text kcvfhtnm[14] @352
text kcvfhtnm[15] @353
text kcvfhtnm[16] @354
text kcvfhtnm[17] @355
text kcvfhtnm[18] @356
text kcvfhtnm[19] @357
text kcvfhtnm[20] @358
text kcvfhtnm[21] @359
text kcvfhtnm[22] @360
text kcvfhtnm[23] @361
text kcvfhtnm[24] @362
text kcvfhtnm[25] @363
text kcvfhtnm[26] @364
text kcvfhtnm[27] @365
text kcvfhtnm[28] @366
text kcvfhtnm[29] @367
ub4 kcvfhrfn @368 0x00000004
struct kcvfhrfs, 8 bytes @372
ub4 kscnbas @372 0x00000000
ub2 kscnwrp @376 0x0000
ub4 kcvfhrft @380 0x00000000
struct kcvfhafs, 8 bytes @384
ub4 kscnbas @384 0x00000000
ub2 kscnwrp @388 0x0000
ub4 kcvfhbbc @392 0x00000000
ub4 kcvfhncb @396 0x00000000
ub4 kcvfhmcb @400 0x00000000
ub4 kcvfhlcb @404 0x00000000
ub4 kcvfhbcs @408 0x00000000
ub2 kcvfhofb @412 0x0000
ub2 kcvfhnfb @414 0x0000
ub4 kcvfhprc @416 0x00000000
struct kcvfhprs, 8 bytes @420
ub4 kscnbas @420 0x00000000
ub2 kscnwrp @424 0x0000
struct kcvfhprfs, 8 bytes @428
ub4 kscnbas @428 0x00000000
ub2 kscnwrp @432 0x0000
ub4 kcvfhtrt @444 0x00000000

June 9, 2010

How to calculate oracle row size using block dump?

Filed under: Oracle Block,Oracle internals — sundar5 @ 7:39 pm

Row bytes calculation:

data block internal structure

KTB Data Block (Table/Cluster)

struct kcbh, 20 bytes                  @0
ub1 type_kcbh                              @0
ub1 frmt_kcbh                              @1
ub1 spare1_kcbh                         @2
ub1 spare2_kcbh                         @3
ub4 rdba_kcbh                           @4
ub4 bas_kcbh                            @8
ub2 wrp_kcbh                            @12
ub1 seq_kcbh                            @14
ub1 flg_kcbh                            @15
ub2 chkval_kcbh                         @16
ub2 spare3_kcbh                         @18
struct ktbbh, 72 bytes                  @20
ub1 ktbbhtyp                            @20
union ktbbhsid, 4 bytes                 @24
struct ktbbhcsc, 8 bytes                @28
b2 ktbbhict                             @36
ub1 ktbbhflg                            @38
ub1 ktbbhfsl                            @39
ub4 ktbbhfnx                            @40
struct ktbbhitl[2], 48 bytes            @44

ITL STRUCTURE
————–
struct ktbbhitl[0], 24 bytes                @44
struct ktbitxid, 8 bytes                 @44
ub2 kxidusn                           @44       0x0001
ub2 kxidslt                           @46       0x000f
ub4 kxidsqn                           @48       0x000000da
struct ktbituba, 8 bytes                 @52
ub4 kubadba                           @52       0x008008d9
ub2 kubaseq                           @56       0x0083
ub1 kubarec                           @58       0x12
ub2 ktbitflg                             @60       0x2003 (KTBFUPB)
union _ktbitun, 2 bytes                  @62
b2 _ktbitfsc                          @62       0
ub2 _ktbitwrp                         @62       0x0000
ub4 ktbitbas                             @64       0x000bd7c1
struct ktbbhitl[1], 24 bytes                @68
struct ktbitxid, 8 bytes                 @68
ub2 kxidusn                           @68       0x0000
ub2 kxidslt                           @70       0x0000
ub4 kxidsqn                           @72       0x00000000
struct ktbituba, 8 bytes                 @76
ub4 kubadba                           @76       0x00000000
ub2 kubaseq                           @80       0x0000
ub1 kubarec                           @82       0x00
ub2 ktbitflg                             @84       0x0000 (NONE)
union _ktbitun, 2 bytes                  @86
b2 _ktbitfsc                          @86       0
ub2 _ktbitwrp                         @86       0x0000
ub4 ktbitbas                             @88       0x00000000
struct kdbh, 14 bytes                   @100
ub1 kdbhflag                            @100
b1 kdbhntab                             @101
b2 kdbhnrow                             @102
sb2 kdbhfrre                            @104
sb2 kdbhfsbo                            @106
sb2 kdbhfseo                            @108
b2 kdbhavsp                             @110
b2 kdbhtosp                             @112
struct kdbt[1], 4 bytes                 @114
b2 kdbtoffs                             @114
b2 kdbtnrow                             @116
sb2 kdbr[389]                           @118
ub1 freespace[1052]                     @896
ub1 rowdata[6240]                       @1948
ub4 tailchk                             @8188

row directory
kdbr
sb2 kdbr[0]                              @118      4107
sb2 kdbr[1]                              @120      4119
sb2 kdbr[2]                              @122      4133

row data:
rowdata[2285]                               @4233

————-

flag@4233: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@4234: 0x00

cols@4235:    2

col    0[2] @4236: 28

col    1[4] @4239: CON$

rowdata[2296]                               @4244

————-

flag@4244: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@4245: 0x00

cols@4246:    2

col    0[2] @4247: 15

col    1[5] @4250: UNDO$

rowdata[2308]                               @4256

————-

flag@4256: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@4257: 0x00

cols@4258:    2

col    0[2] @4259: 29

col    1[7] @4262: C_COBJ#

3 bytes for row header
1 bytes for each colume size
3 bytes for column size, if colume value  is more than 254 bytes(xfe xxxx)
x bytes for column data
1 bytes if column value is null

dump the block using  oracle command and locate the rows in the bottom of the block

Example 1

tab 0, row 0, @0x1f88
tl: 16 fb: –H-FL– lb: 0x0 cc: 2
col 0: [ 2] c2 02
col 1: [ 9] 54 45 53 54 44 41 54 41 31

16 = 3 + 1 + 2 +1 + 9

Example 2

tab 0, row 0, @0x1f89
tl: 15 fb: –H-FL– lb: 0x0 cc: 2
col 0: [ 1] 80
col 1: [ 9] 54 45 53 54 44 41 54 41 30

15 = 3+1 + 1 + 1 + 9

Example 3

tab 0, row 1, @0x1f78
tl: 17 fb: –H-FL– lb: 0x1 cc: 7
col 0: [ 2] 31 31
col 1: *NULL*
col 2: *NULL*
col 3: *NULL*
col 4: *NULL*
col 5: *NULL*
col 6: [ 5] 46 49 52 53 54

17 =  3 +1 + 2 + 1 +1 + 1 + 1 +1+ 1+5

Example 4

tab 0, row 4, @0x1f4c
tl: 6 fb: –H-FL– lb: 0x2 cc: 1
col 0: [ 2] 34 34

6 = 3+1+2

Example 5

column size is more than 254 bytes

tab 0, row 0, @0x1e63
tl: 309 fb: –H-FL– lb: 0x0  cc: 2
col  0: [ 2]  c1 0b
col  1: [300]
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78

309 = 3 + 1 + 2 +3 + 300

Hex dump

79797979 7979792c 000202c1 0bfe2c01 l yyyyyyy,…Á.þ,.
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 78787878 l xxxxxxxxxxxxxxxx
78787878 78787878 78787878 0206d607 l xxxxxxxxxxxx..Ö.

02c1 0bfe2c01

02 bytes first column size
c1 0b = first column value
fe=  Indicator above 254 bytes column size (second column more than 254 bytes)
012c = 300 bytes second column size

How to recover deleted rows

Filed under: Oracle Block,Oracle internals — sundar5 @ 3:59 am

Caution:  Below example only for education purpose.  It should not be used any real production environment

1. Create tablespace

CREATE TABLESPACE deleterow   DATAFILE ‘/mnt/poc/ETLTEST/deleterow.dbf’ SIZE 5M   SEGMENT SPACE MANAGEMENT AUTO;

2.   SQL> SELECT TABLESPACE_NAME,FILE_ID,BYTES,BLOCKS,FILE_NAME FROM DBA_DATA_FILES;

TABLESPACE_NAME    FILE_ID      BYTES     BLOCKS FILE_NAME
--------------- ---------- ---------- ---------- ---------------------------------
DELETEROW                9    5242880        640 /mnt/poc/ETLTEST/deleterow.dbf

3.SQL> SELECT * FROM DBA_FREE_SPACE;

TABLESPACE_NAME    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
--------------- ---------- ---------- ---------- ---------- ------------
DELETEROW                9          9    5177344        632            9

4. SQL> CREATE TABLE T10 (N1 number, desc1 varchar2(10)) TABLESPACE DELETEROW;

Table created.

5. SQL> SELECT OBJECT_NAME,OBJECT_ID,OBJECT_TYPE FROM DBA_OBJECTS
 WHERE OBJECT_NAME='T10' AND OWNER='SYS'

OBJECT_NAM  OBJECT_ID OBJECT_TYPE
---------- ---------- ------------
T10             10576 TABLE

6. SQL> SELECT * FROM DBA_FREE_SPACE;

TABLESPACE_NAME    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
--------------- ---------- ---------- ---------- ---------- ------------
DELETEROW                9         17    5111808        624            9

7.SQL> SELECT SEGMENT_NAME,HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS
 FROM DBA_SEGMENTS WHERE SEGMENT_NAME LIKE 'T10' AND OWNER ='SYS'
 /
SEGMENT_NA HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS
---------- ----------- ------------ ---------- ---------- ----------
T10                  9           11      65536          8          1

8.SQL> select * from t10;

no rows selected

9.SQL> insert into t10 values (100,'TESTDATA1');

1 row created.

SQL> insert into t10 values (200,'TESTDATA2');

1 row created.

SQL> insert into t10 values (300,'TESTDATA2');


1 row created.

SQL> insert into t10 values (400,'TESTDATA4');

1 row created.

SQL> insert into t10 values (500,'TESTDATA5');

1 row created.

SQL> COMMIT;

Commit complete.

10. SQL>
 1  select ROWID,DBMS_ROWID.ROWID_OBJECT(rowid) "OBJECT",
 2  DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) "FILE",
 3  DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) "BLOCK",
 4  DBMS_ROWID.ROWID_ROW_NUMBER(rowid) "ROW",N1,DESC1
 5* from T10
ROWID                  OBJECT       FILE      BLOCK        ROW         N1 DESC1
------------------ ---------- ---------- ---------- ---------- ---------- ----------
AAAClQAAJAAAAAPAAA      10576          9         15          0        100 TESTDATA1
AAAClQAAJAAAAAPAAB      10576          9         15          1        200 TESTDATA2
AAAClQAAJAAAAAPAAC      10576          9         15          2        300 TESTDATA2
AAAClQAAJAAAAAPAAD      10576          9         15          3        400 TESTDATA4
AAAClQAAJAAAAAPAAE      10576          9         15          4        500 TESTDATA5

11. SELECT N1,DUMP(N1,16) DUMP_N1,DESC1,DUMP(DESC1,16) DUMP_DESC1 FROM T10
 N1 DUMP_N1              DESC1        DUMP_DESC1
----- -------------------- ------------ ----------------------------------------
 100 Typ=2 Len=2: c2,2    TESTDATA1    Typ=1 Len=9: 54,45,53,54,44,41,54,41,31
 200 Typ=2 Len=2: c2,3    TESTDATA2    Typ=1 Len=9: 54,45,53,54,44,41,54,41,32
 300 Typ=2 Len=2: c2,4    TESTDATA2    Typ=1 Len=9: 54,45,53,54,44,41,54,41,32
 400 Typ=2 Len=2: c2,5    TESTDATA4    Typ=1 Len=9: 54,45,53,54,44,41,54,41,34
 500 Typ=2 Len=2: c2,6    TESTDATA5    Typ=1 Len=9: 54,45,53,54,44,41,54,41,35

12. dump

buffer tsn: 9 rdba: 0x0240000f (9/15)
scn: 0x0000.00090d89 seq: 0x02 flg: 0x02 tail: 0x0d890602
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1

Block header dump:  0x0240000f
 Object id on Block? Y
 seg/obj: 0x2950  csc: 0x00.8ff2e  itc: 2  flg: E  typ: 1 - DATA
 brn: 0  bdba: 0x2400009 ver: 0x01 opc: 0
 inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.02d.000000a5  0x00800659.005b.0f  C---    0  scn 0x0000.0008fece
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

data_block_dump,data header at 0x6820864
===============
tsiz: 0x1f98
hsiz: 0x1c
pbl: 0x06820864
bdba: 0x0240000f
 76543210
flag=--------
ntab=1
nrow=5
frre=-1
fsbo=0x1c
fseo=0x1f48
avsp=0x1f2c
tosp=0x1f2c
0xe:pti[0]      nrow=5  offs=0
0x12:pri[0]     offs=0x1f88
0x14:pri[1]     offs=0x1f78
0x16:pri[2]     offs=0x1f68
0x18:pri[3]     offs=0x1f58
0x1a:pri[4]     offs=0x1f48
block_row_dump:
tab 0, row 0, @0x1f88
tl: 16 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c2 02
col  1: [ 9]  54 45 53 54 44 41 54 41 31
tab 0, row 1, @0x1f78
tl: 16 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c2 03
col  1: [ 9]  54 45 53 54 44 41 54 41 32
tab 0, row 2, @0x1f68
tl: 16 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c2 04
col  1: [ 9]  54 45 53 54 44 41 54 41 32
tab 0, row 3, @0x1f58
tl: 16 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c2 05
col  1: [ 9]  54 45 53 54 44 41 54 41 34
tab 0, row 4, @0x1f48
tl: 16 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c2 06
col  1: [ 9]  54 45 53 54 44 41 54 41 35
end_of_block_dump

13.02c11302 c1080600 4002a100 060cc000 l .Á..Á...@.¡...À.
   10001800 10003100 02000100 03000200 l ......1.........
   fd000000 fb000000 00000000 c3028000 l ý...û.......Ã...
   0b010e00 00009e00 02010000 2c000202 l ............,...
   c2060954 45535444 41544135 2c000202 l Â..TESTDATA5,...
   c2050954 45535444 41544134 2c000202 l Â..TESTDATA4,...
   c2040954 45535444 41544132 2c000202 l Â..TESTDATA2,...
   c2030954 45535444 41544132 2c000202 l Â..TESTDATA2,...
   c2020954 45535444 41544131 01062eff l Â..TESTDATA1....

14.SQL> select * from t10;

 N1 DESC1
---------- ----------
 100 TESTDATA1
 200 TESTDATA2
 300 TESTDATA2
 400 TESTDATA4
 500 TESTDATA5

15. SQL> delete t10 where n1=300;

1 row deleted.

SQL>  select * from t10;

 N1 DESC1
---------- ----------
 100 TESTDATA1
 200 TESTDATA2
 400 TESTDATA4
 500 TESTDATA5

16.
buffer tsn: 9 rdba: 0x0240000f (9/15)
scn: 0x0000.00090d89 seq: 0x02 flg: 0x02 tail: 0x0d890602
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Block header dump:  0x0240000f
Object id on Block? Y
seg/obj: 0x2950  csc: 0x00.8ff2e  itc: 2  flg: E  typ: 1 – DATA
brn: 0  bdba: 0x2400009 ver: 0x01 opc: 0
inc: 0  exflg: 0
Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.02d.000000a5  0x00800659.005b.0f  C—    0  scn 0x0000.0008fece
0x02   0x000a.020.0000014e  0x008005f9.0073.2a  –U-    1  fsc 0x000e.00090d89
data_block_dump,data header at 0x6820864
data_block_dump,data header at 0x6820864
===============
tsiz: 0x1f98
hsiz: 0x1c
pbl: 0x06820864
bdba: 0x0240000f
76543210
flag=——–
ntab=1
nrow=5
frre=-1
fsbo=0x1c
fseo=0x1f48
avsp=0x1f2c
tosp=0x1f3c
0xe:pti[0]      nrow=5  offs=0
0x12:pri[0]     offs=0x1f88
0x14:pri[1]     offs=0x1f78
0x16:pri[2]     offs=0x1f68
0x18:pri[3]     offs=0x1f58
0x1a:pri[4]     offs=0x1f48
block_row_dump:
tab 0, row 0, @0x1f88
tl: 16 fb: –H-FL– lb: 0x0  cc: 2
col  0: [ 2]  c2 02
col  1: [ 9]  54 45 53 54 44 41 54 41 31
tab 0, row 1, @0x1f78
tl: 16 fb: –H-FL– lb: 0x0  cc: 2
col  0: [ 2]  c2 03
col  1: [ 9]  54 45 53 54 44 41 54 41 32
tab 0, row 2, @0x1f68
tl: 2 fb: –HDFL– lb: 0x2
tab 0, row 3, @0x1f58
tl: 16 fb: –H-FL– lb: 0x0  cc: 2
col  0: [ 2]  c2 05
col  1: [ 9]  54 45 53 54 44 41 54 41 34
tab 0, row 4, @0x1f48
tl: 16 fb: –H-FL– lb: 0x0  cc: 2
col  0: [ 2]  c2 06
col  1: [ 9]  54 45 53 54 44 41 54 41 35
end_of_block_dump
17. hex dump
 10001800 10003100 02000100 03000200 l ......1.........
 fd000000 fb000000 00000000 c3028000 l ý...û.......Ã...
 0b010e00 00009e00 02010000 2c000202 l ............,...
 c2060954 45535444 41544135 2c000202 l Â..TESTDATA5,...
 c2050954 45535444 41544134 3c020202 l Â..TESTDATA4<...
 c2040954 45535444 41544132 2c000202 l Â..TESTDATA2,...
 c2030954 45535444 41544132 2c000202 l Â..TESTDATA2,...
 c2020954 45535444 41544131 0206890d l Â..TESTDATA1....

18.  using hex editor , change the 0ffset 8140 to  2c

    tl: 2 fb: --HDFL-- lb: 0x2

oracle row header flag value changed  2c to 3c while deleting the row (–HDFL)

Flag byte
– – – – – – –  Continues data piece from previous row
H: Head of row piece
K: Cluster key
C: Cluster table member
D: Deleted row
F: First data piece,
L: Last data piece
P: First column continues from previous row
N: Last column continues

19

buffer tsn: 9 rdba: 0x0240000f (9/15)
scn: 0x0000.00090d89 seq: 0x02 flg: 0x06 tail: 0x0d890602
frmt: 0x02 chkval: 0xe269 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1

 006822770 0213C102 000608C1 00A10240 00C00C06  [........@.......]
 006822780 00180010 00310010 00010002 00020003  [......1.........]
 006822790 000000FD 000000FB 00000000 008002C3  [................]
 0068227A0 000E010B 009E0000 00000102 0202002C  [............,...]
 0068227B0 540906C2 44545345 35415441 0202002C  [...TESTDATA5,...]
 0068227C0 540905C2 44545345 34415441 0202022C  [...TESTDATA4,...]
 0068227D0 540904C2 44545345 32415441 0202002C  [...TESTDATA2,...]
 0068227E0 540903C2 44545345 32415441 0202002C  [...TESTDATA2,...]
 0068227F0 540902C2 44545345 31415441 0D890602  [...TESTDATA1....]

Block header dump:  0x0240000f
Object id on Block? Y
seg/obj: 0x2950  csc: 0x00.8ff2e  itc: 2  flg: E  typ: 1 – DATA
brn: 0  bdba: 0x2400009 ver: 0x01 opc: 0
inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.02d.000000a5  0x00800659.005b.0f  C—    0  scn 0x0000.0008fece
0x02   0x000a.020.0000014e  0x008005f9.0073.2a  –U-    1  fsc 0x000e.00090d89

data_block_dump,data header at 0x6820864
===============
tsiz: 0x1f98
hsiz: 0x1c
pbl: 0x06820864
bdba: 0x0240000f
76543210
flag=——–
ntab=1
nrow=5
frre=-1
fsbo=0x1c
fseo=0x1f48
avsp=0x1f2c
tosp=0x1f3c
0xe:pti[0]      nrow=5  offs=0
0x12:pri[0]     offs=0x1f88
0x14:pri[1]     offs=0x1f78
0x16:pri[2]     offs=0x1f68
0x18:pri[3]     offs=0x1f58
0x1a:pri[4]     offs=0x1f48
block_row_dump:
tab 0, row 0, @0x1f88
tl: 16 fb: –H-FL– lb: 0x0  cc: 2
col  0: [ 2]  c2 02
col  1: [ 9]  54 45 53 54 44 41 54 41 31
tab 0, row 1, @0x1f78
tl: 16 fb: –H-FL– lb: 0x0  cc: 2
col  0: [ 2]  c2 03
col  1: [ 9]  54 45 53 54 44 41 54 41 32
tab 0, row 2, @0x1f68
tl: 16 fb: –H-FL– lb: 0x2  cc: 2
col  0: [ 2]  c2 04
col  1: [ 9]  54 45 53 54 44 41 54 41 32

tab 0, row 3, @0x1f58
tl: 16 fb: –H-FL– lb: 0x0  cc: 2
col  0: [ 2]  c2 05
col  1: [ 9]  54 45 53 54 44 41 54 41 34
tab 0, row 4, @0x1f48
tl: 16 fb: –H-FL– lb: 0x0  cc: 2
col  0: [ 2]  c2 06
col  1: [ 9]  54 45 53 54 44 41 54 41 35
end_of_block_dump

20.

fd000000 fb000000 00000000 c3028000 l ý…û…….Ã…
0b010e00 00009e00 02010000 2c000202 l …………,…
c2060954 45535444 41544135 2c000202 l Â..TESTDATA5,…
c2050954 45535444 41544134 2c020202 l Â..TESTDATA4,…
c2040954 45535444 41544132 2c000202 l Â..TESTDATA2,…
c2030954 45535444 41544132 2c000202 l Â..TESTDATA2,…
c2020954 45535444 41544131 0206890d l Â..TESTDATA1….

21.

SQL> l
1  SELECT ROWID,DBMS_ROWID.ROWID_OBJECT(ROWID) “OBJECT”,
2  DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) “FILE”,
3  DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) “BLOCK”,
4  DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) “ROW”,N1,DESC1
5* FROM T10
SQL> /

ROWID                  OBJECT       FILE      BLOCK        ROW         N1 DESC1
—————— ———- ———- ———- ———- ———- ———-
AAAClQAAJAAAAAPAAA      10576          9         15          0        100 TESTDATA1
AAAClQAAJAAAAAPAAB      10576          9         15          1        200 TESTDATA2
AAAClQAAJAAAAAPAAC      10576          9         15          2        300 TESTDATA2
AAAClQAAJAAAAAPAAD      10576          9         15          3        400 TESTDATA4
AAAClQAAJAAAAAPAAE      10576          9         15          4        500 TESTDATA5

SQL> select * from t10;

N1 DESC1
———- ———-
100 TESTDATA1
200 TESTDATA2
300 TESTDATA2
400 TESTDATA4
500 TESTDATA5

June 8, 2010

How to findout corrupted object from oracle error

Filed under: Oracle Block,Oracle internals — sundar5 @ 9:09 pm

From alertlog file.

——————-

Example

ORA-01578: ORACLE data block corrupted (file # 9, block # 15)

ORA-01110: data file 9: ‘/mnt/poc/ETLTEST/deleterow.dbf’

ORA-26040: Data block was loaded using the NOLOGGING option

AFN :  file 9: Absolute FILE NUMBER


SQL>SELECT tablespace_name, segment_type, owner, segment_name,FILE_ID,BLOCK_ID,

BLOCKS  FROM dba_extents WHERE file_id = 9 and 15 between block_id

AND block_id + blocks – 1

/

TABLESPACE_NAME SEGMENT_TYPE OWNER SEGMENT_NAME  FILE_ID BLOCK_ID  BLOCKS

————— ———— —– ————- ——- ——–  ——

DELETEROW       TABLE        SYS   T10                 9        9       8

or

SQL> set long 64000

SQL> select dbms_metadata.get_ddl(segment_type, segment_name, owner)

FROM dba_extents WHERE file_id=9 AND 15 BETWEEN block_id AND block_id + blocks -1;

DBMS_METADATA.GET_DDL(SEGMENT_TYPE,SEGMENT_NAME,OWNER)

——————————————————————————–

CREATE TABLE “SYS”.”T10″

(    “N1” NUMBER,

“DESC1” VARCHAR2(10)

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

TABLESPACE “DELETEROW”

May 6, 2010

How to convert data block address to file number and block number

Filed under: Oracle Block,Oracle internals — sundar5 @ 10:33 pm

Because of a  host  crash,  I  had a corruption issue on my undo tablespace  in my database .
The alert log was showing the following error.

I wanted to find out the object that had caused the issue

SMON: Parallel transaction recovery slave got internal error
SMON: Downgrading transaction recovery to serial
SMON: about to recover undo segment 212
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [ktbsdp1], [], [], [], [], [], [], []

Here is the steps
Note: some time alert log show object id also

1. Find out the segment in our case 212

Select usn,name from v$rollname where usn=212;
USN NAME

———- ——————————

212 _SYSSMU212$
2. segement Tabelspace was UNDOTBS3
3. alter system dump undo header “_SYSSMU212$” ;

********************************************************************************
Undo Segment:  _SYSSMU212$ (212)
********************************************************************************
Extent Control Header
—————————————————————–
Extent Header:: spare1: 0      spare2: 0      #extents: 114    #blocks: 57359
last map  0x00000000  #maps: 0      offset: 4080
Highwater::  0x0301a93f  ext#: 112    blk#: 950    ext size: 1024
#blocks in seg. hdr’s freelists: 0
#blocks below: 0
mapblk  0x00000000  offset: 112
Unlocked
Map Header:: next  0x00000000  #extents: 114  obj#: 0      flag: 0x40000000
Extent Map
—————————————————————–
0x03000542  length: 7
0x030000d9  length: 8
0x03000c09  length: 128
0x03005a89  length: 128
0x03005189  length: 128

index  state cflags  wrap#    uel         scn            dba parent-xid    nub     stmt_num    cmt
————————————————————————————————
0x00    9    0x00  0x193ae  0x0015  0x08ff.296efb8e  0x03006e62  0x0000.000.00000000  0x00000001   0x00000000  1273026159
0x01    9    0x00  0x196fd  0x000f  0x08ff.296efb91  0x03006e62  0x0000.000.00000000  0x00000001   0x00000000  1273026159
0x29   10    0x90  0x19715  0x0071  0x08ff.296efae3 0x0300c7f8 0x0000.000.00000000  0x00005b3b   0x00000000  0

4. undo data block

SQL> select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE( TO_NUMBER(‘0300c7f8‘, ‘XXXXXXXX’) ) FILE#,
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK( TO_NUMBER(‘0300c7f8‘, ‘XXXXXXXX’) ) BLOCK#
from dual;
2    3
FILE#     BLOCK#
———- ———-
12      51192

or we can use binary to finout the file and block number

0x0300c7f8

0=0000

3=0011

0=0000

0=0000

c=1100

7=0111

f=1111

8=1000

0000 0011 0000 0000 1100 0111 1111 1000

0000 0011 00   000000 11000111 1111 1000

0000 0011 00 =12

00 0000 1100 0111 1111 1000 = 51192

5. alter system dump datafile 12 block 51192;

* Rec #0x29 slt: 0x29  objn: 5462030(0x0053580e)  objd: 5462030 tblspc: 423(0x000001a7)
*       Layer:  11 (Row)   opc: 1   rci 0x28
undo type:  Regular undo   Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*—————————–
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x0300c7f8.08a7.28
KDO Op code: URP row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0xee44d2d2  hdba: 0xee444e0c
itli: 2  ispac: 0  maxfr: 8098
tabn: 0 slot: 22(0x16) flag: 0x2c lock: 2 ckix: 0
ncol: 23 nnew: 10 size: 0
Vector content:
col  3: [ 5]  19 11 18 1f f
col  4: [ 9]  34 69 72 35 33 74 6f 72 79
col  5: [14]  3e 65 77 73 20 61 6e 64 20 4d 65 64 69 61
col  6: [ 5]  32 6c 6f 67 73
col  7: [19]  33 6f 6c 6c 61 72 6f 72 61 77 79 76 75 70 72 7c 7f 77 73
col  8: [ 9]  34 68 65 20 53 70 61 72 6b
col  9: [19]  54 79 73 78 71 72 64 20 53 74 61 35 36 36 31 33 38 35 72
col 10: [32]
54 4f 40 43 4c 45 64 40 40 49 43 40 48 45 4d 41 4e 4b 40 54 4f 40 4e 61 70
20 44 69 76 49 4e 65
col 11: *NULL*
col 12: *NULL*

7. select owner,object_name from dba_objects where object_id=5462030

OWNER                          OBJECT_NAME
—————————— ——————–
POC                            DIMTEST

February 13, 2010

Inside data block

Filed under: Oracle Block,Oracle internals — sundar5 @ 12:08 am

Block:

Below is the data block dump and see the details on the bottom of the page.

buffer tsn: 4 rdba: 0×010001b8 (4/440)
scn: 0×0000.000a3982 seq: 0×02 flg: 0×00 tail: 0×39820602
frmt: 0×02 chkval: 0×0000 type: 0×06=trans dataBlock header dump:  0×010001b8
Object id on Block? Y
seg/obj: 0xcd60  csc: 0×00.a37e1  itc: 2  flg: E  typ: 1 – DATA
brn: 0  bdba: 0×10001b1 ver: 0×01 opc: 0
inc: 0  exflg: 0Itl     Xid                            Uba     Flag  Lck        Scn/Fsc
0×01   0×0007.018.00000172  0×0080025b.013c.0c  —-    1  fsc 0×0000.00000000
0×02   0×0000.000.00000000  0×00000000.0000.00  —-    0  fsc 0×0000.00000000
data_block_dump,data header at 0xd5b6664
===============
tsiz: 0×1f98
hsiz: 0×14
pbl: 0×0d5b6664
bdba: 0×010001b8
76543210
flag=——–
ntab=1
nrow=1
frre=-1
fsbo=0×14fseo=0×1f8a
avsp=0×1f76
tosp=0×1f76
0xe:pti[0]    nrow=1    offs=0
0×12:pri[0]    offs=0×1f8a
block_row_dump:
tab 0, row 0, @0×1f8a
tl: 14 fb: –H-FL– lb: 0×1  cc: 2
col  0: [ 2]  c1 02
col  1: [ 7]  53 55 4e 44 41 52 31

Legend:

scn: scn number last changed this block.
seq: number of Changes within the block.

seg/obj: table name or object id test02
itc: number of itl allocated (2)

Itl : Intersting transaction list
Xid : Undo header Transaction id (0x0007.018.00000172)

0x0007 : undo segment #
018 : Trasnsaction #
00000172: Slot #

Uba : Undo data block (0x0080025b.013c.0c)

0x0080025b : undo File block #
013c : Sequance #
0c : record #

Flags:
—- = Transaction is active
C— = Transaction has been committed and locks cleaned
-B– = This undo record contains the undo for this ITL entry
–U- = Transaction committed (maybe long ago) delayed blcok cleanout not occure.
—T = Transaction was still active at block cleanout SCN

Lck    Row locks(1 record locked so far current ITL)

scn/Fsc

Free space credit/system changes number
tsiz : Total size ( 8088 bytes )
hsiz : Header size (14 bytes)
pbl  : pointer block in the buffer cache
bdba : database block address

ntab=1       number of table (For cluster table it will be more than 1)
nrow=1       number of rows in the block.
frre=-1      First free row idnex entry
fsbo=0x14    Free space begining offset
fseo=0x1f8a  Free space end offset
avsp=0x1f76  Average block space in bytes
tosp=0x1f76  Space available in post commit.

0xe:pti[0]   nrow=1    offs=0   Table details
0x12:pri[0]  offs=0x1f8a    row information offset (first row start 0)

tab 0        data for table 0
row 0        number of record in the block (0 mean 1 record)
@0x1f8a      the record is starting offset number from  0x1f8a
tl           Total bytes of the record + header info = 14
fb           Block bytes (KCHDFLPN)
K=Cluster key
C=Cluster table member
H=Header of row piece
D=Delete flag
F=First data piece
L=Last piece
P= First column continue from privious piece chaining
N=Last column continue from next piece.
lb           Lock bytes( 0 is unlocked 1 locked)
cc           Column count 2


February 2, 2010

Underscore parameter

Filed under: Oracle internals,Parameters — sundar5 @ 3:40 pm
LMD process checks every 60 seconds fron any resource dead lock.
 
1  select m.ksppinm , y.ksppstvl from   x$ksppi  m , x$ksppcv y where  m.indx = y.indx and    m.ksppinm like '%lm_dd%'
2* order  by m.ksppinm
SQL> /
KSPPINM                        KSPPSTVL
------------------------------ ----------------------------------------
_lm_dd_interval                60
_lm_dd_scan_interval           5

February 1, 2010

V$TYPE_SIZE

Filed under: Oracle internals — sundar5 @ 10:49 pm

V$TYPE_SIZE displays the sizes of various database components for use in estimating data block capacity

SQL> SELECT * FROM V$TYPE_SIZE;

COMPONEN TYPE     DESCRIPTION                       TYPE_SIZE
-------- -------- -------------------------------- ----------
S        EWORD    EITHER WORD                               4
S        EB1      EITHER BYTE 1                             1
S        EB2      EITHER BYTE 2                             2
S        EB4      EITHER BYTE 4                             4
S        UWORD    UNSIGNED WORD                             4
S        UB1      UNSIGNED BYTE 1                           1
S        UB2      UNSIGNED BYTE 2                           2
S        UB4      UNSIGNED BYTE 4                           4
S        SWORD    SIGNED WORD                               4
S        SB1      SIGNED BYTE 1                             1
S        SB2      SIGNED BYTE 2                             2
S        SB4      SIGNED BYTE 4                             4
S        BOOLEAN  BOOLEAN                                   4
S        FLOAT    FLOAT                                     4
S        DOUBLE   DOUBLE                                    8
S        SIZE_T   SIZE_T                                    8
S        DSIZE_T  DSIZE_T                                   4
S        PTR_T    PTR_T                                     8
K        KDBA     DATABASE BLOCK ADDRESS                    4
K        KTNO     TABLE NUMBER IN CLUSTER                   1
K        KSCN     SYSTEM COMMIT NUMBER                      8
K        KXID     TRANSACTION ID                            8
K        KUBA     UNDO ADDRESS                              8
KCB      KCBH     BLOCK COMMON HEADER                      20
KTB      KTBIT    TRANSACTION VARIABLE HEADER              24
KTB      KTBBH    TRANSACTION FIXED HEADER                 48
KTB      KTBBH_BS TRANSACTION BLOCK BITMAP SEGMENT          8
KDB      KDBH     DATA HEADER                              14
KDB      KDBT     TABLE DIRECTORY ENTRY                     4
KTE      KTECT    EXTENT CONTROL                           44
KTE      KTECH    EXTENT CONTROL                           72
KTE      KTETB    EXTENT TABLE                              8
KTS      KTSHC    SEGMENT HEADER                            8
KTS      KTSFS    SEGMENT FREE SPACE LIST                  20
KTS      KTSPHW   PAGE TABLE SEGMENT HWM                   60
KTS      KTSPHC   PAGE TABLE SEGMENT HEADER               112
KTS      KTSPFHC  LEVEL 1 BITMAP BLOCK HEADER             184
KTS      KTSPSHC  LEVEL 2 BITMAP BLOCK HEADER              96
KTS      KTSPTHC  LEVEL 3 BITMAP BLOCK HEADER              88
KTU      KTUBH    UNDO HEADER                              16
KTU      KTUXE    UNDO TRANSACTION ENTRY                   40
KTU      KTUXC    UNDO TRANSACTION CONTROL                104
KDX      KDXCO    INDEX HEADER                             16
KDX      KDXLE    INDEX LEAF HEADER                        32
KDX      KDXBR    INDEX BRANCH HEADER                      24

45 rows selected.


	

Check point progress

Filed under: Oracle internals — sundar5 @ 10:38 pm

select cphbt from X$KCCCP;

CPHBT
———-
704556657

SQL> /

CPHBT
———-
704556658

Next Page »

Create a free website or blog at WordPress.com.