— create a user with grant select privilege create user TEST_USR1 identified by TEST_USR1; grant create session to TEST_USR1; grant SELECT ON dba_raj.testing to test_usr1; with this select privilege, it can run select for update also. SESSION 1( connect with test_usr1) connect test_usr1/test_usr1 select * from dba_raj.testing for update; SESSION 2 ( Open another session as owner of the table): SQL> connect dba_raj/dba_raj Connected. SQL> show user USER is “DBA_RAJ” SQL> update dba_raj.testing set OBJECT_NAME=’TEST’ where owner=’SYS’; <<< it is stuck>> SESSION 3 ( check the blocking session ) : > Get the monitoring script DB MONITORING SCRIPT Get the monitoring script DB MONITORING SCRIPT SQL> @blocker INST_ID BLOCKING_SESSION SID SERIAL# SECONDS_IN_WAIT ———- —————- ———- ———- ————— 1 246 321 2709 30 SQL> @csql Enter value for 1: 321 old 2: (select sql_hash_value from gv$session where sid=&1) new 2: (select sql_hash_value from gv$session where sid=321) SQL_TEXT ——————————————————————————– update dba_raj.testing set OBJECT_NAME=’TEST’ where owner=’SYS’ SQL> @csql Enter value for 1: 246 old 2: (select sql_hash_value from gv$session where sid=&1) new 2: (select sql_hash_value from gv$session where sid=246) SQL_TEXT ——————————————————————————– select * from dba_raj.testing for update We have noticed that despite the user  TEST_USR1   has only GRANT SELECT privilege on dba_raj.testing table, it is able to lock the table .

— create user with grant read privilege create user TEST_USR2 identified by TEST_USR2; grant create session to TEST_USR2; grant READ ON dba_raj.testing to test_usr2; this user will be able to do select operation as usual: SQL> connect TEST_USR2/TEST_USR2 Connected. SQL> select count(*) from dba_raj.testing; COUNT(*) ———- 85812 Lets try the same command select for update.( It wont allow this operation) SQL> select * from dba_raj.testing for update; select * from dba_raj.testing for update * ERROR at line 1: ORA-01031: insufficient privileges With oracle 12, its time to revoke GRANT SELECT and use GRANT READ privilege Similar to GRANT SELECT ANY , we can use GRANT READ ANY  privilege