[20180626]延遲塊清除與只讀表.txt
[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.總結:
--//對於只讀表出現延遲塊清除時,會修改資料塊(即使是隻讀表),
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2156703/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20150409]只讀表空間與延遲塊清除.txt
- [20190124]bbed恢復資料遇到延遲塊清除的問題.txt
- [20190125]bbed恢復資料遇到延遲塊清除的問題3.txt
- [20190124]bbed恢復資料遇到延遲塊清除的問題2.txt
- 29、undo_2_1（事務槽、延遲塊清除、構造CR塊、ora-01555）
- [20210301]延遲顯示輸出.txt
- [20180626]函式與標量子查詢14.txt函式
- [20210529]延遲開啟資料庫.txt資料庫
- MONGODB 讀寫佇列增高與寫延遲與多粒度鎖MongoDB佇列
- [20190218]延遲約束問題2.txt
- 延遲繫結與retdlresolve
- [20211105]索引分裂 塊清除 日誌增加.txt索引
- 只讀表空間
- MySQL 中讀寫分離資料延遲MySql
- [20211105]索引分裂塊清除日誌增加(唯一索引).txt索引
- RabbitMQ延遲訊息的延遲極限是多少？MQ
- 延遲繫結
- RocketMQ系列（五）廣播與延遲訊息MQ
- JMeter定時器設定延遲與同步JMeter定時器
- [20220223]bbed讀取資料塊mssm與assm 2.txtSSM
- 延遲塊清理介紹(select也會產生redo的原因)
- 疫情延遲 題解
- redis 延遲佇列Redis佇列
- Mybatis延遲查詢MyBatis
- WebGL之延遲著色Web
- Laravel 延遲佇列Laravel佇列
- [20211108]索引分裂塊清除日誌增加(唯一索引)2.txt索引
- 基於rabbitmq延遲外掛實現分散式延遲任務MQ分散式
- 實現簡單延遲佇列和分散式延遲佇列佇列分散式
- [20210318]bbed讀取資料塊.txt
- Python中排隊理論：吞吐量與延遲Python
- 高可用延遲佇列設計與實現佇列
- Android WorkManager工作約束，延遲與查詢工作Android
- 如何避免MYSQL主從延遲帶來的讀寫問題？MySql
- hyperf redis延遲佇列Redis佇列
- 從庫延遲案例分析
- 延遲阻塞佇列 DelayQueue佇列
- 定時器setInterval，只執行一次/第一次延遲執行定時器