Systems Engineering and RDBMS

Read Only Tables in Oracle 11g

Posted by decipherinfosys on January 23, 2008

Prior to Oracle 11g, one could create read only tablespaces and not read only tables.  This is now possible in the new version of Oracle.  You can convert a table from read/write to read only and vice-versa.  Example:

SQL> create table testcase (col1 varchar2(10));
Table Created.

SQL>Alter table testcase read only;
Table altered.

The DBA_TABLES/USER_TABLES/ALL_TABLES views have a new column called READ_ONLY which can be used to see which tables are read only in the schema.  Likewise, you can turn it back to be a read/write table:

SQL> Alter table testcase read write;

Needless to state, once a table has been put into the read only mode, the update/delete/insert DML operations are not allowed on it.  The select for update statement is also not allowed.  The DDL statements like alter table are allowed though.  You can use this feature to implement security or you can also use this feature during the maintenance mode when you want to prevent changes to the data in a given table or set of tables or it can also be used to protect the data in the static/look-up/configuration tables in a given schema.

Sorry, the comment form is closed at this time.

%d bloggers like this: