[20241016]建立完善sql_id.sql脚本.txt

[20241016]建立完善sql_id.sql脚本.txt --//增加注解说明出处，增加一些无法显示的sql语句的显示，比如语法错误或者表根本不存在的sql语句，例子： SYS@book> @ sql_id 2utgtqyrt3vag -- SQL_ID = 2utgtqyrt3vag come from x$kglob -------------------------------------------------------------------------------- select count(1) from deptxxx; --//另外来之x$kglob的sql语句可能不对，例子： SYS@book> @ sql_id a3f28s02mdg78 -- SQL_ID = a3f28s02mdg78 SELECT snap_id, per_pdb , SQL_ID, PLAN_HASH_VALUE, CON_DBID FROM (SELECT /*+ use_hash(t1 t2) */ t2.snap_id, t1.con_id per_pdb , t1.SQLID_KEWRSPE  SQL_ID, t1.PLANHASH_KEWRSPE  PLAN_HASH_VALUE, con_id_to_dbid(t1.CON_ID)  CON_DBID FROM X$KEWRTSQLPLAN t1, WRH$_SQL_PLAN t2   WHERE t2.dbid(+)  = :dbid  AND t2.SQL_ID(+) = t1.SQLID_KEWRSPE AND t2.PLAN_HASH_VALUE(+) = t1.PLANHASH_KEWRSPE AND t2.CON_DBID(+) = con_id_to_dbid(t1.CON_ID) AND t2.ID(+) = 0) WHERE nvl(snap_id, 0) < :snap_id; --//开始在共享池，不知道为什么相关视图查询不到的情况下，查询x$kglob出现这样的情况。 SYS@book> @ sql_id a3f28s02mdg78 -- SQL_ID = a3f28s02mdg78 come from x$kglob -------------------------------------------------------------------------------- SELECT snap_id, per_; $ cat sql_id.sql --COLUMN SQL_FULLTEXT FORMAT A180 --COLUMN SQLTEXT FORMAT A255 -- --SELECT SQL_ID,HASH_VALUE,REPLACE(SQL_FULLTEXT,CHR(13),'') SQLTEXT FROM GV$SQLAREA WHERE SQL_ID='&1' AND ROWNUM=1; --PROMPT VIEW DBA_HIST_SQLTEXT --SELECT SQL_ID ,REPLACE(SQL_TEXT,CHR(13),'0')  SQLTEXT FROM DBA_HIST_SQLTEXT WHERE SQL_ID='&&1' AND ROWNUM=1; --SELECT SQL_ID,TO_CHAR(SQL_FULLTEXT) SQLTEXT FROM GV$SQLAREA WHERE SQL_ID='&1' AND ROWNUM=1 --UNION --SELECT SQL_ID,TO_CHAR(SQL_TEXT) SQLTEXT FROM DBA_HIST_SQLTEXT WHERE SQL_ID='&&1' AND ROWNUM=1; SET LINESIZE 32767 --SET LINESIZE 4000 VAR V_SQL_FULLTEXT CLOB COL SQL_FULLTEXT FOR A4000 WORD_WRAP SET FEEDBACK OFF SET SERVEROUTPUT ON --PROMPT --PROMPT -- SQL_ID = &&1 PROMPT DECLARE     V_SQL_FULLTEXT   CLOB;     V_COUNT          NUMBER; BEGIN     SELECT COUNT(*) INTO V_COUNT  FROM GV$SQLAREA WHERE SQL_ID = '&&1' AND ROWNUM=1;     IF  V_COUNT=1     THEN         SELECT REPLACE (SQL_FULLTEXT||';', CHR(13), '') SQL_FULLTEXT INTO V_SQL_FULLTEXT FROM GV$SQLAREA WHERE SQL_ID = '&&1' AND ROWNUM = 1;         --SELECT REPLACE (SQL_FULLTEXT||';', CHR(13), chr(13)) SQL_FULLTEXT INTO V_SQL_FULLTEXT FROM GV$SQLAREA WHERE SQL_ID = '&&1' AND ROWNUM = 1;         DBMS_OUTPUT.PUT_LINE ('-- SQL_ID = &1 come from shared pool'||chr(10));         DBMS_OUTPUT.PUT_LINE (V_SQL_FULLTEXT);     ELSE         SELECT COUNT(*)  INTO V_COUNT  FROM DBA_HIST_SQLTEXT WHERE SQL_ID='&&1' AND ROWNUM=1;         IF  V_COUNT=1         THEN             SELECT REPLACE (SQL_TEXT||';',CHR(13),'')  INTO V_SQL_FULLTEXT  FROM DBA_HIST_SQLTEXT WHERE SQL_ID='&&1' AND ROWNUM=1;             --SELECT REPLACE (SQL_TEXT||';',CHR(13),chr(13))  INTO V_SQL_FULLTEXT  FROM DBA_HIST_SQLTEXT WHERE SQL_ID='&&1' AND ROWNUM=1;             DBMS_OUTPUT.PUT_LINE ('-- SQL_ID = &1 come from awr'||chr(10));             DBMS_OUTPUT.PUT_LINE (V_SQL_FULLTEXT);         END IF;     END IF;     IF  V_COUNT=0         THEN         SELECT COUNT(*)  INTO V_COUNT  FROM x$kglob WHERE kglobt03='&&1' AND ROWNUM=1;         IF  V_COUNT=1         THEN             SELECT REPLACE (kglnaobj||';',CHR(13),'')  INTO V_SQL_FULLTEXT  FROM x$kglob WHERE kglobt03='&&1' AND ROWNUM=1;             --SELECT REPLACE (kglnaobj||';',CHR(13),chr(13))  INTO V_SQL_FULLTEXT  FROM x$kglob WHERE kglobt03='&&1' AND ROWNUM=1;             DBMS_OUTPUT.PUT_LINE ('-- SQL_ID = &1 come from x$kglob'||chr(10));             DBMS_OUTPUT.PUT_LINE (V_SQL_FULLTEXT);         END IF;     END IF;     EXCEPTION WHEN NO_DATA_FOUND THEN         NULL; END; / PROMPT SET SERVEROUTPUT OFF SET FEEDBACK 6 SET LINESIZE 277