首页 > 数据库 >[20240902]验证sql_idz.sh计算PLSQL代码块.txt

[20240902]验证sql_idz.sh计算PLSQL代码块.txt

时间:2024-09-02 20:38:21浏览次数:14  
标签:count idz PLSQL 16 -- text sql 20240902 SQL

[20240902]验证sql_idz.sh计算PLSQL代码块.txt

--//测试验证sql_idz.sh计算PLSQL代码块是否正确.

1.环境:
SYS@book> @ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

$ cat -Av mn.txt
DECLARE$
    l_count PLS_INTEGER;$
BEGIN$
    FOR i IN 1..&&1$
    LOOP$
       EXECUTE IMMEDIATE 'SELECT count(*)  FROM dept ,emp WHERE dept.deptno = emp.deptno' into l_count;$
    END LOOP;$
END;$
/$
$

2.测试:
SCOTT@book01p> @ mn.txt
Enter value for 1: 1e8

SYS@book> @ ashtop sql_id,TOP_LEVEL_SQL_ID    1=1 &1min

    Total                                                                                       Distinct Distinct    Distinct
  Seconds     AAS %This   SQL_ID        TOP_LEVEL_SQL FIRST_SEEN          LAST_SEEN           Execs Seen  Tstamps Execs Seen1
--------- ------- ------- ------------- ------------- ------------------- ------------------- ---------- -------- -----------
       45      .8   73% | b7zpx6jm6mdpy 8uqtnw78t2sa4 2024-09-02 16:35:10 2024-09-02 16:36:09         35       45          45
        9      .2   15% |               8uqtnw78t2sa4 2024-09-02 16:35:20 2024-09-02 16:36:08          1        9           9
        6      .1   10% | 8uqtnw78t2sa4 8uqtnw78t2sa4 2024-09-02 16:35:14 2024-09-02 16:36:01          2        6           2
        2      .0    3% |                             2024-09-02 16:35:42 2024-09-02 16:36:03          1        2           2
--//sql_id=8uqtnw78t2sa4对应plsql代码块.

SYS@book> @ sql_id 8uqtnw78t2sa4
--SQL_ID = 8uqtnw78t2sa4

DECLARE
    l_count PLS_INTEGER;
BEGIN
    FOR i IN 1..1e8
    LOOP
       EXECUTE IMMEDIATE 'SELECT count(*)  FROM dept ,emp WHERE dept.deptno = emp.deptno' into l_count;
    END LOOP;
END;;
--//注:我写的sql_id脚本自动在结尾加上一个分号.PL/sql变成2个分号.

$ cp mn.txt aa2.txt
--//适当编辑aa2.txt,替换&&1=>1e8,取消结尾的/以及后面字符,在结尾的;在加上一个;主要原因我写的计算脚本计算时会丢弃结尾的1
--//个分号.

$ cat -Av aa2.txt
DECLARE$
    l_count PLS_INTEGER;$
BEGIN$
    FOR i IN 1..1e8$
    LOOP$
       EXECUTE IMMEDIATE 'SELECT count(*)  FROM dept ,emp WHERE dept.deptno = emp.deptno' into l_count;$
    END LOOP;$
END;;$

$ sql_idz.sh aa2.txt 2
sql_text = DECLARE
    l_count PLS_INTEGER;
BEGIN
    FOR i IN 1..1e8
    LOOP
       EXECUTE IMMEDIATE 'SELECT count(*)  FROM dept ,emp WHERE dept.deptno = emp.deptno' into l_count;
    END LOOP;
END;\0
full_hash_value(16) = 4A569457CB346ADE8D5B34E1D1916144 or 4a569457cb346ade8d5b34e1d1916144
hash_value(10) = 3515965764
sql_id(32) = 8uqtnw78t2sa4
sql_id(32) = 8uqtnw78t2sa4
sql_id(32) = 8uqtnw78t2sa4
--//计算完成正确.

SYS@book> @ sharepool/shp4x 8uqtnw78t2sa4 0
HANDLE_TYPE            KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address   0000000070210BB8 000000006A7BB380 DECLARE     l_count PLS_INTEGER; BEGIN            1          2          0 000000006A7B9C50 000000006A7BA638       4032      16464       3455     23951      23951 3515965764 8uqtnw78t2sa4          0
parent handle address  000000006A7BB380 000000006A7BB380 DECLARE     l_count PLS_INTEGER; BEGIN            1          0          0 0000000070211158 00                     4064          0          0      4064       4064 3515965764 8uqtnw78t2sa4      65535

--//如果脚本如下,结尾仅仅1个分号.
$ cat aa2.txt
DECLARE
    l_count PLS_INTEGER;
BEGIN
    FOR i IN 1..1e8
    LOOP
       EXECUTE IMMEDIATE 'SELECT count(*)  FROM dept ,emp WHERE dept.deptno = emp.deptno' into l_count;
    END LOOP;
END;

--//加入参数3补上';\0'
$ sql_idz.sh aa2.txt 2 ';\0'
sql_text = DECLARE
    l_count PLS_INTEGER;
BEGIN
    FOR i IN 1..1e8
    LOOP
       EXECUTE IMMEDIATE 'SELECT count(*)  FROM dept ,emp WHERE dept.deptno = emp.deptno' into l_count;
    END LOOP;
END;\0
full_hash_value(16) = 4A569457CB346ADE8D5B34E1D1916144 or 4a569457cb346ade8d5b34e1d1916144
hash_value(10) = 3515965764
sql_id(32) = 8uqtnw78t2sa4
sql_id(32) = 8uqtnw78t2sa4
sql_id(32) = 8uqtnw78t2sa4
--//计算完成正确.

3.附上源代码:
--//^M 在vim for linux下按ctrl+v ctrl+M输入.
$ cat -v ~/bin/sql_idz.sh
#! /bin/bash
# calcucate sql_text of full_hash_value(16),hash_value(10),sql_id(32).
# argv1 sql statement or sql of text file
# argv2 flag: 0= sql statement 1=sql of text file for sqlplus  2=sql of text file for other 3=original
# argv3 default = '\0' add tailstr

odebug=${ODEBUG:-0}
oflag=${2:-0}
tailstr=${3:-'\0'}

if [ $oflag -eq 0 ]
then
    sql_text=${1}${tailstr}
fi

# sqlplus format sql_text
if [ $oflag -eq 1 ]
then
sql_text="$( cat $1 | sed -e "s/^M$//" -e "s/\s*$//" -e '$s/;$//')""${tailstr}"
# sql_text="$( cat $1 | unix2dos | sed '$s/;\s*$//')"'\0'
# sql_text="$( cat $1 | sed -e "s/^M$//" -e sed '$s/;\s*$//')"'\0'
# sql_text="$( cat $1 | sed '$s/;\s*$//')"'\0'
fi

# other format sql_text
if [ $oflag -eq 2 ]
then
sql_text="$( cat $1 | sed '$s/;\s*$//')""${tailstr}"
# sql_text="$( cat $1 | unix2dos | sed '$s/;\s*$//')"'\0'
# sql_text="$( cat $1 | sed -e "s/^M$//" -e '$s/;\s*$//')"'\0'
# sql_text="$( cat $1 | sed '$s/;\s*//')"'\0'
fi

# exact_matching_signature, force_matching_signature
if [ $oflag -eq 3 ]
then
    sql_text=${1}
fi

v1=$(echo -e -n "$sql_text" | md5sum | sed 's/  -//' | xxd -r -p | od -t x4 |  sed   -n  -e 's/^0\+ //' -e 's/ //gp' | tr 'a-z' 'A-Z')
v2=${v1:(-16):16}
v3=${v2:(-8):8}
# v2=$(echo "obase=16;ibase=16; $v1 % 10000000000000000" | bc| tr -d '\\\r\n')
# v3=$(echo "obase=10;ibase=16; $v1 % 100000000" | bc| tr -d '\\\r\n')

if [ $odebug -eq 1 ] ; then
        echo v1=$v1 v2=$v2 v3=$v3
fi

echo "sql_text = $sql_text"
echo "full_hash_value(16) = $v1 or ${v1,,}"

if [ $oflag -eq 3 ] ; then
        echo "xxxxx_matching_signature(10) = $(( 16#$v2 )) or " $(echo $(( 16#$v2 )) + 2^64|bc )
fi

echo "hash_value(10) = $(( 16#$v3 )) "

BASE32=($(echo {0..9} {a..z} | tr -d 'eilo'))
res=''
for i in $(echo "obase=32;ibase=16; $v2" | bc| tr -d '\\\r\n')
do
        res=${res}${BASE32[$(( 10#$i ))]}
done
echo "sql_id(32) = $(printf "%13s" $res | tr ' ' '0')"
echo "sql_id(32) = $(printf "%013s" $res)"

res1=$(eval $(echo "obase=32;ibase=16; $v2" | bc| tr -d '\\\r\n' | awk 'BEGIN{RS=" +"; printf "echo " }/./{printf "${BASE32[$(( 10#%02d))]}", $1}' ))
echo "sql_id(32) = $(printf "%013s" $res1)"

$ cat shp4x.sql
column N0_6_16 format 99999999
column handle_type format a22
SELECT /*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) */ DECODE (kglhdadr,
               kglhdpar, 'parent handle address',
               'child handle address')
           handle_type,
       kglhdadr,
       kglhdpar,
       --//substr(kglnaobj,1,40) c40,
       substr(replace(nvl(decode(kglnaown, null, kglnaobj, kglnaown||'.'||kglnaobj), '(name not found)'),chr(13),'') ,1,40)  c40,
           KGLHDLMD,
           KGLHDPMD,
           kglhdivc,
       kglobhd0,
       kglobhd6,
       kglobhs0,kglobhs6,kglobt16,
       kglobhs0+kglobhs6+kglobt16 N0_6_16,
           kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20,
           kglnahsh,
           kglobt03,
           kglobt09
  FROM x$kglob
 WHERE kglobt03 = lower('&1') or KGLNAHSH= &2;

$ cat sql_id.sql
SET LINESIZE 32767
--SET LINESIZE 4000
VAR V_SQL_FULLTEXT CLOB
COL SQL_FULLTEXT FOR A4000 WORD_WRAP

SET FEEDBACK OFF
SET SERVEROUTPUT ON

PROMPT
PROMPT --SQL_ID = &&1
PROMPT

DECLARE
    V_SQL_FULLTEXT   CLOB;
    V_COUNT          NUMBER;
BEGIN
    SELECT COUNT(*) INTO V_COUNT  FROM GV$SQLAREA WHERE SQL_ID = '&&1' AND ROWNUM=1;
    IF  V_COUNT=1
    THEN
        SELECT REPLACE (SQL_FULLTEXT||';', CHR(13), '') SQL_FULLTEXT INTO V_SQL_FULLTEXT FROM GV$SQLAREA WHERE SQL_ID = '&&1' AND ROWNUM = 1;
        --SELECT REPLACE (SQL_FULLTEXT||';', CHR(13), chr(13)) SQL_FULLTEXT INTO V_SQL_FULLTEXT FROM GV$SQLAREA WHERE SQL_ID = '&&1' AND ROWNUM = 1;
        DBMS_OUTPUT.PUT_LINE (V_SQL_FULLTEXT);
    ELSE
        SELECT COUNT(*)  INTO V_COUNT  FROM DBA_HIST_SQLTEXT WHERE SQL_ID='&&1' AND ROWNUM=1;
        IF  V_COUNT=1
        THEN
            SELECT REPLACE (SQL_TEXT||';',CHR(13),'')  INTO V_SQL_FULLTEXT  FROM DBA_HIST_SQLTEXT WHERE SQL_ID='&&1' AND ROWNUM=1;
            --SELECT REPLACE (SQL_TEXT||';',CHR(13),chr(13))  INTO V_SQL_FULLTEXT  FROM DBA_HIST_SQLTEXT WHERE SQL_ID='&&1' AND ROWNUM=1;
            DBMS_OUTPUT.PUT_LINE (V_SQL_FULLTEXT);
        END IF;
    END IF;
    EXCEPTION WHEN NO_DATA_FOUND THEN
        NULL;
END;
/

PROMPT
SET SERVEROUTPUT OFF
SET FEEDBACK 6
SET LINESIZE 277
 

标签:count,idz,PLSQL,16,--,text,sql,20240902,SQL
From: https://www.cnblogs.com/lfree/p/18393480

相关文章

  • [20240902]建立完善sql_idz.sh脚本.txt
    [20240902]建立完善sql_idz.sh脚本.txt--//增加hash_value的16进制显示采用大小写显示.$cat-v~/bin/sql_idz.sh#!/bin/bash#calcucatesql_textoffull_hash_value(16),hash_value(10),sql_id(32).#argv1sqlstatementorsqloftextfile#argv2flag:0=sqlstatement......
  • 20240902_171049 mysql 填空题 ddl表
    创建一个名为tb的表creatatabletb()创建一个名为tb的表,先判断再创建createtableifnotexiststb()新建一个student表,拷备teacher表的结构createtablestudentliketeacher删除一个名为student的表droptablestudent删除名为student的表,先判断再删除droptableif......
  • 20240902_162002 mysql 填空题 数据类型
    定义一个int类型的列,名为ageageint定义一个无符号的int类型的列,名为scorescoreintunsigned定义一个单精度小数类型的列,名为weightweightfloat定义一个双精度小数类型的列,名为weightweightdouble定义一个小数列,要求总长度为5,小数部分为2,列名为weightweightdecimal(5,......
  • 20240902_145040 填空题小工具的配置与使用
    收到文件夹配置名称修改config中的name的值不要删双引号启动测试配置题库在数据源目录下新建一个记事本在记事本中输入问题与答案主要的问题与答案由老师提供......
  • PLSQL导入/导出数据方法
    https://blog.csdn.net/zhouleiblog/article/details/8893005以下为借用以上信息:PLSQL导入/导出数据方法PLSQL导入/导出数据方法以前导数据库信息的时候,总是会先开启sql窗口,把自己手写的建表文件复制进去,然后再导入数据信息。今天突然懒得去找以前的建表文件,而想用SLSQL直接从......
  • 什么raidZ
    RAIDZ(有时也写作RAID-Z或ZFSRAID)是SUNMicrosystem(现为Oracle公司的一部分)在其ZFS(ZFileSystem)文件系统中采用的一种软件RAID解决方案。RAIDZ并不是传统意义上的硬件RAID,而是一种更高层的解决方案,它利用ZFS文件系统的特性来提供数据冗余和恢复能力。一、RAIDZ的定义与背景定义......
  • 数据库raidZ数据恢复
    一、RAIDZ概述RAIDZ(有时也写作RAID-Z或ZFSRAID)是一种基于ZFS(ZFileSystem)文件系统的RAID技术,它特别为存储系统提供了高级别的数据冗余和恢复能力。RAIDZ将数据分散存储到多个磁盘上,并通过特定的校验和机制来确保数据的完整性和可恢复性。与传统的RAID级别(如RAID0、RAID1、RAI......
  • 联想服务器阵列数据恢复Raid5/0/6数据库RaidZ/虚拟机
    针对联想服务器阵列数据恢复,特别是涉及RAID5/0/6、数据库RAIDZ以及虚拟机的情况一、RAID5/0/6数据恢复故障诊断确定故障类型:首先需要明确是RAID5/0/6阵列中的哪一部分出现故障,如硬盘掉线、阵列卡损坏、意外断电等。收集信息:记录服务器型号、RAID配置、硬盘序列号等关键......
  • plsql中导入excel数据
    方法一:1、点击“工具”—“ODBC导入器…”菜单,打开导入功能 2、选择要连接类型和导入的Excel文件 3、选择所要导入数据所在的“Sheet”表 4、根据Sheet表结构创建Oracle数据表   5、完成数据导入 方法二:1、execl文件另存为“文本文件(制表符分隔)(*.txt)......
  • PLSQL 12安装及汉化
    一、下载PLSQL下载PLSQL(本次安装的PLSQL版本为12.0.7,建议安装64位)下载地址:https://www.allroundautomations.com/registered-plsqldev/二、下载汉化包三、注册码PLSQLDeveloper12.0.7注册码productcode:4vkjwhfeh3ufnqnmpr9brvcuyujrx3n3leserialNumber:226959pas......