drop table 后恢复数据的例子

d.c.b.a · 发表于 2005-1-18 08:25

C:\AnySQL>sqlplus /nolog

SQL*Plus: Release 8.1.7.0.0 - Production on Tue Jan 18 08:15:51 2005

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

SQL> conn test/test
Connected.
SQL> select * from test0;

USERNAME                         USER_ID CREATED
------------------------------ ---------- ---------
SYS                                  0 24-DEC-04
SYSTEM                               5 24-DEC-04
OUTLN                               11 24-DEC-04
DBSNMP                               16 24-DEC-04
TEST1                               21 17-JAN-05
TEST                                  20 17-JAN-05

6 rows selected.

SQL> SELECT DATA_OBJECT_ID FROM USER_OBJECTS WHERE OBJECT_NAME='TEST0';

DATA_OBJECT_ID
--------------
      3988

SQL> DELETE TEST0;

6 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> CONN SYSTEM/MANAGER
Connected.
SQL> ALTER SYSTEM CHECKPOINT;

System altered.

SQL> EXIT
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
With the Partitioning option
JServer Release 8.1.7.4.1 - Production

C:\AnySQL>DUL CONFIG=DUL_SAMPLE.CFG
'DUL' is not recognized as an internal or external command,
operable program or batch file.

C:\AnySQL>MYDUL CONFIG=DUL_SAMPLE.CFG
Tue Jan 18 08:17:33 CST 2005
Tue Jan 18 08:17:33 CST 2005

C:\AnySQL>MYDUL CONFIG=DUL_SAMPLE.CFG DELETE=YES
Tue Jan 18 08:17:57 CST 2005
SYS,0,2004-12-24 11:16:58
SYSTEM,5,2004-12-24 11:16:58
OUTLN,11,2004-12-24 11:17:01
DBSNMP,16,2004-12-24 11:19:10
TEST1,21,2005-01-17 20:13:48
TEST,20,2005-01-17 20:13:18
Tue Jan 18 08:17:57 CST 2005

C:\AnySQL>

angelsliu · 发表于 2005-1-18 08:52

try to use dbms_flashback

d.c.b.a · 发表于 2005-1-18 09:06

My exampe is under oracle 8i

biti_rainy · 发表于 2005-1-18 09:56

最初由 d.c.b.a 发布
[B]mydul是自已写的程序，
delete时，只是将记录头的标志位改成删除标志，不会将这个记录的空间马上放出来，而是用到时再去清除的，在block中的dump中有一个ffre(first free row entry)，在delete后，ffre就不是-1了，而是被删除的记录的在block中的记录号了，当然这样读出来的数据不会完全都对的。 [/B]

关于 block空间的存储问题，两年前我也醉心研究过，现在不妨再做个测试来看看。当然那时我用的817，现在是9203。

我不清楚你的测试是不是因为删除的记录的block还没有被写入数据文件的缘故还是其他原因？  如果因为意外delete，还是使用logmnr
来的最方便，因为delete后的空间很可能是需要被重用的，退一步讲即使真地没有清除，而你也很难识别哪些delete的需要读出来哪些不需要读出来。

从另外一个角度来说，oracle为什么在 delete 的时候产生的日志和消耗的回滚段最多？

alibaba@OCN> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
PL/SQL Release 9.2.0.3.0 - Production
CORE 9.2.0.3.0    Production
TNS for Linux: Version 9.2.0.3.0 - Production
NLSRTL Version 9.2.0.3.0 - Production

alibaba@OCN>create  table t as select * from  dba_objects where  rownum < 101;

Table created.

alibaba@OCN>select  extent_id,file_id,block_id,blocks from  dba_extents where segment_name ='T' and  owner= 'ALIBABA';

EXTENT_ID FILE_ID BLOCK_ID    BLOCKS
---------- ---------- ---------- ----------
      0       3    35313       8

alibaba@OCN>update t set  object_id = rownum;

100 rows updated.

alibaba@OCN>commit;

alter  system  dump  datafile  3  block  min  35313 block  max 35316;

buffer tsn: 3 rdba: 0x00c089f4 (3/35316)
scn: 0x0000.51b968f1 seq: 0x01 flg: 0x02 tail: 0x68f10601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump:  0x00c089f4
Object id on Block? Y
seg/obj: 0x51d4  csc: 0x00.51b966d9  itc: 3  flg: E  typ: 1 - DATA
   brn: 0  bdba: 0xc089f1 ver: 0x01
   inc: 0  exflg: 0

Itl          Xid                Uba       Flag  Lck       Scn/Fsc
0x01 0xffff.000.00000000  0x00000000.0000.00  C--- 0  scn 0x0000.51b966d9
0x02 0x0002.01b.00035559  0x0080001c.2840.2a  --U- 83  fsc 0x0051.51b968f1
0x03 0x0000.000.00000000  0x00000000.0000.00  ---- 0  fsc 0x0000.00000000

data_block_dump,data header at 0xaa5287c
===============
tsiz: 0x1f80
hsiz: 0xb8
pbl: 0x0aa5287c
bdba: 0x00c089f4
   76543210
flag=--------
ntab=1
nrow=83
frre=-1
fsbo=0xb8
fseo=0x42c
avsp=0x374
tosp=0x3c5
0xe

ti[0] nrow=83 offs=0
0x12

ri[0] offs=0x1f34
0x14

ri[1] offs=0x1ee9
0x16

ri[2] offs=0x1e98
0x18

ri[3] offs=0x1e48
0x1a

ri[4] offs=0x1dfd
0x1c

ri[5] offs=0x1da1
0x1e

ri[6] offs=0x1d49
0x20

ri[7] offs=0x1cf8
0x22

ri[8]    offs=0x1ca1
0x24:pri[9]    offs=0x1c4b
0x26:pri[10] offs=0x1bf7
0x28:pri[11] offs=0x1ba8
0x2a:pri[12] offs=0x1b56
0x2c:pri[13] offs=0x1b02
0x2e:pri[14] offs=0x1aab
0x30:pri[15] offs=0x1a5e
0x32:pri[16] offs=0x1a06
0x34:pri[17] offs=0x19a7
0x36:pri[18] offs=0x1949
0x38:pri[19] offs=0x18ec
0x3a:pri[20] offs=0x189f
0x3c:pri[21] offs=0x1851
0x3e:pri[22] offs=0x17f3
0x40:pri[23] offs=0x17a3
0x42:pri[24] offs=0x1751
0x44:pri[25] offs=0x1702
0x46:pri[26] offs=0x16ae
0x48:pri[27] offs=0x165a
0x4a:pri[28] offs=0x1609
0x4c:pri[29] offs=0x15b7
0x4e:pri[30] offs=0x1561
0x50:pri[31] offs=0x1514
0x52:pri[32] offs=0x14c6
0x54:pri[33] offs=0x1472
0x56:pri[34] offs=0x1420
0x58:pri[35] offs=0x13d0
0x5a:pri[36] offs=0x137c
0x5c:pri[37] offs=0x132a
0x5e:pri[38] offs=0x12d5
0x60:pri[39] offs=0x1283
0x62:pri[40] offs=0x1233
0x64:pri[41] offs=0x11e0
0x66:pri[42] offs=0x118f
0x68:pri[43] offs=0x1143
0x6a:pri[44] offs=0x10ea
0x6c:pri[45] offs=0x108b
0x6e:pri[46] offs=0x102e
0x70:pri[47] offs=0xfd6
0x72:pri[48] offs=0xf81
0x74:pri[49] offs=0xf34
0x76:pri[50] offs=0xee4
0x78:pri[51] offs=0xe8c
0x7a:pri[52] offs=0xe33
0x7c:pri[53] offs=0xde2
0x7e:pri[54] offs=0xd8d
0x80:pri[55] offs=0xd39
0x82:pri[56] offs=0xce2
0x84:pri[57] offs=0xc8b
0x86:pri[58] offs=0xc35
0x88:pri[59] offs=0xbe6
0x8a:pri[60] offs=0xb9c
0x8c:pri[61] offs=0xb48
0x8e:pri[62] offs=0xaf1
0x90:pri[63] offs=0xa9e
0x92:pri[64] offs=0xa4e
0x94:pri[65] offs=0x9f7
0x96:pri[66] offs=0x9a4
0x98:pri[67] offs=0x950
0x9a:pri[68] offs=0x904
0x9c:pri[69] offs=0x8ae
0x9e:pri[70] offs=0x85a
0xa0:pri[71] offs=0x7fe
0xa2:pri[72] offs=0x7ad
0xa4:pri[73] offs=0x75d
0xa6:pri[74] offs=0x70d
0xa8:pri[75] offs=0x6b4
0xaa:pri[76] offs=0x661
0xac:pri[77] offs=0x614
0xae:pri[78] offs=0x5c1
0xb0:pri[79] offs=0x56f
0xb2:pri[80] offs=0x51e
0xb4:pri[81] offs=0x47c
0xb6:pri[82] offs=0x42c
block_row_dump:
tab 0, row 0, @0x1f34
tl: 76 fb: --H-FL-- lb: 0x2  cc: 13
col  0: [ 3]  53 59 53
col  1: [ 7]  41 43 43 45 53 53 24
col  2: *NULL*
col  3: [ 2]  c1 02
col  4: [ 2]  c1 62
col  5: [ 5]  54 41 42 4c 45
col  6: [ 7]  78 67 06 07 01 0d 05
col  7: [ 7]  78 67 06 07 01 0d 05
col  8: [19]  32 30 30 33 2d 30 36 2d 30 37 3a 30 30 3a 31 32 3a 30 34
col  9: [ 5]  56 41 4c 49 44
col 10: [ 1]  4e
col 11: [ 1]  4e
col 12: [ 1]  4e
tab 0, row 1, @0x1ee9
tl: 75 fb: --H-FL-- lb: 0x2  cc: 13
col  0: [ 3]  53 59 53
col  1: [ 9]  41 47 47 58 4d 4c 49 4d 50
col  2: *NULL*
col  3: [ 2]  c1 03
col  4: *NULL*
col  5: [ 4]  54 59 50 45
col  6: [ 7]  78 67 06 07 01 11 12
col  7: [ 7]  78 67 06 07 01 11 13
col  8: [19]  32 30 30 33 2d 30 36 2d 30 37 3a 30 30 3a 31 36 3a 31 37
col  9: [ 5]  56 41 4c 49 44
col 10: [ 1]  4e
col 11: [ 1]  4e
col 12: [ 1]  4e
tab 0, row 2, @0x1e98
tl: 81 fb: --H-FL-- lb: 0x2  cc: 13
col  0: [ 3]  53 59 53
col  1: [15]  41 47 47 58 4d 4c 49 4e 50 55 54 54 59 50 45
col  2: *NULL*
col  3: [ 2]  c1 04
col  4: *NULL*
col  5: [ 4]  54 59 50 45
col  6: [ 7]  78 67 06 07 01 11 13
col  7: [ 7]  78 67 06 07 01 11 13
col  8: [19]  32 30 30 33 2d 30 36 2d 30 37 3a 30 30 3a 31 36 3a 31 38
col  9: [ 5]  56 41 4c 49 44
col 10: [ 1]  4e
col 11: [ 1]  4e
col 12: [ 1]  4e
tab 0, row 3, @0x1e48
tl: 80 fb: --H-FL-- lb: 0x2  cc: 13
col  0: [ 3]  53 59 53
col  1: [14]  41 4c 4c 5f 41 4c 4c 5f 54 41 42 4c 45 53
col  2: *NULL*
col  3: [ 2]  c1 05
col  4: *NULL*
col  5: [ 4]  56 49 45 57
col  6: [ 7]  78 67 06 07 01 0e 2d
col  7: [ 7]  78 67 06 07 01 0e 2d
col  8: [19]  32 30 30 33 2d 30 36 2d 30 37 3a 30 30 3a 31 33 3a 34 34
col  9: [ 5]  56 41 4c 49 44
col 10: [ 1]  4e

alibaba@OCN>delete t where  object_id < 51;

50 rows deleted.

alibaba@OCN>commit;

Commit complete.

alibaba@OCN>conn  alibaba/ca
Connected.
alibaba@OCN> alter  system  dump  datafile  3  block  min  35313 block  max 35316;

System altered.

buffer tsn: 3 rdba: 0x00c089f4 (3/35316)
scn: 0x0000.51b96995 seq: 0x01 flg: 0x02 tail: 0x69950601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump:  0x00c089f4
Object id on Block? Y
seg/obj: 0x51d4  csc: 0x00.51b96994  itc: 3  flg: E  typ: 1 - DATA
   brn: 0  bdba: 0xc089f1 ver: 0x01
   inc: 0  exflg: 0

Itl          Xid                Uba       Flag  Lck       Scn/Fsc
0x01 0xffff.000.00000000  0x00000000.0000.00  C--- 0  scn 0x0000.51b966d9
0x02 0x0002.01b.00035559  0x0080001c.2840.2a  C--- 0  scn 0x0000.51b968f1
0x03 0x0004.029.00035525  0x00801c5b.25ae.03  --U- 50  fsc 0x0fe8.51b96995

data_block_dump,data header at 0xaa5287c
===============
tsiz: 0x1f80
hsiz: 0xb8
pbl: 0x0aa5287c
bdba: 0x00c089f4
   76543210
flag=--------
ntab=1
nrow=83
frre=-1
fsbo=0xb8
fseo=0x42c
avsp=0x3c5
tosp=0x1411
0xe:pti[0]    nrow=83 offs=0
0x12:pri[0]    offs=0x1f34
0x14:pri[1]    offs=0x1ee9
0x16:pri[2]    offs=0x1e98
0x18:pri[3]    offs=0x1e48
0x1a:pri[4]    offs=0x1dfd
0x1c:pri[5]    offs=0x1da1
0x1e:pri[6]    offs=0x1d49
0x20:pri[7]    offs=0x1cf8
0x22:pri[8]    offs=0x1ca1
0x24:pri[9]    offs=0x1c4b
0x26:pri[10] offs=0x1bf7
0x28:pri[11] offs=0x1ba8
0x2a:pri[12] offs=0x1b56
0x2c:pri[13] offs=0x1b02
0x2e:pri[14] offs=0x1aab
0x30:pri[15] offs=0x1a5e
0x32:pri[16] offs=0x1a06
0x34:pri[17] offs=0x19a7
0x36:pri[18] offs=0x1949
0x38:pri[19] offs=0x18ec
0x3a:pri[20] offs=0x189f
0x3c:pri[21] offs=0x1851
0x3e:pri[22] offs=0x17f3
0x40:pri[23] offs=0x17a3
0x42:pri[24] offs=0x1751
0x44:pri[25] offs=0x1702
0x46:pri[26] offs=0x16ae
0x48:pri[27] offs=0x165a
0x4a:pri[28] offs=0x1609
0x4c:pri[29] offs=0x15b7
0x4e:pri[30] offs=0x1561
0x50:pri[31] offs=0x1514
0x52:pri[32] offs=0x14c6
0x54:pri[33] offs=0x1472
0x56:pri[34] offs=0x1420
0x58:pri[35] offs=0x13d0
0x5a:pri[36] offs=0x137c
0x5c:pri[37] offs=0x132a
0x5e:pri[38] offs=0x12d5
0x60:pri[39] offs=0x1283
0x62:pri[40] offs=0x1233
0x64:pri[41] offs=0x11e0
0x66:pri[42] offs=0x118f
0x68:pri[43] offs=0x1143
0x6a:pri[44] offs=0x10ea
0x6c:pri[45] offs=0x108b
0x6e:pri[46] offs=0x102e
0x70:pri[47] offs=0xfd6
0x72:pri[48] offs=0xf81
0x74:pri[49] offs=0xf34
0x76:pri[50] offs=0xee4
0x78:pri[51] offs=0xe8c
0x7a:pri[52] offs=0xe33
0x7c:pri[53] offs=0xde2
0x7e:pri[54] offs=0xd8d
0x80:pri[55] offs=0xd39
0x82:pri[56] offs=0xce2
0x84:pri[57] offs=0xc8b
0x86:pri[58] offs=0xc35
0x88:pri[59] offs=0xbe6
0x8a:pri[60] offs=0xb9c
0x8c:pri[61] offs=0xb48
0x8e:pri[62] offs=0xaf1
0x90:pri[63] offs=0xa9e
0x92:pri[64] offs=0xa4e
0x94:pri[65] offs=0x9f7
0x96:pri[66] offs=0x9a4
0x98:pri[67] offs=0x950
0x9a:pri[68] offs=0x904
0x9c:pri[69] offs=0x8ae
0x9e:pri[70] offs=0x85a
0xa0:pri[71] offs=0x7fe
0xa2:pri[72] offs=0x7ad
0xa4:pri[73] offs=0x75d
0xa6:pri[74] offs=0x70d
0xa8:pri[75] offs=0x6b4
0xaa:pri[76] offs=0x661
0xac:pri[77] offs=0x614
0xae:pri[78] offs=0x5c1
0xb0:pri[79] offs=0x56f
0xb2:pri[80] offs=0x51e
0xb4:pri[81] offs=0x47c
0xb6:pri[82] offs=0x42c
block_row_dump:
tab 0, row 0, @0x1f34
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 1, @0x1ee9
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 2, @0x1e98
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 3, @0x1e48
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 4, @0x1dfd
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 5, @0x1da1
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 6, @0x1d49
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 7, @0x1cf8
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 8, @0x1ca1
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 9, @0x1c4b
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 10, @0x1bf7
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 11, @0x1ba8
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 12, @0x1b56
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 13, @0x1b02
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 14, @0x1aab
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 15, @0x1a5e
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 16, @0x1a06
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 17, @0x19a7
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 18, @0x1949
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 19, @0x18ec
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 20, @0x189f
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 21, @0x1851
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 22, @0x17f3
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 23, @0x17a3
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 24, @0x1751
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 25, @0x1702
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 26, @0x16ae
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 27, @0x165a
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 28, @0x1609
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 29, @0x15b7
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 30, @0x1561
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 31, @0x1514
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 32, @0x14c6
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 33, @0x1472
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 34, @0x1420
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 35, @0x13d0
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 36, @0x137c
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 37, @0x132a
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 38, @0x12d5
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 39, @0x1283
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 40, @0x1233
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 41, @0x11e0
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 42, @0x118f
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 43, @0x1143
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 44, @0x10ea
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 45, @0x108b
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 46, @0x102e
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 47, @0xfd6
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 48, @0xf81
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 49, @0xf34
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 50, @0xee4
tl: 80 fb: --H-FL-- lb: 0x0  cc: 13
col  0: [ 3]  53 59 53
col  1: [14]  41 4c 4c 5f 49 4e 44 45 58 54 59 50 45 53
col  2: *NULL*
col  3: [ 2]  c1 34
col  4: *NULL*
col  5: [ 4]  56 49 45 57
col  6: [ 7]  78 67 06 07 01 0e 35
col  7: [ 7]  78 67 06 07 01 0e 35
col  8: [19]  32 30 30 33 2d 30 36 2d 30 37 3a 30 30 3a 31 33 3a 35 32
col  9: [ 5]  56 41 4c 49 44
col 10: [ 1]  4e
col 11: [ 1]  4e
col 12: [ 1]  4e
tab 0, row 51, @0xe8c
tl: 88 fb: --H-FL-- lb: 0x0  cc: 13
col  0: [ 3]  53 59 53
col  1: [22]
41 4c 4c 5f 49 4e 44 45 58 54 59 50 45 5f 43 4f 4d 4d 45 4e 54 53
col  2: *NULL*

piner · 发表于 2005-1-18 11:01

delete的实际上用途真的不大，不如logmnr。

想想看，真实环境中，如果存在大量的并发写操作，delete后的空间很容易被重新利用
但是，truncate与drop的意义会大一些。

d.c.b.a · 发表于 2005-1-18 17:45

biti_rainy : 你应当checkpoint一下再dump block来看的.

lojian · 发表于 2005-1-18 17:52

怎么搞得？？？

d.c.b.a · 发表于 2005-1-18 17:59

mydul也可以读取10g的数据,刚刚增加了对binary_float和binary_double的支持

别外dul这个工具也应当可以支持10g的.

biti_rainy · 发表于 2005-1-18 18:03

最初由 d.c.b.a 发布
[B]biti_rainy : 你应当checkpoint一下再dump block来看的. [/B]

你能否参照我下面这个步骤做一个dump来看看？
你要告诉我 delete 的时候没有真正地清除数据而仅仅是做了一个删除标志，我从来就没看见过这个现象。

SQL>  create table t as select * from dba_objects where rownum < 11;

Table created.

SQL> select extent_id,file_id,block_id,blocks from dba_extents where segment_name ='T' and owner= 'ALIBABA';

EXTENT_ID FILE_ID BLOCK_ID    BLOCKS
---------- ---------- ---------- ----------
      0       3    48057       8

SQL> update t set object_id = rownum;

10 rows updated.

SQL> commit;

Commit complete.

SQL> delete t where  rownum < 6;

5 rows deleted.

SQL> commit;

Commit complete.

SQL> alter  system  checkpoint;

System altered.

SQL> alter  system  dump  datafile 3  block  min 48057 block  max 48060;

System altered.

SQL>

buffer tsn: 3 rdba: 0x00c0bbbc (3/48060)
scn: 0x0000.361bd9e4 seq: 0x01 flg: 0x06 tail: 0xd9e40601
frmt: 0x02 chkval: 0x57a1 type: 0x06=trans data
Block header dump:  0x00c0bbbc
Object id on Block? Y
seg/obj: 0x9ec8  csc: 0x00.361bd9e2  itc: 3  flg: E  typ: 1 - DATA
   brn: 0  bdba: 0xc0bbb9 ver: 0x01
   inc: 0  exflg: 0

Itl          Xid                Uba       Flag  Lck       Scn/Fsc
0x01 0xffff.000.00000000  0x00000000.0000.00  C--- 0  scn 0x0000.361bd9be
0x02 0x0008.011.000270fb  0x04800009.4ea1.11  C--- 0  scn 0x0000.361bd9d7
0x03 0x0006.00a.000260e8  0x0080005f.4a1f.1f  --U- 5  fsc 0x01dc.361bd9e4

data_block_dump,data header at 0xaa8647c
===============
tsiz: 0x1f80
hsiz: 0x26
pbl: 0x0aa8647c
bdba: 0x00c0bbbc
   76543210
flag=--------
ntab=1
nrow=10
frre=-1
fsbo=0x26
fseo=0x17b2
avsp=0x1b7c
tosp=0x1d62
0xe

ti[0] nrow=10 offs=0
0x12

ri[0] offs=0x1b32
0x14

ri[1] offs=0x1acc
0x16

ri[2] offs=0x1a6f
0x18

ri[3] offs=0x1a09
0x1a

ri[4] offs=0x19aa
0x1c

ri[5] offs=0x1944
0x1e

ri[6] offs=0x18de
0x20

ri[7] offs=0x187c
0x22

ri[8]    offs=0x1816
0x24:pri[9]    offs=0x17b2
block_row_dump:
tab 0, row 0, @0x1b32
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 1, @0x1acc
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 2, @0x1a6f
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 3, @0x1a09
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 4, @0x19aa
tl: 2 fb: --HDFL-- lb: 0x3
tab 0, row 5, @0x1944
tl: 102 fb: --H-FL-- lb: 0x0  cc: 13
col  0: [ 3]  53 59 53
col  1: [30]
2f 31 30 35 30 31 39 30 32 5f 42 61 73 69 63 46 69 6c 65 43 68 6f 6f 73 65
72 55 49 4e 65
col  2: *NULL*
col  3: [ 2]  c1 07
col  4: *NULL*
col  5: [10]  4a 41 56 41 20 43 4c 41 53 53
col  6: [ 7]  78 66 07 03 06 35 23
col  7: [ 7]  78 66 07 03 06 35 23
col  8: [19]  32 30 30 32 2d 30 37 2d 30 33 3a 30 35 3a 35 32 3a 33 34
col  9: [ 5]  56 41 4c 49 44
col 10: [ 1]  4e
col 11: [ 1]  4e
col 12: [ 1]  4e
tab 0, row 6, @0x18de
tl: 102 fb: --H-FL-- lb: 0x0  cc: 13
col  0: [ 3]  53 59 53
col  1: [30]
2f 31 30 35 30 37 32 65 37 5f 48 74 74 70 53 65 73 73 69 6f 6e 42 69 6e 64
69 6e 67 45 76
col  2: *NULL*
col  3: [ 2]  c1 08
col  4: *NULL*
col  5: [10]  4a 41 56 41 20 43 4c 41 53 53
col  6: [ 7]  78 66 07 03 06 39 24
col  7: [ 7]  78 66 07 03 06 3b 0e
col  8: [19]  32 30 30 32 2d 30 37 2d 30 33 3a 30 35 3a 35 36 3a 33 35
col  9: [ 5]  56 41 4c 49 44
col 10: [ 1]  4e
col 11: [ 1]  4e
col 12: [ 1]  4e
tab 0, row 7, @0x187c
tl: 98 fb: --H-FL-- lb: 0x0  cc: 13
col  0: [ 3]  53 59 53
col  1: [26]
2f 31 30 36 62 61 30 61 35 5f 41 72 72 61 79 45 6e 75 6d 65 72 61 74 69 6f
6e
col  2: *NULL*
col  3: [ 2]  c1 09
col  4: *NULL*
col  5: [10]  4a 41 56 41 20 43 4c 41 53 53
col  6: [ 7]  78 66 07 03 06 39 25
col  7: [ 7]  78 66 07 03 06 3b 19
col  8: [19]  32 30 30 32 2d 30 37 2d 30 33 3a 30 35 3a 35 36 3a 33 36
col  9: [ 5]  56 41 4c 49 44
col 10: [ 1]  4e
col 11: [ 1]  4e
col 12: [ 1]  4e
tab 0, row 8, @0x1816
tl: 102 fb: --H-FL-- lb: 0x0  cc: 13
col  0: [ 3]  53 59 53
col  1: [30]
2f 31 30 36 66 61 61 62 63 5f 42 61 73 69 63 54 72 65 65 55 49 4b 65 79 48
61 6e 64 6c 65
col  2: *NULL*
col  3: [ 2]  c1 0a
col  4: *NULL*
col  5: [10]  4a 41 56 41 20 43 4c 41 53 53
col  6: [ 7]  78 66 07 03 06 35 1e
col  7: [ 7]  78 66 07 03 06 35 1e
col  8: [19]  32 30 30 32 2d 30 37 2d 30 33 3a 30 35 3a 35 32 3a 32 39
col  9: [ 5]  56 41 4c 49 44
col 10: [ 1]  4e
col 11: [ 1]  4e
col 12: [ 1]  4e
tab 0, row 9, @0x17b2
tl: 100 fb: --H-FL-- lb: 0x0  cc: 13
col  0: [ 3]  53 59 53
col  1: [28]
2f 31 30 37 34 34 38 33 37 5f 4f 62 6a 65 63 74 53 74 72 65 61 6d 43 6c 61
73 73 32
col  2: *NULL*
col  3: [ 2]  c1 0b
col  4: *NULL*
col  5: [10]  4a 41 56 41 20 43 4c 41 53 53
col  6: [ 7]  78 66 07 03 06 35 32
col  7: [ 7]  78 66 07 03 06 35 32
col  8: [19]  32 30 30 32 2d 30 37 2d 30 33 3a 30 35 3a 35 32 3a 34 39
col  9: [ 5]  56 41 4c 49 44
col 10: [ 1]  4e
col 11: [ 1]  4e
col 12: [ 1]  4e
end_of_block_dump
End dump data blocks tsn: 3 file#: 3 minblk 48057 maxblk 48060
[oracle@oratest udump]$
[oracle@oratest udump]$

d.c.b.a · 发表于 2005-1-18 18:04

其实是没有被用过中的block中的记录可以读出来，不过我在hash cluster上试时，读出来的就不对了，是乱码的，我刚好加了开关，这说明oracle中的block格式是多样的，要搞清楚真的很难。

有没有人研究过，dul读出的数据是commit的呢还是连没有commit的全部读出来？或者没有commit时从回滚段去读取原来的记录值？

我只是在模拟dul而已！