Read only table – Oracle 11g

Read only table – Oracle 11g

Olá,

Outra funcionalidade interessante no Oracle 11g é o modo read only em tabelas.

Vejamos um exemplo :

dbajccorrea@ORCL11G> alter session set nls_language=english;

Session altered.

dbajccorrea@ORCL11G> set lines 2000 pages 50 timing on serveroutput on

Table dropped.

Elapsed: 00:00:00.96

dbajccorrea@ORCL11G> create table test_read
2 (x number,y varchar2(20)) ;

Table created.

Elapsed: 00:00:00.07
dbajccorrea@ORCL11G>
dbajccorrea@ORCL11G> insert into test_read values(1,'OCA');

1 row created.

Elapsed: 00:00:00.01
dbajccorrea@ORCL11G> insert into test_read values(2,'OCP');

1 row created.

Elapsed: 00:00:00.01
dbajccorrea@ORCL11G> commit ;

Commit complete.

Elapsed: 00:00:00.01
dbajccorrea@ORCL11G> alter table test_read read only ;

Table altered.

Elapsed: 00:00:00.07
dbajccorrea@ORCL11G> insert into test_read values(3,'OCM') ;
insert into test_read values(3,'OCM')
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "DBAJCCORREA"."TEST_READ"

Elapsed: 00:00:00.03

A mensagem parece estranha,mas informa que a operação não é permiitida mesmo sabendo que como owner da tabela tenho todos os privilégios sobre a tabela. Realmente deixa a tabela em modo read only.

dbajccorrea@ORCL11G> select owner,table_name,read_only from dba_tables
2 where table_name ='TEST_READ' ;

OWNER TABLE_NAME REA
------------------------------ ------------------------------ ---
DBAJCCORREA TEST_READ YES

Elapsed: 00:00:00.08

dbajccorrea@ORCL11G> alter table test_read read write ;

Table altered.

Elapsed: 00:00:00.02

dbajccorrea@ORCL11G> alter table test_read read write;

Table altered.

Elapsed: 00:00:00.01
dbajccorrea@ORCL11G> select owner,table_name,read_only from dba_tables
2 where table_name ='TEST_READ' ;

OWNER TABLE_NAME REA
------------------------------ ------------------------------ ---
DBAJCCORREA TEST_READ NO

Elapsed: 00:00:00.01

dbajccorrea@ORCL11G> insert into test_read values(3,'OCM') ;

1 row created.

Elapsed: 00:00:00.01
dbajccorrea@ORCL11G> commit ;

Commit complete.

Elapsed: 00:00:00.00
dbajccorrea@ORCL11G>

Abs