实战:oracle巡检脚本v1(三)
行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 725.txt
column sql_text format a35 heading "SQL text"
column blocking_user format a8 Heading "Blocking|user"
column blocked_user format a8 heading "Blocked|user"
column blocking_sid format 9999 heading "Blocking|SID"
column blocked_sid format 9999 heading "Blocked|SID"
column type format a4 heading "Lock|Type"
WITH sessions AS
(SELECT /*+ materialize*/ username,sid,sql_id
FROM v\$session),
locks AS
(SELECT /*+ materialize */ *
FROM v\$lock)
SELECT l2.type,s1.username blocking_user, s1.sid blocking_sid,
s2.username blocked_user, s2.sid blocked_sid, sq.sql_text
FROM locks l1
JOIN locks l2 USING (id1, id2)
JOIN sessions s1 ON (s1.sid = l1.sid)
JOIN sessions s2 ON (s2.sid = l2.sid)
LEFT OUTER JOIN v\$sql sq
ON (sq.sql_id = s2.sql_id)
WHERE l1.BLOCK = 1 AND l2.request >
0;
spool off
exit;
!01
cechon "7.25 who waiting who is: " red
echo
cat 725.txt
echo
rm -rf 725.txt