Since Oracle 11g we can place a
table in read -only mode with the ALTER TABLE …READ
ONLY statement. Read-Only tables are like normal tables, the only dissimilarity
being it disallows any attempt to modify the data. Any DML (insert/update/delete/truncate)
and certain DDL statement that affects the data are not allowed on these
tables.
SYNTAX
ALTER
TABLE SALES_TEST READ ONLY;
To Alter a table to read-only
mode you must have ALTER ANY TABLE privilege on the table.
If you try to insert a record
into this table now,
INSERT
INTO SALES_TEST VALUES (100);
Error:
Update operation not allowed on the
table.
Even though the table is read –only
mode, index creation statement is possible.
CREATE
INDEX SALES_TEST_IND ON SALES_TEST (ID);
Index
Created.
- To Revert back the table to READ/WRITE mode.
ALTER
TABLE SALES_TEST READ WRITE;
Prior 11g Read-only mode could
only be associated to database and tablespace level. Previously to make a table
read-only we have to grant SELECT privilege to appropriate users on the table. But
still the table will be available for read and write to the creator of the
table. With the new Oracle 11g enhancement no DML will be allowed on the table
even by the owner of the table.
- How To Check A Table Is In Read Only Or Not?
SELECT
TABLE_NAME ,READ_ONLY FROM USER_TABLES WHERE TABLE_NAME=’SALES_TEST ‘
The best example for tables
which makes sense in read-only mode is the configuration tables that are not to
be modified after installation. The same is applicable for tables that store
historical or reference data.
Operations like DML,
TRUNCATION, DROP, and FLASHBACK are not permitted on READ
ONLY TABLE.
Read the most discussed topics of DATAWAREHOUSECONCEPTS- TOP 10 TOPICS OF 2014
