[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的限制。