If you’re wondering why particular query or procedure is talking a long such a long time to run; then make sure you check out whether the object you’re accessing is locked or not. There are quite a few dba views available to make our task less complicated. You can use the below query which will return you the list of locked objects.
SELECT c.owner, c.object_name, c.object_type, b.SID, b.serial#, b.status, b.osuser, b.machine FROM v$locked_object a, v$session b, dba_objects c WHERE b.SID = a.session_id AND a.object_id = c.object_id;
You can now decide whether to kill the job/ process or someother roundabout to remove the lock obtained on the particular object.