玉面飞龙的BLOG

read only table and online index build

December 23rd, 2007 | Categories: Boring | Tags:

Oracle Database 11g introduces new ALTER TABLE syntax. For example:

ALTER TABLE READ ONLY
and
ALTER TABLE READ WRITE

The operating system sets the precedent to make a file read-only even for its owner.

Earlier, a table could be made read-only (by granting only SELECT on it) to users other than the owner of the table.

With this feature, the owner too can be prevented from doing unintended DML to a table.

Look below example, table still can’t prevent DDL.

SQL> create table t as select * from dba_objects;
Table created.

SQL> create index tidx on t(object_id) online;
Index created.

SQL> alter table t read only;
Table altered.

SQL> delete from t where rownum=1;
delete from t where rownum=1
*
ERROR at line 1:
ORA-12081: update operation not allowed on table “BINZHANG”.”T”

SQL> drop index tidx;
Index dropped.

SQL> drop table t;
Table dropped.

SQL> !oerr ora 12081
12081, 00000, “update operation not allowed on table \”%s\”.\”%s\”"
// *Cause: An attempt was made to update a read-only materialized view.
// *Action: No action required. Only Oracle is allowed to update a
// read-only materialized view.
//

SQL> FLASHBACK TABLE t TO BEFORE DROP ;
Flashback complete.

SQL> delete from t;
delete from t
*
ERROR at line 1:
ORA-12081: update operation not allowed on table “BINZHANG”.”T”

SQL> alter table t read write;
Table altered.

SQL> delete from t;
69120 rows deleted.

Oracle11g在online index create&rebuild上也有增强。

In highly concurrent environments, the requirement of acquiring a DML-blocking lock at the beginning and end of an online index creation and rebuild could lead to spikes of waiting DML operations and, therefore, a short drop and spike of system usage. While this is not an overall problem for the database, this anomaly in system usage could trigger operating system alarm levels. This feature eliminates the need for DML-blocking locks when creating or rebuilding an online index.

Online index creation and rebuild prior to this release required a DML-blocking lock at the beginning and end of the rebuild for a short period of time. This meant that there would be two points at which DML activity came to a halt. This DML-blocking lock is no longer required, making these online index operations fully transparent.

11g以前，online创建和重建索引在开始和结束的时候需要在表上获得共享锁TM (mode=4)。如果在这2个时间点，表上的DML操作特别频繁，则有可能导致较高的enqueue等待和较高的active session.

使用10704在11g中获得如下跟踪文件，

*** 2007-12-23 01:26:31.949
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0×401 timeout=21474836 ***
ksqgtl: xcb=0×80f5e964, ktcdix=2147483647, topxcb=0×80f5e964
ktcipt(topxcb)=0×0

*** 2007-12-23 01:26:31.949
ksucti: init txn DID from session DID
ksqgtl:
ksqlkdid: 0001-0014-0000000A

*** 2007-12-23 01:26:31.949
*** ksudidTrace: ksqgtl
ktcmydid(): 0001-0014-0000000A
ksusesdi: 0001-0014-0000000C
ksusetxn: 0001-0014-0000000A
ksqgtl: RETURNS 0

*** 2007-12-23 01:26:31.949
ksqgtl *** TX-00020008-000004dc mode=6 flags=0×401 timeout=0 ***
ksqgtl: xcb=0×80f5e964, ktcdix=2147483647, topxcb=0×80f5e964
ktcipt(topxcb)=0×0

*** 2007-12-23 01:26:31.949
ksucti: init session DID from txn DID:
ksqgtl:
ksqlkdid: 0001-0014-0000000A

*** 2007-12-23 01:26:31.949
*** ksudidTrace: ksqgtl
ktcmydid(): 0001-0014-0000000A
ksusesdi: 0001-0014-0000000C
ksusetxn: 0001-0014-0000000A
ksqgtl: RETURNS 0

*** 2007-12-23 01:26:31.950
ksqrcl: TX,20008,4dc
ksqrcl: returns 0

*** 2007-12-23 01:26:31.950
ksqrcl: TM,e,0
ksqrcl: returns 0

*** 2007-12-23 01:26:31.950
ksqgtl *** TX-000a001f-0000048a mode=6 flags=0×401 timeout=0 ***
ksqgtl: xcb=0×80f5e964, ktcdix=2147483647, topxcb=0×80f5e964
ktcipt(topxcb)=0×0

*** 2007-12-23 01:26:31.950
ksucti: init txn DID from session DID
ksqgtl:
ksqlkdid: 0001-0014-0000000A

*** 2007-12-23 01:26:31.950
*** ksudidTrace: ksqgtl
ktcmydid(): 0001-0014-0000000A
ksusesdi: 0001-0014-0000000C
ksusetxn: 0001-0014-0000000A
ksqgtl: RETURNS 0

*** 2007-12-23 01:26:31.950
ksqgtl *** CI-00000001-00000005 mode=6 flags=0×10 timeout=21474836 ***
ksqgtl: xcb=0×80f5e964, ktcdix=2147483647, topxcb=0×80f5e964
ktcipt(topxcb)=0×0

*** 2007-12-23 01:26:31.950
ksucti: init session DID from txn DID:
ksqgtl:
ksqlkdid: 0001-0014-0000000A

*** 2007-12-23 01:26:31.951
*** ksudidTrace: ksqgtl
ktcmydid(): 0001-0014-0000000A
ksusesdi: 0001-0014-0000000C
ksusetxn: 0001-0014-0000000A
ksqgtl: RETURNS 0

*** 2007-12-23 01:26:31.982
ksqrcl: CI,1,5
ksqrcl: returns 0

*** 2007-12-23 01:26:31.982
ksqgtl *** TM-00000043-00000000 mode=3 flags=0×401 timeout=21474836 ***
ksqgtl: xcb=0×80f5e964, ktcdix=2147483647, topxcb=0×80f5e964
ktcipt(topxcb)=0×0

*** 2007-12-23 01:26:31.982
ksucti: init session DID from txn DID:
ksqgtl:
ksqlkdid: 0001-0014-0000000A

*** 2007-12-23 01:26:31.982
*** ksudidTrace: ksqgtl
ktcmydid(): 0001-0014-0000000A
ksusesdi: 0001-0014-0000000C
ksusetxn: 0001-0014-0000000A
ksqgtl: RETURNS 0

*** 2007-12-23 01:26:31.983
ksqgtl *** TM-0000000e-00000000 mode=3 flags=0×401 timeout=21474836 ***
ksqgtl: xcb=0×80f5e964, ktcdix=2147483647, topxcb=0×80f5e964
ktcipt(topxcb)=0×0

*** 2007-12-23 01:26:31.983
ksucti: init session DID from txn DID:
ksqgtl:
ksqlkdid: 0001-0014-0000000A

*** 2007-12-23 01:26:31.983
*** ksudidTrace: ksqgtl
ktcmydid(): 0001-0014-0000000A
ksusesdi: 0001-0014-0000000C
ksusetxn: 0001-0014-0000000A
ksqgtl: RETURNS 0

*** 2007-12-23 01:26:31.983
ksqrcl: TX,a001f,48a
ksqrcl: returns 0

*** 2007-12-23 01:26:31.983
ksqrcl: TM,e,0
ksqrcl: returns 0

*** 2007-12-23 01:26:31.984
ksqrcl: TM,43,0
ksqrcl: returns 0

*** 2007-12-23 01:26:31.984
ksqrcl: TS,4,1002f8b
ksqrcl: returns 0

*** 2007-12-23 01:26:31.984
ksqrcl: TT,4,10
ksqrcl: returns 0