使用游标抓取锁的类型、锁的模式、被锁对象的名称
范例一：
|
declare v1 varchar(5); cursor c1 is select type from v$lock where sid=142; begin open c1; loop fetch c1 into v1; exit when c1%notfound; insert into t4 values(v1); commit; end loop; end; |
范例二：抓取锁的类型
|
declare v2 varchar(10); cursor c2 is select DECODE(LK.TYPE,'TX','Transaction','TM','DML','UL','PL/SQL User Lock',LK.TYPE) from v$lock lk; begin open c2; loop fetch c2 into v2; exit when c2%notfound; insert into t4 values(v2); commit; end loop; end; |
范例三：抓取锁的模式
|
declare v3 varchar(10); cursor c3 is select DECODE(LK.LMODE,0,'None',1,'Null',2,'Row-S (SS)',3,'Row-X (SX)',4,'Share',5,'S/Row-X (SSX)',6,'Exclusive',TO_CHAR(LK.LMODE)) from v$lock lk; begin open c3; loop fetch c3 into v3; exit when c3%notfound; insert into t4 values(v3); commit; end loop; end; |
范例四：抓取请求锁的 类型
|
declare v4 varchar(10); cursor c4 is select DECODE(LK.REQUEST,0,'None',1,'Null',2,'Row-S (SS)',3,'Row-X (SX)',4,'Share',5,'S/Row-X (SSX)',6,'Exclusive') from v$lock lk; begin open c4; loop fetch c4 into v4; exit when c4%notfound; insert into t4 values(v4); commit; end loop; end; |
范例五：联合抓取被锁的对象、锁定的模式
|
declare v3 varchar(10); v4 number; cursor c3 is select DECODE(LK.LMODE,0,'None',1,'Null',2,'Row-S (SS)',3,'Row-X (SX)',4,'Share',5,'S/Row-X (SSX)',6,'Exclusive',TO_CHAR(LK.LMODE)) from v$lock lk; cursor c4 is select id1 from v$lock where sid=142; begin open c3; open c4; for i in 1..100000 loop fetch c3 into v3; fetch c4 into v4; exit when c4%notfound; insert into t4 values(v3,v4); commit; end loop; end; |
范例六：模拟产生锁
|
[oracle@cuug ~]$ vi ct_lock.sql declare begin for i in 4..10000000 loop update t1 set n1=n1*1 where n1=1; end loop; end; / |
范例七：检查是否有锁
|
[oracle@cuug ~]$ vi chk_lock.sql declare v3 varchar(10); cursor c3 is select DECODE(LK.LMODE,0,'None',1,'Null',2,'Row-S (SS)',3,'Row-X (SX)',4,'Share',5,'S/Row-X (SSX)',6,'Exclusive',TO_CHAR(LK.LMODE)) from v$lock lk where sid=142; begin open c3; loop fetch c3 into v3; exit when c3%notfound; insert into t4 values(v3); commit; end loop; end; |
范例八： 模拟抓取锁 (完整脚本)
|
[oracle@cuug ~]$ vi 1510.sql declare v1 number; v2 number; v3 varchar(10); v4 varchar(50); cursor c1 is select lk.sid from v$locked_object lk_obj,dba_objects obj,v$lock lk where lk.sid=lk_obj.session_id and lk_obj.object_id=obj.object_id and lk.sid=142;
cursor c2 is select lk_obj.object_id from v$locked_object lk_obj,dba_objects obj,v$lock lk where lk.sid=lk_obj.session_id and lk_obj.object_id=obj.object_id and lk.sid=142;
cursor c3 is select obj.object_name from v$locked_object lk_obj,dba_objects obj,v$lock lk where lk.sid=lk_obj.session_id and lk_obj.object_id=obj.object_id and lk.sid=142;
cursor c4 is select DECODE(LK.LMODE,0,'None',1,'Null',2,'Row-S (SS)',3,'Row-X (SX)',4,'Share',5,'S/Row-X (SSX)',6,'Exclusive') lmode from v$locked_object lk_obj,dba_objects obj,v$lock lk where lk.sid=lk_obj.session_id and lk_obj.object_id=obj.object_id and lk.sid=142; begin open c1; open c2; open c3; open c4; loop fetch c1 into v1; fetch c2 into v2; fetch c3 into v3; fetch c4 into v4; exit when c3%notfound; insert into t4 values(v1,v2,v3,v4); commit; end loop; end; / |
范例九：收集索引的统计信息
|
[oracle@cuug ~]$ vi idx_stat.sql set linesize 190 col idx_name format a25 wrap col last_analyzed format a18 col avg_leaf_blocks_per_key heading 'AVG LEAF BLKS|PER KEY' col avg_data_blocks_per_key heading 'AVG DATA BLKS|PER KEY' col blev format 99
SELECT dis.blevel blev , dis.index_name idx_name , dis.leaf_blocks leaf_blks , dis.distinct_keys dst_keys , dis.avg_leaf_blocks_per_key , dis.avg_data_blocks_per_key , dis.clustering_factor clust_fact , dis.last_analyzed , dts.blocks tb_blks , dts.num_rows tb_rows FROM dba_ind_statistics dis JOIN dba_tab_statistics dts ON dis.table_name = dts.table_name WHERE dts.table_name = UPPER ('&input_table_name') AND dts.owner = UPPER ('&owner'); |
范例十：
|
select lk.sid,lk_obj.object_id,obj.object_name,DECODE(LK.LMODE,0,'None',1,'Null',2,'Row-S (SS)',3,'Row-X (SX)',4,'Share',5,'S/Row-X (SSX)',6,'Exclusive') lmode from v$locked_object lk_obj,dba_objects obj,v$lock lk where lk.sid=lk_obj.session_id and lk_obj.object_id=obj.object_id and lk.sid=142;
|
