41 lines
1.1 KiB
SQL
41 lines
1.1 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Search SQL Text from SQLArea to find SQLId
|
|
* Parameters : 1 - SQLText to search (Use % as wild card)
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 12-Jun-13 Vishal Gupta Added force_matching_signature to output
|
|
* 08-Mar-12 Vishal Gupta Created
|
|
*
|
|
*
|
|
*/
|
|
|
|
DEFINE sqltext="&&1"
|
|
|
|
SET long 100
|
|
SET longchunksize 100
|
|
SET pages 0
|
|
|
|
COLUMN sql_fulltext HEADING "SQLText" FORMAT a200 WRAP
|
|
COLUMN force_matching_signature HEADING "FORCE_MATCHING_SIGNATURE" FORMAT 999999999999999999999
|
|
|
|
-- Using a DISTINCT gives following error
|
|
-- ORA-00932: inconsistent datatypes: expected - got CLOB
|
|
SELECT inst_id, s.sql_id, force_matching_signature, s.sql_fulltext
|
|
FROM gv$sqlarea s
|
|
WHERE upper(s.sql_text) like upper(q'[&&sqltext]')
|
|
AND s.sql_text NOT LIKE '%gv$sql%'
|
|
AND s.sql_text NOT LIKE '%dba_hist_sql%'
|
|
;
|
|
|
|
UNDEFINE sqltext
|
|
|
|
@@footer
|