Oracle 11g 新特性:只读表(Read-only)

0    88    3

Tags:

👉 本文共约2277个字,系统预计阅读时间或需9分钟。

Oracle11g推出了一个新的特性,可以将table置于read only状态,处于该状态的table的不能执行DML操作和某些DDL操作。在Oracle11g之前的版本,只能将整个tablespace或者database置于read only状态。对于table的控制则只能通过权限来设定。

案例分析:

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;

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

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>

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

2 + 12 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部