Systems Engineering and RDBMS

Read Only tablespaces

Posted by decipherinfosys on October 17, 2007

At times, we have to restrain users from updating certain data or inserting new data in certain tables. This can be achieved in a couple of different ways. We can give only ‘SELECT’ privilege to the user and revoke all other privileges from the user on the specific table. Another way of achieving this is to create a read only tablespace in which those table(s) reside. As we make tablespace itself read only, nobody will be able to update/delete or insert data into any of the table. Another good thing is that we can switch back tablespace to READ WRITE mode once we are done with our activity.  Let us start with creating tablespace and a table with some data in it. We are assuming that you have the correct privileges to issue the create tablespace command.

SQL> CREATE TABLESPACE TEST_TB
2  DATAFILE ‘C:\oracle\oradata\orcl\TEST_TB_01.DBF’ SIZE 500M
3  /

Tablespace created.

We are changing user’s default tablespace to TEST_TB. In our case username is decipher. It may be different for your scenario.

SQL> ALTER USER DECIPHER DEFAULT TABLESPACE TEST_TB
2  /

Now connect to user with proper credentials to create a table and populate it with some data.
SQL> CONNECT DECIPHER/DECIPHER@ORCL
Connected.

We can also verify user’s default tablespace by issuing following command.

SQL> SELECT DEFAULT_TABLESPACE FROM USER_USERS
2   WHERE USERNAME = ‘DECIPHER’;

DEFAULT_TABLESPACE
——————————
TEST_TB

CREATE TABLE TEST
(
TEST_ID NUMBER(9) NOT NULL,
TEST_NAME VARCHAR(30),
TEST_DATE DATE
)
/

INSERT INTO TEST(TEST_ID,TEST_NAME)
SELECT ROWNUM,OBJECT_NAME
FROM DBA_OBJECTS
WHERE ROWNUM <= 100
/

Now let us change the tablespace to read only by issuing ALTER TABLESPACE command.

SQL> ALTER TABLESPACE TEST_TB READ ONLY;

Tablespace altered.

Since tablespace is altered in READ ONLY mode, any DML operation on the table will fail with an error. Let us try to delete records from the table.

SQL> DELETE FROM TEST;
DELETE FROM TEST
*
ERROR at line 1:
ORA-00372: file 6 cannot be modified at this time
ORA-01110: data file 6: ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST_TB_01.DBF’

We will get the same error when we try to update or insert the records in the table. This is one of the ways to give READ ONLY access to specific group of people.  We can revert back the READ ONLY change and can make tablespace back to normal where we can make updates to the table. In order to make tablespace writable we have to issue following command.

SQL> ALTER TABLESPACE TEST_TB READ WRITE;

Tablespace altered.

Now performing delete or update on TEST table will go through without any errors.

SQL> DELETE FROM TEST;

100 rows deleted.

In fact we can check the status of the tablespace in USER_TABLESPACE to determine whether it is read only or not.

One Response to “Read Only tablespaces”

  1. […] by decipherinfosys on October 22nd, 2007 In one of our previous blog post, we had discussed how to go about making tablespaces read only. In this blog post, we will discuss […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: