玉面飞龙的BLOG

最近alter一个归档数据库的很多tablespace read only,结果发现alter statement都hang住了,显示等待TX enqueue.

查了一下,alter tablespace read only要等待所有的active transaction都结束。Oracle无法轻量级的判断数据库中当前active transaction是否有修改过要R/O的表空间的数据。这个知识点还涉及到延迟块清除,一致读等概念。

一个发现就是，虽然alter tablespace read only hang住,但任何active的和新的transaction都不能继续DML pending R/O的tablespace了，会提示ORA-00372错误。

Session 1 先产生active transaction

SQL>  insert into test values(1,sysdate);
1 row created.

Session 2执行 R/O tablespace

SQL> alter tablespace sm_data read only;

会产生TX等待

SQL>  select * from v$lock where block>0;
TYPE   LMODE    REQUEST  BLOCK
———— ———- ——
TX         6          0      1

此时pending R/O的表空间 SM_DATA还是 ONLINE状态

SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME  STATUS
—————- ——–
SM_DATA        ONLINE

Session 1继续执行DML操作，提示 ORA-00372错误。
SQL> insert into test values(1,sysdate);
insert into test values(1,sysdate)
*
ERROR at line 1:
ORA-00372: file 4 cannot be modified at this time
ORA-01110: data file 4: ‘/oracle/DDS/data03/lewu/data/sm_data01.dbf’

SQL> !oerr ora 372
00372, 00000, “file %s cannot be modified at this time”
// *Cause:  attempting to modify the contents of a file that cannot be
//          modified. The file is most likely part of a read only tablespace
//          but may be in the process of going offline, or the database may
//          be in the process of closing.
// *Action: check the status of the file and its tablespace

Session 1 commit后,R/O statement返回。

SQL> alter tablespace sm_data read only;
Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces;
NAME           STATUS
—————- ——
SM_DATA     READ ONLY