[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)
--//建立一个简单输入简单文本计算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)
