Tip of Month - Apr 2000

Read Only Tables

In Oracle (all versions) 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 <tablespace_name> READ ONLY;

To make the tablespace back to read write mode, issue the command:

ALTER TABLESPACE <tablespace_name> 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'

Biju Thomas is Oracle7.3 OCP, Oracle8 OCP,
Oracle8i OCP and Oracle9i OCA/OCP Certified DBA

Questions/Comments? Write to webmaster@bijoos.com. © The scripts, tips and articles appearing on BijooS.com cannot be reproduced elsewhere without the prior permission from the webmaster.