[20260107]建立完善b5.sql脚本.txt

[20260107]建立完善b5.sql脚本.txt --//以前写的生成执行SQL语句的执行脚本b5.sql，存在1个问题，就是无法抓取timestamp类型，适当修改解决此问题，并做了适当调整。 --//比如改用gv$sqlarea视图并且取一条记录，当然缺点就是如果不在共享池，无法抓取sql语句。 --//不过该脚本我个人很少使用,当然还是存在许多问题，to_char(sql_fulltext)超过4000个字符报错. $ cat b5.sql /* Formatted on 2026-01-08 17:03:13 (QP5 v5.277) */ SET FEEDBACK OFF SET HEAD OFF SET VERIFY OFF DEFINE sql_id=&1 -- --Ascertain child_no , inst_id -- COL CHILD_NUMBER  NEW_VALUE CHILD_NO COL INST_ID   NEW_VALUE INST_ID OLD_VALUE INST_ID SET TERMOUT OFF SELECT child_number, inst_id   FROM GV$SQL  WHERE sql_id = '&&sql_id' AND ROWNUM = 1; SELECT child_number, inst_id   FROM GV$SQL_BIND_CAPTURE  WHERE sql_id = '&&sql_id' AND ROWNUM = 1; SET TERMOUT ON SPOOL &&1..sql5 SELECT 'set sqlblanklines on' txt FROM DUAL UNION ALL SELECT 'alter session set current_schema=' || PARSING_SCHEMA_NAME || ';' txt   FROM gv$sqlarea  WHERE sql_id = '&&sql_id' AND ROWNUM = 1 UNION ALL SELECT 'alter session set statistics_level=all;' txt FROM DUAL; SELECT '--@10046on 12' txt FROM DUAL; SELECT 'begin' txt FROM DUAL UNION ALL SELECT    'execute immediate q'''        || CHR (7)        || (SELECT TO_CHAR (sql_fulltext) txt              FROM gv$sqlarea             WHERE sql_id = '&&1' AND ROWNUM = 1)        || CHR (7)        || ''' using'           txt   FROM DUAL UNION ALL --SELECT LISTAGG ( DECODE ( DATATYPE_STRING ,'NUMBER', value_string --            ,'DATE', '''' || TO_CHAR ( TO_DATE (value_string, 'mm/dd/yy hh24:mi:ss') ,'yyyy/mm/dd hh24:mi:ss') || '''' --            ,'TIMESTAMP', '''' ||TO_CHAR ( ANYDATA.accesstimestamp (value_anydata) ,'yyyy/mm/dd hh24:mi:ss.ff9')|| '''' --            ,'''' || value_string || '''') ,CHR (10) || ',') WITHIN GROUP (ORDER BY POSITION) --          txt --  FROM gv$sql_bind_capture -- WHERE     sql_id = '&&sql_id' --      AND child_number = &&child_no --       AND inst_id = &&inst_id --       AND was_captured = 'YES' --       AND DUP_POSITION IS NULL SELECT LISTAGG (              --CASE  WHEN (datatype_string='NUMBER' or value_string='NULL') THEN NULL ELSE '' END ||              CASE datatype_string                 WHEN 'NUMBER'                 THEN                    DECODE (value_string, 'NULL', NULL, NVL (value_string, '00'))                 WHEN 'DATE'                 THEN                       'TO_DATE('''                    || TO_CHAR (TO_DATE (value_string, 'mm/dd/yy hh24:mi:ss'), 'yyyy/mm/dd hh24:mi:ss')                    || ''',''yyyy/mm/dd hh24:mi:ss'')'                 WHEN 'TIMESTAMP'                 THEN                       'TO_TIMESTAMP('''                    || TO_CHAR (ANYDATA.accesstimestamp (value_anydata), 'yyyy/mm/dd hh24:mi:ss.ff9')                    || ''',''yyyy/mm/dd hh24:mi:ss.ff9'')'                 ELSE                    '''' || DECODE (value_string, 'NULL', NULL, NVL (value_string, '00')) || ''''              END           || '  --  '           || name          --|| CASE  WHEN (datatype_string='NUMBER' or value_string='NULL') THEN NULL ELSE '' END          ,CHR (10) || ',')        WITHIN GROUP (ORDER BY POSITION)           txt   FROM v$sql_bind_capture  WHERE sql_id = '&&1' AND child_number = &&child_no AND was_captured = 'YES' AND DUP_POSITION IS NULL UNION ALL SELECT ';' txt FROM DUAL UNION ALL SELECT 'end;' txt FROM DUAL UNION ALL SELECT '/' txt FROM DUAL; SELECT '@dpc &sql_id outline ''''' txt FROM DUAL; SELECT 'set sqlblanklines off' txt FROM DUAL UNION ALL SELECT 'rollback;' txt FROM DUAL UNION ALL SELECT '--@10046off' txt FROM DUAL UNION ALL SELECT 'alter session set current_schema=' || USER || ' ;' txt   FROM DUAL; PROMPT SPOOL OFF UNDEF sql_id UNDEF child_no UNDEF inst_id SET FEEDBACK 6; SET HEAD ON SET LINESIZE 277