[20240314]建立完善sqlpatch.sql脚本.txt

[20240314]建立完善sqlpatch.sql脚本.txt --//以前写的脚本sql语句只能从v$sql视图抽取,但是有一些情况不在共享池,要从DBA_HIST_SQLTEXT抽取. --//纠正一些拼写错误.加入一些版本信息.如果在共享池改写为gv$sqlarea视图.  $ cat sqlpatchz.sql -- Copyright 2023 lfree. All rights reserved. -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions. -------------------------------------------------------------------------------- -- -- File name:   sqlpatch.sql -- Purpose:     sql patch -- -- Author:      lfree -- -- Usage: --     @sqlpatch <sql_id> 'hint_text' -- -- Example: --     @sqlpatch <sql_id> bind_aware --     @sqlpatch <sql_id> cursor_sharing_exact -- -- -------------------------------------------------------------------------------- prompt prompt input @sqlpatch <sql_id> 'hint_text' oracle_version(11 or 12) prompt drop sql patch ,run  exec sys.dbms_sqldiag.drop_sql_patch('sqlpatch_&1');; prompt display sql patch message , run @spext &1 prompt define noprint='noprint' set term off col tpt_version_old  &noprint new_value _tpt_version_old col tpt_version_new  &noprint new_value _tpt_version_new col tpt_comment1 &noprint new_value _tpt_comment1 col tpt_comment2 &noprint new_value _tpt_comment2 col tpt_noprint      &noprint new_value _tpt_noprint SELECT decode(cnt, 1,'',0,'--') tpt_comment1    ,decode(cnt,1,'--',0,'') tpt_comment2   FROM (SELECT count(*) cnt FROM gv$sqlarea WHERE sql_id = '&1' AND rownum = 1); WITH version AS (SELECT TO_NUMBER (SUBSTR (version, 1, 2)) v FROM v$instance) SELECT CASE WHEN v <= 11 THEN '' ELSE '--' END tpt_version_old       ,CASE WHEN v > 11  THEN '' ELSE '--' END tpt_version_new   FROM version; set term on declare    v_sql CLOB;    patch_name   VARCHAR2 (100); begin &&_tpt_comment1 select sql_fulltext into v_sql from gv$sqlarea   where sql_id='&1' and rownum=1; &&_tpt_comment2 select sql_text into v_sql from DBA_HIST_SQLTEXT where sql_id='&1' and rownum=1; &&_tpt_version_old   sys.dbms_sqldiag_internal.i_create_patch( &&_tpt_version_old      sql_text  => v_sql, &&_tpt_version_old      hint_text => '&2', &&_tpt_version_old      name      => 'sqlpatch_&1'); &&_tpt_version_new   patch_name := &&_tpt_version_new       sys.DBMS_SQLDIAG.create_sql_patch &&_tpt_version_new       ( &&_tpt_version_new          sql_text    => v_sql &&_tpt_version_new         ,hint_text   => '&2' &&_tpt_version_new         ,name        => 'sqlpatch_&1' &&_tpt_version_new       ); end; /