READ ONLY tables in oracle 11g

READ ONLY tables in oracle 11g

In 11G previous oracle release, we don’t change the table in READ ONLY mode.

SQL> select version from v$instance;

VERSION
-----------------
10.2.0.1.0

SQL> create user raja identified by raja;

User created.

SQL> grant dba, connect, resource to raja;

Grant succeeded.

SQL> create table raja.test as select * from dba_objects;

Table created.

SQL> alter table raja.test read only;
alter table raja.test read only
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option

In Oracle 11g, we change the table in READ ONLY mode.

I have tested the below scenario.

SQL> create table raja.objects as select * from dba_objects;

Table created.

SQL> desc raja.objects
Name Null? Type
----------------------------------------- -------- -------------------------

OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(30)

SQL> alter table raja.objects read only;

Table altered.

SQL> select
owner,
table_name,
read_only from dba_tables
where owner='RAJA'
and table_name='OBJECTS';

OWNER TABLE_NAME READ_ONLY
---------- --------------- ---------------
RAJA OBJECTS YES

SQL> update raja.objects
2 set owner='RAJA';
update objects
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "RAJA"."OBJECTS"

SQL> delete from raja.objects where owner='SYS';
delete from objects where owner='SYS'
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "RAJA"."OBJECTS"

SQL> insert into raja.objects(owner)values ('RAJA');
insert into objects(owner)values ('RAJA')
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "RAJA"."OBJECTS"

SQL> truncate table raja.objects;
truncate table objects
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "RAJA"."OBJECTS"

SQL> alter table objects add (test varchar2(25));
alter table objects add (test varchar2(25))
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "RAJA"."OBJECTS"

SQL> alter table raja.objects read write;

Table altered.

SQL> select owner,table_name,read_only from dba_tables where owner='RAJA'
2 and table_name='OBJECTS';

OWNER TABLE_NAME READ_ONLY
---------- --------------- ---------------
RAJA OBJECTS NO

SQL> truncate table raja.objects;

Table truncated.

I Hope this article helped to you. Suggestions are welcome.

Thanks & Regards
RajaBaskar Thangaraj

www.dbarajabaskar.blogspot.com