Debugging Oracle Rowid

DEBUGGING ORACLE ROWID Author JP Vijaykumar Date Sept 14th 2009 --Create a database. connect / as sysdba startup nomount pfile=/u001/oracle/product/10.2.0/db_1/dbs/initsavi.ora create database SAVI datafile '/u101/oradata/SAVI/systemsavi01.dbf' size 500m reuse sysaux datafile '/u101/oradata/SAVI/sysaux.dbf' size 500m reuse undo tablespace UNDOSAVI datafile '/u101/oradata/SAVI/undosavi01.dbf' size 50m reuse default temporary tablespace TEMPSAVI tempfile '/u101/oradata/SAVI/tempsavi01.dbf' size 10m reuse logfile group 1 ('/u101/oradata/SAVI/redosavi01.rlg') size 10m reuse, group 2 ('/u101/oradata/SAVI/redosavi02.rlg') size 10m reuse controlfile reuse; --Create a tablespace. create tablespace SAVI_DATA datafile '/u101/oradata/SAVI/savi_data_01.dbf' size 10m autoextend on next 100m maxsize 500m extent management local uniform size 1m segment space management auto; --The database version is 10.2.0.3.0 select * from v$version; BANNER ---------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi PL/SQL Release 10.2.0.3.0 - Production CORE 10.2.0.3.0 Production TNS for HPUX: Version 10.2.0.3.0 - Production NLSRTL Version 10.2.0.3.0 - Production --Create a table. create table VEEKSHA(col1 varchar2(15)) tablespace SAVI_DATA; --Truncate the table truncate table VEEKSHA; --Insert five rows into the table. SQL> declare 2 begin 3 for i in 1..5 loop 4 insert into VEEKSHA values(i); 5 commit; 6 end loop; 7 end; 8 /

PL/SQL procedure successfully completed. --Check the rowid, rownum, length of each row. SQL> select rowid,rownum,length(col1),col1 from VEEKSHA; ROWID ROWNUM LENGTH(COL1) COL1 ------------------ ---------- ------------ --------------AAACYfAAEAAAAEaAAA 1 11 AAACYfAAEAAAAEaAAB 2 12 AAACYfAAEAAAAEaAAC 3 13 AAACYfAAEAAAAEaAAD 4 14 AAACYfAAEAAAAEaAAE 5 15 5 rows selected. SQL> select dbms_rowid.rowid_relative_fno(rowid) file_num, 2 dbms_rowid.rowid_block_number(rowid) block_num , 3 count(*) num_row 4 from VEEKSHA 5 group by dbms_rowid.rowid_relative_fno(rowid), 6 dbms_rowid.rowid_block_number(rowid) 7 order by dbms_rowid.rowid_relative_fno(rowid), 8 dbms_rowid.rowid_block_number(rowid); FILE_NUM BLOCK_NUM ---------- ---------- ---------4 282 5

NUM_ROW

1 row selected. --The inserted five rows are accomodated in a single block. SQL> select 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" 5 from VEEKSHA; object file block row ---------- ---------- ---------- ---------9759 4 282 0 9759 4 282 1 9759 4 282 2 9759 4 282 3 9759 4 282 4 5 rows selected. --Truncate the table. SQL> truncate table VEEKSHA; Table truncated.

--Insert five string values using a pl/sql sting parser. SQL> set serverout on size 1000000 SQL> declare 2 i number:=1; 3 j number:=0; 4 v_str varchar2(10000):='1 5 6 22 333 7 4444 8 9 55555'; 10 11 begin 12 while (i ' ' ) and ( substr(v_str,i,1) IS NOT NULL ) ) then 22 dbms_output.put_line(substr(v_str,i,j)); 23 insert into VEEKSHA values(substr(v_str,i,j)); 24 end if; 25 26 i:=i+j; 27 28 exception 29 when others then 30 dbms_output.put_line(sqlerrm); 31 end; 32 end loop; 33 end; 34 / 1 22 333 4444 55555 PL/SQL procedure successfully completed. --Check the rowid, rownum, length of each row. SQL> select rowid,rownum,length(col1),col1 from VEEKSHA; ROWID ROWNUM LENGTH(COL1) COL1 ------------------ ---------- ------------ --------------AAACYgAAEAAAAEaAAA 1 61 22

AAACYgAAEAAAAEaAAB 4444

2

15 333

55555 2 rows selected. --Strangely the inserted five values are treated by Oracle as two records. --Oracle had spread each record into multiple rows. --The row length of the first record is 6 bytes, that of second record is 15 bytes. SQL> set serverout on size 10000 SQL> declare 2 begin 3 for c1 in (select col1 from VEEKSHA) loop 4 dbms_output.put_line(c1.col1||' '||length(c1.col1)); 5 end loop; 6 end; 7 / 1 22 6 333 4444 55555 15 PL/SQL procedure successfully completed. --The length and value of the two records are displayed from the table through a pl/sql loop. SQL> select rowid,rownum,length(col1),col1 from VEEKSHA where rowid='AAACYgAAEAAAAEaAAA'; ROWID ROWNUM LENGTH(COL1) COL1 ------------------ ---------- ------------ --------------AAACYgAAEAAAAEaAAA 1 61 22 1 row selected. SQL> select rowid,rownum,length(col1),col1 from VEEKSHA where rowid='AAACYgAAEAAAAEaAAB'; ROWID ROWNUM LENGTH(COL1) COL1 ------------------ ---------- ------------ --------------AAACYgAAEAAAAEaAAB 1 15 333 4444 55555

1 row selected. SQL> select * from VEEKSHA where rownum < 2; COL1 --------------1 22 1 row selected. SQL> select * from (select rownum rnum, a.* from VEEKSHA a) t 2 where t.rnum > 1 and t.rnum <= 2; RNUM COL1 ---------- --------------2 333 4444 55555 1 row selected. --The record count in the Oracle block 282 is two. SQL> select dbms_rowid.rowid_relative_fno(rowid) file_num, 2 dbms_rowid.rowid_block_number(rowid) block_num , count(*) 3 from VEEKSHA 4 group by dbms_rowid.rowid_relative_fno(rowid), 5 dbms_rowid.rowid_block_number(rowid) 6 order by dbms_rowid.rowid_relative_fno(rowid), 7 dbms_rowid.rowid_block_number(rowid); FILE_NUM BLOCK_NUM COUNT(*) ---------- ---------- ---------4 282 2 1 row selected. SQL> select 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" from VEEKSHA; object file block row ---------- ---------- ---------- ---------9760 4 282 0 9760 4 282 1 2 rows selected. SQL> select col1 from VEEKSHA where col1 like '%4444%';

COL1 --------------333 4444 55555 1 row selected. SQL> select col1 from VEEKSHA where col1 like '%55555'; COL1 --------------333 4444 55555 1 row selected. SQL> select col1 from VEEKSHA where col1 like '333%4444%55555'; COL1 --------------333 4444 55555 1 row selected. SQL> select * from VEEKSHA; COL1 --------------1 22 333 4444 55555 2 rows selected. SQL> select * from VEEKSHA where rownum=1; COL1 --------------1

22 1 row selected. --Created a function to find the details of a rowid. SQL> create or replace procedure get_rowinfo_jp(rid IN rowid) as 2 sm varchar2(9):='SMALLFILE'; 3 rid_t number; 4 obj_n number; 5 file_n number; 6 block_n number; 7 row_n number; 8 begin 9 DBMS_ROWID.ROWID_INFO(rid, rid_t,obj_n,file_n, block_n, row_n,sm); 10 DBMS_OUTPUT.PUT_LINE('Type: '||to_char(rid_t)); 11 DBMS_OUTPUT.PUT_LINE('Data obj number: '||to_char(obj_n)); 12 DBMS_OUTPUT.PUT_LINE('Relative fno: '||to_char(file_n)); 13 DBMS_OUTPUT.PUT_LINE('Block number: '||to_char(block_n)); 14 DBMS_OUTPUT.PUT_LINE('Row number: '||to_char(row_n)); 15 end; 16 / Procedure created. SQL> exec get_rowinfo_jp('AAACYgAAEAAAAEaAAB'); Type: 1 Data obj number: 9760 Relative fno: 4 Block number: 282 Row number: 1 PL/SQL procedure successfully completed. SQL> spool off select name from v$datafile where file#=4; NAME -------------------------------------------------------------------------------/u101/oradata/SAVI/savi_data_01.dbf 1 row selected. Shutdown the database. --Shutdown the db and installed bbed(block browser editor) tool. make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed `/u001/oracle/product/10.2.0/db_1/rdbms/lib/bbed' is up to date. $ ./bbed Password:

BBED: Release 2.0.0.0.0 - Limited Production on Tue Oct 6 17:13:18 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. ************* !!! For Oracle Internal Use only !!! *************** BBED> set filename '/u101/oradata/SAVI/savi_data_01.dbf' FILENAME /u101/oradata/SAVI/savi_data_01.dbf BBED> set block 282 BLOCK# 282 BBED> find /c 4444 File: /u101/oradata/SAVI/savi_data_01.dbf (0) Block: 282 Offsets: 8167 to 8191 Dba:0x00000000 -----------------------------------------------------------------------34343434 0a0a3535 3535352c 01010631 0a0a3232 20ed8b06 01 <32 bytes per line> BBED> x /rnccntnnn rowdata[8] @8167 ---------flag@8167: 0x34 (KDRHFL, KDRHFD, KDRHFH) lock@8168: 0x34 cols@8169: 0

BBED> find /c 22 File: /u101/oradata/SAVI/savi_data_01.dbf (0) Block: 282 Offsets: 8185 to 8191 Dba:0x00000000 -----------------------------------------------------------------------323220ed 8b0601 <32 bytes per line> BBED> exit

$ ./bbed Password: BBED: Release 2.0.0.0.0 - Limited Production on Tue Oct 6 21:07:34 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. ************* !!! For Oracle Internal Use only !!! *************** BBED> set list '/u101/oradata/SAVI/jp.log' BBED-00303: unable to open file '/u101/oradata/SAVI/jp.log' BBED> set list '/u101/oradata/SAVI/jp.log'

BBED-00310: no datafile specified BBED> exit

$ ./bbed Password: BBED: Release 2.0.0.0.0 - Limited Production on Tue Oct 6 21:15:05 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. ************* !!! For Oracle Internal Use only !!! *************** BBED> set list '/u101/oradata/SAVI/jp.log' LISTFILE /u101/oradata/SAVI/jp.log BBED> set block 282 BLOCK# 282 BBED> find /c 4444 BBED-00212: search string not found BBED> find /c 22 File: /u101/oradata/SAVI/systemsavi01.dbf (1) Block: 282 Offsets: 7933 to 8191 Dba:0x0040011a -----------------------------------------------------------------------3232fe00 400e6e03 c22a4bfe 00400e6d 03c2295e 02c11200 400e6c03 c22813fe 00400e6b 03c22517 fe00400b 9403c224 4bfe0040 0b9303c2 241302c1 0e00400b 9203c223 4bfe0040 0b9103c2 1d0cfe00 400b9003 c21c47fe 00400b8f 03c21c0c fe00400b 8e03c21b 28fe0040 0b8d03c2 195ffe00 400b8c03 c2193fff 00400b8b 03c21935 fe00400b 8a03c219 2b02c114 00400b89 03c21905 02c11a00 400b8803 c2184202 c10f0040 0b8703c2 1822fe00 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 0000007f 140601 <32 bytes per line> BBED> find /c 333 BBED-00212: search string not found BBED> find /c 55555 BBED-00212: search string not found BBED> find /c 4444 BBED-00212: search string not found BBED> dump /v dba 4,282 offset 7933 count 64 File: /u101/oradata/SAVI/savi_data_01.dbf (4)

Block: 282 Offsets: 7933 to 7996 Dba:0x0100011a ------------------------------------------------------03c22d2f 018002c1 0203c233 07018002 l .Â-/...Á..Â3.... c10202c1 0b02c103 03c20338 ff02c102 l Á..Á..Á..Â.8ÿ.Á. 018003c2 2962ffff ffffffff ffffff02 l ...Â)bÿÿÿÿÿÿÿÿÿ. c102ffff ff02c102 ffffffff 07786d0a l Á.ÿÿÿ.Á.ÿÿÿÿ.xm. <16 bytes per line> BBED> dump /v dba 4,282 offset 7933 count 32 File: /u101/oradata/SAVI/savi_data_01.dbf (4) Block: 282 Offsets: 7933 to 7964 Dba:0x0100011a ------------------------------------------------------03c22d2f 018002c1 0203c233 07018002 l .Â-/...Á..Â3.... c10202c1 0b02c103 03c20338 ff02c102 l Á..Á..Á..Â.8ÿ.Á. <16 bytes per line> BBED> dump /v dba 4,282 offset 7933 count 128 File: /u101/oradata/SAVI/savi_data_01.dbf (4) Block: 282 Offsets: 7933 to 8060 Dba:0x0100011a ------------------------------------------------------03c22d2f 018002c1 0203c233 07018002 l .Â-/...Á..Â3.... c10202c1 0b02c103 03c20338 ff02c102 l Á..Á..Á..Â.8ÿ.Á. 018003c2 2962ffff ffffffff ffffff02 l ...Â)bÿÿÿÿÿÿÿÿÿ. c102ffff ff02c102 ffffffff 07786d0a l Á.ÿÿÿ.Á.ÿÿÿÿ.xm. 06102727 6c000902 03c22d2f 02c10202 l ..''l....Â-/.Á.. c1020180 01800180 02c10201 800180ac l Á........Á.....¬ 00010006 00060040 11a70002 004011a7 l .......@.§...@.§ 000203c2 2d2e6c00 0201262d 2d2d2d2d l ...Â-.l...&----<16 bytes per line> BBED> verify dba 4,282 DBVERIFY - Verification starting FILE = /u101/oradata/SAVI/savi_data_01.dbf BLOCK = 282 DBVERIFY - Verification complete Total Blocks Examined :1 Total Blocks Processed (Data) : 1 Total Blocks Failing (Data) : 0 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty :0 Total Blocks Marked Corrupt : 0 Total Blocks Influx :0 BBED> map dba 4,282 File: /u101/oradata/SAVI/savi_data_01.dbf (4) Block: 282 Dba:0x0100011a -----------------------------------------------------------KTB Data Block (Table/Cluster)

struct kcbh, 20 bytes

@0

struct ktbbh, 72 bytes

@20

struct kdbh, 14 bytes

@100

struct kdbt[1], 4 bytes

@114

sb2 kdbr[2] ub1 freespace[8037] ub1 rowdata[29] ub4 tailchk BBED> print dba 4,282 freespace[7811] --------------ub1 freespace[7811]

@118 @122 @8159 @8188

@7933

0x03

BBED> find / c 4444 File: /u101/oradata/SAVI/savi_data_01.dbf (4) Block: 282 Offsets: 8167 to 8191 Dba:0x0100011a -----------------------------------------------------------------------34343434 0a0a3535 3535352c 01010631 0a0a3232 20ed8b06 01 <32 bytes per line> BBED> find / c 1 File: /u101/oradata/SAVI/savi_data_01.dbf (4) Block: 282 Offsets: 8182 to 8191 Dba:0x0100011a -----------------------------------------------------------------------310a0a32 3220ed8b 0601 <32 bytes per line> BBED> find / c 22 File: /u101/oradata/SAVI/savi_data_01.dbf (4) Block: 282 Offsets: 8185 to 8191 Dba:0x0100011a -----------------------------------------------------------------------323220ed 8b0601 <32 bytes per line> BBED> find / c 333 BBED-00212: search string not found BBED> show FILE# 4 BLOCK# 282 OFFSET 8185 DBA 0x0100011a (16777498 4,282) FILENAME /u101/oradata/SAVI/savi_data_01.dbf

BIFILE bifile.bbd LISTFILE /u101/oradata/SAVI/jp.log BLOCKSIZE 8192 MODE Browse EDIT Unrecoverable IBASE Dec OBASE Dec WIDTH 80 COUNT 128 LOGFILE log.bbd SPOOL No BBED> map / v dba 4,282 File: /u101/oradata/SAVI/savi_data_01.dbf (4) Block: 282 Dba:0x0100011a -----------------------------------------------------------KTB Data Block (Table/Cluster) struct kcbh, 20 bytes ub1 type_kcbh ub1 frmt_kcbh ub1 spare1_kcbh ub1 spare2_kcbh ub4 rdba_kcbh ub4 bas_kcbh ub2 wrp_kcbh ub1 seq_kcbh ub1 flg_kcbh ub2 chkval_kcbh ub2 spare3_kcbh struct ktbbh, 72 bytes ub1 ktbbhtyp union ktbbhsid, 4 bytes struct ktbbhcsc, 8 bytes b2 ktbbhict ub1 ktbbhflg ub1 ktbbhfsl ub4 ktbbhfnx struct ktbbhitl[2], 48 bytes

@0 @0 @1 @2 @3 @4 @8 @12 @14 @15 @16 @18 @20 @20 @24 @28 @36 @38 @39 @40 @44

struct kdbh, 14 bytes ub1 kdbhflag b1 kdbhntab b2 kdbhnrow sb2 kdbhfrre sb2 kdbhfsbo sb2 kdbhfseo b2 kdbhavsp b2 kdbhtosp

@100 @100 @101 @102 @104 @106 @108 @110 @112

struct kdbt[1], 4 bytes b2 kdbtoffs b2 kdbtnrow

@114 @114 @116

sb2 kdbr[2] ub1 freespace[8037] ub1 rowdata[29] ub4 tailchk

@118 @122 @8159 @8188

BBED> x /rcnn rowdata[26] @8185 ----------flag@8185: 0x32 (KDRHFP, KDRHFD, KDRHFH) lock@8186: 0x32 cols@8187: 0 --Much of the bbed commands are of little help in debugging the spread of records into multiple rows. BBED> p *kdbr[1] rowdata[0] ---------ub1 rowdata[0]

@8159

0x2c

BBED> p *kdbr[2] BBED-00401: out of range array index (2) BBED> p *kdbr[0] rowdata[19] ----------ub1 rowdata[19]

@8178

0x2c

BBED> x /1rcnn rowdata[19] @8178 ----------flag@8178: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@8179: 0x01 cols@8180: 1 col

0[6] @8181: 1..22

--Here the first row and it's length are displayed. BBED> x /2rcnn rowdata[19] @8178 ----------flag@8178: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@8179: 0x01 cols@8180: 1 col

0[6] @8181: 1..22

tailchk @8188 ------BBED-00210: no row at this offset

BBED> x /3rcnn rowdata[19] @8178 ----------flag@8178: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@8179: 0x01 cols@8180: 1 col

0[6] @8181: 1..22

tailchk @8188 ------BBED-00210: no row at this offset BBED> x /4rcnn rowdata[19] @8178 ----------flag@8178: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@8179: 0x01 cols@8180: 1 col

0[6] @8181: 1..22

tailchk @8188 ------BBED-00210: no row at this offset BBED> x /5rcnn rowdata[19] @8178 ----------flag@8178: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@8179: 0x01 cols@8180: 1 col

0[6] @8181: 1..22

tailchk @8188 ------BBED-00210: no row at this offset BBED> x /6rcnn rowdata[19] @8178 ----------flag@8178: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@8179: 0x01 cols@8180: 1 col

0[6] @8181: 1..22

tailchk @8188 ------BBED-00210: no row at this offset

--Took a dump of the Oracle block 282. alter system dump datafile 4 block 282; $cat savi_ora_28660.trc /u101/oradata/SAVI/udump/savi_ora_28660.trc Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, OLAP and Data Mining options ORACLE_HOME = /u001/oracle/product/10.2.0/db_1 System name: HP-UX Node name: wmsdb09 Release: B.11.23 Version: U Machine: ia64 Instance name: SAVI Redo thread mounted by this instance: 1 Oracle process number: 13 Unix process pid: 28660, image: oracle@wmsdb09 (TNS V1-V3) *** 2009-10-07 07:59:27.046 *** SERVICE NAME:(SYS$USERS) 2009-10-07 07:59:27.039 *** SESSION ID:(159.3) 2009-10-07 07:59:27.039 Start dump data blocks tsn: 4 file#: 4 minblk 282 maxblk 282 buffer tsn: 4 rdba: 0x0100011a (4/282) scn: 0x0000.0002ed8b seq: 0x01 flg: 0x06 tail: 0xed8b0601 frmt: 0x02 chkval: 0xb268 type: 0x06=trans data Hex dump of block: st=0, typ_found=1 Dump of memory from 0x60000000001EAE00 to 0x60000000001ECE00 60000000001EAE00 06A20000 0100011A 0002ED8B 00000106 [................] 60000000001EAE10 B2680000 01010024 00002620 0002EC18 [.h.....$..& ....] 60000000001EAE20 00001FE4 1F023200 01000109 0006002D [......2........-] 60000000001EAE30 00000078 00800B2F 00451400 20020000 [...x.../.E.. ...] 60000000001EAE40 0002ED8B 00000000 00000000 00000000 [................] 60000000001EAE50 00000000 00000000 00000000 00000000 [................] 60000000001EAE60 00000000 00010002 FFFF0016 1F7B1F65 [.............{.e] 60000000001EAE70 1F650000 00021F8E 1F7B001D 000C0029 [.e.......{.....)] 60000000001EAE80 0036005F 00020061 00020063 00000063 [.6._...a...c...c] 60000000001EAE90 00010064 00000064 00021F89 1F441F00 [...d...d.....D..] 60000000001EAEA0 1D531B54 174214DD 12A31050 0BDE0255 [.S.T.B.....P...U] 60000000001EAEB0 0437076B 0589067A 094D085C 03461E99 [.7.k...z.M.\.F..] 60000000001EAEC0 03D418B5 186B0708 052704C5 061708EB [.....k...'......] 60000000001EAED0 07FA02E3 1EE51BB6 17A415A3 153E1305 [.............>..] 60000000001EAEE0 10CC10B1 0C5A0C3F 0A410A26 1DE41DA5 [.....Z.?.A.&....] ............... ............... ............... Repeat 1 times 60000000001ECB80 2D2D2D2D FFFFFFFF FFFFFFFF FFFFFFFF [----............] 60000000001ECB90 02C10602 C10606C5 0625580A 0D018003 [.........%X.....] 60000000001ECBA0 C20825FF FFFFFF07 786D0A06 102727AC [..%.....xm...''.] 60000000001ECBB0 00010008 00080040 11A70003 004011A7 [.......@.....@..] 60000000001ECBC0 000303C2 2D306C00 140202C1 0402C104 [....-0l.........] 60000000001ECBD0 02C22901 800B434C 495F434F 4D4D454E [..)...CLI_COMMEN] 60000000001ECBE0 5402C102 02C22901 80FFFF01 80FFFF02 [T.....).........]

60000000001ECBF0 C1040180 02C10202 C1020180 018002C2 [................] 60000000001ECC00 296C0014 0202C103 02C10302 C2020180 [)l..............] 60000000001ECC10 0F455850 4F52545F 46554E43 54494F4E [.EXPORT_FUNCTION] 60000000001ECC20 02C10202 C2020180 FFFF0180 FFFF02C1 [................] 60000000001ECC30 03018002 C10202C1 02018001 8002C202 [................] 60000000001ECC40 6C001402 02C10202 C10202C1 1F01800B [l...............] 60000000001ECC50 434C4945 4E545F4E 414D4502 C10202C1 [CLIENT_NAME.....] 60000000001ECC60 1F0180FF FF02C102 FFFF02C1 02018002 [................] 60000000001ECC70 C10202C1 02018001 8002C11F 6C002402 [............l.$.] 60000000001ECC80 03C22D2E 018002C1 0203C233 05FFFF02 [..-........3....] 60000000001ECC90 C104FF02 C10B02C1 2902C102 03C20338 [........)......8] 60000000001ECCA0 02C10226 2D2D2D2D 2D2D2D2D 2D2D2D2D [...&------------] 60000000001ECCB0 2D2D2D2D 2D2D2D2D 2D2D2D2D 2D2D2D2D [----------------] 60000000001ECCC0 2D2D2D2D 2D2D2D2D 2D2DFFFF FFFFFFFF [----------......] 60000000001ECCD0 FFFFFFFF FFFF02C1 0402C104 06C50625 [...............%] 60000000001ECCE0 580A0D01 8003C208 25FFFFFF FF07786D [X.......%.....xm] 60000000001ECCF0 0A061027 276C0021 0203C22D 2F03C22D [...''l.!...-/..-] 60000000001ECD00 2F018002 C10203C2 33070180 02C10202 [/.......3.......] 60000000001ECD10 C10B02C1 0303C203 38FF02C1 02018003 [........8.......] 60000000001ECD20 C22962FF FFFFFFFF FFFFFFFF 02C102FF [.)b.............] 60000000001ECD30 FFFF02C1 02FFFFFF FF07786D 0A061027 [..........xm...'] 60000000001ECD40 276C0009 0203C22D 2F02C102 02C10201 ['l.....-/.......] 60000000001ECD50 80018001 8002C102 01800180 AC000100 [................] 60000000001ECD60 06000600 4011A700 02004011 A7000203 [....@.....@.....] 60000000001ECD70 C22D2E6C 00020126 2D2D2D2D 2D2D2D2D [.-.l...&--------] 60000000001ECD80 2D2D2D2D 2D2D2D2D 2D2D2D2D 2D2D2D2D [----------------] 60000000001ECD90 2D2D2D2D 2D2D2D2D 2D2D2D2D 2D2D0180 [--------------..] 60000000001ECDA0 AC000100 01000100 4011A700 01004011 [........@.....@.] 60000000001ECDB0 A7000103 C22D2D6C 00020026 2D2D2D2D [.....--l...&----] 60000000001ECDC0 2D2D2D2D 2D2D2D2D 2D2D2D2D 2D2D2D2D [----------------] 60000000001ECDD0 2D2D2D2D 2D2D2D2D 2D2D2D2D 2D2D2D2C [---------------,] 60000000001ECDE0 01010F33 33330A34 3434340A 0A353535 [...333.4444..555] <- Second row value 60000000001ECDF0 35352C01 0106310A 0A323220 ED8B0601 [55,...1..22 ....] <- First row value Block header dump: 0x0100011a Object id on Block? Y seg/obj: 0x2620 csc: 0x00.2ec18 itc: 2 flg: E typ: 1 - DATA brn: 0 bdba: 0x1000109 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0006.02d.00000078 0x00800b2f.0045.14 --U- 2 fsc 0x0000.0002ed8b 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 data_block_dump,data header at 0x60000000001eae64 =============== tsiz: 0x1f98 hsiz: 0x16 pbl: 0x60000000001eae64 bdba: 0x0100011a 76543210 flag=-------ntab=1 nrow=2 frre=-1

fsbo=0x16 fseo=0x1f7b avsp=0x1f65 tosp=0x1f65 0xe:pti[0] nrow=2 offs=0 0x12:pri[0] offs=0x1f8e 0x14:pri[1] offs=0x1f7b block_row_dump: tab 0, row 0, @0x1f8e tl: 10 fb: --H-FL-- lb: 0x1 cc: 1 col 0: [ 6] 31 0a 0a 32 32 20 <- First record length tab 0, row 1, @0x1f7b tl: 19 fb: --H-FL-- lb: 0x1 cc: 1 col 0: [15] 33 33 33 0a 34 34 34 34 0a 0a 35 35 35 35 35 <- Second record length end_of_block_dump End dump data blocks tsn: 4 file#: 4 minblk 282 maxblk 282 --Started the db. SQL> SELECT * FROM V$DATABASE_BLOCK_CORRUPTION; no rows selected SQL> SELECT * FROM V$BACKUP_CORRUPTION; no rows selected SQL> SELECT * FROM V$COPY_CORRUPTION; no rows selected --Verified that there is no corruption in the db. --In closing, if VEEKSHA table's column is 5 bytes in length, then the pl/sql string parser will fail with with the error "ORA-12899: value too large for column". Eventhough the col1 length is 15 bytes, Oracle did not insert the string value into a single row, inserted the row into multiple rows. Why and how Oracle inserted a single record into multiple rows, is a puzzle. --Let us display the string value and length from the pl/sql string parser. SQL> set serverout on size 1000000 declare i number:=1; j number:=0; SQL> 2 3 4 v_str varchar2(10000):='1 5 6 22 7 4444 8 9 55555'; 10 11 begin

333

12 13 14 15 16 17 18 19 20 21 then 22 23 24 25 26 27 28 29 30 31 32 33 34 / 1

while (i ' ' ) and ( substr(v_str,i,1) IS NOT NULL ) ) dbms_output.put_line(substr(v_str,i,j)||' '||length(substr(v_str,i,j))); --insert into VEEKSHA values(substr(v_str,i,j)); end if; i:=i+j; exception when others then dbms_output.put_line(sqlerrm); end; end loop; end;

4 22 3 333 5 4444 7 55555 5 PL/SQL procedure successfully completed.

--Inserted a record with an <ENTER> between 1 and 22 values in the string. SQL> insert into VEEKSHA values('1 2 22'); 1 row created. SQL> select * from VEEKSHA; COL1 --------------1 22 --Strangely, Oracle did not complain, but inserted the record.

SQL> insert into VEEKSHA values('333 2 4444 3 55555'); 1 row created. SQL> commit; Commit complete. SQL> select * from VEEKSHA; COL1 --------------1 22 333 4444 55555 --Inserted a second record into the table and displayed the records with rowid, rownum, length of col1 and col1 value. select rowid, rownum, length(col1), col1 from VEEKSHA; ROWID ROWNUM LENGTH(COL1) COL1 ------------------ ---------- ------------ --------------AAA4FAAABAAANdiAAA 1 41 22 AAA4FAAABAAANdiAAB 4444 55555

2

14 333

--Drop the table and recreate with three columns. SQL> drop table VEEKSHA; Table dropped. SQL> create table VEEKSHA(empno number, ename varchar2(15), hiredate date); Table created. SQL> insert into VEEKSHA values(1,'ra 2 m',sysdate); 1 row created. SQL> select * from VEEKSHA; EMPNO ENAME

HIREDATE

---------- --------------- --------1 ra 10-OCT-09 m

SQL> insert into VEEKSHA values(2,'si 2 t 3 a',sysdate); 1 row created. SQL> commit; Commit complete. SQL> select * from VEEKSHA; EMPNO ENAME HIREDATE ---------- --------------- --------1 ra 10-OCT-09 m 2 si 10-OCT-09 t a --Inserted two record into the table that were spread into multiple rows successfully. SQL> select rowid, rownum, length(ename),empno, ename,hiredate from VEEKSHA; ROWID ROWNUM LENGTH(ENAME) EMPNO ENAME ------------------ ---------- ------------- ---------- --------------- --------AAA4FBAABAAANdiAAA 1 5 1 ra 10-OCT-09 m AAA4FBAABAAANdiAAB

2 t a

7

2 si

HIREDATE

10-OCT-09

--From the above inserts, it is evident, Oracle is not counting the nulls between the inserted string values, that were generated with an <ENTER>. How could Oracle insert a record into multiple rows is a puzzle to decypher. References: http://www.databasejournal.com/scripts/article.php/3678311 http://www.databasejournal.com/features/oracle/article.phpr/3758471 http://www.databasejournal.com/scripts/article.php/3828731/Nulli-Secundus.htm http://www.dbasupport.com/oracle/ora10g/bbed.shtml http://www.dbasupport.com/oracle/ora10g/bbed02.shtml http://www.orafaq.com/papers/dissassembling_the_data_block.pdf http://translate.google.com/translate?hl=en&sl=ru&u=http://vpahom.blogspot.com/2009/05/bbedblock-browser-and-editor.html&ei=7AXMSs60LsYtgf5g9m5Bw&sa=X&oi=translate&resnum=1&ct=result&prev=/search%3Fq%3D %2522BBED-00312:%2Bno%2BLISTFILE%2Bspecified%2522%26hl%3Den%26sa%3DG