Example shows how “DISABLE TABLE LOCK” works

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 —…

List locks held by a session

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,…

Find locks on a table

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 ———-…

How to find sessions that are blocked?

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…

How to lock/unlock statistics on a table?

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…

How to find DDL lock on objects?

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;…