Following example shows what happens when locks are disabled on the TABLE. -- disable lock on a table SQL> alter table t disable table lock; Table altered. -- shows one can’t drop table as table locks are disable SQL> drop table t; drop table t * ERROR at line 1: ORA-00069: cannot acquire lock — table locks disabled for T -- shows one can’t truncate … Continue reading Example shows how “DISABLE TABLE LOCK” works
To list locks held by a current session select convert (smallint, req_spid) As spid, rsc_dbid As dbid, rsc_objid As ObjId, object_name(rsc_objid) tablename, rsc_indid As IndId, substring (v.name, 1, 4) As Type, substring (rsc_text, 1, 16) as Resource, substring (u.name, 1, 8) As Mode, substring (x.name, 1, 5) As Status from master.dbo.syslockinfo, master.dbo.spt_values v, master.dbo.spt_values x, master.dbo.spt_values u where master.dbo.syslockinfo.rsc_type = v.number and v.type = 'LR' … Continue reading List locks held by a session
Using the step below one can find the locks on the table by looking for object id. Example: Window 1: SQL> update scott.test1 set x = 200; 16 rows updated. SQL> select userenv(‘sid’) from dual; USERENV(‘SID’) ————– 19 Window 2: SQL> select object_id from dba_objects where object_name = ‘TEST1’ and owner = ‘SCOTT’; OBJECT_ID ———- 13846 SQL> select * FROM V$lock where id1 = 13846; … Continue reading Find locks on a table
To find sessions that are blocking a session and blocked session, one can run the following SQL which works on SQLServer 2000 and SQL Server 2005. select spid, blocked, dbid, open_tran, status, hostname, loginame, waittime, waitresource, login_time from master..sysprocesses where spid in (select blocked from master..sysprocesses) or blocked != 0 Sample Ouptut: In this example it shows spid 53 is waiting for spid 52, the … Continue reading How to find sessions that are blocked?
In certain cases you may want to lock statistics in a table in certain cases, for example if you want a table not be analyzed by automatic statistics job but analyze it later or in cases where you want prevent from analyzing statistics in cases where data in the table doesn’t change. The following example shows how to lock table statistics and what happens when … Continue reading How to lock/unlock statistics on a table?
The following example shows how to find lock on a store procedure, the following store procedure has infinite loop. A store-procedure is locked when it’s running that it can’t be modified/recompiled when it’s executing. Window 1) SQL> create or replace procedure p as begin while true loop null; end loop; end; / SQL> exec p; Window 2) — run after starting execution of store procedure … Continue reading How to find DDL lock on objects?