Useful SOA Purge SQL's- Cube Instance and Mediator

/* Formatted on 2012/08/09 15:07 (Formatter Plus v4.8.7) */
SELECT    'SELECT TO_CHAR('
      || column_name
      || ',''MON-YYYY'') "month-year('||table_name||')",COUNT(*) FROM SOA_SOAINFRA.'
      || table_name
      || ' GROUP BY TO_CHAR('
      || column_name
      || ',''MON-YYYY'' ) order by max(CREATION_DATE) ;'
 FROM (SELECT DISTINCT table_name, column_name
                  FROM cols
                 WHERE (   column_name LIKE 'CREATION_DATE%'
                        OR column_name LIKE 'CREATED_TIME%'
                       ))

/* Formatted on 2012/08/09 17:35 (Formatter Plus v4.8.7) */
SELECT   (CASE
            WHEN state = 1
               THEN 'OPEN AND RUNNING'
            WHEN state = 2
               THEN 'OPEN AND SUSPENDED'
            WHEN state = 3
               THEN 'OPEN AND FAULTED'
            WHEN state = 4
               THEN 'CLOSED AND PENDING'
            WHEN state = 5
               THEN 'CLOSED AND COMPLETED'
            WHEN state = 6
               THEN 'CLOSED AND FAUTED'
            WHEN state = 7
               THEN 'CLOSED AND CANCELLED'
            WHEN state = 8
               THEN 'CLOSED AND ABORTED'
            WHEN state = 9
               THEN 'CLOSED AND STALE'
            WHEN state = 10
               THEN 'NON-RECOVERABLE'
            ELSE state || ''
         END
        )|| '-' ||state AS state,
        TO_CHAR (creation_date, 'MON-YYYY') "month-year(CUBE_INSTANCE)",
        COUNT (*)
   FROM soa_soainfra.cube_instance
GROUP BY TO_CHAR (creation_date, 'MON-YYYY'), state
ORDER BY MAX (creation_date);

/* Formatted on 2012/08/09 17:35 (Formatter Plus v4.8.7) */
SELECT   (CASE
             WHEN component_state = 0
                THEN 'No faults but there still might be running instances'
             WHEN component_state = 1
                THEN ' At least one case is aborted by user'
             WHEN component_state = 2
                THEN ' At least one case is faulted (non-recoverable)'
             WHEN component_state = 3
                THEN ' At least one case is faulted and one case is aborted'
             WHEN component_state = 4
                THEN ' At least one case is in recovery required state'
             WHEN component_state = 5
                THEN 'At least one case is in recovery required state and at least one is aborted'
             WHEN component_state = 6
                THEN 'At least one case is in recovery required state and at least one is faulted'
             WHEN component_state = 7
                THEN 'At least one case is in recovery required state, one faulted and one aborted'
             WHEN component_state >= 8 and component_state <16
                THEN 'Running'
             WHEN component_state >= 16
                THEN 'Stale'
             ELSE component_state || ''
          END
         ) ||'-'||component_state AS component_state,
         TO_CHAR (created_time, 'MON-YYYY') "month-year(CUBE_INSTANCE)",
         COUNT (*)
    FROM soa_soainfra.mediator_instance
GROUP BY TO_CHAR (created_time, 'MON-YYYY'), component_state
ORDER BY MAX (created_time);