[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