oracle實驗記錄 （基礎,truncate與delete區別實驗）

runce資料字典 是ddl,
delete 刪除資料記錄undo(整行) DML (insert undo記錄rowid,update記錄更新欄位 )

具體實驗下

SQL> create table test (a int) tablespace test;

Table created.

SQL> insert into test values(1);

1 row created.

SQL> select file#,block# from (select dbms_rowid.rowid_relative_fno(rowid) file#
,dbms_rowid.rowid_block_number(rowid) block# from test);

     FILE#     BLOCK#
---------- ----------
         6         18

SQL> alter system dump datafile 6 block 18 ;

System altered.

SQL> variable file# number;
SQL> variable blk# number;
SQL> execute :file#:=dbms_utility.data_block_address_file(to_number('80039a','xx
xxxxxx'));

PL/SQL procedure successfully completed.

SQL> execute :blk#:=dbms_utility.data_block_address_block(to_number('80039a','xx
xxxx'));

PL/SQL procedure successfully completed.

SQL> print file#

     FILE#
----------
         2

SQL> print blk#

      BLK#
----------
       922
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0004.02a.00000b37  0x0080039a.0319.48  ----    1  fsc 0x0000.00000000
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
 
data_block_dump,data header at 0x41c025c
===============
tsiz: 0x1fa0
hsiz: 0x14
pbl: 0x041c025c
bdba: 0x01800012
     76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f9a
avsp=0x1f83
tosp=0x1f83
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f9a
block_row_dump:
tab 0, row 0, @0x1f9a
tl: 6 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [ 2]  c1 02~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~值1
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 18 maxblk 18

0x0080039a.0319.48在undo中資訊
 
48 在 48solt中
UNDO BLK: 
xid: 0x0004.02a.00000b37  seq: 0x319 cnt: 0x48  irb: 0x48  icl: 0x0   flg: 0x0000
irb:undo 段中記錄最近未提交變更的開始 找到rec #0x48就找到undo中記錄了

SQL> delete test ;

1 row deleted.

SQL> commit;

Commit complete.

SQL> alter system dump datafile 6 block 18 ;

System altered.

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0004.02a.00000b37  0x0080039a.0319.48  C---    0  scn 0x0000.004d88c7
0x02   0x0003.00c.00000c44  0x00800949.0545.1c  --U-    1  fsc 0x0007.004d8958
 
data_block_dump,data header at 0x65f025c
===============
tsiz: 0x1fa0
hsiz: 0x14
pbl: 0x065f025c
bdba: 0x01800012
     76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f9a
avsp=0x1f83
tosp=0x1f8c
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f9a
block_row_dump:
tab 0, row 0, @0x1f9a
tl: 2 fb: --HDFL-- lb: 0x2
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 18 maxblk 18

SQL> insert into test values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> truncate table test;

Table truncated.

SQL> alter system dump datafile 6 block 18 ;

System altered.

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0002.016.0000082e  0x00800294.067f.0a  --U-    1  fsc 0x0000.004d896d
0x02   0x0003.00c.00000c44  0x00800949.0545.1c  --U-    1  fsc 0x0007.004d8958
 
data_block_dump,data header at 0x65f025c
===============
tsiz: 0x1fa0
hsiz: 0x16
pbl: 0x065f025c
bdba: 0x01800012
     76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f94
avsp=0x1f78
tosp=0x1f81
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f9a
0x14:pri[1] offs=0x1f94
block_row_dump:
tab 0, row 0, @0x1f9a
tl: 2 fb: --HDFL-- lb: 0x2
tab 0, row 1, @0x1f94
tl: 6 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [ 2]  c1 02~*****************************************
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 18 maxblk 18
 delete刪除塊中值,truncate只該資料字典不刪除塊中值(truncate會產生redo undo 修改資料字典產生的)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-609072/，如需轉載，請註明出處，否則將追究法律責任。