[20180626]延迟块清除与只读表.txt
--//以前测试过延迟块清除与只读表空间的情况.今天测试只读表的情况.
--//链接:[20150409]只读表空间与延迟块清除.txt
1.环境:
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> create table deptx as select * from dept ;
Table created.
SCOTT@book> select rowid,ora_rowscn,deptx.* from deptx ;
ROWID ORA_ROWSCN DEPTNO DNAME LOC
------------------ ------------ ------------ -------------- -------------
AAAWFJAAEAAAALTAAA 13277186635 10 ACCOUNTING NEW YORK
AAAWFJAAEAAAALTAAB 13277186635 20 RESEARCH DALLAS
AAAWFJAAEAAAALTAAC 13277186635 30 SALES CHICAGO
AAAWFJAAEAAAALTAAD 13277186635 40 OPERATIONS BOSTON
SCOTT@book> @ rowid AAAWFJAAEAAAALTAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
90441 4 723 0 0x10002D3 4,723 alter system dump datafile 4 block 723 ;
SCOTT@book> update deptx set dname=lower(dname);
4 rows updated.
SCOTT@book> @ &r/xid
XIDUSN_XIDSLOT_XIDSQN
------------------------------
10.20.20473
C70 XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS USED_UBLK USED_UREC XID ADDR START_DATE FLAG
---------------------------------------------------------------------- ------ ------- ------ ------ ------ ------ ------ ------ --------- --------- ---------------- ---------------- ------------------- ----
ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU10_1197734989$' XID 10 20 20473; 10 20 20473 3 952 3879 41 ACTIVE 1 1 0A001400F94F0000 0000000081B91F18 2018-06-27 08:44:56 3587
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU10_1197734989$';
ALTER SYSTEM DUMP DATAFILE 3 BLOCK 952;
SCOTT@book> alter system flush buffer_cache;
System altered.
SCOTT@book> alter system flush buffer_cache;
System altered.
SCOTT@book> commit ;
Commit complete.
2.设置deptx只读:
SCOTT@book> alter table deptx read only;
Table altered.
SCOTT@book> alter system dump datafile 4 block 723 ;
System altered.
--//检查转储文件.
Block header dump: 0x010002d3
Object id on Block? Y
seg/obj: 0x16149 csc: 0x03.1761ca4b itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x10002d0 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0003.1761ca4b
0x02 0x000a.014.00004ff9 0x00c003b8.0f27.29 ---- 4 fsc 0x0000.00000000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x010002d3
data_block_dump,data header at 0x7f36be75ee7c
===============
tsiz: 0x1f80
hsiz: 0x1a
pbl: 0x7f36be75ee7c
76543210
flag=--------
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x1f24
avsp=0x1f0a
tosp=0x1f0a
0xe:pti[0] nrow=4 offs=0
0x12:pri[0] offs=0x1f66
0x14:pri[1] offs=0x1f50
0x16:pri[2] offs=0x1f3c
0x18:pri[3] offs=0x1f24
block_row_dump:
tab 0, row 0, @0x1f66
tl: 26 fb: --H-FL-- lb: 0x2 cc: 3
col 0: [ 2] c1 0b
col 1: [10] 61 63 63 6f 75 6e 74 69 6e 67
col 2: [ 8] 4e 45 57 20 59 4f 52 4b
tab 0, row 1, @0x1f50
....
tl: 24 fb: --H-FL-- lb: 0x2 cc: 3
col 0: [ 2] c1 29
col 1: [10] 6f 70 65 72 61 74 69 6f 6e 73
col 2: [ 6] 42 4f 53 54 4f 4e
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 723 maxblk 723
--//注意看下划线内容.没有更新itl槽信息.
3.再次访问数据块看看.
SCOTT@book> set autot traceonly
SCOTT@book> select rowid,ora_rowscn,deptx.* from deptx ;
Execution Plan
----------------------------------------------------------
Plan hash value: 428208148
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 168 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEPTX | 4 | 168 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
19 recursive calls
0 db block gets
36 consistent gets
8 physical reads
116 redo size
1017 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
4 rows processed
--//可以看看有日志产生.
SCOTT@book> set autot off
SCOTT@book> alter system flush buffer_cache;
System altered.
SCOTT@book> alter system dump datafile 4 block 723 ;
System altered.
--//检查转储文件.
Block header dump: 0x010002d3
Object id on Block? Y
seg/obj: 0x16149 csc: 0x03.1761ddd9 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x10002d0 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0003.1761ca4b
0x02 0x000a.014.00004ff9 0x00c003b8.0f27.29 C-U- 0 scn 0x0003.1761dceb
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x010002d3
data_block_dump,data header at 0x7f1ee9b3ba7c
===============
tsiz: 0x1f80
hsiz: 0x1a
pbl: 0x7f1ee9b3ba7c
76543210
flag=--------
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x1f24
avsp=0x1f0a
tosp=0x1f0a
0xe:pti[0] nrow=4 offs=0
0x12:pri[0] offs=0x1f66
0x14:pri[1] offs=0x1f50
0x16:pri[2] offs=0x1f3c
0x18:pri[3] offs=0x1f24
block_row_dump:
tab 0, row 0, @0x1f66
tl: 26 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 0b
col 1: [10] 61 63 63 6f 75 6e 74 69 6e 67
col 2: [ 8] 4e 45 57 20 59 4f 52 4b
tab 0, row 1, @0x1f50
...
tl: 24 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 29
col 1: [10] 6f 70 65 72 61 74 69 6f 6e 73
col 2: [ 6] 42 4f 53 54 4f 4e
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 723 maxblk 723
--//注意下划线,可以发现即使表deptx设置只读,itl信息一样更新.
SCOTT@book> select rowid,ora_rowscn,deptx.* from deptx ;
ROWID ORA_ROWSCN DEPTNO DNAME LOC
------------------ ------------ ------------ -------------- -------------
AAAWFJAAEAAAALTAAA 13277191403 10 accounting NEW YORK
AAAWFJAAEAAAALTAAB 13277191403 20 research DALLAS
AAAWFJAAEAAAALTAAC 13277191403 30 sales CHICAGO
AAAWFJAAEAAAALTAAD 13277191403 40 operations BOSTON
SCOTT@book> @ &r/scn16 13277191403
C20 WRAP BASE
-------------------- -------------- --------------
13277191403 3 1761dceb
--//可以发现即使只读表,一样会更新的scn信息.scn也能与上面的事务对上.
4.另外注意的问题.
SCOTT@book> delete from deptx ;
delete from deptx
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."DEPTX"
--//只读表虽然不允许dml语句,但是ddl一样可以执行.
SCOTT@book> drop table deptx;
Table dropped.
SCOTT@book> flashback table deptx to before drop;
Flashback complete.
SCOTT@book> select rowid,ora_rowscn,deptx.* from deptx ;
ROWID ORA_ROWSCN DEPTNO DNAME LOC
------------------ ------------ ------------ -------------- -------------
AAAWFJAAEAAAALTAAA 13277191403 10 accounting NEW YORK
AAAWFJAAEAAAALTAAB 13277191403 20 research DALLAS
AAAWFJAAEAAAALTAAC 13277191403 30 sales CHICAGO
AAAWFJAAEAAAALTAAD 13277191403 40 operations BOSTON
5.总结:
--//对于只读表出现延迟块清除时,会修改数据块(即使是只读表),
