How to make a table read only in Oracle
Oracle 11g allows tables to be marked as read-only using the ALTER TABLE command.
ALTER TABLE table_name READ ONLY;
ALTER TABLE table_name READ WRITE;
Let’s create a table and make it read-only.
CREATE TABLE Example (id NUMBER);
INSERT INTO Example VALUES (1);
ALTER TABLE Example READ ONLY;
CREATE TABLE Example (id NUMBER);
INSERT INTO Example VALUES (1);
ALTER TABLE Example READ ONLY;
Any DML statements that affect the table data results in an ORA-12081 error message.
SQL> INSERT INTO Example VALUES (10);
INSERT INTO Example VALUES (10);
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."Example"
SQL> INSERT INTO Example VALUES (10);
INSERT INTO Example VALUES (10);
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."Example"
DML and DDL operations return to normal once the table is switched back to read-write mode.
SQL> ALTER TABLE Example READ WRITE;
Table altered.
SQL> DELETE FROM Example;
1 row deleted.
SQL> ALTER TABLE Example READ WRITE;
Table altered.
SQL> DELETE FROM Example;
1 row deleted.
The read-only status of tables is displayed in the READ_ONLY column of the [DBA|ALL|USER]_TABLES views.
No comments:
Post a Comment