[20220330]编写sql打补丁的脚本.txt

[20220330]编写sql打补丁的脚本.txt --//给sql语句打补丁适用包不同，必须执行不同的命令，我将两者合在一起，写一个脚本。 $ cat sqlpatch.sql prompt prompt input @sqlpatch sqlid 'hint_text' oracle_version(11 or 12) prompt drop sql patch ,run  exec sys.dbms_sqldiag.drop_sql_patch('sqlpatch_&1');; prompt display sql path 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_noprint      &noprint new_value _tpt_noprint 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    select sql_fulltext into v_sql from v$sql 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; / --//测试看看。 1.测试一： SCOTT@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SCOTT@book> select /*+ full(emp) */  * from emp where empno=111; no rows selected SCOTT@book> @ hash HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------  297818799 8skxyzn8w0qpg            0      23215      3956160932  11c05aaf  2022-03-30 10:02:08    16777222 SYS@book>  @ sqlpatch 8skxyzn8w0qpg 'INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))' input @sqlpatch sqlid 'hint_text' oracle_version(11 or 12) drop sql patch ,run  exec sys.dbms_sqldiag.drop_sql_patch('sqlpatch_8skxyzn8w0qpg'); display sql path message , run @spext 8skxyzn8w0qpg PL/SQL procedure successfully completed. --//注：必须以用户执行。 SCOTT@book> select /*+ full(emp) */  * from emp where empno=111; no rows selected SCOTT@book> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  8skxyzn8w0qpg, child number 0 ------------------------------------- select /*+ full(emp) */  * from emp where empno=111 Plan hash value: 2949544139 --------------------------------------------------------------------------------------- | Id  | Operation                   | Name   | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | --------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |        |        |       |     1 (100)|          | |   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |      1 |    39 |     1   (0)| 00:00:01 | |*  2 |   INDEX UNIQUE SCAN         | PK_EMP |      1 |       |     0   (0)|          | --------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / EMP@SEL$1    2 - SEL$1 / EMP@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("EMPNO"=111) Note -----    - SQL patch "sqlpatch_8skxyzn8w0qpg" used for this statement    - Warning: basic plan statistics not available. These are only collected when:        * hint 'gather_plan_statistics' is used for the statement or        * parameter 'statistics_level' is set to 'ALL', at session or system level SYS@book>  @spext 8skxyzn8w0qpg HINT                                                 NAME ---------------------------------------------------- ------------------------------ INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO")) sqlpatch_8skxyzn8w0qpg SYS@book>  exec sys.dbms_sqldiag.drop_sql_patch('sqlpatch_8skxyzn8w0qpg'); PL/SQL procedure successfully completed. 2.测试二： TTT@xxxx:1521/orcl> @ prxx ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 18.0.0.0.0 BANNER                        : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 BANNER_LEGACY                 : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. TTT@xxxx:1521/orcl> select /*+ full(emp) */  * from emp where empno=111; no rows selected TTT@xxxx:1521/orcl> @ hash HASH_VALUE SQL_ID                     CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID ---------- -------------------------- ------------ ---------- --------------- ---------- ------------------- -----------  297818799 8skxyzn8w0qpg                         0      23215      3956160932  11c05aaf  2022-03-30 10:05:10    16777216 SYS@xxxx:1521/orcl> @ sqlpatch 8skxyzn8w0qpg 'INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))' input @sqlpatch sqlid 'hint_text' oracle_version(11 or 12) drop sql patch ,run  exec sys.dbms_sqldiag.drop_sql_patch('sqlpatch_8skxyzn8w0qpg'); display sql path message , run @spext 8skxyzn8w0qpg PL/SQL procedure successfully completed. SYS@xxxx:1521/orcl> @ spext 8skxyzn8w0qpg HINT                                                 NAME ---------------------------------------------------- ------------------------------ INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO")) sqlpatch_8skxyzn8w0qpg TTT@xxxx:1521/orcl> select /*+ full(emp) */  * from emp where empno=111; no rows selected TTT@xxxx:1521/orcl> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  8skxyzn8w0qpg, child number 0 ------------------------------------- select /*+ full(emp) */  * from emp where empno=111 Plan hash value: 2949544139 --------------------------------------------------------------------------------------- | Id  | Operation                   | Name   | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | --------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |        |        |       |     1 (100)|          | |   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |      1 |    38 |     1   (0)| 00:00:01 | |*  2 |   INDEX UNIQUE SCAN         | PK_EMP |      1 |       |     0   (0)|          | --------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / EMP@SEL$1    2 - SEL$1 / EMP@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("EMPNO"=111) Note -----    - SQL patch "sqlpatch_8skxyzn8w0qpg" used for this statement    - Warning: basic plan statistics not available. These are only collected when:        * hint 'gather_plan_statistics' is used for the statement or        * parameter 'statistics_level' is set to 'ALL', at session or system level SYS@xxxx:1521/orcl> exec sys.dbms_sqldiag.drop_sql_patch('sqlpatch_8skxyzn8w0qpg'); PL/SQL procedure successfully completed. 3.附上spext.sql脚本： $ cat spext.sql /* Formatted on 2015/4/10 17:03:49 (QP5 v5.252.13127.32867) */ column hint format a200 column name format a30 SELECT EXTRACTVALUE (VALUE (h), '.') AS hint,so.name   FROM SYS.sqlobj$data od       ,SYS.sqlobj$ so       ,TABLE        (           XMLSEQUENCE           (              EXTRACT (XMLTYPE (od.comp_data), '/outline_data/hint')           )        ) h  WHERE    ( so.NAME in ( 'profile &&1', 'tuning &&1','switch tuning &&1') or lower(so.name) like lower('%&&1%'))        AND so.signature = od.signature        AND so.CATEGORY = od.CATEGORY        AND so.obj_type = od.obj_type        AND so.plan_id = od.plan_id;