Oracle11g新特性之只讀表

Hoegh發表於2015-08-04
 

    在Oracle 11g之前的版本中,若想對錶設定只讀,可以透過賦予SELECT物件許可權給指定使用者,但是表的擁有者仍然擁有讀寫許可權。而Oracle 11g 允許透過ALTER  TABLE 命令將表標記為只讀(read-only)。只讀表跟普通的表沒有區別,只是不允許任何事務對其執行任何 DML(Insert, Update, Delete) 操作。

測試環境

    我們在Oracle11g(11.2.0.3)進行測試。


點選(此處)摺疊或開啟

  1. SQL>

  2. SQL> select * from v$version;


  3. BANNER

  4. --------------------------------------------------------------------------------


  5. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

  6. PL/SQL Release 11.2.0.3.0 - Production

  7. CORE 11.2.0.3.0 Production

  8. TNS for Linux: Version 11.2.0.3.0 - Production

  9. NLSRTL Version 11.2.0.3.0 - Production


  10. SQL>



建立測試表

    我們建立一個測試表,命名為hoegh;然後,插入兩條測試資料。

點選(此處)摺疊或開啟

  1. SQL>

  2. SQL> create table hoegh(id number,name varchar2(20));


  3. Table created.


  4. SQL> insert into hoegh values(1,'hoegh');


  5. 1 row created.


  6. SQL> insert into hoegh values(10,'hoegh');


  7. 1 row created.


  8. SQL> commit;


  9. Commit complete.


  10. SQL> select * from hoegh;


  11.         ID NAME

  12. ---------- --------------------


  13.          1 hoegh

  14.         10 hoegh


  15. SQL>

  16. SQL>


將普通表設為只讀表

    我們透過alter table ... read only;語句來實現只讀表;而且,我們可以透過資料字典檢視 ALL_TABLES,DBA_TABLES,USER_TABLES,TABS)中的 READ_ONLY 列查詢表的只讀屬性,如下所示:

點選(此處)摺疊或開啟

  1. SQL>

  2. SQL> alter table hoegh read only;


  3. Table altered.



  4. SQL>

  5. SQL> select table_name ,read_only from user_tables;


  6. TABLE_NAME REA

  7. ------------------------------ ---


  8. HOEGH YES


  9. SQL>


執行DML語句報錯

    只讀表不允許任何事務對其執行任何 DML(Insert, Update, Delete) 操作,否則系統會報ORA-12081錯誤,提示操作不被允許。

點選(此處)摺疊或開啟

  1. SQL>

  2. SQL> insert into hoegh values(100,'hoegh');

  3. insert into hoegh values(100,'hoegh')

  4.             *

  5. ERROR at line 1:

  6. ORA-12081: update operation not allowed on table "HOEGH"."HOEGH"



  7. SQL>

  8. SQL> update hoegh set id=100 where id=10;

  9. update hoegh set id=100 where id=10

  10.        *

  11. ERROR at line 1:

  12. ORA-12081: update operation not allowed on table "HOEGH"."HOEGH"



  13. SQL>

  14. SQL> delete from hoegh where id=10;

  15. delete from hoegh where id=10

  16.             *

  17. ERROR at line 1:

  18. ORA-12081: update operation not allowed on table "HOEGH"."HOEGH"



  19. SQL>



執行TRUNCATE語句報錯

    只讀表除了不能執行所有DML語句操作外,部分DDL語句也不能執行,比如TRUNCATE,否則系統同樣會報ORA-12081錯誤,提示操作不被允許。

點選(此處)摺疊或開啟

  1. SQL>

  2. SQL> truncate table hoegh;

  3. truncate table hoegh

  4.                *

  5. ERROR at line 1:

  6. ORA-12081: update operation not allowed on table "HOEGH"."HOEGH"



  7. SQL>




執行DROP語句成功

    針對只讀表的DROP操作,是被允許的。

點選(此處)摺疊或開啟

  1. SQL> drop table hoegh;


  2. Table dropped.


  3. SQL>


 

將只讀表設為普通表

    我們透過alter table ... read write;語句來實現將只讀表設為普通讀寫表。參看下面SQL語句:

點選(此處)摺疊或開啟

  1. SQL>

  2. SQL> alter table hoegh read write;


  3. Table altered.


  4. SQL> select table_name ,read_only from user_tables;


  5. TABLE_NAME REA

  6. ------------------------------ ---


  7. HOEGH NO


  8. SQL>

  9. SQL> insert into hoegh values(100,'hoegh');


  10. 1 row created.


  11. SQL> commit;


  12. Commit complete.


  13. SQL> update hoegh set id=11 where id=10;


  14. 1 row updated.


  15. SQL> commit;


  16. Commit complete.


  17. SQL> select * from hoegh;


  18.         ID NAME

  19. ---------- --------------------


  20.          1 hoegh

  21.         11 hoegh

  22.        100 hoegh


  23. SQL>


hoegh
15.8.4
-- The End --



來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/30162081/viewspace-1761309/,如需轉載,請註明出處,否則將追究法律責任。

相關文章