1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Read Only tables in Oracle 11g

Discussion in 'SQL PL/SQL' started by SBH, Nov 8, 2010.

  1. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    1. Overview

    Read Only table is the new feature introduced in Oracle 11g Release 1. The article explains the permissions and restrictions of Read Only tables.

    2. Introduction

    Before Oracle 11g Release 1, Oracle provided Read/Write feature at Tablespace level. A Tablespace once set as READ ONLY would lift transactional feature from all tables residing in the table by making them read-only. Below commands were used to achieve the READ/WRITE feature of tablespace.

    Code (Text):
    ALTER TABLESPACE <tablespace_name> READ ONLY;
    ALTER TABLESPACE <tablespace_name> READ WRITE;
    Transactional feature could be restricted at table level by restricting DMLs in the Table Triggers or Owner can grant only SELECT privilege to other user on a table.

    But now in Oracle 11g, tables can be set read-only or read-write within the tablespace. Below list of shows operations permitted and restricted for READ ONLY tables.

    [TABLE]Restricted Permitted
    DML operations SELECT
    TRUNCATE TABLE CREATE/ALTER/DROP INDEX
    SELECT FOR UPDATE ALTER TABLE ADD/MODIFY/DROP/ENABLE/DISABLE CONSTRAINT
    ALTER TABLE ADD/MODIFY/RENAME/DROP COLUMN ALTER TABLE DROP UNUSED COLUMNS
    ALTER TABLE SET COLUMN UNUSED ALTER TABLE ADD/COALESCE/MERGE/MODIFY/MOVE/RENAME/SPLIT (SUB)PARTITION
    ALTER TABLE DROP/TRUNCATE/EXCHANGE (SUB)PARTITION ALTER TABLE MOVE
    FLASHBACK TABLE ALTER TABLE ENABLE ROW MOVEMENT and ALTER TABLE SHRINK
    RENAME TABLE and ALTER TABLE RENAME TO
    DROP TABLE
    ALTER TABLE DEALLOCATE UNUSED
    ALTER TABLE ADD/DROP SUPPLEMENTAL LOG[/TABLE]

    Syntax:

    Code (Text):
    ALTER TABLE table_name READ ONLY;
    ALTER TABLE table_name READ WRITE;
    3. Test Case Illustration

    Below DDL command creates a Table CLUB_TEST, inserts a record and then sets it READ-ONLY

    Code (SQL):
    SQL> CREATE TABLE CLUB_TEST
             (ID  NUMBER,
             NAME VARCHAR2(100));

    SQL> INSERT INTO CLUB_TEST VALUES (1,'CLUB0ORACLE');

    1 ROW inserted.

    SQL> ALTER TABLE CLUB_TEST READ ONLY;

    TABLE altered.
    Below DML attempts to insert a Record in CLUB_TEST

    Code (SQL):
    SQL> INSERT INTO CLUB_TEST VALUES (2,'ADVISOR');
    INSERT INTO CLUB_TEST VALUES (2,'ADVISOR')
                *
    ERROR at line 1:
    ORA-12081: UPDATE operation NOT allowed ON TABLE "ORCL"."CLUB_TEST"
    Besides DML, READ ONLY tables are also restricted for DDL operations

    Code (SQL):
    SQL> TRUNCATE TABLE CLUB_TEST;
    TRUNCATE TABLE CLUB_TEST
                *
    ERROR at line 1:
    ORA-12081: UPDATE operation NOT allowed ON TABLE "ORCL"."CLUB_TEST"
    4. Reverting back the READ/WRITE feature

    Code (SQL):
    SQL> ALTER TABLE CLUB_TEST  READ WRITE;

    TABLE altered.
    Now it will allow all the DML and DDL features on the table.

    Code (SQL):
    SQL> INSERT INTO CLUB_TEST VALUES (2,'ADVISOR');

    1 ROW inserted.
    Querying the READ ONLY status of a table

    READ_ONLY column of USER_TABLES stores the current READ/WRITE status of the table. Below query can be used to retrieve the same.

    Code (SQL):
    SELECT TABLE_NAME, READ_ONLY
    FROM USER_TABLES
    WHERE TABLE_NAME = ‘CLUB_TEST’;

    TABLE_NAME     READ_ONLY
    ----------  -------  ---------
    CLUB_TEST          YES
    Apart from USER_TABLES, ALL_TABLES and DBA_TABLES also include the READ_ONLY column to specify the READ/WRITE status of a table, but it must be queried along with the owner’s name.

    For example,

    Code (SQL):
    SELECT TABLE_NAME, READ_ONLY
    FROM ALL_TABLE
    WHERE TABLE_NAME = ‘CLUB_TEST’
    AND OWNER = ‘ORCL’;