Pavan DBA's Blog

The DBA Knowledge Store

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…

Advertisements

2 Responses to “Grant command hanging for long time”

  1. Krishna reddy said

    Hi pawan,
    Can you provide oracle dba training.
    Regards,
    Krishna reddy

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: