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
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
Now connect to user with proper credentials to create a table and populate it with some data.
SQL> CONNECT DECIPHER/DECIPHER@ORCL
We can also verify user’s default tablespace by issuing following command.
SQL> SELECT DEFAULT_TABLESPACE FROM USER_USERS
2 WHERE USERNAME = ‘DECIPHER';
CREATE TABLE TEST
TEST_ID NUMBER(9) NOT NULL,
INSERT INTO TEST(TEST_ID,TEST_NAME)
WHERE ROWNUM <= 100
Now let us change the tablespace to read only by issuing ALTER TABLESPACE command.
SQL> ALTER TABLESPACE TEST_TB READ ONLY;
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;
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.