8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Read-Only Tables in Oracle Database 11g Release 1
In previous Oracle releases, tables could be made to appear read-only to other users by only granting the SELECT
object privilege to them, but the tables remained read-write for the owner. Oracle 11g allows tables to be marked as read-only using the ALTER TABLE
command.
alter table table_name read only; alter table table_name read write;
The following script creates a table, inserts a row, then sets the table to read-only.
drop table ro_tab purge; create table ro_tab ( id number ); insert into ro_tab values (1); alter table ro_tab read only;
Any DML statements that affect the table data and SELECT ... FOR UPDATE
queries result in an ORA-12081 error message.
SQL> insert into ro_tab values (2); insert into ro_tab values (2) * ERROR at line 1: ORA-12081: update operation not allowed on table "TEST"."RO_TAB" SQL> update ro_tab set id = 2; update ro_tab set id = 2 * ERROR at line 1: ORA-12081: update operation not allowed on table "TEST"."RO_TAB" SQL> delete from ro_tab; delete from ro_tab * ERROR at line 1: ORA-12081: update operation not allowed on table "TEST"."RO_TAB" declare l_id ro_tab.id%type; begin select id into l_id from ro_tab where rownum = 1 for update; rollback; end; / declare * ERROR at line 1: ORA-12081: update operation not allowed on table "TEST"."RO_TAB" ORA-06512: at line 4 SQL>
DDL statements that affect the table data are also restricted.
SQL> truncate table ro_tab; truncate table ro_tab * ERROR at line 1: ORA-12081: update operation not allowed on table "TEST"."RO_TAB" SQL> alter table ro_tab add (description varchar2(50)); alter table ro_tab add (description varchar2(50)) * ERROR at line 1: ORA-12081: update operation not allowed on table "TEST"."RO_TAB"
Operations on indexes associated with the table are unaffected by the read-only state. DML and DDL operations return to normal once the table is switched back to read-write mode.
SQL> alter table ro_tab read write; Table altered. SQL> delete from ro_tab; 1 row deleted. SQL>
The read-only status of tables is displayed in the READ_ONLY
column of the [DBA|ALL|USER]_TABLES
views.
For more information see:
Hope this helps. Regards Tim...