[20230210]建立完善swcnm.sql脚本.txt

[20230210]建立完善swcnm.sql脚本.txt --//遇到asm例子出现阻塞的情况.正常使用tpt的swc脚本无法查询.因为asm实例在nomount状态.with subquery报错. --//自己建立一个新的查询脚本.使用v$instance,v$thread作为子查询的子表. SYS@test> startup nomount ORACLE instance started. Total System Global Area  805306368 bytes Fixed Size                  8924064 bytes Variable Size             297796704 bytes Database Buffers          490733568 bytes Redo Buffers                7852032 bytes SYS@test> set verify off SYS@test> @ swc sid||','||SERIAL#||',@'||inst_id||','||event 1=1                               THEN ' ['||NVL((SELECT class FROM bclass WHERE r = s.p3),'undo @bclass '||s.p3)||']' ELSE null END,'ON CPU')                                                                 * ERROR at line 22: ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only --//主要里面使用with子查询,使用别名不对. --//改写如下: $ cat swcnm.sql prompt  @ swcnm sid||','||SERIAL#||',@'||inst_id||','||event 1=1 col path format a180 WITH     v$instance AS (SELECT /*+ INLINE */ class, ROWNUM r from v$waitstat)   , v$thread AS (     SELECT         REPLACE(SYS_CONNECT_BY_PATH(&1, '->'), '->', ' -> ')                ||CASE WHEN CONNECT_BY_ISLEAF = 1 AND ses.blocking_session IS NOT NULL                       THEN ' -> [idle blocker '||ses.blocking_instance||','||ses.blocking_session||' ('||ses.program||')]' ELSE NULL                     END path     FROM (         SELECT             s.*           , CASE WHEN s.type = 'BACKGROUND' AND s.program LIKE '%(DBW%)' THEN               '(DBWn)'             WHEN s.type = 'BACKGROUND' OR REGEXP_LIKE(s.program, '.*\([PJ]\d+\)') THEN               REGEXP_REPLACE(SUBSTR(s.program,INSTR(s.program,'(')), '\d', 'n')             ELSE                 '('||REGEXP_REPLACE(REGEXP_REPLACE(s.program, '(.*)@(.*)(\(.*\))', '\1'), '\d', 'n')||')'             END || ' ' program2           , NVL(s.event||CASE WHEN event like 'enq%' AND state = 'WAITING'                               THEN ' [mode='||BITAND(p1, POWER(2,14)-1)||']'                               WHEN s.event IN (SELECT name FROM v$event_name WHERE parameter3 = 'class#')                               THEN ' ['||NVL((SELECT class FROM v$instance WHERE r = s.p3),'undo @bclass '||s.p3)||']' ELSE null END,'ON CPU')                        || ' ' event2           , TO_CHAR(CASE WHEN state = 'WAITING' THEN p1 ELSE null END, '0XXXXXXXXXXXXXXX') p1hex           , TO_CHAR(CASE WHEN state = 'WAITING' THEN p2 ELSE null END, '0XXXXXXXXXXXXXXX') p2hex           , TO_CHAR(CASE WHEN state = 'WAITING' THEN p3 ELSE null END, '0XXXXXXXXXXXXXXX') p3hex         FROM             gv$session s     ) ses     CONNECT BY NOCYCLE         (    PRIOR ses.blocking_session  = ses.sid          AND PRIOR ses.blocking_instance = ses.inst_id         )     START WITH (ses.state='WAITING' AND ses.wait_class!='Idle') AND &2 ) SELECT     COUNT(*) sessions   , path FROM     v$thread GROUP BY     path ORDER BY     sessions DESC / --//例子: @ swcnm sid||','||SERIAL#||',@'||inst_id||','||event 1=1 --//简单测试: --//先在nomunt下测试: SYS@test> @ swcnm sid||','||SERIAL#||',@'||inst_id||','||event 1=1 @ swcnm sid||','||SERIAL#||',@'||inst_id||','||event 1=1 no rows selected --//ok没有问题,进入open模式,继续测试: SCOTT@test01p> @ ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0 --//session 1: SCOTT@test01p> @ spid        SID    SERIAL# PROCESS                  SERVER    SPID                     PID  P_SERIAL# C50 ---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------        169      61475 9544:1400                DEDICATED 12952                     26          4 alter system kill session '169,61475' immediate; SCOTT@test01p> create table t ( a number); Table created. SCOTT@test01p> insert into t values (1); 1 row created. SCOTT@test01p> commit ; Commit complete. SCOTT@test01p> update t set a=1; 1 row updated. --//session 2: SCOTT@test01p> @ spid        SID    SERIAL# PROCESS                  SERVER    SPID                     PID  P_SERIAL# C50 ---------- ---------- ------------------------ --------- -------------------- ------- ---------- --------------------------------------------------        178      25684 8384:7108                DEDICATED 3004                      42          7 alter system kill session '178,25684' immediate; SCOTT@test01p> update t set a=2; --//挂起!! --//session 1: SCOTT@test01p> @ swcnm sid||','||SERIAL#||',@'||inst_id||','||event 1=1 @ swcnm sid||','||SERIAL#||',@'||inst_id||','||event 1=1   SESSIONS PATH ---------- -----------------------------------------------------------------------------------------------          1  -> 178,25684,@1,enq: TX - row lock contention -> 169,61475,@1,SQL*Net message to client          1  -> 178,25684,@1,enq: TX - row lock contention