Read-Only Tables in Oracle Database 11g

最新推荐文章于 2021-04-07 23:49:46 发布

转载最新推荐文章于 2021-04-07 23:49:46 发布 · 744 阅读

oracle常用专栏收录该内容

39 篇文章

订阅专栏

本文介绍如何使用 Oracle 11g 的新特性来标记表为只读状态，通过 ALTER TABLE 命令实现，并展示了当表处于只读状态时，对表进行 DDL 和 DML 操作会触发 ORA-12081 错误。同时，文章还解释了只读状态不会限制与表相关的索引操作。

In previous Oracle releases, tables could be made to appear read-only to other users by only granting the SELECT object privilege to them, but the tables remained read-write for the owner. Oracle 11g allows tables to be marked as read-only using the ALTER TABLE command.

ALTER TABLE table_name READ ONLY;
ALTER TABLE table_name READ WRITE;

The following script creates a table, inserts a row, then sets the table to read-only.

CREATE TABLE ro_tab (
  id  NUMBER
);

INSERT INTO ro_tab VALUES (1);
ALTER TABLE ro_tab READ ONLY;

Any DML statements that affect the table data and SELECT ... FOR UPDATE queries result in an ORA-12081 error message.

SQL> INSERT INTO ro_tab VALUES (2);
INSERT INTO ro_tab VALUES (2)
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."RO_TAB"


SQL> UPDATE ro_tab SET id = 2;
UPDATE ro_tab SET id = 2
       *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."RO_TAB"


SQL> DELETE FROM ro_tab;
DELETE FROM ro_tab
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."RO_TAB"

DDL statements that affect the table data are also restricted,但是只读模式的表可以用drop命令删除。

SQL> TRUNCATE TABLE ro_tab;
TRUNCATE TABLE ro_tab
               *
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."RO_TAB"


SQL> ALTER TABLE ro_tab ADD (description VARCHAR2(50));
ALTER TABLE ro_tab ADD (description VARCHAR2(50))
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "TEST"."RO_TAB"

Operations on indexes associated with the table are unaffected by the read-only state. DML and DDL operations return to normal once the table is switched back to read-write mode.

SQL> ALTER TABLE ro_tab READ WRITE;

Table altered.

SQL> DELETE FROM ro_tab;

1 row deleted.

SQL>

The read-only status of tables is displayed in the READ_ONLY column of the [DBA|ALL|USER]_TABLES views.