Making Tables Read-only in Oracle

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:


To reverse the change and set all the tables in the tablespace back to read-write mode, use this code:


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_only2 before insert or update or delete3 on emp_sal_tbl4 begin5 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 2ORA-04088: error during execution of trigger 'SRIDHAR.EMP_SAL_READ_ONLY'
Share the Post:
Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular

XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several