Monday 23 February 2015

READ ONLY TABLES In Oracle 11g


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.
SYNTAX
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?
To identify the mode of the table you can use the READ_ONLY column. Its set to YES if table is read only and NO when table is not read-only.

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


  
Related Posts Plugin for WordPress, Blogger...

ShareThis