在11g版本中,对表的操作可以进行read only 操作 来保护表的数据和表定义

对表执行DML和DDl都是不允许的 对表进行只读和恢复读写用以下两个语句:

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

以下是测试过程:

DML验证   会抛出ORA-12081错误

[oracle@asm11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Jul 22 19:41:18 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn paul/paul                                                            
Connected.
SQL> create table ro_tab(id number);                                           

Table created.

SQL> insert into ro_tab values(1);                                             

1 row created.

SQL> alter table ro_tab read only;                                             

Table altered.

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 "PAUL"."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 "PAUL"."RO_TAB"

SQL> delete from ro_tab;                                                       
delete from ro_tab
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "PAUL"."RO_TAB"

SQL>

DDL验证

SQL> truncate table ro_tab;                                                                                                                  
truncate table ro_tab
               *
ERROR at line 1:
ORA-12081: update operation not allowed on table "PAUL"."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 "PAUL"."RO_TAB"

Operations on indexes associated with the table are unaffected by the read-only state.

索引在表只读期间是可以创建的

SQL> create index idx_test on ro_tab(id);                                                                                                    

Index created.

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>