admin 管理员组文章数量: 887032
2024年1月15日发(作者：socketpro安卓版)
BK_USER VARCHAR2(30),BK_SID NUMBER not null,BK_SERIAL NUMBER,BK_WAIT_EVENT VARCHAR2(64),BK_WAIT_CLASS VARCHAR2(64),BK_APP VARCHAR2(48),BK_MACHINE VARCHAR2(64),BK_OS_USER VARCHAR2(30),BK_SQL_ID VARCHAR2(13),BK_SQL_TEXT VARCHAR2(1000),WT_USER VARCHAR2(30),WT_SID NUMBER not null,WT_SERIAL NUMBER,WT_WAIT_EVENT VARCHAR2(64),WT_WAIT_CLASS VARCHAR2(64),WT_APP VARCHAR2(48),WT_MACHINE VARCHAR2(64),WT_OS_USER VARCHAR2(30),WT_SQL_ID VARCHAR2(13),WT_SQL_TEXT VARCHAR2(1000),LOCK_TYPE VARCHAR2(26),MODE_HELD VARCHAR2(40),MODE_REQUESTED VARCHAR2(40),LOCK_ID1 VARCHAR2(40) not null,LOCK_ID2 VARCHAR2(40) not null,BLOCKING_OTHERS VARCHAR2(40),BK_TIME DATE default sysdate not null)tablespace DBADMINpctfree 10initrans 1maxtrans 255storage(
initial 64Knext 1Mminextents 1maxextents unlimited);-- Create/Recreate primary, unique and foreign key constraintsalter table DB_BLOCK_RECORDadd constraint PK_DB_BLOCK_RECOR primary key (BK_SID, WT_SID, LOCK_ID1, LOCK_ID2)using indextablespace DBADMINpctfree 10initrans 2maxtrans 255storage(initial 64Knext 1Mminextents 1maxextents unlimited);2.创建存储过程create or replace procedure proc_DB_BLOCK_RECORD asv_num number;v_ROWS number;v_count number;cursor v_CURSOR isSELECT me DB_User,me BK_User, BK_SID,# BK_SERIAL, BK_WAIT_EVENT,_CLASS BK_WAIT_CLASS,m BK_App,e BK_Machine, BK_OS_User,_ID BK_SQL_ID,_TEXT BK_SQL_TEXT,me WT_User, WT_SID,# WT_SERIAL, WT_WAIT_EVENT,_CLASS WT_WAIT_CLASS,m WT_App,e WT_Machine, WT_OS_User,
_ID WT_SQL_ID,_TEXT WT_SQL_TEXT,DECODE (,'MR', 'Media Recovery','RT', 'Redo Thread','UN', 'USER Name','TX', 'Row Locks','TM', 'Table Locks','UL', 'PL/SQL USER LOCK','DX', 'Distributed Xaction','CF', 'Control FILE','IS', 'Instance State','FS', 'FILE SET','IR', 'Instance Recovery','ST', 'Disk SPACE Transaction','TS', 'Temp Segment','IV', 'Library Cache Invalidation','LS', 'LOG START OR Switch','RW', 'ROW Wait','SQ', 'Sequence Number','TE', 'Extend TABLE','TT', 'Temp TABLE',) lock_type,DECODE (,0, 'None',1, 'NULL',2, 'ROW-S (SS)',3, 'ROW-X (SX)',4, 'SHARE',5, 'S/ROW-X (SSX)',6, 'EXCLUSIVE',TO_CHAR ()) mode_held,DECODE (t,
0, 'None',1, 'NULL',2, 'ROW-S (SS)',3, 'ROW-X (SX)',4, 'SHARE',5, 'S/ROW-X (SSX)',6, 'EXCLUSIVE',TO_CHAR (t)) mode_requested,TO_CHAR (1) lock_id1, TO_CHAR (2) lock_id2,DECODE(,0, 'NOT Blocking', /**//* Not blocking any other processes */1, 'Blocking', /**//* This lock blocks other processes */2, 'Global', /**//* This lock is global, so we can't tell */TO_CHAR ()) blocking_others ,sysdateFROM v$lock hk, v$session bs, v$lock wk, v$session ws,v$sqlarea saWHERE = 1AND != 0AND != 1AND t != 0AND (+) = D 1(+) = 1AND 2(+) = 2AND = (+)AND = (+)AND (me IS NOT NULL)AND (me <> 'SYSTEM')AND (me <> 'SYS')and _ID=_IDORDER BY 4,11,23,24;beginselect count(1) into v_num from dba_blockers;
v_rows := 0;if v_num > 0 thenfor varA in v_CURSOR loopinsert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(DB_BLOCK_RECORD,PK_DB_BLOCK_RECOR) */into _BLOCK_RECORDvalues(_User,_User,_SID,_SERIAL,_WAIT_EVENT,_WAIT_CLASS,_App,_Machine,_OS_User,_SQL_ID,_SQL_TEXT,_User,_SID,_SERIAL,_WAIT_EVENT,_WAIT_CLASS,_App,_Machine,_OS_User,_SQL_ID,_SQL_TEXT,_type,_held,_requested,_id1,_id2,ng_others,e);--insert into _BLOCK_RECORD_temp--values(_SID,_SERIAL,_SID,_SERIAL,_id1,_id2);v_rows := v_rows+1;end loop;commit;---select from _BLOCK_RECORD_temp where rownum<=v_rows;--DBMS__LINE(v_ROWS);DBMS__LINE(v_ROWS);elseDBMS__LINE(0);end if;end;3.定时任务cat db_block_#!/bin/bashsource /home/oracle/.bash_profilewhile truedosleep 1v_log=/u01/dba_scripts/db_block_record/log/db_block_
版权声明：本文标题：Oracle查询长时间执行sql,Oracle11g查询长时间运行的SQL 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.freenas.com.cn/jishu/1705309453h480565.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论