Logo

Navigation
  • Home
  • Services
    • ERP Solutions
    • Implementation Solutions
    • Support and Maintenance Solutions
    • Custom Solutions
    • Upgrade Solutions
    • Training and Mentoring
    • Web Solutions
    • Production Support
    • Architecture Designing
    • Independent Validation and Testing Services
    • Infrastructure Management
  • Expertise
    • Microsoft Development Expertise
    • Mobile Development
    • SQL Server Database and BI
    • SAP BI, SAP Hana, SAP BO
    • Oracle and BI
    • Oracle RAC
  • Technical Training
    • Learn Data Management
      • Business Intelligence
      • Data Mining
      • Data Modeling
      • Data Warehousing
      • Disaster Recovery
    • Learn Concepts
      • Application Development
      • Client Server
      • Cloud Computing Tutorials
      • Cluster Computing
      • CRM Tutorial
      • EDI Tutorials
      • ERP Tutorials
      • NLP
      • OOPS
      • Concepts
      • SOA Tutorial
      • Supply Chain
      • Technology Trends
      • UML
      • Virtualization
      • Web 2.0
    • Learn Java
      • JavaScript Tutorial
      • JSP Tutorials
      • J2EE
    • Learn Microsoft
      • MSAS
      • ASP.NET
      • ASP.NET 2.0
      • C Sharp
      • MS Project Training
      • Silverlight
      • SQL Server 2005
      • VB.NET 2005
    • Learn Networking
      • Networking
      • Wireless
    • Learn Oracle
      • Oracle 10g
      • PL/SQL
      • Oracle 11g Tutorials
      • Oracle 9i
      • Oracle Apps
    • Learn Programming
      • Ajax Tutorial
      • C Language
      • C++ Tutorials
      • CSS Tutorial
      • CSS3 Tutorial
      • JavaScript Tutorial
      • jQuery Tutorial
      • MainFrame
      • PHP Tutorial
      • VBScript Tutorial
      • XML Tutorial
    • Learn Software Testing
      • Software Testing Types
      • SQA
      • Testing
  • Career Training
    • Career Improvement
      • Career Articles
      • Certification Articles
      • Conflict Management
      • Core Skills
      • Decision Making
      • Entrepreneurship
      • Goal Setting
      • Life Skills
      • Performance Development
      • Personal Excellence
      • Personality Development
      • Problem Solving
      • Relationship Management
      • Self Confidence
      • Self Supervision
      • Social Networking
      • Strategic Planning
      • Time Management
    • Education Help
      • Career Tracks
      • Essay Writing
      • Internship Tips
      • Online Education
      • Scholarships
      • Student Loans
    • Managerial Skills
      • Business Communication
      • Business Networking
      • Facilitator Skills
      • Managing Change
      • Marketing Management
      • Meeting Management
      • Process Management
      • Project Management
      • Project Management Life Cycle
      • Project Management Process
      • Project Risk Management
      • Relationship Management
      • Task Management
      • Team Building
      • Virtual Team Management
    • Essential Life Skills
      • Anger Management
      • Anxiety Management
      • Attitude Development
      • Coaching and Mentoring
      • Emotional Intelligence
      • Stress Management
      • Positive Thinking
    • Communication Skills
      • Conversation Skills
      • Cross Culture Competence
      • English Vocabulary
      • Listening Skills
      • Public Speaking Skills
      • Questioning Skills
    • Soft Skills
      • Assertive Skills
      • Influence Skills
      • Leadership Skills
      • Memory Skills
      • People Skills
      • Presentation Skills
    • Finding a Job
      • Etiquette Tips
      • Group Discussions
      • HR Interviews
      • Interview Notes
      • Job Search Tips
      • Resume Tips
      • Sample Resumes
 

Oracle 11g Read Only Tables

By Saurabh Gupta | on June 16, 2011 |
Oracle 11g Tutorials

Before the release of Oracle 11g, READ-ONLY mode could only be associated with the database and tablespace. Oracle 11g extends this feature by introducing behavioral modes for database tables.

A Read-Only table is a normal data storage table, which restricts any transactional action on it. In the tutorial, we shall study the behavior of READ-ONLY tables under different scenarios.

Read-Only tables: History

Prior to Oracle 11g, all tables were used to be transactional. The table owner and the privileged users used to perform DML and DDL operations on the table. Possible way to impose restriction on transactions was through check constraint method or through triggers. We shall see them as below.

Method 1: DML trigger method

A table TAB_ORCL_10G was created. A Before DML trigger TRG_ORCL_10G was created to restrict the DML operations on the table. Note that DDL commands are still executed on the table.

Refer the illustration below.

SQL> CREATE TABLE TAB_ORCL_10G
     (A NUMBER, B NUMBER);
 
TABLE created.
 
SQL> CREATE OR REPLACE TRIGGER TRG_ORCL_10G
     BEFORE INSERT OR UPDATE OR DELETE
     ON TAB_ORCL_10G
     BEGIN
     RAISE_APPLICATION_ERROR (-20001, 'OPERATION RESTRICTED.');
     END;
     /
 
TRIGGER created.
 
SQL> INSERT INTO TAB_ORCL_10G VALUES(1,3);
INSERT INTO TAB_ORCL_10G VALUES(1,3)
            *
ERROR at line 1:
ORA-20001: OPERATION RESTRICTED.
ORA-06512: at "SCOTT. TRG_ORCL_10G ", line 2
ORA-04088: error during execution OF TRIGGER SCOTT. TRG_ORCL_10G

Method 2: Disable Validate Constraint method

The second method creates a table and a check constraint in disable validated state. Note that the DDL commands like ALTER, TRUNCATE and DROP are executed successfully.

SQL> CREATE TABLE TAB_ORCL_10G
     (A NUMBER, B NUMBER);
 
TABLE created.
 
SQL> ALTER TABLE TAB_ORCL_10G ADD CONSTRAINT CHECK_CONST CHECK(1=1) DISABLE VALIDATE;
 
TABLE altered.
 
SQL> INSERT INTO TAB_ORCL_10G VALUES(1,3);
INSERT INTO TAB_ORCL_10G VALUES(1,3)
*
ERROR at line 1:
ORA-25128: No INSERT/UPDATE/DELETE ON TABLE WITH CONSTRAINT
(SCOTT.TEST_READ_ONLY) disabled AND validated
 
SQL> TRUNCATE TABLE t_read_ol2;
TRUNCATE TABLE t_read_ol2
*
ERROR at line 1:
ORA-25128: No INSERT/UPDATE/DELETE ON TABLE WITH CONSTRAINT
(CCPDEV.TEST_READ_ONLY) disabled AND validated

Read-Only tables: the Purpose

In spite of smooth feature flow, Oracle considered both the workaround solutions as implementation overhead. Very soon, this consideration was molded into action because read only is an object property and it must not be imposed with a workaround solution.

With 11g release, a table can be established in READ-ONLY mode. The mode restricts all DML operations and DDL operations (TRUNCATE and ALTER). Surely and simply, this way enhances security at table level.

Oracle 11g release doesn’t provide READ-ONLY specification at table creation level. Therefore, a table has to be created in transactional mode still as in Code [1].

Code [1]
SQL> CREATE TABLE T_READ
     (COL1 NUMBER,
      COL2 VARCHAR2(10))
      /
 
TABLE created.

Now, above table can be switched over to READ-ONLY mode as shown in Code [2].

Code [2]
SQL> ALTER TABLE T_READ READ ONLY;
 
TABLE altered.

A new column READ_ONLY has been added in [USER | DBA | ALL] _TABLES views to reveal the mode of the table.

Code [3]
SQL> SELECT TABLE_NAME, READ_ONLY FROM USER_TABLE 
WHERE TABLE_NAME='T_READ'
  /
 
TABLE_NAME                     REA
------------------------------ ---
T_READ                         YES

READ ONLY: Notes

As the name suggests and I stated earlier too, the READ-ONLY tables are only query tables. Except the SELECT and DROP commands, DML and DDL commands are ineffective in this mode.

 

Here, we shall see the impact of DML operations on the table T_READ. Oracle raises exception ORA-12081 to restrict the DML operations on the table.

Code [4]
SQL> INSERT INTO T_READ VALUES (1, 'INSERT');
INSERT INTO T_READ VALUES (1, 'INSERT')
            *
ERROR at line 1:
ORA-12081: UPDATE operation NOT allowed ON TABLE "SCOTT"."T_READ"

Likewise INSERT statement; same exception would be raised for UPDATE and DELETE operations on the table T_READ. Similar to DML operations, few DDL operations like TRUNCATE and ALTER are also restricted for their user.

Code [5]
SQL> TRUNCATE TABLE T_READ;
TRUNCATE TABLE T_READ
               *
ERROR at line 1:
ORA-12081: UPDATE operation NOT allowed ON TABLE "SCOTT"."T_READ"

Note the exception number and error message is same for all the restricted actions, DML or DDL on the table.

The ALTER command below attempts to add a column COL3 to the T_READ. The ALTER command fails since T_READ is Read-Only table.

Code [6]
SQL> ALTER TABLE T_READ 
     ADD COL3 NUMBER;
ALTER TABLE T_READ
*
ERROR at line 1:
ORA-12081: UPDATE operation NOT allowed ON TABLE "SCOTT"."T_READ"

But the DDL commands, which are not concerned with the data and structure of the table execute successfully. The ANALYZE, RENAME, and DROP commands execute successfully on READ-ONLY tables.

Code [7]
SQL> ANALYZE TABLE T_READ COMPUTE STATISTICS;
 
TABLE analyzed.
 
SQL> RENAME T_READ1 TO T_READ;
 
TABLE renamed.

Indexes can be created on the columns of READ-ONLY tables. These indexes are used by the optimizer in SELECT statements using READ-ONLY tables.

Code [8]
SQL> CREATE INDEX IDX_READ ON T_READ (COL1);
 
INDEX created.

Similarly, partitions can be created, DDL commands like DROP

READ-ONLY Mode Switch

A table in READ-ONLY mode can be switched back to READ WRITE mode using ALTER TABLE command.

Code [9]
SQL> ALTER TABLE T_READ READ WRITE;
 
TABLE altered.

The READ_ONLY status of the T_READ table in USER_TABLES is changed to ‘NO’.

Code [10]
SQL> SELECT TABLE_NAME, READ_ONLY FROM USER_TABLES WHERE TABLE_NAME='T_READ';
 
TABLE_NAME                     REA
------------------------------ ---
T_READ                         NO

Since T_READ is now a normal read/write supported table, data can be easily inserted, modified or deleted.

Code [11]
SQL> INSERT INTO T_READ VALUES (1,'INSERT');
 
1 ROW created.
 
SQL> INSERT INTO T_READ VALUES (2,'INSERT');
 
1 ROW created.
 
SQL> UPDATE T_READ
     SET COL2 = 'INSERT'||COL1;
 
2 ROWS updated.
 
SQL> COMMIT;
 
Commit complete.

The mode can be toggled over to READ-ONLY mode to restrict further data changes.

Code [12]
SQL> ALTER TABLE T_READ READ ONLY;
 
TABLE altered.
 
SQL> SELECT * FROM T_READ;
 
      COL1 COL2
---------- ----------
         1 INSERT1
         2 INSERT2

Applications of Read-Only Tables

Read-Only tables provide a secure way to ensure data integrity and data access. On a highly secure production system, a table containing confidential data can be declared READ ONLY, so as to make sure that data can only be selected, but not frequently modified.

« « People Skills – Why Negotiation Skills are Important
Oracle 11g Subprogram Overloading » »

Author Description

Avatar

Free Training

RSSSubscribe 394 Followers
  • Popular
  • Recent
  • Manage Oracle dependencies

    August 16, 2011 - 0 Comment
  • Oracle 11g Virtual Columns

    May 30, 2011 - 0 Comment
  • UTL_FILE

    August 10, 2011 - 0 Comment
  • Oracle 11g Invisible Index

    May 27, 2011 - 0 Comment
  • Compound Triggers in Oracle 11g

    May 9, 2011 - 0 Comment
  • Oracle 11g Collections

    June 2, 2011 - 0 Comment
  • Oracle VPD implementation

    October 24, 2011 - 0 Comment
  • Oracle 11g Result Cache

    April 28, 2011 - 0 Comment
  • PL/Scope and PL/SQL Hierarchical Profiler

    November 6, 2011 - 0 Comment
  • Oracle 11g Subprogram Overloading

    June 17, 2011 - 0 Comment
  • Oracle XML Storage

    June 19, 2012 - 0 Comment
  • Oracle Analytic Enhancements

    June 19, 2012 - 0 Comment
  • Oracle Partitioning Overview

    January 17, 2012 - 0 Comment
  • PL/Scope and PL/SQL Hierarchical Profiler

    November 6, 2011 - 0 Comment
  • Oracle VPD implementation

    October 24, 2011 - 0 Comment
  • Oracle 11g SecureFiles

    September 4, 2011 - 0 Comment
  • Manage Oracle dependencies

    August 16, 2011 - 0 Comment
  • UTL_FILE

    August 10, 2011 - 0 Comment
  • Oracle Pragma

    July 6, 2011 - 0 Comment
  • Conditional compilation in Oracle PL/SQL

    June 28, 2011 - 0 Comment

Exforsys e-Newsletter

ebook
 

Related Articles

  • Oracle XML Storage
  • Oracle Analytic Enhancements
  • Oracle Partitioning Overview
  • PL/Scope and PL/SQL Hierarchical Profiler
  • Oracle VPD implementation

Latest Articles

  • Project Management Techniques
  • Product Development Best Practices
  • Importance of Quality Data Management
  • How to Maximize Quality Assurance
  • Utilizing Effective Quality Assurance Strategies
  • Sitemap
  • Privacy Policy
  • DMCA
  • Trademark Information
  • Contact Us
© 2023. All Rights Reserved.IT Training and Consulting
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.AcceptReject Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT