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

讓客戶滿意是我們工作的目標，不斷超越客戶的期望值來自于我們對這個行業的熱愛。我們立志把好的技術通過有效、簡單的方式提供給客戶，將通過不懈努力成為客戶在信息化領域值得信任、有價值的長期合作伙伴，公司提供的服務項目有：域名注冊、虛擬主機、營銷軟件、網站建設、臨桂網站維護、網站推廣。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

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"

鄭州不孕不育醫院：http://yyk.39.net/zz3/zonghe/1d427.html

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>

名稱欄目：Oracle11g新特性：只讀表（Read-only）
轉載來源：http://www.2m8n56k.cn/article24/jphece.html

成都網站建設公司_創新互聯，為您提供企業網站制作、網站設計、企業建站、用戶體驗、App開發、服務器托管

廣告

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：[email protected]。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯