自動メンテナンスタスクの実行履歴を確認するSQL

INTERVAL DAY TO SECOND型なんて殆ど使ったことないから、
型変換に若干戸惑った。EXTRACT関数を使用する。

SQLは↓

SET LINESIZE 170
SET PAGESIZE 100
COLUMN CLIENT_NAME       FORMAT A33
COLUMN WINDOW_NAME       FORMAT A20
COLUMN WINDOW_START_TIME FORMAT A20
COLUMN JOB_START_TIME    FORMAT A30
COLUMN JOB_DURATION      FORMAT A20
COLUMN JOB_DURATION_HMS  FORMAT A12
SELECT CLIENT_NAME
     , WINDOW_NAME
     , TO_CHAR(WINDOW_START_TIME, 'YYYY/MM/DD HH24:MI:SS') AS WINDOW_START_TIME
     , TO_CHAR(JOB_START_TIME, 'YYYY/MM/DD HH24:MI:SS.FF') AS JOB_START_TIME
     , LPAD(EXTRACT(HOUR   FROM JOB_DURATION), 2, '0') || ':' ||
       LPAD(EXTRACT(MINUTE FROM JOB_DURATION), 2, '0') || ':' ||
       LPAD(EXTRACT(SECOND FROM JOB_DURATION), 2, '0') AS JOB_DURATION_HMS
  FROM DBA_AUTOTASK_JOB_HISTORY
 ORDER BY CLIENT_NAME
     , JOB_START_TIME;

結果は↓

CLIENT_NAME                       WINDOW_NAME          WINDOW_START_TIME    JOB_START_TIME                 JOB_DURATION
--------------------------------- -------------------- -------------------- ------------------------------ ------------
auto optimizer stats collection   SUNDAY_WINDOW        2011/10/02 20:00:00  2011/10/02 20:00:01.256234     00:05:42
auto optimizer stats collection   MONDAY_WINDOW        2011/10/03 20:00:00  2011/10/03 20:00:02.079586     00:12:29
auto optimizer stats collection   TUESDAY_WINDOW       2011/10/04 20:00:00  2011/10/04 20:00:01.461465     00:14:42
auto optimizer stats collection   WEDNESDAY_WINDOW     2011/10/05 20:00:00  2011/10/05 20:00:01.799999     00:17:54
auto optimizer stats collection   THURSDAY_WINDOW      2011/10/06 20:00:00  2011/10/06 20:00:03.623236     00:14:18
auto optimizer stats collection   FRIDAY_WINDOW        2011/10/07 20:00:00  2011/10/07 20:00:01.169406     00:16:20
auto optimizer stats collection   SATURDAY_WINDOW      2011/10/08 20:00:00  2011/10/08 20:00:02.170269     00:07:57

１つの列で期間を表現できるのは良いが、ちとややこしいな。