捕获非绑定变量的SQL语句
MOE@xbtst SQL>select * from test;DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
MOE@xbtst SQL>alter system flush shared_pool;
System altered.
MOE@xbtst SQL>select * from test where deptno=10;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
MOE@xbtst SQL>select * from test where deptno=20;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
MOE@xbtst SQL>select * from test where deptno=30;
DEPTNO DNAME LOC
---------- -------------- -------------
30 SALES CHICAGO
MOE@xbtst SQL>select * from test where deptno='10';
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
MOE@xbtst SQL>select * from test where deptno='20';
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
MOE@xbtst SQL>select * from test where deptno='30';
DEPTNO DNAME LOC
---------- -------------- -------------
30 SALES CHICAGO
MOE@xbtst SQL>var v_id number
MOE@xbtst SQL>exec :v_id := 10
PL/SQL procedure successfully completed.
MOE@xbtst SQL>select * from test where deptno=:v_id;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
MOE@xbtst SQL>exec :v_id := 20
PL/SQL procedure successfully completed.
MOE@xbtst SQL>select * from test where deptno=:v_id;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
MOE@xbtst SQL>exec :v_id := 30
PL/SQL procedure successfully completed.
MOE@xbtst SQL>select * from test where deptno=:v_id;
DEPTNO DNAME LOC
---------- -------------- -------------
30 SALES CHICAGO
MOE@xbtst SQL>set line 123
MOE@xbtst SQL>col sql_text format a40
MOE@xbtst SQL>set numwidth 30
MOE@xbtst SQL>select sql_text,FORCE_MATCHING_SIGNATURE,EXACT_MATCHING_SIGNATURE FROM V$SQL WHERE sql_text like '%select * from test%';
SQL_TEXT FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE
---------------------------------------- ------------------------------ ------------------------------
select * from test where deptno=20 1674223644458057282 5701787720123824641
select * from test where deptno='20' 1674223644458057282 6624213459289620561
select * from test where deptno='30' 1674223644458057282 15799720645668840753
select * from test where deptno='10' 1674223644458057282 7423854019058606662
select * from test where deptno=30 1674223644458057282 6295409922938069091
select * from test where deptno=10 1674223644458057282 5918141949209886904
select * from test where deptno=:v_id 5038495461207490287 5038495461207490287
MOE@xbtst SQL>show parameter cursor_shar
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
页:
[1]