Oracle11新特性——在线操作功能增强（五）

在11g以前，Oracle提供的只读功能只到了数据库级和表空间级：

SQL> CONN / AS SYSDBA
Connected.
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP OPEN READ ONLY
ORACLE instance started.

Total System Global Area 267825152 bytes
Fixed Size 1299316 bytes
Variable Size 176163980 bytes
Database Buffers 88080384 bytes
Redo Buffers 2281472 bytes
Database mounted.
Database opened.
SQL> INSERT INTO YANGTK.T VALUES (2);
INSERT INTO YANGTK.T VALUES (2)
*
ERROR at line 1:
ORA-16000: database open for read-only access

如果数据库级设置了只读打开，数据库中任何对象都无法进行修改。

SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.

Total System Global Area 267825152 bytes
Fixed Size 1299316 bytes
Variable Size 176163980 bytes
Database Buffers 88080384 bytes
Redo Buffers 2281472 bytes
Database mounted.
Database opened.
SQL> INSERT INTO YANGTK.T VALUES (2);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT TABLESPACE_NAME FROM ALL_TABLES WHERE OWNER = 'YANGTK' AND TABLE_NAME = 'T';

TABLESPACE_NAME
------------------------------
YANGTK

SQL> ALTER TABLESPACE YANGTK READ ONLY;

Tablespace altered.

SQL> INSERT INTO YANGTK.T VALUES (3);
INSERT INTO YANGTK.T VALUES (3)
*
ERROR at line 1:
ORA-00372: file 5 cannot be modified at this time
ORA-01110: data file 5:
'/data/oracle/oradata/ora11g/ORA11G/datafile/o1_mf_yangtk_3d3kn7k4_.dbf'

设置表空间只读，那么表空间中所有的对象都无法被修改。

也就是说，如果在11g之前，想要实现对某张表的只读，只有建立一个表空间，将表放到该表空间中，再将表空间至于只读状态。

不但操作麻烦，而且如果需要添加新的只读表，而不打算新建立一个表空间的话，需要先将只读表空间置为可写，然后将新的表放进去，再将其置为只读，而在操作期间，表空间中原有的对象可能会被修改。

从11g开始，Oracle终于提供了表级的只读设置了，显现方法也很简单，一个ALTER TABLE READ ONLY语句就搞定了：

SQL> ALTER TABLESPACE YANGTK READ WRITE;

Tablespace altered.

SQL> CONN YANGTK/yangtk
Connected.
SQL> INSERT INTO T VALUES (3);

1 row created.

SQL> ALTER TABLE T READ ONLY;

Table altered.

SQL> INSERT INTO T VALUES (4);
INSERT INTO T VALUES (4)
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "YANGTK"."T"

将表置于只读状态，并非只是禁止DML语句，包含FOR UPDATE的查询语句，已经会引起表中数据发生变化的DDL语句都会被禁止。

而不影响表中数据的DDL则可以正常执行：

SQL> SELECT * FROM T;

ID
----------
1
2
3

SQL> SELECT * FROM T FOR UPDATE;
SELECT * FROM T FOR UPDATE
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "YANGTK"."T"


SQL> ALTER TABLE T ADD NAME VARCHAR2(30);
ALTER TABLE T ADD NAME VARCHAR2(30)
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "YANGTK"."T"


SQL> ALTER TABLE T MODIFY ID NOT NULL;
ALTER TABLE T MODIFY ID NOT NULL
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "YANGTK"."T"


SQL> ALTER TABLE T ALLOCATE EXTENT;

Table altered.

SQL> ALTER TABLE T CACHE;

Table altered.