Systems Engineering and RDBMS

Archive for December 7th, 2007

Oracle 11g: ALTER TABLE READ ONLY

Posted by decipherinfosys on December 7, 2007

One more nice enhancement in Oracle 11g is the change that is made to the ALTER TABLE command. In prior versions, to make table read only we have to grant only SELECT privilege on the table. But table was still available for read and write to creator of the table. With this new enhancement to the ‘ALTER TABLE’ command, even owner/creator of the table will not be allowed to issue DML statement(s).

CREATE TABLE TEST
(
TEST_ID NUMBER(9) NOT NULL,
TEST_OBJECT VARCHAR(30),
CONSTRAINT PK_TEST PRIMARY KEY(TEST_ID)
)
/

INSERT INTO TEST(TEST_ID,TEST_OBJECT) VALUES(1,’DECIPHER’)
/

COMMIT;

Now let us make table READ ONLY.

ALTER TABLE TEST READ ONLY
/

Above command will put table in READ ONLY mode. We can check whether table is in READ ONLY mode or READ WRITE MODE by accessing USER_TABLES view.

SELECT table_name, read_only
FROM user_tables WHERE table_name = ‘TEST';
/

TABLE_NAME              READ_ONLY
———————– ———
TEST                    YES

Once table is in read only mode, no DML statement or DDL statement that affects the data (truncate) can be issued against it. Also SELECT FOR UPDATE is also prohibited.

SELECT *
FROM TEST FOR UPDATE NOWAIT;

OR

UPDATE TEST
SET TEST_OBJECT =’TEST’ WHERE TEST_ID = 1;

OR

TRUNCATE TABLE TEST;

Any of the above statement will result into following error.

ORA-12081: update operation not allowed on table “DECIPHER”.”TEST”

Even though table is set to READ ONLY mode, any DDL statement on underlying indexes will be successful. We can create new index on table in READ ONLY mode.

SQL> CREATE INDEX TEST_IND_1 ON TEST(TEST_OBJECT);

Index created.

We can put table back to READ WRITE mode by simply issuing following command.

ALTER TABLE TEST READ WRITE;

This can be a quick and useful alternative to provide read only access to users.

Posted in Oracle | Leave a Comment »

 
Follow

Get every new post delivered to your Inbox.

Join 78 other followers