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 — table locks disabled for T

-- shows one can’t truncate table as table locks are disable
SQL> truncate table t;
truncate table t
*
ERROR at line 1:
ORA-00069: cannot acquire lock — table locks disabled for T

-- DML is okay to run
SQL> delete from t;
1 row deleted.

-- new columns can be added
SQL> alter table t add tt varchar2(1);
Table altered.

-- columns can’t be modified
SQL> alter table t modify tt varchar2(10);
alter table t modify tt varchar2(10)
*
ERROR at line 1:
ORA-00069: cannot acquire lock — table locks disabled for T

-- columns can’t be dropped
SQL> alter table t drop column tt;
alter table t drop column tt
*
ERROR at line 1:
ORA-00069: cannot acquire lock — table locks disabled for T

-- enable locking on the table
SQL> alter table t enable table lock;
Table altered.

-- table now can be dropped
SQL> drop table t;
Table dropped.

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,
master.dbo.spt_values u
where master.dbo.syslockinfo.rsc_type = v.number
and v.type = 'LR'
and master.dbo.syslockinfo.req_status = x.number
and x.type ='LS'
and master.dbo.syslockinfo.req_mode + 1 = u.number
and u.type = 'L'
and rsc_dbid = db_id()
and req_spid = @@spid
order by spid

Example
BEGIN TRANSACTION
INSERT INTO TEST VALUES ( 1 )

Output
spid dbid ObjId tablename IndId Type Resource Mode Status
—— —— ———– ——————————— —— —- ————– ——– ——
52 5 0 NULL 0 DB S GRANT
52 5 2105058535 TEST 0 RID 1:143:0 X GRANT
52 5 2105058535 TEST 0 PAG 1:143 IX GRANT
52 5 2105058535 TEST 0 TAB IX GRANT

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

SQL> select * FROM V$lock where id1 = 13846;

ADDR KADDR SID TY ID1 ID2 LMODE
—————- —————- ———- — ———- ———- ———-
REQUEST CTIME BLOCK
———- ———- ———-
000000006934A5B0 000000006934A5D8 19 TM 13846 0 3
0 14775 0

Shows SID=19 has lock on the 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 it shows spid 53 is waiting for spid 52, the blocked value for spid 53 is 52.

spid blocked dbid open_tran status hostname loginame waittime waitresource login_time
—— ——- —— ——— —————————— ——————————————————————————————————————————– ——————————————————————————————————————————– ——————– —————————————————————————————————————————————————————————————————————————————————————- ———————–
52 0 1 1 runnable HOST amin 0 2010-03-20 10:38:38.903

53 52 1 0 suspended HOST amin 2914937 RID: 1:1:238:1 2010-03-20 11:40:19.530

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 shows how to lock table statistics and what happens when one tries to gather statistics on table that has statistics locked.

— create table
SQL> create table test ( x number );

Table created.

— create index
SQL> create index test_idx on test(x);

Index created.

— shows when stats is not locked the value of stattype_locked is NULL
SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'SCOTT';

STATT
—–
— lock statistics
SQL> exec dbms_stats.lock_table_stats('scott', 'test');

PL/SQL procedure successfully completed.

— shows when stats is locked the value of stattype_locked is ALL
SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'SCOTT';

STATT
—–
ALL

— try to gather statistics on locked table
SQL> exec dbms_stats.gather_index_stats('scott', 'test_idx');
BEGIN dbms_stats.gather_index_stats('scott', 'test_idx'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at “SYS.DBMS_STATS”, line 10640
ORA-06512: at “SYS.DBMS_STATS”, line 10664
ORA-06512: at line 1

— try to gather statistics on the index using analyze
SQL> analyze index ajaffer.test_idx compute statistics;
analyze index ajaffer.test_idx compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked

— unlock statistics
SQL> exec dbms_stats.unlock_table_stats('scott', 'test');

PL/SQL procedure successfully completed.

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;

Window 2)
— run after starting execution of store procedure p
— owner – owner of the lock
— session_id – session id of the lock
— name – object name
— type – object type
— mode_held – None (value when the store procedure is executing), Exclusive (value when store procedure is being compiled)
SQL> select * FROM dba_ddl_locks where name = ‘P’;

SESSION_ID OWNER NAME
———- —————————— ——————————
TYPE MODE_HELD MODE_REQU
—————————————- ——— ———
145 USER P
Table/Procedure/Type Null None

This view is not installed by default and can be created by executing the following $ORACLE_HOME/rdbms/admin/catblock.sql