/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display all SQL Profiles
*  Parameters : 
*          
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  13-Jan-14  Vishal Gupta  Add additional columns
*  16-May-12  Vishal Gupta  Created
*/

COLUMN  name               HEADING "Name"                         FORMAT a30
COLUMN  category           HEADING "Category"                     FORMAT a15
COLUMN  creator            HEADING "Creator"                      FORMAT a15
COLUMN  created            HEADING "Created"                      FORMAT a18
COLUMN  last_modified      HEADING "LastModified"                 FORMAT a18
COLUMN  last_executed      HEADING "LastExecuted"                 FORMAT a18
COLUMN  force_matching     HEADING "Force|Match"                  FORMAT a5
COLUMN  signature          HEADING "Signature"                    FORMAT 999999999999999999999
COLUMN  description        HEADING "Description"                  FORMAT a50

BREAK ON name ON CATEGORY ON created ON last_modified ON type ON status ON force_matching ON signature ON description

SELECT /*+ ORDERED */ 
       sp.name
     , sp.category
     , ad.creator
     , to_char(sp.created,'DD-MON-YY HH24:MI:SS') created
     , to_char(sp.last_modified,'DD-MON-YY HH24:MI:SS') last_modified
     -- Last executed is display NULL for all the rows
     -- , to_char(so.last_executed,'DD-MON-YY HH24:MI:SS') last_executed
     , sp.type
     , sp.status
     , sp.force_matching
     , sp.signature
     , sp.description
  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  */ 
 WHERE 1=1
ORDER BY sp.last_modified DESC  
;