Monday, September 26, 2011

Read Only Tables [Orig Published Apr 2000]


In Oracle (all versions including 10g) you can make a tablespace read only, but not a single table. To make a table read only, you need to have that table in its own tablespace. If the tablespace has many tables, then you can either make all the tables read only or not. For large or critical tables, you can have the tables that need to be made read only grouped together in tablespaces. This is especially important when you're are partitioning large tables. For example, if you partition a table that loads historical data, you can make the older partitions read only by making their corresponding tablespace read only.
To make the tablespace read only, issue the command:
ALTER TABLESPACE READ ONLY;
To make the tablespace back to read write mode, issue the command:
ALTER TABLESPACE READ WRITE;
FYI, you cannot make the SYSTEM tablespace to READ ONLY. You can drop a table from the read only tablespace, but not truncate, update, delete or insert.
So, what do you do if you need to make just one table read only, not all the tables in the tablespace.... Well, you can use triggers. Create a trigger which fires for all insert, update and delete operations on the table... Here is an example:
SQL> create or replace trigger emp_read_only
2 before insert or update or delete
3 on emp
4 begin
5 raise_application_error (-20001, 'Table EMP is read only, you cannot modify data.');
6 end;
7 /

Trigger created.

SQL> delete from emp;
delete from emp
*
ERROR at line 1:
ORA-20001: Table EMP is read only, you cannot modify data.
ORA-06512: at "BIJU.EMP_READ_ONLY", line 2
ORA-04088: error during execution of trigger 'BIJU.EMP_READ_ONLY'

Update:

As of Oracle11g, a new clause is introduced in the ALTER TABLE statement, that can make a table to read-only.

ALTER TABLE READ ONLY;

If you try to perform insert or update or delete on read-only table, you get an ORA-12081 error. The nice part about this feature is you are not able to do TRUNCATE operation on a read-only table. Remember, even if the tablespace is read-only, you can truncate a table. You can perform operations on index associated with read-only tables.

To resume normal update activity on the table, perform 

ALTER TABLE READ WRITE;

A new column READ_ONLY is added to DBA_TABLES. To list all the read-only tables in the database, you could do

select owner, table_name, tablespace_name
from dba_tables
where read_only = 'YES'; 


No comments:

Post a Comment

Thank you for your time... Appreciate your feedback.