Sundaramurthy Blog

June 9, 2010

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

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: