find Literal SQL

利用FORCE_MATCHING_SIGNATURE捕获非绑定变量SQL

Applies to:

PL/SQL – Version: 8.1.7 to 10.2

Information in this document applies to any platform.

Goal

There is no direct way to query the dictionary for literal SQL only.

However the following example will try to exclude all SQL statements in the

shared pool that do use bind variables.

There still might be situations, with statements using subqueries, where the

example still will show SQL statements using bind variables.

Solution

Create the following PL/SQL block:

http://www.askmaclean.com/archives/%E5%88%A9%E7%94%A8force_matching_signature%E6%8D%95%E8%8E%B7%E9%9D%9E%E7%BB%91%E5%AE%9A%E5%8F%98%E9%87%8Fsql.html

set serveroutput onset linesize 120---- This anonymous PL/SQL block must be executed as INTERNAL or SYS-- Execute from : SQL*PLUS-- CAUTION:-- This sample program has been tested on Oracle Server - Enterprise Edition-- However, there is no guarantee of effectiveness because of the possibility-- of error in transmitting or implementing it. It is meant to be used as a-- template, and it may require modification.--declareb_myadr VARCHAR2(20);b_myadr1 VARCHAR2(20);qstring VARCHAR2(100);b_anybind NUMBER;cursor my_statement isselect address from v$sqlgroup by address;cursor getsqlcode isselect substr(sql_text,1,60)from v$sqlwhere address = b_myadr;cursor kglcur isselect kglhdadr from x$kglcursorwhere kglhdpar = b_myadrand kglhdpar != kglhdadrand kglobt09 = 0;cursor isthisliteral isselect kkscbndtfrom x$kksbvwhere kglhdadr = b_myadr1;begindbms_output.enable(10000000);open my_statement;loopFetch my_statement into b_myadr;open kglcur;fetch kglcur into b_myadr1;if kglcur%FOUND Thenopen isthisliteral;fetch isthisliteral into b_anybind;if isthisliteral%NOTFOUND Thenopen getsqlcode;fetch getsqlcode into qstring;dbms_output.put_line('Literal:'||qstring||' address: '||b_myadr);close getsqlcode;end if;close isthisliteral;end if;close kglcur;Exit When my_statement%NOTFOUND;End loop;close my_statement;end;//*尝试执行*/SQL> @find_literalLiteral:select inst_id, java_size, round(java_size / basejava_size,  address: 00000000BC6E94E8Literal:select reason_id, object_id, subobject_id, internal_instance address: 00000000BC5F1D60Literal:select  DBID, NAME, CREATED, RESETLOGS_CHANGE#, RESETLOGS_TI address: 00000000BC6000B0Literal:select di.inst_id,di.didbi,di.didbn,to_date(di.dicts,'MM/DD/ address: 00000000BC530DA8Literal:      declare          vsn  varchar2(20);             begin  address: 00000000BC85A9F8Literal:SELECT INCARNATION#, RESETLOGS_CHANGE#, RESETLOGS_TIME, PRIO address: 00000000BC829978Literal:select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where  address: 00000000BCA84D00Literal:select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('U address: 00000000BC771BF0Literal: select sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_S address: 00000000BC4673A8Literal:select streams_pool_size_for_estimate s,           streams_p address: 00000000BCA58848Literal:         select open_mode from v$database address: 00000000BC5DF2D0Literal:select FORCE_MATCHING_SIGNATURE, count(1)   from v$sql  wher address: 00000000BCA91628Literal:select inst_id, tablespace_name, segment_file, segment_block address: 00000000BC66EF38Literal:select sum(used_blocks), ts.ts#   from GV$SORT_SEGMENT gv, t address: 00000000BCAA01B0Literal:BEGIN DBMS_OUTPUT.ENABLE(NULL); END; address: 00000000BC61D2D8Literal:select value$ from props$ where name = 'GLOBAL_DB_NAME' address: 00000000BC570500Literal:select count(*) from sys.job$ where (next_date > sysdate) an address: 00000000BC6C53F8Literal:select java_pool_size_for_estimate s,           java_pool_si address: 00000000BCA65070Literal:select local_tran_id, global_tran_fmt, global_oracle_id, glo address: 00000000BC5900B8Literal:select inst_id,kglnaobj,kglfnobj,kglobt03, kglobhs0+kglobhs1 address: 00000000BC921538Literal:select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname, address: 00000000BCA83E90Literal:SELECT * FROM V$SQL address: 00000000BCA58BC0Literal:SELECT ADDRESS FROM V$SQL GROUP BY ADDRESS address: 00000000BC565BE8Literal:      begin          dbms_rcvman.resetAll;       end; address: 00000000BC759858Literal:declare b_myadr VARCHAR2(20); b_myadr1 VARCHAR2(20); qstring address: 00000000BC928FF8Literal:select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, address: 00000000BC898BF8Literal:select CONF#, NAME, VALUE from GV$RMAN_CONFIGURATION where i address: 00000000BC8CB7F8Literal:select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t address: 00000000BC8CDFE8Literal:select u.name, o.name, trigger$.sys_evts, trigger$.type#  fr address: 00000000BCA877B8Literal:select id, name, block_size, advice_status,                  address: 00000000BC636B38Literal:select incarnation#, resetlogs_change#, resetlogs_time,      address: 00000000BCA94250Literal:select  INSTANCE_NUMBER , INSTANCE_NAME , HOST_NAME , VERSIO address: 00000000BC62A678Literal:select ks.inst_id,ksuxsins,ksuxssid,ksuxshst,ksuxsver,ksuxst address: 00000000BC8E5440Literal:select timestamp, flags from fixed_obj$ where obj#=:1 address: 00000000BC916C78Literal:select size_for_estimate,                      size_factor * address: 00000000BCA5F830Literal:select shared_pool_size_for_estimate s,          shared_pool address: 00000000BCA5A350Literal:select  SQL_TEXT , SQL_FULLTEXT , SQL_ID,  SHARABLE_MEM , PE address: 00000000BC76B3A0Literal:lock table sys.col_usage$ in exclusive mode nowait address: 00000000BCA05978Literal:select 'x' from dual  address: 00000000BC583818Literal:      select name, resetlogs_time,              resetlogs_ch address: 00000000BCA9D430Literal:select inst_id, sp_size, round(sp_size / basesp_size, 4),  k address: 00000000BC65A9F0Literal:select userenv('Instance'),  icrid, to_number(icrls),        address: 00000000BC692260Literal:select shared_pool_size_for_estimate, shared_pool_size_facto address: 00000000BCAE0750Literal:select INST_ID, RMRNO, RMNAM, RMVAL from X$KCCRM where RMNAM address: 00000000BC8CD778Literal:select metadata from kopm$  where name='DB_FDO' address: 00000000BC9EBB98Literal:select java_pool_size_for_estimate, java_pool_size_factor,   address: 00000000BC5B27D0Literal:SELECT INCARNATION#, INCARNATION#, RESETLOGS_CHANGE#, RESETL address: 00000000BC829C48Literal:select file# from file$ where ts#=:1 address: 00000000BC87CF18Literal:select A.inst_id, A.bpid, B.bp_name, A.blksz,                address: 00000000BC802248Literal:lock table sys.mon_mods$ in exclusive mode nowait address: 00000000BC5CBE68Literal:lock table sys.mon_mods$ in exclusive mode nowait address: 00000000BC5CBE68

本文出自 “无双城” 博客，请务必保留此出处http://929044991.blog.51cto.com/1758347/1262809