Oracle Illustrated

Feb 21 2010   8:33AM GMT

Migrating from 9i to 11g – Read only tables

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

Feature in Oracle 11g.

Prior to Oracle 11g – grant READ-ONLY permissions to a table can be done at GRANT level. Now, it is possible to make the table read-only at definition level itself. ALTER TABLE

READ ONLY – alters the table to read only. It is also possible to change the READ ONLY table to READ WRITE using the ALTER table script.

Read only tables can
select, manage indexes, constraints etc., row movement
dropping unused column
drop table

Read only tables cannot
insert, delete or update
truncate
select for update
drop column / setting column to unused
flashback, partitionedonline redefinition

How could we achieve this in ORACLE 9i?

CREATE TABLE test_readonly
(a NUMBER)

create or replace public synonym test_readonly for system.test_readonly;

GRANT SELECT ON test_readonly TO SYSTEM;

Issuing select statement / for update : (from owner – SYSTEM & SCOTT)

SQL> select * from test_readonly;

A
———-
1

Elapsed: 00:00:00.10

SQL> select * from test_readonly for update;

A
———-
1

Elapsed: 00:00:00.00

** owner is SYSTEM
INSERT / UPDATE statement: (from SCOTT – not the owner)

SQL> insert into test_readonly values (2);
insert into test_readonly values (2)
*
ERROR at line 1:
ORA-01031: insufficient privileges

Elapsed: 00:00:00.28
SQL> update test_readonly set a = 5;
update test_readonly set a = 5
*
ERROR at line 1:
ORA-01031: insufficient privileges

Elapsed: 00:00:00.01

Drop / Rename table:

SQL> drop table test_readonly;
drop table test_readonly

Table dropped

Elapsed: 00:00:00.00

SQL> rename test_readonly to test1_readonly;
rename test_readonly to test1_readonly
*
ERROR at line 1:
ORA-04043: object TEST_READONLY does not exist

**But all the above can be done frorm owner – SYSTEM user
Row movement etc., :

SQL> alter table test_Readonly enable row movement;

Table altered.

Elapsed: 00:00:00.06
Drop column / Drop unsed columns / Set it to unused:

SQL> alter table test_readonly add ( b number);

Table altered.

Elapsed: 00:00:00.50
SQL>

SQL> alter table test_readonly set unused column b;

Table altered.

Elapsed: 00:00:01.48
SQL>

SQL> alter table test_readonly drop unused columns;

Table altered.

Elapsed: 00:00:02.01
Delete / Truncate: (from SCOTT)

SQL> delete from test_readonly where a = 1;
delete from test_readonly where a = 1
*
ERROR at line 1:
ORA-01031: insufficient privileges

Elapsed: 00:00:00.00

SQL> truncate table test_readonly;
truncate table test_readonly
*
ERROR at line 1:
ORA-00942: table or view does not exist

Elapsed: 00:00:00.00
Flashback on table:

SQL> FLASHBACK TABLE test_readonly
2 TO TIMESTAMP (SYSTIMESTAMP – INTERVAL ‘1’ minute);

Flashback complete.

Elapsed: 00:00:03.71
SQL>
DML operations from table owner:

SQL> CREATE TABLE test_readonly
(a NUMBER);

Table created.

Elapsed: 00:00:01.54

create or replace public synonym test_readonly for system.test_readonly;

SQL> GRANT SELECT ON test_readonly TO SCOTT;

Grant succeeded.

Elapsed: 00:00:00.23
SQL> insert into test_readonly values (1);

1 row created.

Elapsed: 00:00:00.17
SQL>

**********************************************************************************

The same in Oracle 11g – Simple

CREATE TABLE test_readonly
(a NUMBER)

create or replace public synonym test_readonly for system.test_readonly;

ALTER TABLE test_readonly READ ONLY;

Can be modified to writable table – ALTER TABLE test_readonly READ WRITE;
Issuing select statement / for update:

SQL> select * from test_readonly;

no rows selected

Elapsed: 00:00:00.04

SQL> select * from test_readonly for update;
select * from test_readonly for update
*
ERROR at line 1:
ORA-12081: update operation not allowed on table “SYSTEM”.”TEST_READONLY”

Elapsed: 00:00:00.04
SQL>
INSERT / UPDATE statement:

SQL> insert into test_readonly values (2);
insert into test_readonly values (2)
*
ERROR at line 1:
ORA-12081: update operation not allowed on table “SYSTEM”.”TEST_READONLY”


Elapsed: 00:00:00.00

Drop / Rename table:

SQL> drop table test_readonly;
drop table test_readonly

Table dropped
Row movement etc., :

SQL> alter table test_Readonly enable row movement;

Table altered.

Elapsed: 00:00:00.09
Drop column / Drop unsed columns / Set it to unused:

SQL> alter table test_readonly add ( b number);
alter table test_readonly add ( b number)
*
ERROR at line 1:
ORA-12081: update operation not allowed on table “SYSTEM”.”TEST_READONLY”

Elapsed: 00:00:00.00
SQL> alter table test_readonly set unused column b;
alter table test_readonly set unused column b
*
ERROR at line 1:
ORA-12081: update operation not allowed on table “SYSTEM”.”TEST_READONLY”

Elapsed: 00:00:00.04

Delete / Truncate:

SQL> delete from test_readonly where a = 1;
delete from test_readonly where a = 1
*
ERROR at line 1:
ORA-12081: update operation not allowed on table “SYSTEM”.”TEST_READONLY”

Elapsed: 00:00:00.01
SQL> truncate table test_readonly;
truncate table test_readonly
*
ERROR at line 1:
ORA-12081: update operation not allowed on table “SYSTEM”.”TEST_READONLY”

Elapsed: 00:00:00.06
SQL>

Flashback on table:

SQL> FLASHBACK TABLE test_readonly
2 TO TIMESTAMP (SYSTIMESTAMP – INTERVAL ‘1’ minute);
FLASHBACK TABLE test_readonly
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-12081: update operation not allowed on table “SYSTEM”.”TEST_READONLY”

Elapsed: 00:00:01.48
DML operations from table owner:

Cannot modify read only tables.

**********************************************************************************

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: