1. 首页
  2. IT资讯

[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/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/182850.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code