54 lines
1.1 KiB
SQL
54 lines
1.1 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Expanded SQL Text from AWR repository
|
|
* Parameters : 1 - SQL_ID
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 29-Jul-15 Vishal Gupta Created
|
|
*
|
|
*
|
|
*/
|
|
|
|
DEFINE sqlid="&&1"
|
|
|
|
SET long 4000000
|
|
SET longchunksize 400000
|
|
SET pages 0
|
|
set lines 300
|
|
|
|
COLUMN sql_text HEADING "SQLText" FORMAT a150 WRAP
|
|
|
|
Prompt ***********************************************
|
|
Prompt * Expanded SQL Text for SQLID = &&sqlid
|
|
Prompt ***********************************************
|
|
|
|
VARIABLE EXPANDED_SQLTEXT CLOB;
|
|
|
|
DECLARE
|
|
L_SQLTEXT CLOB;
|
|
BEGIN
|
|
SELECT s.sql_text
|
|
INTO L_SQLTEXT
|
|
FROM dba_hist_sqltext s
|
|
, v$database d
|
|
WHERE d.dbid = s.dbid
|
|
AND s.sql_id = '&&sqlid'
|
|
;
|
|
sys.dbms_sql2.expand_sql_text(L_SQLTEXT,:EXPANDED_SQLTEXT);
|
|
EXCEPTION
|
|
WHEN NO_DATA_FOUND THEN
|
|
NULL;
|
|
END;
|
|
/
|
|
|
|
print :EXPANDED_SQLTEXT
|
|
|
|
@@footer
|