Delete SCOTT's record in SQL*Plus, look at the record's information
within the block, alter that information, and come back to SQL*Plus
to confirm the change or resurrection, as it may be. First, what
marks a row as deleted or not? Or chained, for that matter? Each row
contains header information in a binary or bitmask format. The fifth
position in the row flag contains the flag indicating deleted or
not. There are two ways this data can be viewed. One of them, as
seen within bbed, shows the values as 0x2c or 0x3c for deleted. The
other way shows a D, or alter system dump datafile X block Y, and
then one can view the trace file in the udump directory. Using the
same file and block number, after deleting SCOTT, the trace file
shows that the row is marked as deleted.
tab 0, row 7, @0x1e4c
tl: 2 fb: --HDFL-- lb: 0x2
An undeleted row shows "- - H - F L - -" plus the dumped data. What
does bbed show?"
BBED> p *kdbr[7]
rowdata[235]
------------
ub1 rowdata[235]
@7856
0x3c
On the last line, at the far right, note the 0x3c value. Undeleting
the record is simply a matter of setting 0x3c back to 0x2c. So far one
has searched for data and will continue to do so, and when found, the
offset needs to backed up a bit more to move the pointer into the row
flag bitmask area. Or, having some knowledge of the data including
having dumped the block into a trace file, when 0x3c and its offset
value is found, that is where one needs to perform the modification.
From above, the offset of interest is 7856 to target the 3c in 0x3c.
To change the flag or bit to remove the deletion, modify and update
the checksum. Since the modification is being made to hexadecimal
data, the "x" flag is used:
BBED> modify /x 2c offset 7856
Warning: contents of previous BIFILE will be
lost. Proceed? (Y/N) Y
File:
/opt/app/oracle/oradata/ORCL2/users01.dbf (4)
Block:
32
Offsets: 7856 to 8191
Dba:0x01000020
------------------------------------------------------------------------
2c020803
c24e5905 53434f54 54074d41 4e414745 5203c24c 430777bb 04130101
0102c21f ff02c115 2c000803
c24e5305 434c4152 4b074d41 4e414745 5203c24f
280777b5 06090101
0103c219 33ff02c1 0b2c0008 03c24d63 05424c41 4b45074d
414e4147
455203c2 4f280777 b5050101 010103c2 1d33ff02 c11f2c00 0803c24d
37064d41 5254494e 0853414c
45534d41 4e03c24d 630777b5 091c0101 0103c20d
3302c20f 02c11f2c 000803c2
4c43054a 4f4e4553 074d414e 41474552 03c24f28
0777b504 02010101 03c21e4c
ff02c115 2c000803 c24c1604 57415244 0853414c
45534d41 4e03c24d 630777b5
02160101 0103c20d 3302c206 02c11f2c 000803c2
4b640541 4c4c454e 0853414c
45534d41 4e03c24d 630777b5 02140101 0102c211
02c20402 c11f2c00
0803c24a 4605534d 49544805 434c4552 4b03c250 030777b4
0c110101
0102c209 ff02c115 0206627f
<32
bytes per line>
Verify the change, and then apply it.
BBED> p *kdbr[7]
rowdata[235]
------------
ub1 rowdata[235]
@7856
0x2c
BBED> sum dba 4,32 apply
Check value for File 4, Block 32:
current = 0x0e62, required = 0x0e62
Now the big test - is the row undeleted in the table? It appears to
be.
SQL> select empno, ename, job from emp;
EMPNO ENAME
JOB
---------- ---------- ---------
7369 SMITH
CLERK
7499 ALLEN
SALESMAN
7521 WARD
SALESMAN
7566 JONES
MANAGER
7654 MARTIN
SALESMAN
7698 BLAKE
MANAGER
7782 CLARK
MANAGER
7788 SCOTT
MANAGER
7839 KING
CLERK
7844 TURNER
SALESMAN
7876 ADAMS
CLERK
7900 JAMES
CLERK
7902 FORD
ANALYST
7934 MILLER
CLERK
14 rows selected.
But another query seems to say otherwise.
SQL> select empno, ename, job
2
from emp where empno = 7788;
no rows selected
and include the URL for the page.
