Saturday, August 29, 2009

Read only tables - 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,
JC

No comments: