56 lines
2.4 KiB
SQL
56 lines
2.4 KiB
SQL
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display all SQL Profiles
|
|
* Parameters :
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 16-May-12 Vishal Gupta Created
|
|
*/
|
|
|
|
COLUMN column1 FORMAT a120
|
|
|
|
UNDEFINE sql_profile_name
|
|
DEFINE sql_profile_name="&&1"
|
|
|
|
|
|
DEFINE rpad_length=15
|
|
|
|
PROMPT *****************************************************************
|
|
PROMPT * Profile Details
|
|
PROMPT *****************************************************************
|
|
|
|
set heading off
|
|
SELECT /*+ ORDERED */
|
|
RPAD('Name',&&rpad_length) || ' : ' || so.name || chr(10) ||
|
|
RPAD('Description',&&rpad_length) || ' : ' || sp.description || chr(10) ||
|
|
RPAD('Version',&&rpad_length) || ' : ' || ad.version || chr(10) ||
|
|
RPAD('Category',&&rpad_length) || ' : ' || sp.category || chr(10) ||
|
|
RPAD('Force Matching',&&rpad_length)|| ' : ' || sp.force_matching || chr(10) ||
|
|
RPAD('Type',&&rpad_length) || ' : ' || sp.type || chr(10) ||
|
|
RPAD('Status',&&rpad_length) || ' : ' || sp.status || chr(10) ||
|
|
RPAD('Signature',&&rpad_length) || ' : ' || so.signature || chr(10) ||
|
|
RPAD('Creator',&&rpad_length) || ' : ' || ad.creator || chr(10) ||
|
|
RPAD('Creation Time',&&rpad_length) || ' : ' || to_char(sp.created,'DD-MON-YY HH24:MI:SS') || chr(10) ||
|
|
RPAD('Last Modified',&&rpad_length) || ' : ' || to_char(sp.last_modified,'DD-MON-YY HH24:MI:SS') || chr(10) ||
|
|
RPAD('Last Executed',&&rpad_length) || ' : ' || to_char(so.last_executed,'DD-MON-YY HH24:MI:SS') || chr(10) ||
|
|
RPAD('Last Verified',&&rpad_length) || ' : ' || to_char(ad.last_verified,'DD-MON-YY HH24:MI:SS') || chr(10) ||
|
|
RPAD('SQL Text',&&rpad_length) || ' : ' || chr(10) ||
|
|
sp.sql_text || chr(10) ||
|
|
' ' column1
|
|
FROM dba_sql_profiles sp
|
|
JOIN sys.sqlobj$ so ON so.signature = sp.signature AND so.category = sp.category AND so.obj_type = 1 /* 1 = SQLProfile, 2=SQL Plan */
|
|
JOIN sys.sqlobj$auxdata ad ON so.signature = ad.signature AND so.category = ad.category AND ad.obj_type = 1 /* 1 = SQLProfile, 2=SQL Plan */
|
|
JOIN sys.sql$text st ON so.signature = st.signature
|
|
JOIN sys.sql$ sq ON so.signature = sq.signature
|
|
WHERE 1=1
|
|
AND upper(sp.name) = upper('&&sql_profile_name')
|
|
ORDER BY sp.last_modified DESC
|
|
;
|
|
|
|
set heading on
|