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 -- … Continue reading Example shows how “DISABLE TABLE LOCK” works
Tag: 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, … Continue reading List locks held by a session
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 ---------- … Continue reading Find locks on a table
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 … Continue reading How to find sessions that are blocked?
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 … Continue reading How to lock/unlock statistics on a table?
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; … Continue reading How to find DDL lock on objects?
alexlima.com 