Grant command hanging for long time
Posted by Pavan DBA on April 2, 2017
Recently I have faced one issue regarding granting some privileges to the user.
I got a request to grant select on a table to a user. when I issued grant command, it just got hung.
Even though select doesn’t hold exclusive lock on a table, I verified in v$locked_object to check if any more information can be collected.
select object_id,session_id,oracle_username from v$locked_object;
OBJECT_ID SESSION_ID ORACLE_USERNAME
———- ———- —————
26187 18 SCOTT
select object_name,owner,object_id,object_type,status from dba_objects where object_id=26187;
OBJECT_NAME OWNER OBJECT_ID OBJECT_TYPE STATUS
———– —– ——— ———– ——
OBJ$ SYS 26187 TABLE VALID
From the above, it is evident that SCOTT user is holding a lock on SYS.OBJ$ table which actually caused the issue.
After confirmation from application team, I have killed the session with id 18. After that grant command got successful.
Hope this helps…
Pavan DBA's Blog 
Krishna reddy said
Hi pawan,
Can you provide oracle dba training.
Regards,
Krishna reddy
Pavan DBA said
Hi Krishna, for training related queries, plz contact TechMaster – +919676516545