What is Read Only Tables in Oracle?
It is possible to use read only tables in Oracle databases starting from 11.1. You can not update read only tables. A table cannot be created as read only, but can be converted to read only with the alter table command.
It is not possible to add columns to Read Only tables in 11gR1, 11gR2 and 12c versions. But in the 18c version and later versions, a new column can be added to the read-only tables.
How do I make a table read only in Oracle?
You can convert a table to read only as follows.
|
1 2 3 |
SQL> alter table test1 read only; Table altered. |
DML operations to a read-only table will result the ORA-12081 error.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SQL> insert into test1 values(1,'Ahmet','Duruoz'); insert into test1 values(1,'Ahmet','Duruoz') * ERROR at line 1: ORA-12081: update operation not allowed on table "SYS"."TEST1" SQL> delete from test1; delete from test1 * ERROR at line 1: ORA-12081: update operation not allowed on table "SYS"."TEST1" SQL> update test1 set id=2 where ad='Ahmet'; update test1 set id=2 where ad='Ahmet' * ERROR at line 1: ORA-12081: update operation not allowed on table "SYS"."TEST1" |
You can use the following command to make a “read only” table “read write” again.
|
1 2 3 |
SQL> alter table test1 read write; Table altered. |
How to check if a table is read only oracle
|
1 |
SELECT table_name,read_only FROM dba_tables WHERE owner = your_owner_name and table_name = your_table_name; |
