Shulou(Shulou.com)06/01 Report--

Oracle11g has introduced a new feature that allows table to be placed in the read only state, where table cannot perform DML operations and some DDL operations. In previous versions of Oracle11g, only the entire tablespace or database could be placed in the read only state. The control of table can only be set by permissions.

123456789101112131415161718192021222324252627282930313233CREATE 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.

Case study:

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 (*)

-

eighteen

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 the read-only table:

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 purl 11 purl 47 purl 04

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

Flashback complete.

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

COUNT (*)

-

eighteen

MOVE TABLE:

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

Table altered.

Elapsed: 00:00:00.54

Compression table:

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

Constraint management:

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.

Index Management:

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

Index dropped.

Configure 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

In versions prior to 11g, if you want to make the table read-only, you can grant permissions to these users by giving SELECT object permissions, but the table owner is still read-write. Oracle 11g allows tables to be marked as read-only (read-only) through the ALTER TABLE command.

You can set the read and write permissions of the table with the following command:

ALTER TABLE table_name READ ONLY

ALTER TABLE table_name READ WRITE

A simple example is as follows:

CREATE TABLE ro_test (

Id number

);

INSERT INTO ro_test VALUES (1)

ALTER TABLE ro_test READ ONLY

Any DML statements and SELECT...FOR UPDATE query statements that affect table data return ORA-12081 error messages

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"

Zhengzhou Infertility Hospital: 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 statements that affect table data are also restricted

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"

The table is read-only but the operation on the index associated with it is not affected. DML and DDL operations return to normal when the table is switched back to read-write mode.

SQL > ALTER TABLE ro_test READ WRITE

Table altered.

SQL > DELETE FROM ro_test

1 row deleted.

SQL >

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.