玉面飞龙的BLOG

判断SQL来自PL/SQL

February 28th, 2007 | Categories: Boring | Tags:

发现PL/SQL中的SQL语句到了shared pool中都被转换成了大写的形式,并且提示部分的大小写不变,注释的文本部分被删除。有了这个特点，可以直觉的判断SQL语句是否来自某个PL/SQL.

SQL> declare
2 cc number;
3 begin
4 select /*Search Me*/ /*+ all_rows */ count(*) into cc from t;
5 update /*Search Me*/ /*+ all_rows */ t set username=username;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> update /*Search Me*/ /*+ all_rows */ t set username=username;

——————–V$SQL.hash_value&sql_text ———————–
1944588583 declare cc number; begin select /*Search Me*/ /*+ all_rows */ count(*) into cc from t; update /*Search Me*/ /*+ all_rows */ t set username=username; end;

2676112427 update /*Search Me*/ /*+ all_rows */ t set username=username

443710615 UPDATE /*+ all_rows */ T SET USERNAME=USERNAME

1989632644 SELECT /*+ all_rows */ COUNT(*) FROM T