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

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


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

WEBINAR:

On-Demand

Application Security Testing: An Integral Part of DevOps


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
Thanks for your registration, follow us on our social networks to keep up-to-date