[20211230]完善sql_id指令碼.txt

[20211230]完善sql_id指令碼.txt

--//工作需要，有時候要抽取sql語句指令碼。我自己經常使用兩個指令碼：sqlid.sql 和 sql_id.sql。

$ cat sqlid.sql
column sql_fulltext format a160
column sqltext format a255
set head off

prompt
prompt --sql_id = &&1
prompt
select to_char(replace(sql_fulltext,chr(13),'')) sqltext  from v$sql where  sql_id = '&&1' and rownum<=1
union
select to_char(replace(sql_text,chr(13),'')) sqltext  from dba_hist_sqltext where  sql_id = '&&1' and rownum<=1;
set head on

$ 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;

--//為什麼要有兩個，主要原因一些開發僅僅使用chr(13)作為換行在PB下。導致我在sqlplus下經常看到的sql語句是亂的。
--//如果sql語句超長大於4000，我會直接使用sql_id.sql，但是輸出兩遍非常不好,必須重新改寫sql_id指令碼。

--//另外我不想輸出兩次，採用sqlid.sql指令碼，必須使用union但是必須加to_char.如果不加會報錯。

--//不加to_char使用union的情況：
SYS@127.0.0.1:17101/dyhis> @sql_id d3bxwwrcjp3tt
sql_id = d3bxwwrcjp3tt
select replace(sql_fulltext,chr(13),'') sqltext  from v$sql where  sql_id = 'd3bxwwrcjp3tt' and rownum<=1
       *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB

--//修改成union all可以透過，但是不是我需要的。但是無法解決sql語句超長問題。

> @sqlid d3bxwwrcjp3tt
select sql_id,hash_value,to_char(replace(sql_fulltext,chr(13),'')) sqltext  from v$sql where  sql_id = 'd3bxwwrcjp3tt' and rownum<=1
                         *
ERROR at line 1:
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 4552, maximum: 4000)

--//單獨執行如下沒有問題。
> select sql_id,hash_value,replace(sql_fulltext,chr(13),'') sqltext  from v$sql where  sql_id = 'd3bxwwrcjp3tt' and rownum<=1;
> select sql_id,hash_value,sql_fulltext sqltext  from v$sql where  sql_id = 'd3bxwwrcjp3tt' and rownum<=1;

--//根據需要修改如下：
$ 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

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;
        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;
            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

--//這樣基本可以滿足我工作需求。實際上使用replace也有現在，最大不能超過32767，一般很少出現怎麼超長的sql語句。
--//另外必須設定SET LINESIZE 32767 ， 不然輸出的sql語句僅僅輸出受linesize的限制。