关于直方图统计信息的两个有趣的知识点

有朋友问了我如下这样一个问题,最后的解决过程挺有意思的,让我发现了直方图统计信息里我之前没有注意到的两个知识点,这里跟大家分享一下。

 

这个问题是这样:

数据库的版本是11.2.0.3

SQL> select * from v$version;

 

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

PL/SQL Release 11.2.0.3.0 – Production

CORE     11.2.0.3.0       Production

TNS for Linux: Version 11.2.0.3.0 – Production

NLSRTL Version 11.2.0.3.0 – Production

 

创建一个测试表T1

SQL> create table t1 as select * from dba_users;

 

Table created

 

从如下查询结果中我们可以看到,表T1OBJECT_ID104192

SQL> select object_id from dba_objects where owner=’SCOTT’ and object_name=’T1′;

 

 OBJECT_ID

——————-

  104192

 

T1的列user_id所对应的INTCOL#2

SQL> select name,intcol# from sys.col$ where obj#=104192 and name=’USER_ID';

 

NAME                              INTCOL#

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

USER_ID                                 2

 

从如下结果里可以看到,SYS.COL_USAGE$现在还没有列USER_ID的使用记录:

SQL> select obj#,intcol#,equality_preds from sys.col_usage$ where obj#=104192;

 

      OBJ#    INTCOL# EQUALITY_PREDS

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

 

 

我们现在来使用一下列USER_ID

SQL> select count(*) from t1 where user_id=5;

 

  COUNT(*)

—————–

         1

 

使用完后,我们发现SYS.COL_USAGE$还是没有列USER_ID的使用记录:

SQL> select obj#,intcol#,equality_preds from sys.col_usage$ where obj#=104192;

 

      OBJ#    INTCOL# EQUALITY_PREDS

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

 

 

这个是正常的,这里不是没有USER_ID的使用记录,是已经有了但只是还没有被持久化到SYS.COL_USAGE$中,这里需要我们手工执行一下dbms_stats.gather_table_stats,这样就能将USER_ID的使用记录flushSYS.COL_USAGE$中了,然后我们就能看到了:

SQL> exec dbms_stats.gather_table_stats(ownname=>’SCOTT’,tabname=>’T1′,estimate_percent=>100);

 

PL/SQL procedure successfully completed

 

SQL> select obj#,intcol#,equality_preds from sys.col_usage$ where obj#=104192;

 

      OBJ#    INTCOL# EQUALITY_PREDS

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

    104192          2              1

 

但现在的问题是无论我们怎么执行dbms_stats.gather_table_stats,列user_id上的直方图统计信息就是没有(这也是那位朋友问的问题):

SQL> exec dbms_stats.gather_table_stats(ownname=>’SCOTT’,tabname=>’T1′,method_opt=>’for all columns size auto‘,estimate_percent=>100);

 

PL/SQL procedure successfully completed

 

SQL> select table_name,column_name,num_distinct,num_buckets,histogram from dba_tab_col_statistics where owner=’SCOTT’ and table_name=’T1′ and column_name=’USER_ID';

 

TABLE_NAME COLUMN_NAM NUM_DISTINCT NUM_BUCKETS HISTOGRAM

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

T1         USER_ID              39           1 NONE

 

SQL> exec dbms_stats.gather_table_stats(ownname=>’SCOTT’,tabname=>’T1′,method_opt=>’for columns size auto USER_ID‘,estimate_percent=>100);

 

PL/SQL procedure successfully completed

 

SQL> select table_name,column_name,num_distinct,num_buckets,histogram from dba_tab_col_statistics where owner=’SCOTT’ and table_name=’T1′ and column_name=’USER_ID';

 

TABLE_NAME COLUMN_NAM NUM_DISTINCT NUM_BUCKETS HISTOGRAM

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

T1         USER_ID              39           1 NONE

 

这里除非我们手工指定user_id列所用的bucket的数量:

SQL> exec dbms_stats.gather_table_stats(ownname=>’SCOTT’,tabname=>’T1′,method_opt=>’for columns size 39 USER_ID‘,estimate_percent=>100);

 

PL/SQL procedure successfully completed

 

SQL> select table_name,column_name,num_distinct,num_buckets,histogram from dba_tab_col_statistics where owner=’SCOTT’ and table_name=’T1′ and column_name=’USER_ID';

 

TABLE_NAME COLUMN_NAM NUM_DISTINCT NUM_BUCKETS HISTOGRAM

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

T1         USER_ID              39          39 HEIGHT BALANCED

 

手工指定了直方图统计信息的bucket的数量为39后,明明列user_iddistinct值的数量也是39,为什么这里直方图的类型居然是HEIGHT BALANCED?按道理讲应该是FREQUENCY啊!

 

当看到上述测试结果的时候,我意识到一定是什么地方出了问题,因为上述现象的出现已经颠覆了我之前对直方图统计信息的如下两个认识:

1、我原先一直以为如果METHOD_OPT的值是默认的“FOR ALL COLUMNS SIZE AUTO”的话,那么只要SYS.COL_USAGE$中有目标列的使用记录,则Oracle在自动收集直方图统计信息的时候就会去收集该列的直方图统计信息;

2、在手工收集直方图统计信息的时候,如果我手工指定的bucket的数量等于目标列的distinct值的数量,且这个值是小于等于254的话,那么Oracle此时收集的直方图统计信息的类型应该是FREQUENCY

 

到底是什么地方出了问题?

 

我们来复习一下Oracle关于自动收集直方图统计信息的定义:

Oracle在“SIZE Clause in METHOD_OPT Parameter of DBMS_STATS Package (Doc ID 338926.1)”中明确指出,METHOD_OPT的值中的AUTO的含义为如下所示:

AUTO: Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.

 

这里的“workload of the columns”指的应该就是目标列是否在SYS.COL_USAGE$中有使用记录。注意到Oracle这里还提到了另外一个条件——“based on data distribution”(这也是我之前没有注意到的条件),但这里的具体含义是什么?

based on data distribution”直译过来就是目标列数据的分布,说白了就是目标列的数据分布确实得是倾斜的,只有满足这个前提条件,再加上该目标列在SYS.COL_USAGE$中有使用记录,Oracle在自动收集直方图统计信息的时候才会对该列收集直方图统计信息

 

Oracle是怎么来判断某列的数据分布是否是倾斜的呢?

我研究了一下,发现Oracle采用了一种很简单的方法——就是判断目标列的distinct值的数量是否和目标表的数据量相同,如果相同,Oracle就认为该列的数据分布不是倾斜的,否则就是倾斜的。也就是说,如果目标列的distinct值的数量和目标表的数据量相同,即使该目标列在SYS.COL_USAGE$中有使用记录,Oracle在自动收集直方图统计信息的时候也不会对该列收集直方图统计信息。

 

搞清楚了上述知识点,那位朋友问的问题自然就有答案了——对于表T1的列user_id而言,其distinct值的数量和表T1的数据量相同,所以这里即使user_idSYS.COL_USAGE$中有使用记录,Oracle在自动收集直方图统计信息的时候也不会对user_id收集直方图统计信息:

SQL> select count(distinct user_id) from t1;

 

COUNT(DISTINCTUSER_ID)

———————-

             39

 

SQL> select count(*) from t1;

 

  COUNT(*)

———-

        39

 

现在我们来验证一下上述理论,往表T1中插入一条记录,使得user_iddistinct值的数量小于表T1的数据量,这样当我们再次对表T1收集统计信息的时候,user_id列的直方图统计信息应该就有了。

 

先把之前对user_id列手工指定bucket数量收集的直方图统计信息删掉:

SQL> exec dbms_stats.gather_table_stats(ownname=>’SCOTT’,tabname=>’T1′,method_opt=>’for columns size 1 USER_ID‘,estimate_percent=>100);

 

PL/SQL procedure successfully completed

 

SQL> select table_name,column_name,num_distinct,num_buckets,histogram from dba_tab_col_statistics where owner=’SCOTT’ and table_name=’T1′ and column_name=’USER_ID';

 

TABLE_NAME COLUMN_NAM NUM_DISTINCT NUM_BUCKETS HISTOGRAM

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

T1         USER_ID              39           1 NONE

 

对表T1插入一条user_id列的值和现有值重复的记录:

SQL> insert into t1 select * from t1 where user_id=5;

 

1 row inserted

 

SQL> commit;

 

Commit complete

 

现在user_id列的distinct值的数量已经小于表T1的数据量了:

SQL> select count(distinct user_id) from t1;

 

COUNT(DISTINCTUSER_ID)

———————-

            39

 

SQL> select count(*) from t1;

 

  COUNT(*)

———-

        40

 

此时对表T1再次收集统计信息:

SQL> exec dbms_stats.gather_table_stats(ownname=>’SCOTT’,tabname=>’T1′,estimate_percent=>100);

 

PL/SQL procedure successfully completed

 

从如下查询结果里我们可以看到,现在user_id列上终于有了直方图统计信息,且其类型就是FREQUENCY,这就和我们以前的认知匹配上了,同时也验证了我们刚才的分析结论:

SQL> select table_name,column_name,num_distinct,num_buckets,histogram from dba_tab_col_statistics where owner=’SCOTT’ and table_name=’T1′ and column_name=’USER_ID';

 

TABLE_NAME COLUMN_NAM NUM_DISTINCT NUM_BUCKETS HISTOGRAM

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

T1         USER_ID              39          39 FREQUENCY

 

再次删除user_id列上的直方图统计信息:

SQL> exec dbms_stats.gather_table_stats(ownname=>’SCOTT’,tabname=>’T1′,method_opt=>’for columns size 1 USER_ID‘,estimate_percent=>100);

 

PL/SQL procedure successfully completed

 

SQL> select table_name,column_name,num_distinct,num_buckets,histogram from dba_tab_col_statistics where owner=’SCOTT’ and table_name=’T1′ and column_name=’USER_ID';

 

TABLE_NAME COLUMN_NAM NUM_DISTINCT NUM_BUCKETS HISTOGRAM

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

T1         USER_ID              39           1 NONE

 

我们再次以手工指定bucket数量的方式收集user_id列上的直方图统计信息:

SQL>  exec dbms_stats.gather_table_stats(ownname=>’SCOTT’,tabname=>’T1′,method_opt=>’for columns size 39 USER_ID‘,estimate_percent=>100);

 

PL/SQL procedure successfully completed

 

从如下查询结果我们可以看到,现在user_id列上的直方图统计信息的类型已经不是之前的HEIGHT BALANCED了,而是变成了FREQUENCY

SQL> select table_name,column_name,num_distinct,num_buckets,histogram from dba_tab_col_statistics where owner=’SCOTT’ and table_name=’T1′ and column_name=’USER_ID';

 

TABLE_NAME COLUMN_NAM NUM_DISTINCT NUM_BUCKETS HISTOGRAM

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

T1         USER_ID              39          39 FREQUENCY

 

这说明我们之前的认识(在手工收集直方图统计信息的时候,如果我手工指定的bucket的数量等于目标列的distinct值的数量,且这个值是小于等于254的话,那么Oracle此时收集的直方图统计信息的类型应该是FREQUENCY)成立的前提条件是该列的数据分布是倾斜的。

 

总结一下,通过这篇文章,我们介绍了如下两个关于直方图统计信息的有趣知识点:

1、如果目标列的distinct值的数量和目标表的数据量相同,即使该目标列在SYS.COL_USAGE$中有使用记录,Oracle在自动收集直方图统计信息的时候也不会对该列收集直方图统计信息;

2、在手工收集直方图统计信息的时候,如果我手工指定的bucket的数量等于目标列的distinct值的数量,且这个值是小于等于254的话,那么Oracle此时收集的直方图统计信息的类型应该是FREQUENCY——这个结论成立的前提条件是该列的数据分布是倾斜的。


How to use embedded BBED in ODU to manually fix missing offset in row directory

Note: The latest ODU version (ODU 4.3.3) supports recover accidentally deleted data automatically even if all the deleted rows’ offsets in corresponding row directory are completely cleared by Oracle. Due to Jun’s amazing work, you don’t need to manually fix missing offset if you use ODU 4.3.3, ODU 4.3.3 can identify all the missing offsets in row directory automatically! Thank God!

 

In this article, I demonstrate how to use embedded BBED in ODU 4.3.2 to manually fix missing offset in row directory.

 

First of all, let’s construct an example of missing offset in row directory.

Create table T1 in user scott and insert more than 580,000 test rows:

SQL> conn scott/tiger@cuihua112;

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as scott

 

SQL> create table t1 as select * from dba_objects;

 

Table created

 

SQL> insert into t1 select * from t1;

 

72532 rows inserted

 

SQL> insert into t1 select * from t1;

 

145064 rows inserted

 

SQL> insert into t1 select * from t1;

 

290128 rows inserted

 

SQL> commit;

 

Commit complete

 

SQL> select count(*) from t1;

 

  COUNT(*)

——————-

  580256

 

SQL> create sequence seq_t1 minvalue 1 maxvalue 999999999999999999999 start with 1 increment by 1 cache 200;

 

Sequence created

 

SQL> alter table t1 add (id number);

 

Table altered

 

SQL> update t1 set id = seq_t1.nextval;

 

580256 rows updated

 

SQL> commit;

 

Commit complete

 

 

Execute the following SQL, you can see that the 580,256 records of table T1 are distributed among 8231 data blocks:

SQL> select min(id) min_id,max(id) max_id,dbms_rowid.rowid_relative_fno(rowid)||’_’||dbms_rowid.rowid_block_number(rowid) location from t1 group by dbms_rowid.rowid_relative_fno(rowid)||’_’||dbms_rowid.rowid_block_number(rowid) order by max_id;

 

    MIN_ID     MAX_ID LOCATION

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

         1         88 4_619

        89        171 4_620

       172        251 4_621

    ……omit some display contents

      4845       4924 4_684

 

    MIN_ID     MAX_ID LOCATION

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

      4925       5000 4_685

      5001       5080 4_686

    ……omit some display contents

    574626     574694 4_9483

    574695     574763 4_9484

    574764     574833 4_9485

    ……omit some display contents

    580111     580181 4_9599

    580182     580256 4_9626

 

8231 rows selected

 

 

Create a backup table T1_BACKUP to record the columns ID of table T1:

SQL> create table t1_backup as select id from t1;

 

Table created

 

SQL> select count(*) from t1_backup;

 

  COUNT(*)

———-

    580256

 

 

Perform a delete operation on table T1, but do not commit:

SQL> delete from t1;

 

580256 rows deleted

 

 

In another session execute flush buffer_cache in order to construct a delay block cleanout:

E:\Recovery_Test\ODU\data>sqlplus /nolog

 

SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 3 16:17:07 2014

 

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

 

SQL> conn / as sysdba;

Connected.

 

SQL> alter system flush buffer_cache;

 

System altered.

 

 

Go back to the session where execute delete opration towards table T1, execute commit, followed by a select and checkpoint operations:

SQL> commit;

 

Commit complete

 

SQL> select * from t1;

 

OWNER                          OBJECT_NAME                                                                      SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED     LAST_DDL_TIME TIMESTAMP           STATUS  TEMPORARY GENERATED SECONDARY  NAMESPACE EDITION_NAME                           ID

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

 

SQL> select count(*) from t1;

 

  COUNT(*)

——————

     0

 

SQL> alter system checkpoint;

 

System altered

 

 

Now we use ODU 4.3.2 to recover all the deleted rows of table T1:

E:\Recovery_Test\ODU>odu

 

Oracle Data Unloader:Release 4.3.2

 

Copyright (c) 2008,2009,2010,2011 XiongJun. All rights reserved.

 

Web: http://www.oracleodu.com

Email: magic007cn@gmail.com

 

loading default config…….

 

byte_order little

block_size  8192

data_path   data

lob_path    lob

db_timezone -7

Invalid db timezone:-7

client_timezone 8

Invalid client timezone:8

asmfile_extract_path   asmfile

charset_name ZHS16GBK

ncharset_name AL16UTF16

output_format dmp

lob_storage infile

clob_byte_order big

trace_level 2

delimiter |

unload_deleted yes

file_header_offset 0

is_tru64 no

record_row_addr no

convert_clob_charset yes

use_scanned_lob  yes

trim_scanned_blob yes

lob_switch_dir_rows 1000

db_block_checksum yes

db_block_checking yes

rdba_file_bits 10

compatible 10

load config file ‘config.txt’ successful

loading default asm disk file ……

 

can not open file ‘asmdisk.txt’, error message:No such file or directory.

loading default control file ……

 

 

 ts#   fn  rfn bsize   blocks bf offset filename

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

   0    1    1  8192   101120 N       0 D:\app\cuihua\oradata\cuihua112\SYSTEM01.DBF

   1    2    2  8192    69120 N       0 D:\app\cuihua\oradata\cuihua112\SYSAUX01.DBF

   2    3    3  8192    72960 N       0 D:\app\cuihua\oradata\cuihua112\UNDOTBS01.DBF

   6    5    5  8192    12800 N       0 D:\app\cuihua\oradata\cuihua112\EXAMPLE01.DBF

   4    4    4  8192   157760 N       0 D:\app\cuihua\oradata\cuihua112\USERS01.DBF

load control file ‘oductl.dat’ successful

loading dictionary data……done

 

loading scanned data……done

 

ODU> unload dict

CLUSTER C_USER# file_no: 1 block_no: 208

TABLE OBJ$ obj_no: 18 file_no: 1 block_no: 240

CLUSTER C_OBJ# file_no: 1 block_no: 144

CLUSTER C_OBJ# file_no: 1 block_no: 144

found IND$’s obj# 19

found IND$’s dataobj#:2,ts#:0,file#:1,block#:144,tab#:3

found TABPART$’s obj# 576

found TABPART$’s dataobj#:576,ts#:0,file#:1,block#:3824,tab#:0

found INDPART$’s obj# 581

found INDPART$’s dataobj#:581,ts#:0,file#:1,block#:3872,tab#:0

found TABSUBPART$’s obj# 588

found TABSUBPART$’s dataobj#:588,ts#:0,file#:1,block#:3928,tab#:0

found INDSUBPART$’s obj# 593

found INDSUBPART$’s dataobj#:593,ts#:0,file#:1,block#:3968,tab#:0

found IND$’s obj# 19

found IND$’s dataobj#:2,ts#:0,file#:1,block#:144,tab#:3

found LOB$’s obj# 80

found LOB$’s dataobj#:2,ts#:0,file#:1,block#:144,tab#:6

found LOBFRAG$’s obj# 609

found LOBFRAG$’s dataobj#:609,ts#:0,file#:1,block#:4096,tab#:0

 

ODU> unload table scott.t1

 

Unloading table: T1,object ID: 74955 at 2014-04-03 16:22:08

Unloading segment,storage(Obj#=74955 DataObj#=74955 TS#=4 File#=4 Block#=618 Cluster=0)

 

Table T1 575558 rows unloaded

At 2014-04-03 16:22:23

 

You can see that ODU only recovered 575,558 records (number of records that has been deleted in table T1 is 580,256).

 

Import the above 575,558 records into the user SYS:

E:\Recovery_Test\ODU\data>imp \”sys/oracle@cuihua112 as sysdba\” file=SCOTT_T1.dmp tables=t1 ignore=true

 

Import: Release 11.2.0.1.0 – Production on Thu Apr 3 16:26:42 2014

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

Export file created by EXPORT:V08.01.07 via conventional path

 

Warning: the objects were exported by SCOTT, not by you

 

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

. importing SCOTT’s objects into SYS

. importing SCOTT’s objects into SYS

. . importing table                           “T1″     575558 rows imported

Import terminated successfully without warnings.

 

 

From the following query results, we can see that there are 4698 deleted records of the table T1 that ODU did not successfully recover:

SQL> conn scott/tiger@cuihua112;

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as scott

 

SQL> select id from (select id from t1_backup minus select id from sys.t1) order by id;

 

        ID

———-

      4925

      4926

      4927

    ……omit some display contents

    574692

    574693

    574694

 

4698 rows selected

 

 

Now we choose two blocks (where id is 4925 and 574694) of the table T1 to dump:

SQL> select min(id) from (select id from t1_backup minus select id from sys.t1);

 

   MIN(ID)

——————

    4925

 

SQL> select max(id) from (select id from t1_backup minus select id from sys.t1);

 

   MAX(ID)

——————

   574694

 

 

The record where ID equals to 4925 stored in datafile 4, block 685:

    MIN_ID      MAX_ID      LOCATION

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

      4925       5000         4_685

     

 

The record where ID equals to 574694 stored in datafile 4, block 9483:

    MIN_ID      MAX_ID      LOCATION

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

    574626       574694      4_9483

   

   

Now we use ODU to dump the above two blocks:

ODU> dump datafile 4 block 685

Block Header:

block type=0x06 (table/index/cluster segment data block)

block format=0xa2 (oracle 10+)

block rdba=0x010002ad (file#=4, block#=685)

scn=0x0000.001ae559, seq=1, tail=0xe5590601

block checksum value=0xa9a2=43426, flag=4

Data Block Header Dump:

 Object id on Block? Y

 seg/obj: 0x124cb=74955  csc: 0x00.1ae559  itc: 3  flg: E  typ: 1 (data)

     brn: 0  bdba: 0x10002a8 ver: 0x01

 

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0xffff.000.00000000  0x00000000.0000.00  C—    0  scn 0x0000.001a30aa

0x02   0x0003.004.00000478  0x00c1168c.0180.51  C—    0  scn 0x0000.001a7899

0x03   0x0004.00f.00000398  0x00c11452.00bb.20  C—    0  scn 0x0000.001ae557

Data Block Dump:

================

flag=0x0 ——–

ntab=1

nrow=76

frre=0

fsbo=0xaa

ffeo=0x170

avsp=0x1ed6

tosp=0x1ed6

0xe:pti[0] nrow=76  offs=0

0x12:pri[0]  sfll=1

0x14:pri[1]  sfll=2

0x16:pri[2]  sfll=3

0x18:pri[3]  sfll=4

0x1a:pri[4]  sfll=5

0x1c:pri[5]  sfll=6

0x1e:pri[6]  sfll=7

0x20:pri[7]  sfll=8

0x22:pri[8]  sfll=9

0x24:pri[9]  sfll=10

0x26:pri[10]  sfll=11

0x28:pri[11]  sfll=12

0x2a:pri[12]  sfll=13

0x2c:pri[13]  sfll=14

0x2e:pri[14]  sfll=15

0x30:pri[15]  sfll=16

0x32:pri[16]  sfll=17

0x34:pri[17]  sfll=18

0x36:pri[18]  sfll=19

0x38:pri[19]  sfll=20

0x3a:pri[20]  sfll=21

0x3c:pri[21]  sfll=22

0x3e:pri[22]  sfll=23

0x40:pri[23]  sfll=24

0x42:pri[24]  sfll=25

0x44:pri[25]  sfll=26

0x46:pri[26]  sfll=27

0x48:pri[27]  sfll=28

0x4a:pri[28]  sfll=29

0x4c:pri[29]  sfll=30

0x4e:pri[30]  sfll=31

0x50:pri[31]  sfll=32

0x52:pri[32]  sfll=33

0x54:pri[33]  sfll=34

0x56:pri[34]  sfll=35

0x58:pri[35]  sfll=36

0x5a:pri[36]  sfll=37

0x5c:pri[37]  sfll=38

0x5e:pri[38]  sfll=39

0x60:pri[39]  sfll=40

0x62:pri[40]  sfll=41

0x64:pri[41]  sfll=42

0x66:pri[42]  sfll=43

0x68:pri[43]  sfll=44

0x6a:pri[44]  sfll=45

0x6c:pri[45]  sfll=46

0x6e:pri[46]  sfll=47

0x70:pri[47]  sfll=48

0x72:pri[48]  sfll=49

0x74:pri[49]  sfll=50

0x76:pri[50]  sfll=51

0x78:pri[51]  sfll=52

0x7a:pri[52]  sfll=53

0x7c:pri[53]  sfll=54

0x7e:pri[54]  sfll=55

0x80:pri[55]  sfll=56

0x82:pri[56]  sfll=57

0x84:pri[57]  sfll=58

0x86:pri[58]  sfll=59

0x88:pri[59]  sfll=60

0x8a:pri[60]  sfll=61

0x8c:pri[61]  sfll=62

0x8e:pri[62]  sfll=63

0x90:pri[63]  sfll=64

0x92:pri[64]  sfll=65

0x94:pri[65]  sfll=66

0x96:pri[66]  sfll=67

0x98:pri[67]  sfll=68

0x9a:pri[68]  sfll=69

0x9c:pri[69]  sfll=70

0x9e:pri[70]  sfll=71

0xa0:pri[71]  sfll=72

0xa2:pri[72]  sfll=73

0xa4:pri[73]  sfll=74

0xa6:pri[74]  sfll=75

0xa8:pri[75]  sfll=-1

Block Rows Dump:

 

ODU> dump datafile 4 block 9483

Block Header:

block type=0x06 (table/index/cluster segment data block)

block format=0xa2 (oracle 10+)

block rdba=0x0100250b (file#=4, block#=9483)

scn=0x0000.001ae559, seq=1, tail=0xe5590601

block checksum value=0xa625=42533, flag=4

Data Block Header Dump:

 Object id on Block? Y

 seg/obj: 0x124cb=74955  csc: 0x00.1ae559  itc: 2  flg: E  typ: 1 (data)

     brn: 0  bdba: 0x1002282 ver: 0x01

 

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0004.00f.00000398  0x00c0cf15.0104.16  C—    0  scn 0x0000.001ae557

0x02   0x0003.004.00000478  0x00c08e1b.01b5.3f  C-U-    0  scn 0x0000.001a7af8

Data Block Dump:

================

flag=0x0 ——–

ntab=1

nrow=69

frre=0

fsbo=0x9c

ffeo=0x204

avsp=0x1efc

tosp=0x1efc

0xe:pti[0] nrow=69  offs=0

0x12:pri[0]  sfll=1

0x14:pri[1]  sfll=2

0x16:pri[2]  sfll=3

0x18:pri[3]  sfll=4

0x1a:pri[4]  sfll=5

0x1c:pri[5]  sfll=6

0x1e:pri[6]  sfll=7

0x20:pri[7]  sfll=8

0x22:pri[8]  sfll=9

0x24:pri[9]  sfll=10

0x26:pri[10]  sfll=11

0x28:pri[11]  sfll=12

0x2a:pri[12]  sfll=13

0x2c:pri[13]  sfll=14

0x2e:pri[14]  sfll=15

0x30:pri[15]  sfll=16

0x32:pri[16]  sfll=17

0x34:pri[17]  sfll=18

0x36:pri[18]  sfll=19

0x38:pri[19]  sfll=20

0x3a:pri[20]  sfll=21

0x3c:pri[21]  sfll=22

0x3e:pri[22]  sfll=23

0x40:pri[23]  sfll=24

0x42:pri[24]  sfll=25

0x44:pri[25]  sfll=26

0x46:pri[26]  sfll=27

0x48:pri[27]  sfll=28

0x4a:pri[28]  sfll=29

0x4c:pri[29]  sfll=30

0x4e:pri[30]  sfll=31

0x50:pri[31]  sfll=32

0x52:pri[32]  sfll=33

0x54:pri[33]  sfll=34

0x56:pri[34]  sfll=35

0x58:pri[35]  sfll=36

0x5a:pri[36]  sfll=37

0x5c:pri[37]  sfll=38

0x5e:pri[38]  sfll=39

0x60:pri[39]  sfll=40

0x62:pri[40]  sfll=41

0x64:pri[41]  sfll=42

0x66:pri[42]  sfll=43

0x68:pri[43]  sfll=44

0x6a:pri[44]  sfll=45

0x6c:pri[45]  sfll=46

0x6e:pri[46]  sfll=47

0x70:pri[47]  sfll=48

0x72:pri[48]  sfll=49

0x74:pri[49]  sfll=50

0x76:pri[50]  sfll=51

0x78:pri[51]  sfll=52

0x7a:pri[52]  sfll=53

0x7c:pri[53]  sfll=54

0x7e:pri[54]  sfll=55

0x80:pri[55]  sfll=56

0x82:pri[56]  sfll=57

0x84:pri[57]  sfll=58

0x86:pri[58]  sfll=59

0x88:pri[59]  sfll=60

0x8a:pri[60]  sfll=61

0x8c:pri[61]  sfll=62

0x8e:pri[62]  sfll=63

0x90:pri[63]  sfll=64

0x92:pri[64]  sfll=65

0x94:pri[65]  sfll=66

0x96:pri[66]  sfll=67

0x98:pri[67]  sfll=68

0x9a:pri[68]  sfll=-1

Block Rows Dump:

 

From the above dump results, we can see that all the offsets in above two blocks’ row directory are totally cleared by Oracle.

That means we successfully construct an example of missing offset in row directory.

 

ODU integrates BBED, you can directly modify Oracle data block in ODU.

Input ‘blockedit’ or ‘exit’ to enter into or quit from the embedded BBED in ODU.

ODU> blockedit

 

Entering block edit module.

 

BBED> help

 

help      —-   get command list

spool     —-   spool information to file

host      —-   enter os terminal

rowid     —-   decode rowid components

rdba      —-   decode RDBA to rfile# and block#

time      —-   convert number to timestamp

exit      —-   exit from blockedit module

set       —-   set file,block,offset for edit

show      —-   show enviroment and variables

dump      —-   dump block

modify    —-   modify block data

sum       —-   calculate checksum and write block to file

rollback  —-   rollback the change

find      —-   find hex string

verify    —-   verify the block

mark      —-   mark undo segments corruption

clone     —-   copy block from another block

clean     —-   clean free block pool or transaction

 

BBED> exit

 

Exiting block edit module.

 

ODU>

 

From the following results, we can see that the deleted rows in the above two blocks are still in fact, Oracle just cleared out all the offset in these two blocks’ row directory:

ODU> blockedit

 

Entering block edit module.

 

BBED> set datafile 4

BBED> set block 685

BBED> dump /x

 

       -0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

0000   06 a2 00 00 ad 02 00 01 59 e5 1a 00 00 00 01 04  ……..Y…….

0010   a2 a9 00 00 01 00 00 00 cb 24 01 00 59 e5 1a 00  ⅸ…….$..Y…

0020   00 00 00 00 03 00 32 00 a8 02 00 01 ff ff 00 00  ……2………

0030   00 00 00 00 00 00 00 00 00 00 00 00 00 80 00 00  …………….

0040   aa 30 1a 00 03 00 04 00 78 04 00 00 8c 16 c1 00  .0……x…….

0050   80 01 51 00 00 80 00 00 99 78 1a 00 04 00 0f 00  ..Q……x……

0060   98 03 00 00 52 14 c1 00 bb 00 20 00 00 80 00 00  ….R….. …..

0070   57 e5 1a 00 00 00 00 00 00 00 00 00 00 01 4c 00  W………….L.

0080   00 00 aa 00 70 01 d6 1e d6 1e 00 00 4c 00 01 00  ….p…….L…

0090   02 00 03 00 04 00 05 00 06 00 07 00 08 00 09 00  …………….

 

BBED> set offset 512

BBED> dump /x

 

       -0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

0200   4e 54 45 58 54 ff 03 c2 33 48 ff 07 53 59 4e 4f  NTEXT…3H..SYNO

0210   4e 59 4d 07 78 6e 03 1e 0b 0a 2f 07 78 6e 03 1e  NYM.xn…./.xn..

0220   0b 0a 2f 13 32 30 31 30 2d 30 33 2d 33 30 3a 31  ../.2010-03-30:1

0230   30 3a 30 39 3a 34 36 05 56 41 4c 49 44 01 4e 01  0:09:46.VALID.N.

0240   4e 01 4e 02 c1 02 ff 02 c2 33 3c 03 10 03 53 59  N.N……3<…SY

0250   53 0e 47 4c 4f 42 41 4c 5f 43 4f 4e 54 45 58 54  S.GLOBAL_CONTEXT

0260   ff 03 c2 33 47 ff 04 56 49 45 57 07 78 6e 03 1e  …3G..VIEW.xn..

0270   0b 0a 2f 07 78 6e 03 1e 0b 0a 2f 13 32 30 31 30  ../.xn…./.2010

0280   2d 30 33 2d 33 30 3a 31 30 3a 30 39 3a 34 36 05  -03-30:10:09:46.

0290   56 41 4c 49 44 01 4e 01 4e 01 4e 02 c1 02 ff 03  VALID.N.N.N…..

 

BBED> set offset 1024

BBED> dump /x

 

       -0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

0400   56 41 4c 49 44 01 4e 01 4e 01 4e 02 c1 02 2c 00  VALID.N.N.N…,.

0410   0e 06 50 55 42 4c 49 43 10 47 56 24 47 4c 4f 42  ..PUBLIC.GV$GLOB

0420   41 4c 43 4f 4e 54 45 58 54 ff 03 c2 33 46 ff 07  ALCONTEXT…3F..

0430   53 59 4e 4f 4e 59 4d 07 78 6e 03 1e 0b 0a 2f 07  SYNONYM.xn…./.

0440   78 6e 03 1e 0b 0a 2f 13 32 30 31 30 2d 30 33 2d  xn…./.2010-03-

0450   33 30 3a 31 30 3a 30 39 3a 34 36 05 56 41 4c 49  30:10:09:46.VALI

0460   44 01 4e 01 4e 01 4e 02 c1 02 3c 03 10 06 50 55  D.N.N.N…<…PU

0470   42 4c 49 43 0f 56 24 47 4c 4f 42 41 4c 43 4f 4e  BLIC.V$GLOBALCON

0480   54 45 58 54 ff 03 c2 33 44 ff 07 53 59 4e 4f 4e  TEXT…3D..SYNON

0490   59 4d 07 78 6e 03 1e 0b 0a 2f 07 78 6e 03 1e 0b  YM.xn…./.xn…

 

BBED> set offset 2048

BBED> dump /x

 

       -0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

0800   24 43 4f 4e 54 45 58 54 ff 03 c2 33 3a ff 07 53  $CONTEXT…3:..S

0810   59 4e 4f 4e 59 4d 07 78 6e 03 1e 0b 0a 2f 07 78  YNONYM.xn…./.x

0820   6e 03 1e 0b 0a 2f 13 32 30 31 30 2d 30 33 2d 33  n…./.2010-03-3

0830   30 3a 31 30 3a 30 39 3a 34 36 05 56 41 4c 49 44  0:10:09:46.VALID

0840   01 4e 01 4e 01 4e 02 c1 02 ff 03 c2 32 57 3c 03  .N.N.N……2W<.

0850   10 03 53 59 53 0a 56 5f 24 43 4f 4e 54 45 58 54  ..SYS.V_$CONTEXT

0860   ff 03 c2 33 39 ff 04 56 49 45 57 07 78 6e 03 1e  …39..VIEW.xn..

0870   0b 0a 2f 07 78 6e 03 1e 0b 0a 2f 13 32 30 31 30  ../.xn…./.2010

0880   2d 30 33 2d 33 30 3a 31 30 3a 30 39 3a 34 36 05  -03-30:10:09:46.

0890   56 41 4c 49 44 01 4e 01 4e 01 4e 02 c1 02 ff 03  VALID.N.N.N…..

 

BBED> set offset 4096

BBED> dump /x

 

       -0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

1000   49 44 01 4e 01 4e 01 4e 02 c1 02 ff 03 c2 32 43  ID.N.N.N……2C

1010   3c 03 10 03 53 59 53 17 54 58 4e 5f 42 41 43 4b  <…SYS.TXN_BACK

1020   4f 55 54 5f 53 54 41 54 45 5f 49 44 58 33 24 ff  OUT_STATE_IDX3$.

1030   03 c2 33 24 03 c2 33 24 05 49 4e 44 45 58 07 78  ..3$..3$.INDEX.x

1040   6e 03 1e 0b 0a 2e 07 78 6e 03 1e 0b 0a 2e 13 32  n……xn……2

1050   30 31 30 2d 30 33 2d 33 30 3a 31 30 3a 30 39 3a  010-03-30:10:09:

1060   34 35 05 56 41 4c 49 44 01 4e 01 4e 01 4e 02 c1  45.VALID.N.N.N..

1070   05 ff 03 c2 32 42 3c 03 10 03 53 59 53 17 54 58  ….2B<…SYS.TX

1080   4e 5f 42 41 43 4b 4f 55 54 5f 53 54 41 54 45 5f  N_BACKOUT_STATE_

1090   49 44 58 32 24 ff 03 c2 33 23 03 c2 33 23 05 49  IDX2$…3#..3#.I

 

BBED> set datafile 4

BBED> set block 9483

BBED> dump /x

 

       -0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

0000   06 a2 00 00 0b 25 00 01 59 e5 1a 00 00 00 01 04  …..%..Y…….

0010   25 a6 00 00 01 00 00 00 cb 24 01 00 59 e5 1a 00  %……..$..Y…

0020   00 00 00 00 02 00 32 00 82 22 00 01 04 00 0f 00  ……2..”……

0030   98 03 00 00 15 cf c0 00 04 01 16 00 00 80 00 00  …..………

0040   57 e5 1a 00 03 00 04 00 78 04 00 00 1b 8e c0 00  W…….x…….

0050   b5 01 3f 00 00 a0 00 00 f8 7a 1a 00 00 00 00 00  ..?……z……

0060   00 00 00 00 00 01 45 00 00 00 9c 00 04 02 fc 1e  ……E………

0070   fc 1e 00 00 45 00 01 00 02 00 03 00 04 00 05 00  ….E………..

0080   06 00 07 00 08 00 09 00 0a 00 0b 00 0c 00 0d 00  …………….

0090   0e 00 0f 00 10 00 11 00 12 00 13 00 14 00 15 00  …………….

 

BBED> set offset 1024

BBED> dump /x

 

       -0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

0400   01 4e 01 59 01 4e 02 c1 02 ff 04 c3 3a 2f 5d 3c  .N.Y.N……:/]<

0410   01 10 06 53 59 53 4d 41 4e 12 50 4b 5f 4d 47 4d  …SYSMAN.PK_MGM

0420   54 5f 48 4f 53 54 5f 43 52 45 44 53 ff 04 c3 07  T_HOST_CREDS….

0430   4a 02 04 c3 07 4a 02 05 49 4e 44 45 58 07 78 6e  J….J..INDEX.xn

0440   03 1e 0b 2b 19 07 78 6e 03 1e 0b 2b 19 13 32 30  …+..xn…+..20

0450   31 30 2d 30 33 2d 33 30 3a 31 30 3a 34 32 3a 32  10-03-30:10:42:2

0460   34 05 56 41 4c 49 44 01 4e 01 4e 01 4e 02 c1 05  4.VALID.N.N.N…

0470   ff 04 c3 3a 2f 5c 3c 01 10 06 53 59 53 4d 41 4e  …:/\<…SYSMAN

0480   15 4d 47 4d 54 5f 48 4f 53 54 5f 43 52 45 44 45  .MGMT_HOST_CREDE

0490   4e 54 49 41 4c 53 ff 04 c3 07 49 64 ff 05 54 41  NTIALS….Id..TA

 

BBED> set offset 2048

BBED> dump /x

 

       -0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

0800   33 30 3a 31 30 3a 34 31 3a 34 32 05 56 41 4c 49  30:10:41:42.VALI

0810   44 01 4e 01 4e 01 4e 02 c1 02 ff 04 c3 3a 2f 53  D.N.N.N……:/S

0820   3c 01 10 06 50 55 42 4c 49 43 1a 55 53 45 52 5f  <…PUBLIC.USER_

0830   53 44 4f 5f 4e 45 54 57 4f 52 4b 5f 55 53 45 52  SDO_NETWORK_USER

0840   5f 44 41 54 41 ff 04 c3 07 3d 28 ff 07 53 59 4e  _DATA….=(..SYN

0850   4f 4e 59 4d 07 78 6e 03 1e 0b 28 14 07 78 6e 03  ONYM.xn…(..xn.

0860   1e 0b 2a 2e 13 32 30 31 30 2d 30 33 2d 33 30 3a  ..*..2010-03-30:

0870   31 30 3a 34 31 3a 34 35 05 56 41 4c 49 44 01 4e  10:41:45.VALID.N

0880   01 4e 01 4e 02 c1 02 ff 04 c3 3a 2f 52 3c 01 10  .N.N……:/R<..

0890   05 4d 44 53 59 53 19 41 4c 4c 5f 53 44 4f 5f 4e  .MDSYS.ALL_SDO_N

 

BBED> set offset 4096

BBED> dump /x

 

       -0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

1000   2f 38 38 33 35 61 33 63 61 5f 44 69 63 6f 6d 4f  /8835a3ca_DicomO

1010   62 6a 32 ff 04 c3 07 0d 3b ff 0a 4a 41 56 41 20  bj2…..;..JAVA

1020   43 4c 41 53 53 07 78 6e 03 1e 0b 20 2c 07 78 6e  CLASS.xn… ,.xn

1030   03 1e 0b 20 36 13 32 30 31 30 2d 30 33 2d 33 30  … 6.2010-03-30

1040   3a 31 30 3a 33 31 3a 34 33 05 56 41 4c 49 44 01  :10:31:43.VALID.

1050   4e 01 4e 01 4e 02 c1 02 ff 04 c3 3a 2f 3f 3c 01  N.N.N……:/?<.

1060   10 06 4f 52 44 53 59 53 13 2f 37 39 62 35 34 66  ..ORDSYS./79b54f

1070   65 34 5f 44 69 63 6f 6d 4f 62 6a 31 ff 04 c3 07  e4_DicomObj1….

1080   0d 3a ff 0a 4a 41 56 41 20 43 4c 41 53 53 07 78  .:..JAVA CLASS.x

1090   6e 03 1e 0b 20 2c 07 78 6e 03 1e 0b 20 36 13 32  n… ,.xn… 6.2

 

 

Now we choose a normal (the associated row directory offset is not cleared by Oracle) data block (datafile 4, block 619) from table T1 to dump:

    MIN_ID     MAX_ID    LOCATION

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

       1          88        4_619

 

ODU> dump datafile 4 block 619

Block Header:

block type=0x06 (table/index/cluster segment data block)

block format=0xa2 (oracle 10+)

block rdba=0x0100026b (file#=4, block#=619)

scn=0x0000.001a8061, seq=30, tail=0x8061061e

block checksum value=0xef07=61191, flag=4

Data Block Header Dump:

 Object id on Block? Y

 seg/obj: 0x124cb=74955  csc: 0x00.1a805f  itc: 3  flg: E  typ: 1 (data)

     brn: 0  bdba: 0x1000268 ver: 0x01

 

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0xffff.000.00000000  0x00000000.0000.00  C—    0  scn 0x0000.001a30aa

0x02   0x0003.004.00000478  0x00c003dd.017f.39  C—    0  scn 0x0000.001a7899

0x03   0x0004.00f.00000398  0x00c016c7.00ba.1e  —-   88  fsc 0x1bfe.00000000

Data Block Dump:

================

flag=0x0 ——–

ntab=1

nrow=88

frre=-1

fsbo=0xc2

ffeo=0x13c

avsp=0x210

tosp=0x1ebe

0xe:pti[0] nrow=88  offs=0

0x12:pri[0]  offs=0x1f2f

0x14:pri[1]  offs=0x1edc

0x16:pri[2]  offs=0x1e8c

0x18:pri[3]  offs=0x1e3b

0x1a:pri[4]  offs=0x1de6

0x1c:pri[5]  offs=0x1d94

0x1e:pri[6]  offs=0x1d38

0x20:pri[7]  offs=0x1ce6

0x22:pri[8]  offs=0x1c93

0x24:pri[9]  offs=0x1c41

0x26:pri[10]  offs=0x1be1

0x28:pri[11]  offs=0x1b90

0x2a:pri[12]  offs=0x1b40

0x2c:pri[13]  offs=0x1ae6

0x2e:pri[14]  offs=0x1a93

0x30:pri[15]  offs=0x1a41

0x32:pri[16]  offs=0x19ef

0x34:pri[17]  offs=0x199e

0x36:pri[18]  offs=0x194b

0x38:pri[19]  offs=0x18fb

0x3a:pri[20]  offs=0x18ab

0x3c:pri[21]  offs=0x1858

0x3e:pri[22]  offs=0x1805

0x40:pri[23]  offs=0x17b5

0x42:pri[24]  offs=0x1764

0x44:pri[25]  offs=0x1711

0x46:pri[26]  offs=0x16c1

0x48:pri[27]  offs=0x166a

0x4a:pri[28]  offs=0x1617

0x4c:pri[29]  offs=0x15be

0x4e:pri[30]  offs=0x156c

0x50:pri[31]  offs=0x151a

0x52:pri[32]  offs=0x14c8

0x54:pri[33]  offs=0x1476

0x56:pri[34]  offs=0x1423

0x58:pri[35]  offs=0x13d4

0x5a:pri[36]  offs=0x1378

0x5c:pri[37]  offs=0x1323

0x5e:pri[38]  offs=0x12d0

0x60:pri[39]  offs=0x127a

0x62:pri[40]  offs=0x122a

0x64:pri[41]  offs=0x11d8

0x66:pri[42]  offs=0x1187

0x68:pri[43]  offs=0x1136

0x6a:pri[44]  offs=0x10e4

0x6c:pri[45]  offs=0x1091

0x6e:pri[46]  offs=0x1041

0x70:pri[47]  offs=0xfef

0x72:pri[48]  offs=0xf9b

0x74:pri[49]  offs=0xf4b

0x76:pri[50]  offs=0xefa

0x78:pri[51]  offs=0xea7

0x7a:pri[52]  offs=0xe54

0x7c:pri[53]  offs=0xe01

0x7e:pri[54]  offs=0xdae

0x80:pri[55]  offs=0xd5c

0x82:pri[56]  offs=0xd09

0x84:pri[57]  offs=0xca9

0x86:pri[58]  offs=0xc54

0x88:pri[59]  offs=0xc00

0x8a:pri[60]  offs=0xbaa

0x8c:pri[61]  offs=0xb54

0x8e:pri[62]  offs=0xb01

0x90:pri[63]  offs=0xaac

0x92:pri[64]  offs=0xa57

0x94:pri[65]  offs=0xa07

0x96:pri[66]  offs=0x9b7

0x98:pri[67]  offs=0x966

0x9a:pri[68]  offs=0x90f

0x9c:pri[69]  offs=0x8ba

0x9e:pri[70]  offs=0x863

0xa0:pri[71]  offs=0x80c

0xa2:pri[72]  offs=0x7bc

0xa4:pri[73]  offs=0x769

0xa6:pri[74]  offs=0x710

0xa8:pri[75]  offs=0x6be

0xaa:pri[76]  offs=0x66c

0xac:pri[77]  offs=0x61a

0xae:pri[78]  offs=0x5ca

0xb0:pri[79]  offs=0x578

0xb2:pri[80]  offs=0x526

0xb4:pri[81]  offs=0x4d2

0xb6:pri[82]  offs=0x2e6

0xb8:pri[83]  offs=0x290

0xba:pri[84]  offs=0x239

0xbc:pri[85]  offs=0x1e0

0xbe:pri[86]  offs=0x18f

0xc0:pri[87]  offs=0x13c

Block Rows Dump:

tab 0, row 0, @0x1f2f

fb: –HDFL– lb: 0x3  cc: 16

col   0: [  3]  53 59 53

col   1: [  5]  49 43 4f 4c 24

col   2: *NULL*

col   3: [  2]  c1 15

col   4: [  2]  c1 03

col   5: [  5]  54 41 42 4c 45

col   6: [  7]  78 6e 03 1e 0b 08 31

col   7: [  7]  78 6e 03 1e 0b 1c 16

col   8: [ 19]  32 30 31 30 2d 30 33 2d 33 30 3a 31 30 3a 30 37 3a 34 38

col   9: [  5]  56 41 4c 49 44

col  10: [  1]  4e

col  11: [  1]  4e

col  12: [  1]  4e

col  13: [  2]  c1 02

col  14: *NULL*

col  15: [  2]  c1 02

tab 0, row 1, @0x1edc

fb: –HDFL– lb: 0x3  cc: 16

col   0: [  3]  53 59 53

col   1: [  7]  49 5f 55 53 45 52 31

col   2: *NULL*

col   3: [  2]  c1 2f

col   4: [  2]  c1 2f

col   5: [  5]  49 4e 44 45 58

col   6: [  7]  78 6e 03 1e 0b 08 31

col   7: [  7]  78 6e 03 1e 0b 08 31

col   8: [ 19]  32 30 31 30 2d 30 33 2d 33 30 3a 31 30 3a 30 37 3a 34 38

col   9: [  5]  56 41 4c 49 44

col  10: [  1]  4e

col  11: [  1]  4e

col  12: [  1]  4e

col  13: [  2]  c1 05

col  14: *NULL*

col  15: [  2]  c1 03

 

……omit some display contents

 

tab 0, row 86, @0x18f

fb: –HDFL– lb: 0x3  cc: 16

col   0: [  3]  53 59 53

col   1: [  5]  4e 54 41 42 24

col   2: *NULL*

col   3: [  2]  c1 59

col   4: [  2]  c1 03

col   5: [  5]  54 41 42 4c 45

col   6: [  7]  78 6e 03 1e 0b 08 33

col   7: [  7]  78 6e 03 1e 0b 08 33

col   8: [ 19]  32 30 31 30 2d 30 33 2d 33 30 3a 31 30 3a 30 37 3a 35 30

col   9: [  5]  56 41 4c 49 44

col  10: [  1]  4e

col  11: [  1]  4e

col  12: [  1]  4e

col  13: [  2]  c1 02

col  14: *NULL*

col  15: [  2]  c1 58

tab 0, row 87, @0x13c

fb: –HDFL– lb: 0x3  cc: 16

col   0: [  3]  53 59 53

col   1: [  7]  49 5f 4e 54 41 42 31

col   2: *NULL*

col   3: [  2]  c1 5a

col   4: [  2]  c1 5a

col   5: [  5]  49 4e 44 45 58

col   6: [  7]  78 6e 03 1e 0b 08 33

col   7: [  7]  78 6e 03 1e 0b 08 33

col   8: [ 19]  32 30 31 30 2d 30 33 2d 33 30 3a 31 30 3a 30 37 3a 35 30

col   9: [  5]  56 41 4c 49 44

col  10: [  1]  4e

col  11: [  1]  4e

col  12: [  1]  4e

col  13: [  2]  c1 05

col  14: *NULL*

col  15: [  2]  c1 59

 

 

Let’s see the last two rows’ relative offset in the above block (datafile 4 block 619)’s row directory.

tab 0, row 86, @0x18f

tab 0, row 87, @0x13c

 

Now you can see that row 86’s relative offset is 0x18f, row 87’s relative offset is 0x13c.

We must add some base to relative offset in order to get the above two rows’ actual offset.

The algorithm of base is as follows:

For ASSMbase = 76+itc-1)*24

For MSSMbase = 68+itc-1)*24

 

From the following contents, we can see that the value of the above block’s itc is 3:

seg/obj: 0x124cb=74955  csc: 0x00.1a805f  itc: 3  flg: E  typ: 1 (data)

 

The tablespace where the table T1 resides use ASSM, so the actual offset of row 86 is 523:

SQL> select to_number(’18f’,’XXXX’) + 76 + (3-1)*24 from dual;

 

TO_NUMBER(’18F’,’XXXX’)+76+(3-

——————————

           523

 

The actual offset of row 87 is 440:

SQL> select to_number(’13c’,’XXXX’) + 76 + (3-1)*24 from dual;

 

TO_NUMBER(’13C’,’XXXX’)+76+(3-

——————————

           440

 

Let’s locate the row 86:                          

BBED> set datafile 4

BBED> set block 619

BBED> set offset 523

BBED> dump /x

 

       -0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

020b   3c 03 10 03 53 59 53 05 4e 54 41 42 24 ff 02 c1  <…SYS.NTAB$…

021b   59 02 c1 03 05 54 41 42 4c 45 07 78 6e 03 1e 0b  Y….TABLE.xn…

022b   08 33 07 78 6e 03 1e 0b 08 33 13 32 30 31 30 2d  .3.xn….3.2010-

023b   30 33 2d 33 30 3a 31 30 3a 30 37 3a 35 30 05 56  03-30:10:07:50.V

024b   41 4c 49 44 01 4e 01 4e 01 4e 02 c1 02 ff 02 c1  ALID.N.N.N……

025b   58 3c 03 10 03 53 59 53 0d 49 5f 53 55 42 43 4f  X<…SYS.I_SUBCO

026b   4c 54 59 50 45 31 ff 02 c1 58 02 c1 58 05 49 4e  LTYPE1…X..X.IN

027b   44 45 58 07 78 6e 03 1e 0b 08 33 07 78 6e 03 1e  DEX.xn….3.xn..

028b   0b 08 33 13 32 30 31 30 2d 30 33 2d 33 30 3a 31  ..3.2010-03-30:1

029b   30 3a 30 37 3a 35 30 05 56 41 4c 49 44 01 4e 01  0:07:50.VALID.N.

 

Let’s locate the row 87: 

BBED> set offset 440

BBED> dump /x

 

       -0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

01b8   3c 03 10 03 53 59 53 07 49 5f 4e 54 41 42 31 ff  <…SYS.I_NTAB1.

01c8   02 c1 5a 02 c1 5a 05 49 4e 44 45 58 07 78 6e 03  ..Z..Z.INDEX.xn.

01d8   1e 0b 08 33 07 78 6e 03 1e 0b 08 33 13 32 30 31  …3.xn….3.201

01e8   30 2d 30 33 2d 33 30 3a 31 30 3a 30 37 3a 35 30  0-03-30:10:07:50

01f8   05 56 41 4c 49 44 01 4e 01 4e 01 4e 02 c1 05 ff  .VALID.N.N.N….

0208   02 c1 59 3c 03 10 03 53 59 53 05 4e 54 41 42 24  ..Y<…SYS.NTAB$

0218   ff 02 c1 59 02 c1 03 05 54 41 42 4c 45 07 78 6e  …Y….TABLE.xn

0228   03 1e 0b 08 33 07 78 6e 03 1e 0b 08 33 13 32 30  ….3.xn….3.20

0238   31 30 2d 30 33 2d 33 30 3a 31 30 3a 30 37 3a 35  10-03-30:10:07:5

0248   30 05 56 41 4c 49 44 01 4e 01 4e 01 4e 02 c1 02  0.VALID.N.N.N…

 

We can see that the value of these two rows’ row header are both 0x3c, that means Oracle actually label a delete flag on these two rows’ row header.

 

 

Before we manually fix missing offset in datafile 4 block 9483’s row directory, even we change the value of ODU config parameter unload_deleted from ‘no’ to ‘yes’, we still can’t unload all the deleted rows from that block:

ODU> unload table scott.t1 datafile 4 block 9483 blocks 1

unload specific block mode.

 

Unloading table: T1,object ID: 74955 at 2014-04-05 11:33:51

Unloading segment,storage(Obj#=74955 DataObj#=74955 TS#=4 File#=4 Block#=618 Cluster=0)

 

Table T1 0 rows unloaded

At 2014-04-05 11:33:51

 

 

Now we start to use embedded BBED in ODU to manually fix missing offset in datafile 4 block 9483’s row directory.

For a normal table (not cluster), its’ data block must have these five structures before row directory structure kdbr:

SQL> select * from v$type_size where type in (‘KCBH’,’KTBBH’,’KTBIT’,’KDBH’,’KDBT’);

 

COMPONENT TYPE     DESCRIPTION                       TYPE_SIZE

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

KCB       KCBH     BLOCK COMMON HEADER                     20

KTB       KTBIT    TRANSACTION VARIABLE HEADER              24

KTB       KTBBH    TRANSACTION FIXED HEADER                 48

KDB       KDBH     DATA HEADER                                14

KDB       KDBT     TABLE DIRECTORY ENTRY                     4

 

For a normal table (not cluster) in ASSM, when you compute the actual offset start from KDBH of a data block, you must add extra 8 bytes.

So for a normal table (not cluster) in ASSM, the algorithm of row directory structure kdbr’s actual offset is as follows:

actual offset of kdbr = 20 + 48 + 24*(itc-1) + 14 + 4 + 8 = 94 + 24*(itc-1)

 

From the following query result, we know that datafile 4 block 9483 has 69 rows:

    MIN_ID      MAX_ID      LOCATION

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

    574626       574694       4_9483       /* 574694 – 574626 + 1 = 69 */

 

From the following dump result, we see that the value of datafile 4 block 9483’s itc is 2 and it has 69 rows in its’ row directory:   

ODU> dump datafile 4 block 9483

Block Header:

block type=0x06 (table/index/cluster segment data block)

block format=0xa2 (oracle 10+)

block rdba=0x0100250b (file#=4, block#=9483)

scn=0x0000.001ae559, seq=1, tail=0xe5590601

block checksum value=0xa625=42533, flag=4

Data Block Header Dump:

 Object id on Block? Y

 seg/obj: 0x124cb=74955  csc: 0x00.1ae559  itc: 2  flg: E  typ: 1 (data)

     brn: 0  bdba: 0x1002282 ver: 0x01

 

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0004.00f.00000398  0x00c0cf15.0104.16  C—    0  scn 0x0000.001ae557

0x02   0x0003.004.00000478  0x00c08e1b.01b5.3f  C-U-    0  scn 0x0000.001a7af8

Data Block Dump:

================

flag=0x0 ——–

ntab=1

nrow=69

frre=0

fsbo=0x9c

ffeo=0x204

avsp=0x1efc

tosp=0x1efc

0xe:pti[0] nrow=69  offs=0

0x12:pri[0]  sfll=1

0x14:pri[1]  sfll=2

0x16:pri[2]  sfll=3

0x18:pri[3]  sfll=4

0x1a:pri[4]  sfll=5

0x1c:pri[5]  sfll=6

0x1e:pri[6]  sfll=7

0x20:pri[7]  sfll=8

0x22:pri[8]  sfll=9

0x24:pri[9]  sfll=10

0x26:pri[10]  sfll=11

0x28:pri[11]  sfll=12

0x2a:pri[12]  sfll=13

0x2c:pri[13]  sfll=14

0x2e:pri[14]  sfll=15

0x30:pri[15]  sfll=16

0x32:pri[16]  sfll=17

0x34:pri[17]  sfll=18

0x36:pri[18]  sfll=19

0x38:pri[19]  sfll=20

0x3a:pri[20]  sfll=21

0x3c:pri[21]  sfll=22

0x3e:pri[22]  sfll=23

0x40:pri[23]  sfll=24

0x42:pri[24]  sfll=25

0x44:pri[25]  sfll=26

0x46:pri[26]  sfll=27

0x48:pri[27]  sfll=28

0x4a:pri[28]  sfll=29

0x4c:pri[29]  sfll=30

0x4e:pri[30]  sfll=31

0x50:pri[31]  sfll=32

0x52:pri[32]  sfll=33

0x54:pri[33]  sfll=34

0x56:pri[34]  sfll=35

0x58:pri[35]  sfll=36

0x5a:pri[36]  sfll=37

0x5c:pri[37]  sfll=38

0x5e:pri[38]  sfll=39

0x60:pri[39]  sfll=40

0x62:pri[40]  sfll=41

0x64:pri[41]  sfll=42

0x66:pri[42]  sfll=43

0x68:pri[43]  sfll=44

0x6a:pri[44]  sfll=45

0x6c:pri[45]  sfll=46

0x6e:pri[46]  sfll=47

0x70:pri[47]  sfll=48

0x72:pri[48]  sfll=49

0x74:pri[49]  sfll=50

0x76:pri[50]  sfll=51

0x78:pri[51]  sfll=52

0x7a:pri[52]  sfll=53

0x7c:pri[53]  sfll=54

0x7e:pri[54]  sfll=55

0x80:pri[55]  sfll=56

0x82:pri[56]  sfll=57

0x84:pri[57]  sfll=58

0x86:pri[58]  sfll=59

0x88:pri[59]  sfll=60

0x8a:pri[60]  sfll=61

0x8c:pri[61]  sfll=62

0x8e:pri[62]  sfll=63

0x90:pri[63]  sfll=64

0x92:pri[64]  sfll=65

0x94:pri[65]  sfll=66

0x96:pri[66]  sfll=67

0x98:pri[67]  sfll=68

0x9a:pri[68]  sfll=-1

Block Rows Dump:

 

So the actual kdbr offset of datafile 4 block 9483 is 94 + 24*(2-1) = 118

 

From the following desc result, we know the number of columns of table T1 is 16, which equals 0x10:

SQL> desc scott.t1

Name           Type          Nullable Default Comments

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

OWNER          VARCHAR2(30)  Y                        

OBJECT_NAME    VARCHAR2(128) Y                        

SUBOBJECT_NAME VARCHAR2(30)  Y                        

OBJECT_ID      NUMBER        Y                        

DATA_OBJECT_ID NUMBER        Y                        

OBJECT_TYPE    VARCHAR2(19)  Y                        

CREATED        DATE          Y                        

LAST_DDL_TIME  DATE          Y                        

TIMESTAMP      VARCHAR2(19)  Y                        

STATUS         VARCHAR2(7)   Y                        

TEMPORARY      VARCHAR2(1)   Y                        

GENERATED      VARCHAR2(1)   Y                        

SECONDARY      VARCHAR2(1)   Y                        

NAMESPACE      NUMBER        Y                        

EDITION_NAME   VARCHAR2(30)  Y                        

ID             NUMBER        Y

 

 

The row header structure of Oracle’s row in a table (not cluster) has three parts.

The first part is row flag, normally (not consider row chain and row migration), the deleted row’s row flag is 0x3c.

The second part is lock byte, the value of a row’s lock byte is between 0x00 to itc of the block where this row belongs to.

The third part is the number of columns, consider NULL value storage optimization of the last column in Oracle, the value of a row’s number of columns is between 0x00 to the number of columns of the table where this row belongs to.

 

According to the above background knowledge, let’s use ODU’s find command to find the actual offset of every deleted rows in datafile 4 block 9483.

The find rules are as follows:

You just need to define a search string, the length of the search string is 3 bytes.

The first byte is 0x3c

The second byte is between 0x00 to 0x02 (the itc of datafile 4 block 9483 is 2)

The third part is between 0x00 to 0x10 (because the number of columns of table T1 is 16, which equals 0x10)

 

Note: the value of third part in that situation is usually 0x10 if the row in datafile 4 block 9483 do not contain null value in last column.

 

So let’s do it:

ODU> blockedit

 

Entering block edit module.

 

BBED> set datafile 4

BBED> set block 9483

BBED> set offset 118

BBED> dump /x

 

       -0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

0076   01 00 02 00 03 00 04 00 05 00 06 00 07 00 08 00  …………….

0086   09 00 0a 00 0b 00 0c 00 0d 00 0e 00 0f 00 10 00  …………….

0096   11 00 12 00 13 00 14 00 15 00 16 00 17 00 18 00  …………….

00a6   19 00 1a 00 1b 00 1c 00 1d 00 1e 00 1f 00 20 00  ………….. .

00b6   21 00 22 00 23 00 24 00 25 00 26 00 27 00 28 00  !.”.#.$.%.&.’.(.

00c6   29 00 2a 00 2b 00 2c 00 2d 00 2e 00 2f 00 30 00  ).*.+.,.-…/.0.

00d6   31 00 32 00 33 00 34 00 35 00 36 00 37 00 38 00  1.2.3.4.5.6.7.8.

00e6   39 00 3a 00 3b 00 3c 00 3d 00 3e 00 3f 00 40 00  9.:.;.<.=.>.?.@.

00f6   41 00 42 00 43 00 44 00 ff ff 00 00 00 00 00 00  A.B.C.D………

0106   00 00 00 00 00 00 00 2c 02 10 06 53 59 53 4d 41  …….,…SYSMA

 

Start search:

BBED> find 3c

 

       -0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

00ec   3c 00 3d 00 3e 00 3f 00 40 00 41 00 42 00 43 00  <.=.>.?.@.A.B.C.

00fc   44 00 ff ff 00 00 00 00 00 00 00 00 00 00 00 00  D……………

010c   00 2c 02 10 06 53 59 53 4d 41 4e 17 4d 47 4d 54  .,…SYSMAN.MGMT

011c   5f 54 41 52 47 45 54 5f 43 52 45 44 45 4e 54 49  _TARGET_CREDENTI

012c   41 4c 53 ff 04 c3 07 49 61 ff 05 54 41 42 4c 45  ALS….Ia..TABLE

013c   07 78 6e 03 1e 0b 2b 18 07 78 6e 03 1e 0b 2d 21  .xn…+..xn…-!

014c   13 32 30 31 30 2d 30 33 2d 33 30 3a 31 30 2c 02  .2010-03-30:10,.

015c   10 06 53 59 53 4d 41 4e 12 53 59 53 5f 49 4f 54  ..SYSMAN.SYS_IOT

016c   5f 4f 56 45 52 5f 36 37 33 30 32 ff 04 c3 07 4a  _OVER_67302….J

017c   04 04 c3 07 4a 04 05 54 41 42 4c 45 07 78 6e 03  ….J..TABLE.xn.

 

From the above search result, 0x3c003d don’t match our search string, so ignore it.

 

Continue search:

BBED> f

 

       -0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

0268   3c 01 10 06 53 59 53 4d 41 4e 18 50 4b 5f 4d 47  <…SYSMAN.PK_MG

0278   4d 54 5f 45 4e 54 45 52 50 52 49 53 45 5f 43 52  MT_ENTERPRISE_CR

0288   45 44 53 ff 04 c3 07 4a 05 04 c3 07 4a 05 05 49  EDS….J….J..I

0298   4e 44 45 58 07 78 6e 03 1e 0b 2b 19 07 78 6e 03  NDEX.xn…+..xn.

02a8   1e 0b 2b 19 13 32 30 31 30 2d 30 33 2d 33 30 3a  ..+..2010-03-30:

02b8   31 30 3a 34 32 3a 32 34 05 56 41 4c 49 44 01 4e  10:42:24.VALID.N

02c8   01 4e 01 4e 02 c1 05 ff 04 c3 3a 2f 5f 3c 01 10  .N.N……:/_<..

02d8   06 53 59 53 4d 41 4e 1b 4d 47 4d 54 5f 45 4e 54  .SYSMAN.MGMT_ENT

02e8   45 52 50 52 49 53 45 5f 43 52 45 44 45 4e 54 49  ERPRISE_CREDENTI

02f8   41 4c 53 ff 04 c3 07 4a 03 ff 05 54 41 42 4c 45  ALS….J…TABLE

 

‘f’ stands for go on search from the current position, from top to down.

From the above search result, 0x3c0110 match our search string, so the sixty-ninth record’s actual offset is 0x0268.

 

Continue search:

BBED> f

 

       -0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

02d5   3c 01 10 06 53 59 53 4d 41 4e 1b 4d 47 4d 54 5f  <…SYSMAN.MGMT_

02e5   45 4e 54 45 52 50 52 49 53 45 5f 43 52 45 44 45  ENTERPRISE_CREDE

02f5   4e 54 49 41 4c 53 ff 04 c3 07 4a 03 ff 05 54 41  NTIALS….J…TA

0305   42 4c 45 07 78 6e 03 1e 0b 2b 19 07 78 6e 03 1e  BLE.xn…+..xn..

0315   0b 2d 21 13 32 30 31 30 2d 30 33 2d 33 30 3a 31  .-!.2010-03-30:1

0325   30 3a 34 32 3a 32 34 05 56 41 4c 49 44 01 4e 01  0:42:24.VALID.N.

0335   4e 01 4e 02 c1 02 ff 04 c3 3a 2f 5e 2c 00 0e 06  N.N……:/^,…

0345   53 59 53 4d 41 4e 18 50 4b 5f 4d 47 4d 54 5f 45  SYSMAN.PK_MGMT_E

0355   4e 54 45 52 50 52 49 53 45 5f 43 52 45 44 53 ff  NTERPRISE_CREDS.

0365   04 c3 07 4a 05 04 c3 07 4a 05 05 49 4e 44 45 58  …J….J..INDEX

 

From the above search result, 0x3c0110 match our search string, so the sixty-eighth record’s actual offset is 0x02d5.

 

Continue the above search process, we can get all the 69 records’ actual offset.

Here I only search the last ten records’ actual offset:

69: 0x0268

68: 0x02d5

67: 0x03a8

66: 0x040f

65: 0x0476

64: 0x04dc

63: 0x0541

62: 0x05aa

61: 0x0612

60: 0x0679

 

I just mentioned, Oracle stores the relative offset in row directory.

We must add some base to relative offset to get the actual offset.

The algorithm of base is as follows:

For ASSMbase = 76+itc-1)*24

For MSSMbase = 68+itc-1)*24

 

Now, here we get last ten records’ actual offset, so we have to substract base to get these ten records’ relative offset.

The calculation results of these ten records’ relative offset are as follows:

69 (0x45) : 0x0268 – (76+2-1)*24) = 0x0204

68 (0x44) : 0x02d5 – (76+2-1)*24) = 0x0271

67 (0x43) : 0x03a8 – (76+2-1)*24) = 0x0344

66 (0x42) : 0x040f – (76+2-1)*24) = 0x03ab

65 (0x41) : 0x0476 – (76+2-1)*24) = 0x0412

64 (0x40) : 0x04dc – (76+2-1)*24) = 0x0478

63 (0x3f): 0x0541 – (76+2-1)*24) = 0x04dd

62 (0x3e): 0x05aa – (76+2-1)*24) = 0x0546

61 (0x3d): 0x0612 – (76+2-1)*24) = 0x05ae

60 (0x3c): 0x0679 – (76+2-1)*24) = 0x0615

 

 

Base on the above calculation results, plus we already know the actual kdbr offset of datafile 4 block 9483 is 94 + 24*(2-1) = 118,

so we can modify the datafile 4 block 9483’s last ten records’ relative offset in row directory:

BBED> set offset 118

BBED> dump /x

 

       -0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

0076   01 00 02 00 03 00 04 00 05 00 06 00 07 00 08 00  …………….

0086   09 00 0a 00 0b 00 0c 00 0d 00 0e 00 0f 00 10 00  …………….

0096   11 00 12 00 13 00 14 00 15 00 16 00 17 00 18 00  …………….

00a6   19 00 1a 00 1b 00 1c 00 1d 00 1e 00 1f 00 20 00  ………….. .

00b6   21 00 22 00 23 00 24 00 25 00 26 00 27 00 28 00  !.”.#.$.%.&.’.(.

00c6   29 00 2a 00 2b 00 2c 00 2d 00 2e 00 2f 00 30 00  ).*.+.,.-…/.0.

00d6   31 00 32 00 33 00 34 00 35 00 36 00 37 00 38 00  1.2.3.4.5.6.7.8.

00e6   39 00 3a 00 3b 00 3c 00 3d 00 3e 00 3f 00 40 00  9.:.;.<.=.>.?.@.

00f6   41 00 42 00 43 00 44 00 ff ff 00 00 00 00 00 00  A.B.C.D………

0106   00 00 00 00 00 00 00 2c 02 10 06 53 59 53 4d 41  …….,…SYSMA

 

Let’s locate to the row 60 (0x3c):

BBED> set offset 0xec

BBED> dump /x

 

       -0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

00ec   3c 00 3d 00 3e 00 3f 00 40 00 41 00 42 00 43 00  <.=.>.?.@.A.B.C.

00fc   44 00 ff ff 00 00 00 00 00 00 00 00 00 00 00 00  D……………

010c   00 2c 02 10 06 53 59 53 4d 41 4e 17 4d 47 4d 54  .,…SYSMAN.MGMT

011c   5f 54 41 52 47 45 54 5f 43 52 45 44 45 4e 54 49  _TARGET_CREDENTI

012c   41 4c 53 ff 04 c3 07 49 61 ff 05 54 41 42 4c 45  ALS….Ia..TABLE

013c   07 78 6e 03 1e 0b 2b 18 07 78 6e 03 1e 0b 2d 21  .xn…+..xn…-!

014c   13 32 30 31 30 2d 30 33 2d 33 30 3a 31 30 2c 02  .2010-03-30:10,.

015c   10 06 53 59 53 4d 41 4e 12 53 59 53 5f 49 4f 54  ..SYSMAN.SYS_IOT

016c   5f 4f 56 45 52 5f 36 37 33 30 32 ff 04 c3 07 4a  _OVER_67302….J

017c   04 04 c3 07 4a 04 05 54 41 42 4c 45 07 78 6e 03  ….J..TABLE.xn.

 

Execute the following modify command:

BBED> modify 1506ae054605dd0478041204ab03440371020402

 

       -0–1–2–3–4–5–6–7–8–9–a–b–c–d–e–f-

00ec   15 06 ae 05 46 05 dd 04 78 04 12 04 ab 03 44 03  ….F…x…..D.

00fc   71 02 04 02 00 00 00 00 00 00 00 00 00 00 00 00  q……………

010c   00 2c 02 10 06 53 59 53 4d 41 4e 17 4d 47 4d 54  .,…SYSMAN.MGMT

011c   5f 54 41 52 47 45 54 5f 43 52 45 44 45 4e 54 49  _TARGET_CREDENTI

012c   41 4c 53 ff 04 c3 07 49 61 ff 05 54 41 42 4c 45  ALS….Ia..TABLE

013c   07 78 6e 03 1e 0b 2b 18 07 78 6e 03 1e 0b 2d 21  .xn…+..xn…-!

014c   13 32 30 31 30 2d 30 33 2d 33 30 3a 31 30 2c 02  .2010-03-30:10,.

015c   10 06 53 59 53 4d 41 4e 12 53 59 53 5f 49 4f 54  ..SYSMAN.SYS_IOT

016c   5f 4f 56 45 52 5f 36 37 33 30 32 ff 04 c3 07 4a  _OVER_67302….J

017c   04 04 c3 07 4a 04 05 54 41 42 4c 45 07 78 6e 03  ….J..TABLE.xn.

 

Execute the following command to update checksum value:

BBED> sum apply

 

Warning: apply the modified data will overwrite original data.

Would you like to continue? (y/n)

y

 

Old checksum value: 0xa625

New checksum value: 0x5b4e

Writing block has completed

 

We finished all the modification towards these ten records’ relative offset.

Now we use ODU to try to unload these ten records:

ODU> unload table scott.t1 datafile 4 block 9483 blocks 1

unload specific block mode.

 

Unloading table: T1,object ID: 74955 at 2014-04-05 17:22:53

Unloading segment,storage(Obj#=74955 DataObj#=74955 TS#=4 File#=4 Block#=618 Cluster=0)

 

Table T1 10 rows unloaded

At 2014-04-05 17:22:53

 

From the above unload result, you can see that ODU has successfully unloaded these ten records.

 

The following are the unloaded result of these ten records:

MDSYS|SDO_NETWORK_UD_UPD_TRIG||66043||TRIGGER|2010-03-30 10:39:19|2010-03-30 10:39:19|2010-03-30:10:39:19|VALID|N|N|N|3||574685

SYSMAN|SYS_IOT_OVER_67296||67297|67297|TABLE|2010-03-30 10:42:24|2010-03-30 10:42:24|2010-03-30:10:42:24|VALID|N|Y|N|1||574686

SYSMAN|MGMT_TARGET_CREDENTIALS||67296||TABLE|2010-03-30 10:42:23|2010-03-30 10:44:32|2010-03-30:10:42:23|VALID|N|N|N|1||574687

SYSMAN|PK_MGMT_TARGET_CREDS||67298|67298|INDEX|2010-03-30 10:42:24|2010-03-30 10:42:24|2010-03-30:10:42:24|VALID|N|N|N|4||574688

SYSMAN|SYS_IOT_OVER_67299||67300|67300|TABLE|2010-03-30 10:42:24|2010-03-30 10:42:24|2010-03-30:10:42:24|VALID|N|Y|N|1||574689

SYSMAN|MGMT_HOST_CREDENTIALS||67299||TABLE|2010-03-30 10:42:24|2010-03-30 10:44:32|2010-03-30:10:42:24|VALID|N|N|N|1||574690

SYSMAN|PK_MGMT_HOST_CREDS||67301|67301|INDEX|2010-03-30 10:42:24|2010-03-30 10:42:24|2010-03-30:10:42:24|VALID|N|N|N|4||574691

SYSMAN|SYS_IOT_OVER_67302||67303|67303|TABLE|2010-03-30 10:42:24|2010-03-30 10:42:24|2010-03-30:10:42:24|VALID|N|Y|N|1||574692

SYSMAN|MGMT_ENTERPRISE_CREDENTIALS||67302||TABLE|2010-03-30 10:42:24|2010-03-30 10:44:32|2010-03-30:10:42:24|VALID|N|N|N|1||574693

SYSMAN|PK_MGMT_ENTERPRISE_CREDS||67304|67304|INDEX|2010-03-30 10:42:24|2010-03-30 10:42:24|2010-03-30:10:42:24|VALID|N|N|N|4||574694

 

Note the last column of the above unloaded records, the last column is the column ID of table T1, you can see that the value of these ten records’s column ID are continuous and its’ range between 574685 to 574694.

That exactly match the query result we select before:

    MIN_ID      MAX_ID      LOCATION

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

    574626      574694       4_9483       /* 574694 – 574626 + 1 = 69 */

 

That means ODU 4.3.2 has recovered the above 10 deleted rows successfully and correctly!