This content has been marked as final. Show 11 replies
I know this i am asking about any body is having any scripts for this so it will chk automatically.
column oracle_username format a15
column os_user_name format a15
column object_name format a37
column object_type format a37
select a.session_id,a.oracle_username, a.os_user_name, b.owner "OBJECT OWNER", b.object_name,b.object_type,a.locked_mode from
(select object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a,
(select object_id, owner, object_name,object_type from dba_objects) b
Check this, it may useful
thnx its working fine.
Here is a version with SID and serial# data:
set linesize 150;
set head on;
col sid_serial form a13
col ora_user for a15;
col object_name for a35;
col object_type for a10;
col lock_mode for a15;
col last_ddl for a8;
col status for a10;
break on sid_serial;
SELECT l.session_id||','||v.serial# sid_serial,
2, 'Row-S (SS)',
3, 'Row-X (SX)',
5, 'S/Row-X (SSX)',
FROM dba_objects o, gv$locked_object l, v$session v
WHERE o.object_id = l.object_id
order by 2,3;
As usual a DBA can see whether the table has been locked or not. But can a normal user can know whether the table has been locked before doing the dml operation on it.
Normally if the table is in lock mode and the user tries to do dml operation then he have to wait untill the lock has been released or he may face deadlock . So before the operation is there any chance to know whether the object is in lock mode or not.
Thanks all to help here.
AFAIK the only option to check whether the object is locked or not is from dba views. FRom the user's side,there is no view that can reveal this information.I guess this has to be designed in the application itself that the user should try to lock with the select...for update.This will throw ora-54 that can be trapped and user can be given some message instead of the wait.Just a workaround fromthe apps part is possible but I doubt that thereis some thing that oracle gives by default.
One thing is locked objects and other thing is blocking locks....
AFAIK, normal user should never be woried about locks....