Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL
Expertise: Advanced
Oct 3, 2005

Making Tables Read-only in Oracle

In Oracle, you can only control read/write capability for an entire tablespace, not for a single table within a tablespace. So, if you want all the tables in a tablespace to be read-only, use the following command:

ALTER TABLESPACE <tablespace_name> READ ONLY; 
To reverse the change and set all the tables in the tablespace back to read-write mode, use this code:

ALTER TABLESPACE <tablespace_name> READ WRITE; 
Despite that limitation, you can simulate a read-only table by simply preventing all table modifications using a trigger. To do that, create a trigger as follows.

SQL> create or replace trigger emp_sal_read_only
2 before insert or update or delete
3 on emp_sal_tbl
4 begin
5 raise_application_error (-20001, 'Table EMP_SAL_TBL is read only, 
   You cannot make changes to the data.');
6 end;
7 /
Trigger created.
Here's a test command that tries to alter the data, and the resulting output:

SQL> DELETE FROM EMP_SAL_TBL;
DELETE FROM EMP_SAL_TBL
*
ERROR at line 1:
ORA-20001: Table EMP_SAL_TBL is read only, You cannot make changes to the data.
ORA-06512: at "SRIDHAR.EMP_SAL_READ_ONLY", line 2
ORA-04088: error during execution of trigger 'SRIDHAR.EMP_SAL_READ_ONLY'
MS Sridhar
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap