Oracle Make Table Read Only

Oracle Make Table Read Only | In this post, we will learn to make the table read-only. A table is read-only means the user can only access/read data from the table but they can’t write/edit/update data in the table. Users will have only read permissions but not write and update permissions.

We can use the ALTER command to make the table read-only. Now let’s create a table “books” and insert some values to the table for demonstration.

CREATE TABLE books (
  id INT,
  bookname VARCHAR(10)
);

INSERT INTO books VALUES(1, 'The Alchemist');
INSERT INTO books VALUES(2, 'The Little Prince');
SELECT * FROM books;

Now let us make the table “books” as read-only by using the alter command.

The query for Oracle Make Table Read Only

ALTER TABLE books read only;

Output:-

Table altered.

Now the written permission is removed. let us check if the table can be edited /altered or inserted with any date.

INSERT INTO books VALUES(2, 'One Indian Girl');

When we tried to insert some data into the books table, we get the following error in the console windows:-

ORA-12081: update operation not allowed on table “SQL_LPFKYYHFWJAGKBSNAQDUPOVHU”.”BOOKS”

In the above result, we can say that if the permission is denied to write then we cannot write any data to that table. Any operations that can update, or delete the data or that affect the table will be denied. Let us consider another example to update the same table.

UPDATE books SET id=1;

ORA-12081: update operation not allowed on table “SQL_DJXKJYCFZOVSHQBCAGHTKZOCP”.”BOOKS”

In the above results, we can see that we cannot update the books table. Once the table is altered to read & write then only we can perform these operations.

We will now see how to make the table from read-only to read-write.

The query for Oracle Make Table Read Write

ALTER TABLE books read write;

Output:-

Table altered.

From the above result, we can say that the Table is now ready for performing the write operation.

We’ll see in the below query to delete the data in the table books.

DELETE FROM books;

Output:-

2 rows deleted.

Now we can say from the above result the table can be performed write operations. You can also try to insert some data into the book table. This brings us to the end of the discussion about Oracle make table read only. Also see:- Truncate AUD$ table in Oracle 12c

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Search