[20240515]建立完善t2sh.sql脚本.txt

[20240515]建立完善t2sh.sql脚本.txt --//建立一个简单输入简单文本计算sql_id,hash_values的sql脚本,主要用于简单sql语句以及对象的full_hash_value,sql_id,hash_value计算. -- Copyright 2023 lfree. All rights reserved. -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions. -------------------------------------------------------------------------------- -- -- File name:   t2sh -- Purpose:     query Sql_id ,Hash_value , full_hash_value from input sql_Text --              only work at Linux (Little Endian) -- Author:      lfree -- -- Usage: --     @ t2sh "'<text>'" -- -------------------------------------------------------------------------------- column sql_id format a13 column hash_value format 9999999999 SELECT lower( sys.utl_raw.reverse(sys.utl_raw.substr(calc.rev_full_hash,1,4))||   sys.utl_raw.reverse(sys.utl_raw.substr(calc.rev_full_hash,5,4))||   sys.utl_raw.reverse(sys.utl_raw.substr(calc.rev_full_hash,9,4))||   sys.utl_raw.reverse(sys.utl_raw.substr(calc.rev_full_hash,13,4))) full_hash_value   , dbms_sql_translator.sql_id (&1) sql_id   , dbms_sql_translator.sql_hash(&1) hash_vaule   FROM dual, --  (select sys.dbms_crypto.hash(src => UTL_I18N.string_to_raw(&1||chr(0),'AL32UTF8'),2) rev_full_hash from dual ) calc (select sys.dbms_crypto.hash(src => rawtohex(&1||chr(0)),2) rev_full_hash from dual ) calc ; --//注意:该脚本做sql_id计算,sql_text计算不需要输入最后的chr(0). --//注意一个细节, &1两边没有单引号,这是故意为之,这样输入参数1格式"'<text>'" --//因为一些特殊对象,比如表 因为后面要加入namespace,本来计算加入chr(1)||chr(0)||chr(0)||chr(0),这样计算时 --//要去掉1个chr(0),这样写法便于操作计算. --//简单测试: --//注意参数不支持多行的文本格式,我仅仅用来做简单计算. SCOTT@test01p> select * from dept ;     DEPTNO DNAME                LOC ---------- -------------------- -------------         10 ACCOUNTING           NEW YORK         20 RESEARCH             DALLAS         30 SALES                CHICAGO         40 OPERATIONS           BOSTON SCOTT@test01p> @ hash HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ---------- ------------------- ----------- 4071881952 4g0qfgmtb7z70            0     130272      3383998547  f2b3fce0  2024-05-15 21:49:12    16777218 SYS@test> select * from v$db_object_cache where hash_value=4071881952 and rownum=1   2   @prxx ============================== OWNER                         : NAME                          : select * from dept DB_LINK                       : NAMESPACE                     : SQL AREA TYPE                          : CURSOR SHARABLE_MEM                  : 16216 LOADS                         : 1 EXECUTIONS                    : 3 LOCKS                         : 1 PINS                          : 0 KEPT                          : NO CHILD_LATCH                   : 0 INVALIDATIONS                 : 0 HASH_VALUE                    : 4071881952 LOCK_MODE                     : NULL PIN_MODE                      : NONE STATUS                        : VALID TIMESTAMP                     : 2024-05-17/22:50:24 PREVIOUS_TIMESTAMP            : LOCKED_TOTAL                  : 3 PINNED_TOTAL                  : 4 PROPERTY                      : FULL_HASH_VALUE               : 2a7dbd05b32556914782ce7cf2b3fce0 CON_ID                        : 3 CON_NAME                      : ADDR                          : 000007FF0078A9C0 EDITION                       : PL/SQL procedure successfully completed. SYS@test> @ t2sh "'select * from dept '" FULL_HASH_VALUE                  SQL_ID        HASH_VAULE -------------------------------- ------------- ---------- 2a7dbd05b32556914782ce7cf2b3fce0 4g0qfgmtb7z70 4071881952 SYS@test> select * from v$db_object_cache where name = 'DEPT'   2   @prxx ============================== OWNER                         : SCOTT NAME                          : DEPT DB_LINK                       : NAMESPACE                     : TABLE/PROCEDURE TYPE                          : TABLE SHARABLE_MEM                  : 4072 LOADS                         : 1 EXECUTIONS                    : 0 LOCKS                         : 0 PINS                          : 0 KEPT                          : NO CHILD_LATCH                   : 93026 INVALIDATIONS                 : 0 HASH_VALUE                    : 2956815202 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ LOCK_MODE                     : NONE PIN_MODE                      : NONE STATUS                        : VALID TIMESTAMP                     : 2019-08-17/21:38:48 PREVIOUS_TIMESTAMP            : LOCKED_TOTAL                  : 2 PINNED_TOTAL                  : 2 PROPERTY                      : FULL_HASH_VALUE               : d228ecb73e713f600e1f246bb03d6b62 CON_ID                        : 3 CON_NAME                      : TEST01P ADDR                          : 000007FF002CD2E0 EDITION                       : PL/SQL procedure successfully completed. SYS@test> @ t2sh "'DEPT.SCOTT.TEST01P'||chr(1)||chr(0)||chr(0)" FULL_HASH_VALUE                  SQL_ID        HASH_VAULE -------------------------------- ------------- ---------- d228ecb73e713f600e1f246bb03d6b62 0w7t4dfs3uuv2 2956815202 --//正好对上.注意少一个chr(0)