Oracle 11g 新特性：只讀表（Read-only）

Oracle11g推出了一個新的特性，可以將table置於read only狀態，處於該狀態的table的不能執行DML操作和某些DDL操作。在Oracle11g之前的版本，只能將整個tablespace或者database置於read only狀態。對於table的控制則只能透過許可權來設定。

CREATE TABLE products(prod_id varchar2(6) NOT null,quantity number,price number,expiry_date date);
ALTER TABLE products READ only;
TRUNCATE TABLE products;
ALTER TABLE products SET unused(expiry_date);
ALTER TABLE products DROP  unused columns;
CREATE INDEX idxxx ON products(price);
ALTER TABLE products DROP COLUMN expiry_date;
DROP TABLE products;
HR@lhr121> CREATE TABLE products(prod_id varchar2(6) NOT null,quantity number,price number,expiry_date date);
Table created.
HR@lhr121> HR@lhr121> ALTER TABLE products READ only;
Table altered.
HR@lhr121> TRUNCATE TABLE products;
TRUNCATE TABLE products
               *
ERROR at line 1:
ORA-12081: update operation not allowed on table "HR"."PRODUCTS"
HR@lhr121> ALTER TABLE products SET unused(expiry_date);
ALTER TABLE products SET unused(expiry_date)
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "HR"."PRODUCTS"
HR@lhr121> ALTER TABLE products DROP  unused columns;
Table altered.
HR@lhr121> CREATE INDEX idxxx ON products(price);
Index created.
HR@lhr121> ALTER TABLE products DROP COLUMN expiry_date;
ALTER TABLE products DROP COLUMN expiry_date
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "HR"."PRODUCTS"
HR@lhr121> DROP TABLE products;
Table dropped.

案例分析：

11:44:46 SCOTT@ test1 >select * from tab;

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

BONUS                          TABLE

CREDIT_CLUSTER                 CLUSTER

CREDIT_ORDERS                  TABLE            1

DEPT                           TABLE

EMP                            TABLE

EMP1                           TABLE

11:44:56 SCOTT@ test1 >select count(*) from emp1;

  COUNT(*)

----------

        18

Elapsed: 00:00:00.04

11:45:12 SCOTT@ test1 >alter table emp1 read only;

Table altered.

11:51:46 SCOTT@ test1 >select read_only from user_tables where table_name='EMP1';

REA

---

YES

對只讀表做DML:

11:45:20 SCOTT@ test1 >insert into emp1 select * from emp where rownum=1;

insert into emp1 select * from emp where rownum=1

            *

ERROR at line 1:

ORA-12081: update operation not allowed on table "SCOTT"."EMP1"

Elapsed: 00:00:00.04

11:45:38 SCOTT@ test1 >delete from emp1;

delete from emp1

            *

ERROR at line 1:

ORA-12081: update operation not allowed on table "SCOTT"."EMP1"

Elapsed: 00:00:00.00

11:45:47 SCOTT@ test1 >update emp1 set sal=6000 where empno=7788;

update emp1 set sal=6000 where empno=7788

       *

ERROR at line 1:

ORA-12081: update operation not allowed on table "SCOTT"."EMP1"

TRUNCATE TABLE:

11:46:03 SCOTT@ test1 >truncate table emp1;

truncate table emp1

               *

ERROR at line 1:

ORA-12081: update operation not allowed on table "SCOTT"."EMP1"

Elapsed: 00:00:00.09

DROP TABLE:

11:46:45 SCOTT@ test1 >drop table emp1;

Table dropped.

Elapsed: 00:00:00.70

11:47:05 SCOTT@ test1 >show recycle;

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

---------------- ------------------------------ ------------ -------------------

EMP1             BIN$ComP5WftmQ7gUKjA+QgIyQ==$0 TABLE        2014-12-19:11:47:04

11:47:52 SCOTT@ test1 >flashback table emp1 to before drop;

Flashback complete.

11:49:56 SCOTT@ test1 >select count(*) from emp1;

  COUNT(*)

----------

        18

        

MOVE TABLE:        

11:50:06 SCOTT@ test1 >alter table emp1 move;

Table altered.

Elapsed: 00:00:00.54

壓縮表：

11:51:27 SCOTT@ test1 >alter table emp1 compress;

Table altered.

Elapsed: 00:00:00.09

11:51:39 SCOTT@ test1 >alter table emp1 nocompress;

Table altered.

Elapsed: 00:00:00.16

約束管理：

11:52:53 SCOTT@ test1 >alter table emp1 add constraint fk_emp1 foreign key(deptno) references dept(deptno);

Table altered.

11:54:29 SCOTT@ test1 >alter table emp1 drop constraint fk_emp1;

Table altered.

11:54:47 SCOTT@ test1 >create index emp1_empno_ind on emp1(empno) tablespace indx;

Index created.

索引管理：

11:55:17 SCOTT@ test1 >drop index emp1_empno_ind;

Index dropped.

配置read write：

11:55:27 SCOTT@ test1 >alter table emp1 read write;

Table altered.

11:55:37 SCOTT@ test1 >select read_only from user_tables where table_name='EMP1';

REA

---

NO

在11g前的版本中，若想對錶設定為只讀，可以透過賦予SELECT物件許可權給這些使用者，但表的擁有者還是讀寫的。而Oracle 11g 允許表標記為只讀（read-only)透過ALTER  TABLE 命令。

可以透過下面命令對錶讀寫許可權進行設定：

  ALTER   TABLE   table_name READ ONLY;

  ALTER   TABLE   table_name READ WRITE;

簡單示例如下：

CREATE   TABLE ro_test (

     id  number

 );

INSERT   INTO  ro_test  VALUES (1);

ALTER  TABLE   ro_test  READ ONLY;

 

任何影響表資料的DML語句和SELECT...FOR UPDATE查詢語句都返回ORA-12081錯誤資訊

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

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

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

影響表資料的DDL語句也受限制

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

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

表是隻讀表但在與之相關的索引上操作不受影響。當表切換回讀寫模式時DML和DDL操作恢復正常。

SQL> ALTER TABLE ro_test READ WRITE;

Table altered.

SQL> DELETE FROM ro_test;

1 row deleted.

SQL>

 

更多詳細請查閱官方文件：

---

---

About Me

........................................................................................................................ ● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除 ● 本文在itpub、部落格園、CSDN和個人微 信公眾號（ xiaomaimiaolhr）上有同步更新 ● 本文itpub地址： http://blog.itpub.net/26736162 ● 本文部落格園地址： http://www.cnblogs.com/lhrbest ● 本文CSDN地址： https://blog.csdn.net/lihuarongaini ● 本文pdf版、個人簡介及小麥苗雲盤地址： http://blog.itpub.net/26736162/viewspace-1624453/ ● 資料庫筆試面試題庫及解答： http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA寶典今日頭條號地址： ........................................................................................................................ ● QQ群號： 230161599 、618766405 ● 微 信群：可加我微 信，我拉大家進群，非誠勿擾 ● 聯絡我請加QQ好友 （ 646634621 ），註明新增緣由 ● 於 2019-09-01 06:00 ~ 2019-09-31 24:00 在西安完成 ● 最新修改時間：2019-09-01 06:00 ~ 2019-09-31 24:00 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ........................................................................................................................ ● 小麥苗的微店： ● 小麥苗出版的資料庫類叢書： http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麥苗OCP、OCM、高可用網路班： http://blog.itpub.net/26736162/viewspace-2148098/ ● 小麥苗騰訊課堂主頁： https://lhr.ke.qq.com/ ........................................................................................................................ 使用 微 信客戶端掃描下面的二維碼來關注小麥苗的微 信公眾號（ xiaomaimiaolhr）及QQ群（DBA寶典）、新增小麥苗微 信， 學習最實用的資料庫技術。 ........................................................................................................................