Amit's Oracle DBA Blog: auto jobs in Oracle database

SQL> select CLIENT_NAME,STATUS from dba_autotask_client;

CLIENT_NAME STATUS

---------------------------------------- ----------

sql tuning advisor ENABLED

auto optimizer stats collection ENABLED

auto space advisor ENABLED

set lines 200 pages 200

col WINDOW_NAME for a20

select window_name, autotask_status, optimizer_stats from dba_autotask_window_clients;

WINDOW_NAME AUTOTASK OPTIMIZE

-------------------- -------- --------

MONDAY_WINDOW ENABLED ENABLED

TUESDAY_WINDOW ENABLED ENABLED

WEDNESDAY_WINDOW ENABLED ENABLED

THURSDAY_WINDOW ENABLED ENABLED

FRIDAY_WINDOW ENABLED ENABLED

SATURDAY_WINDOW ENABLED ENABLED

SUNDAY_WINDOW ENABLED ENABLED

7 rows selected.

COLUMN client_name FORMAT A40
COLUMN window_name FORMAT A20
COLUMN job_start_time FORMAT A45
COLUMN job_duration FORMAT A20
COLUMN job_status FORMAT A10
ELECT client_name,
window_name,
job_start_time,
job_duration,
job_status,
job_error
FROM   dba_autotask_job_history
ORDER BY job_start_time;

CLIENT_NAME                              WINDOW_NAME          JOB_START_TIME                                JOB_DURATION         JOB_STATUS  JOB_ERROR
---------------------------------------- -------------------- --------------------------------------------- -------------------- ---------- ----------
auto space advisor                       MONDAY_WINDOW        27-JAN-25 10.00.01.363593 PM EUROPE/VIENNA    +000 00:02:23        SUCCEEDED           0
auto optimizer stats collection          MONDAY_WINDOW        27-JAN-25 10.00.01.831521 PM EUROPE/VIENNA    +000 00:43:37        SUCCEEDED           0
sql tuning advisor                       MONDAY_WINDOW        27-JAN-25 10.00.01.842112 PM EUROPE/VIENNA    +000 00:04:05        SUCCEEDED           0
auto space advisor                       TUESDAY_WINDOW       28-JAN-25 10.00.02.085979 PM EUROPE/VIENNA    +000 00:02:54        SUCCEEDED           0
auto optimizer stats collection          TUESDAY_WINDOW       28-JAN-25 10.00.02.179086 PM EUROPE/VIENNA    +000 00:59:19        SUCCEEDED           0
sql tuning advisor                       TUESDAY_WINDOW       28-JAN-25 10.00.02.184398 PM EUROPE/VIENNA    +000 00:08:25        SUCCEEDED           0
auto space advisor                       WEDNESDAY_WINDOW     29-JAN-25 10.00.02.092302 PM EUROPE/VIENNA    +000 00:03:46        SUCCEEDED           0
auto optimizer stats collection          WEDNESDAY_WINDOW     29-JAN-25 10.00.02.157363 PM EUROPE/VIENNA    +000 01:02:04        SUCCEEDED           0
sql tuning advisor                       WEDNESDAY_WINDOW     29-JAN-25 10.00.02.162733 PM EUROPE/VIENNA    +000 00:16:11        SUCCEEDED           0
auto optimizer stats collection          THURSDAY_WINDOW      30-JAN-25 10.00.02.124090 PM EUROPE/VIENNA    +000 00:55:03        SUCCEEDED           0
sql tuning advisor                       THURSDAY_WINDOW      30-JAN-25 10.00.02.136178 PM EUROPE/VIENNA    +000 00:00:47        SUCCEEDED           0
auto space advisor                       THURSDAY_WINDOW      30-JAN-25 10.00.02.173986 PM EUROPE/VIENNA    +000 00:03:21        SUCCEEDED           0
auto space advisor                       FRIDAY_WINDOW        31-JAN-25 10.00.01.119982 PM EUROPE/VIENNA    +000 00:03:19        SUCCEEDED           0
auto optimizer stats collection          FRIDAY_WINDOW        31-JAN-25 10.00.01.910291 PM EUROPE/VIENNA    +000 00:51:06        SUCCEEDED           0
sql tuning advisor                       FRIDAY_WINDOW        31-JAN-25 10.00.01.915641 PM EUROPE/VIENNA    +000 00:01:15        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      01-FEB-25 06.00.00.535741 AM EUROPE/VIENNA    +000 00:11:46        SUCCEEDED           0
sql tuning advisor                       SATURDAY_WINDOW      01-FEB-25 06.00.00.550334 AM EUROPE/VIENNA    +000 00:00:18        SUCCEEDED           0
auto space advisor                       SATURDAY_WINDOW      01-FEB-25 06.00.02.278236 AM EUROPE/VIENNA    +000 00:03:29        SUCCEEDED           0
auto space advisor                       SATURDAY_WINDOW      01-FEB-25 10.07.16.175815 AM EUROPE/VIENNA    +000 00:01:09        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      01-FEB-25 10.07.17.765621 AM EUROPE/VIENNA    +000 00:06:05        SUCCEEDED           0
auto space advisor                       SATURDAY_WINDOW      01-FEB-25 02.07.37.820356 PM EUROPE/VIENNA    +000 00:01:04        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      01-FEB-25 02.07.37.948755 PM EUROPE/VIENNA    +000 00:14:42        SUCCEEDED           0
auto space advisor                       SATURDAY_WINDOW      01-FEB-25 06.07.59.547676 PM EUROPE/VIENNA    +000 00:00:42        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      01-FEB-25 06.08.01.192833 PM EUROPE/VIENNA    +000 00:03:44        SUCCEEDED           0
auto space advisor                       SATURDAY_WINDOW      01-FEB-25 10.08.21.491501 PM EUROPE/VIENNA    +000 00:00:45        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      01-FEB-25 10.08.21.980040 PM EUROPE/VIENNA    +000 00:34:47        SUCCEEDED           0
auto space advisor                       SUNDAY_WINDOW        02-FEB-25 06.00.02.158638 AM EUROPE/VIENNA    +000 00:01:23        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        02-FEB-25 06.00.02.221695 AM EUROPE/VIENNA    +000 00:31:47        SUCCEEDED           0
sql tuning advisor                       SUNDAY_WINDOW        02-FEB-25 06.00.02.224352 AM EUROPE/VIENNA    +000 00:00:36        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        02-FEB-25 10.00.55.203602 AM EUROPE/VIENNA    +000 00:05:09        SUCCEEDED           0
auto space advisor                       SUNDAY_WINDOW        02-FEB-25 10.00.55.581203 AM EUROPE/VIENNA    +000 00:00:59        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        02-FEB-25 02.01.25.993951 PM EUROPE/VIENNA    +000 00:04:54        SUCCEEDED           0
auto space advisor                       SUNDAY_WINDOW        02-FEB-25 02.01.26.570091 PM EUROPE/VIENNA    +000 00:01:08        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        02-FEB-25 06.01.56.788824 PM EUROPE/VIENNA    +000 00:03:39        SUCCEEDED           0
auto space advisor                       SUNDAY_WINDOW        02-FEB-25 06.01.58.588920 PM EUROPE/VIENNA    +000 00:00:46        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        02-FEB-25 10.02.28.221273 PM EUROPE/VIENNA    +000 00:05:10        SUCCEEDED           0
auto space advisor                       SUNDAY_WINDOW        02-FEB-25 10.02.28.467424 PM EUROPE/VIENNA    +000 00:00:47        SUCCEEDED           0
auto optimizer stats collection          MONDAY_WINDOW        03-FEB-25 10.00.02.140007 PM EUROPE/VIENNA    +000 00:53:47        SUCCEEDED           0
sql tuning advisor                       MONDAY_WINDOW        03-FEB-25 10.00.02.155204 PM EUROPE/VIENNA    +000 00:07:04        SUCCEEDED           0
auto space advisor                       MONDAY_WINDOW        03-FEB-25 10.00.02.256315 PM EUROPE/VIENNA    +000 00:02:37        SUCCEEDED           0
auto optimizer stats collection          TUESDAY_WINDOW       04-FEB-25 10.00.02.268355 PM EUROPE/VIENNA    +000 00:58:48        SUCCEEDED           0
sql tuning advisor                       TUESDAY_WINDOW       04-FEB-25 10.00.02.271558 PM EUROPE/VIENNA    +000 00:04:01        SUCCEEDED           0
auto space advisor                       TUESDAY_WINDOW       04-FEB-25 10.00.02.422746 PM EUROPE/VIENNA    +000 00:02:46        SUCCEEDED           0
auto optimizer stats collection          WEDNESDAY_WINDOW     05-FEB-25 10.00.02.105067 PM EUROPE/VIENNA    +000 00:56:27        SUCCEEDED           0
sql tuning advisor                       WEDNESDAY_WINDOW     05-FEB-25 10.00.02.121161 PM EUROPE/VIENNA    +000 00:06:32        SUCCEEDED           0
auto space advisor                       WEDNESDAY_WINDOW     05-FEB-25 10.00.02.261629 PM EUROPE/VIENNA    +000 00:02:32        SUCCEEDED           0
auto space advisor                       THURSDAY_WINDOW      06-FEB-25 10.00.02.155453 PM EUROPE/VIENNA    +000 00:02:38        SUCCEEDED           0
auto optimizer stats collection          THURSDAY_WINDOW      06-FEB-25 10.00.02.271994 PM EUROPE/VIENNA    +000 00:51:01        SUCCEEDED           0
sql tuning advisor                       THURSDAY_WINDOW      06-FEB-25 10.00.02.272712 PM EUROPE/VIENNA    +000 00:01:39        SUCCEEDED           0
auto optimizer stats collection          FRIDAY_WINDOW        07-FEB-25 10.00.02.141611 PM EUROPE/VIENNA    +000 00:54:37        SUCCEEDED           0
sql tuning advisor                       FRIDAY_WINDOW        07-FEB-25 10.00.02.159874 PM EUROPE/VIENNA    +000 00:02:49        SUCCEEDED           0
auto space advisor                       FRIDAY_WINDOW        07-FEB-25 10.00.02.163998 PM EUROPE/VIENNA    +000 00:02:55        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      08-FEB-25 06.00.02.100437 AM EUROPE/VIENNA    +000 00:31:31        SUCCEEDED           0
sql tuning advisor                       SATURDAY_WINDOW      08-FEB-25 06.00.02.112048 AM EUROPE/VIENNA    +000 00:00:19        SUCCEEDED           0
auto space advisor                       SATURDAY_WINDOW      08-FEB-25 06.00.02.167898 AM EUROPE/VIENNA    +000 00:03:34        SUCCEEDED           0
auto space advisor                       SATURDAY_WINDOW      08-FEB-25 10.04.06.795189 AM EUROPE/VIENNA    +000 00:01:00        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      08-FEB-25 10.04.07.647752 AM EUROPE/VIENNA    +000 00:02:55        SUCCEEDED           0
auto space advisor                       SATURDAY_WINDOW      08-FEB-25 02.04.45.683795 PM EUROPE/VIENNA    +000 00:01:05        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      08-FEB-25 02.04.46.931043 PM EUROPE/VIENNA    +000 00:07:49        SUCCEEDED           0
auto space advisor                       SATURDAY_WINDOW      08-FEB-25 06.05.23.914144 PM EUROPE/VIENNA    +000 00:00:36        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      08-FEB-25 06.05.25.280237 PM EUROPE/VIENNA    +000 00:02:49        SUCCEEDED           0
auto space advisor                       SATURDAY_WINDOW      08-FEB-25 10.06.02.174402 PM EUROPE/VIENNA    +000 00:00:36        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      08-FEB-25 10.06.03.268771 PM EUROPE/VIENNA    +000 00:35:10        SUCCEEDED           0
auto space advisor                       SUNDAY_WINDOW        09-FEB-25 06.00.02.085043 AM EUROPE/VIENNA    +000 00:01:12        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        09-FEB-25 06.00.02.154818 AM EUROPE/VIENNA    +000 00:36:54        SUCCEEDED           0
sql tuning advisor                       SUNDAY_WINDOW        09-FEB-25 06.00.02.166714 AM EUROPE/VIENNA    +000 00:00:19        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        09-FEB-25 10.08.00.168727 AM EUROPE/VIENNA    +000 00:04:22        SUCCEEDED           0
auto space advisor                       SUNDAY_WINDOW        09-FEB-25 10.08.00.657642 AM EUROPE/VIENNA    +000 00:00:52        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        09-FEB-25 02.08.39.366606 PM EUROPE/VIENNA    +000 00:06:50        SUCCEEDED           0
auto space advisor                       SUNDAY_WINDOW        09-FEB-25 02.08.40.105829 PM EUROPE/VIENNA    +000 00:01:05        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        09-FEB-25 06.09.18.565542 PM EUROPE/VIENNA    +000 00:02:27        SUCCEEDED           0
auto space advisor                       SUNDAY_WINDOW        09-FEB-25 06.09.20.068104 PM EUROPE/VIENNA    +000 00:00:34        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        09-FEB-25 10.09.58.133107 PM EUROPE/VIENNA    +000 00:03:45        SUCCEEDED           0
auto space advisor                       SUNDAY_WINDOW        09-FEB-25 10.09.58.598411 PM EUROPE/VIENNA    +000 00:00:39        SUCCEEDED           0
auto optimizer stats collection          MONDAY_WINDOW        10-FEB-25 10.00.01.061017 PM EUROPE/VIENNA    +000 00:49:01        SUCCEEDED           0
sql tuning advisor                       MONDAY_WINDOW        10-FEB-25 10.00.01.074586 PM EUROPE/VIENNA    +000 00:02:57        SUCCEEDED           0
auto space advisor                       MONDAY_WINDOW        10-FEB-25 10.00.02.049900 PM EUROPE/VIENNA    +000 00:02:11        SUCCEEDED           0
auto optimizer stats collection          TUESDAY_WINDOW       11-FEB-25 10.00.02.090952 PM EUROPE/VIENNA    +000 00:50:57        SUCCEEDED           0
sql tuning advisor                       TUESDAY_WINDOW       11-FEB-25 10.00.02.091583 PM EUROPE/VIENNA    +000 00:01:48        SUCCEEDED           0
auto space advisor                       TUESDAY_WINDOW       11-FEB-25 10.00.02.137180 PM EUROPE/VIENNA    +000 00:03:10        SUCCEEDED           0
auto optimizer stats collection          WEDNESDAY_WINDOW     12-FEB-25 10.00.02.109709 PM EUROPE/VIENNA    +000 01:09:29        SUCCEEDED           0
sql tuning advisor                       WEDNESDAY_WINDOW     12-FEB-25 10.00.02.122420 PM EUROPE/VIENNA    +000 00:01:22        SUCCEEDED           0
auto space advisor                       WEDNESDAY_WINDOW     12-FEB-25 10.00.02.195834 PM EUROPE/VIENNA    +000 00:02:40        SUCCEEDED           0
sql tuning advisor                       THURSDAY_WINDOW      13-FEB-25 10.00.02.117061 PM EUROPE/VIENNA    +000 00:03:45        SUCCEEDED           0
auto optimizer stats collection          THURSDAY_WINDOW      13-FEB-25 10.00.02.118497 PM EUROPE/VIENNA    +000 00:54:47        SUCCEEDED           0
auto space advisor                       THURSDAY_WINDOW      13-FEB-25 10.00.02.188624 PM EUROPE/VIENNA    +000 00:03:54        SUCCEEDED           0
auto space advisor                       FRIDAY_WINDOW        14-FEB-25 10.00.00.407503 PM EUROPE/VIENNA    +000 00:02:11        SUCCEEDED           0
auto optimizer stats collection          FRIDAY_WINDOW        14-FEB-25 10.00.01.031294 PM EUROPE/VIENNA    +000 00:45:50        SUCCEEDED           0
sql tuning advisor                       FRIDAY_WINDOW        14-FEB-25 10.00.01.036402 PM EUROPE/VIENNA    +000 00:00:41        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      15-FEB-25 06.00.01.691384 AM EUROPE/VIENNA    +000 00:19:52        SUCCEEDED           0
sql tuning advisor                       SATURDAY_WINDOW      15-FEB-25 06.00.01.730539 AM EUROPE/VIENNA    +000 00:00:52        SUCCEEDED           0
auto space advisor                       SATURDAY_WINDOW      15-FEB-25 06.00.01.802953 AM EUROPE/VIENNA    +000 00:03:48        SUCCEEDED           0
auto space advisor                       SATURDAY_WINDOW      15-FEB-25 10.01.49.427607 AM EUROPE/VIENNA    +000 00:01:10        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      15-FEB-25 10.01.49.703370 AM EUROPE/VIENNA    +000 00:07:25        SUCCEEDED           0
auto space advisor                       SATURDAY_WINDOW      15-FEB-25 02.02.11.541873 PM EUROPE/VIENNA    +000 00:01:01        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      15-FEB-25 02.02.12.188622 PM EUROPE/VIENNA    +000 00:04:27        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      15-FEB-25 06.02.33.463346 PM EUROPE/VIENNA    +000 00:05:05        SUCCEEDED           0
auto space advisor                       SATURDAY_WINDOW      15-FEB-25 06.02.33.623522 PM EUROPE/VIENNA    +000 00:00:39        SUCCEEDED           0
auto space advisor                       SATURDAY_WINDOW      15-FEB-25 10.02.55.859293 PM EUROPE/VIENNA    +000 00:03:07        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      15-FEB-25 10.02.58.583606 PM EUROPE/VIENNA    +000 00:44:40        SUCCEEDED           0
auto space advisor                       SUNDAY_WINDOW        16-FEB-25 06.00.02.236870 AM EUROPE/VIENNA    +000 00:01:31        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        16-FEB-25 06.00.02.412556 AM EUROPE/VIENNA    +000 00:51:02        SUCCEEDED           0
sql tuning advisor                       SUNDAY_WINDOW        16-FEB-25 06.00.02.420869 AM EUROPE/VIENNA    +000 00:02:06        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        16-FEB-25 10.04.10.094803 AM EUROPE/VIENNA    +000 00:05:39        SUCCEEDED           0
auto space advisor                       SUNDAY_WINDOW        16-FEB-25 10.04.10.482845 AM EUROPE/VIENNA    +000 00:00:55        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        16-FEB-25 02.04.35.425045 PM EUROPE/VIENNA    +000 00:05:13        SUCCEEDED           0
auto space advisor                       SUNDAY_WINDOW        16-FEB-25 02.04.35.645786 PM EUROPE/VIENNA    +000 00:00:56        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        16-FEB-25 06.04.58.896427 PM EUROPE/VIENNA    +000 00:05:07        SUCCEEDED           0
auto space advisor                       SUNDAY_WINDOW        16-FEB-25 06.04.59.749589 PM EUROPE/VIENNA    +000 00:00:38        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        16-FEB-25 10.05.22.494430 PM EUROPE/VIENNA    +000 00:04:44        SUCCEEDED           0
auto space advisor                       SUNDAY_WINDOW        16-FEB-25 10.05.23.126515 PM EUROPE/VIENNA    +000 00:00:36        SUCCEEDED           0
auto space advisor                       MONDAY_WINDOW        17-FEB-25 10.00.01.186325 PM EUROPE/VIENNA    +000 00:02:24        SUCCEEDED           0
auto optimizer stats collection          MONDAY_WINDOW        17-FEB-25 10.00.02.033349 PM EUROPE/VIENNA    +000 00:50:32        SUCCEEDED           0
sql tuning advisor                       MONDAY_WINDOW        17-FEB-25 10.00.02.040049 PM EUROPE/VIENNA    +000 00:01:42        SUCCEEDED           0
auto space advisor                       TUESDAY_WINDOW       18-FEB-25 10.00.01.943307 PM EUROPE/VIENNA    +000 00:02:01        SUCCEEDED           0
auto optimizer stats collection          TUESDAY_WINDOW       18-FEB-25 10.00.01.987854 PM EUROPE/VIENNA    +000 00:45:13        SUCCEEDED           0
sql tuning advisor                       TUESDAY_WINDOW       18-FEB-25 10.00.01.993226 PM EUROPE/VIENNA    +000 00:00:22        SUCCEEDED           0
auto space advisor                       WEDNESDAY_WINDOW     19-FEB-25 10.00.02.196485 PM EUROPE/VIENNA    +000 00:02:20        SUCCEEDED           0
auto optimizer stats collection          WEDNESDAY_WINDOW     19-FEB-25 10.00.02.208539 PM EUROPE/VIENNA    +000 03:12:19        SUCCEEDED           0
sql tuning advisor                       WEDNESDAY_WINDOW     19-FEB-25 10.00.02.214502 PM EUROPE/VIENNA    +000 00:00:42        SUCCEEDED           0
auto optimizer stats collection          THURSDAY_WINDOW      20-FEB-25 10.00.02.541976 PM EUROPE/VIENNA    +000 00:48:44        SUCCEEDED           0
sql tuning advisor                       THURSDAY_WINDOW      20-FEB-25 10.00.02.554391 PM EUROPE/VIENNA    +000 00:18:22        SUCCEEDED           0
auto space advisor                       THURSDAY_WINDOW      20-FEB-25 10.00.02.661662 PM EUROPE/VIENNA    +000 00:06:46        SUCCEEDED           0
auto optimizer stats collection          FRIDAY_WINDOW        21-FEB-25 10.00.01.440892 PM EUROPE/VIENNA    +000 00:55:42        SUCCEEDED           0
sql tuning advisor                       FRIDAY_WINDOW        21-FEB-25 10.00.01.447948 PM EUROPE/VIENNA    +000 00:07:49        SUCCEEDED           0
auto space advisor                       FRIDAY_WINDOW        21-FEB-25 10.00.01.996956 PM EUROPE/VIENNA    +000 00:05:48        SUCCEEDED           0
auto space advisor                       SATURDAY_WINDOW      22-FEB-25 06.00.02.178793 AM EUROPE/VIENNA    +000 00:04:05        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      22-FEB-25 06.00.02.255533 AM EUROPE/VIENNA    +000 00:23:05        SUCCEEDED           0
sql tuning advisor                       SATURDAY_WINDOW      22-FEB-25 06.00.02.267820 AM EUROPE/VIENNA    +000 00:00:19        SUCCEEDED           0
auto space advisor                       SATURDAY_WINDOW      22-FEB-25 10.02.16.119064 AM EUROPE/VIENNA    +000 00:03:04        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      22-FEB-25 10.02.17.260535 AM EUROPE/VIENNA    +000 00:08:39        SUCCEEDED           0
auto space advisor                       SATURDAY_WINDOW      22-FEB-25 02.02.28.148920 PM EUROPE/VIENNA    +000 00:01:03        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      22-FEB-25 02.02.29.521844 PM EUROPE/VIENNA    +000 00:04:47        SUCCEEDED           0
auto space advisor                       SATURDAY_WINDOW      22-FEB-25 06.02.39.797361 PM EUROPE/VIENNA    +000 00:00:32        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      22-FEB-25 06.02.40.526516 PM EUROPE/VIENNA    +000 00:02:32        SUCCEEDED           0
auto space advisor                       SATURDAY_WINDOW      22-FEB-25 10.02.51.675859 PM EUROPE/VIENNA    +000 00:02:58        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      22-FEB-25 10.02.52.452264 PM EUROPE/VIENNA    +000 00:37:16        SUCCEEDED           0
auto space advisor                       SUNDAY_WINDOW        23-FEB-25 06.00.02.290828 AM EUROPE/VIENNA    +000 00:02:03        SUCCEEDED           0
sql tuning advisor                       SUNDAY_WINDOW        23-FEB-25 06.00.02.494378 AM EUROPE/VIENNA    +000 00:02:31        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        23-FEB-25 06.00.02.497059 AM EUROPE/VIENNA    +000 00:50:08        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        23-FEB-25 10.03.30.785736 AM EUROPE/VIENNA    +000 00:04:37        SUCCEEDED           0
auto space advisor                       SUNDAY_WINDOW        23-FEB-25 10.03.34.818922 AM EUROPE/VIENNA    +000 00:01:29        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        23-FEB-25 02.03.45.323118 PM EUROPE/VIENNA    +000 00:06:38        SUCCEEDED           0
auto space advisor                       SUNDAY_WINDOW        23-FEB-25 02.03.45.600399 PM EUROPE/VIENNA    +000 00:01:02        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        23-FEB-25 06.03.58.690141 PM EUROPE/VIENNA    +000 00:02:44        SUCCEEDED           0
auto space advisor                       SUNDAY_WINDOW        23-FEB-25 06.04.00.388695 PM EUROPE/VIENNA    +000 00:00:38        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        23-FEB-25 10.04.12.003046 PM EUROPE/VIENNA    +000 00:04:42        SUCCEEDED           0
auto space advisor                       SUNDAY_WINDOW        23-FEB-25 10.04.12.861053 PM EUROPE/VIENNA    +000 00:01:04        SUCCEEDED           0
auto optimizer stats collection          MONDAY_WINDOW        24-FEB-25 10.00.00.705692 PM EUROPE/VIENNA    +000 01:03:20        SUCCEEDED           0
sql tuning advisor                       MONDAY_WINDOW        24-FEB-25 10.00.00.712772 PM EUROPE/VIENNA    +000 00:12:53        SUCCEEDED           0
auto space advisor                       MONDAY_WINDOW        24-FEB-25 10.00.02.309166 PM EUROPE/VIENNA    +000 00:06:38        SUCCEEDED           0
auto optimizer stats collection          TUESDAY_WINDOW       25-FEB-25 10.00.02.620535 PM EUROPE/VIENNA    +000 00:51:34        SUCCEEDED           0
sql tuning advisor                       TUESDAY_WINDOW       25-FEB-25 10.00.02.625730 PM EUROPE/VIENNA    +000 00:05:52        SUCCEEDED           0
auto space advisor                       TUESDAY_WINDOW       25-FEB-25 10.00.02.815784 PM EUROPE/VIENNA    +000 00:05:54        SUCCEEDED           0

154 rows selected.

COLUMN client_name FORMAT A40
COLUMN window_name FORMAT A20
COLUMN job_start_time FORMAT A45
COLUMN job_duration FORMAT A20
COLUMN job_status FORMAT A10
SELECT client_name,
window_name,
job_start_time,
job_duration,
job_status,
job_error
FROM dba_autotask_job_history where client_name='auto optimizer stats collection'
ORDER BY job_start_time;

CLIENT_NAME                              WINDOW_NAME          JOB_START_TIME                                JOB_DURATION         JOB_STATUS  JOB_ERROR
---------------------------------------- -------------------- --------------------------------------------- -------------------- ---------- ----------
auto optimizer stats collection          MONDAY_WINDOW        27-JAN-25 10.00.01.831521 PM EUROPE/VIENNA    +000 00:43:37        SUCCEEDED           0
auto optimizer stats collection          TUESDAY_WINDOW       28-JAN-25 10.00.02.179086 PM EUROPE/VIENNA    +000 00:59:19        SUCCEEDED           0
auto optimizer stats collection          WEDNESDAY_WINDOW     29-JAN-25 10.00.02.157363 PM EUROPE/VIENNA    +000 01:02:04        SUCCEEDED           0
auto optimizer stats collection          THURSDAY_WINDOW      30-JAN-25 10.00.02.124090 PM EUROPE/VIENNA    +000 00:55:03        SUCCEEDED           0
auto optimizer stats collection          FRIDAY_WINDOW        31-JAN-25 10.00.01.910291 PM EUROPE/VIENNA    +000 00:51:06        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      01-FEB-25 06.00.00.535741 AM EUROPE/VIENNA    +000 00:11:46        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      01-FEB-25 10.07.17.765621 AM EUROPE/VIENNA    +000 00:06:05        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      01-FEB-25 02.07.37.948755 PM EUROPE/VIENNA    +000 00:14:42        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      01-FEB-25 06.08.01.192833 PM EUROPE/VIENNA    +000 00:03:44        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      01-FEB-25 10.08.21.980040 PM EUROPE/VIENNA    +000 00:34:47        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        02-FEB-25 06.00.02.221695 AM EUROPE/VIENNA    +000 00:31:47        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        02-FEB-25 10.00.55.203602 AM EUROPE/VIENNA    +000 00:05:09        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        02-FEB-25 02.01.25.993951 PM EUROPE/VIENNA    +000 00:04:54        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        02-FEB-25 06.01.56.788824 PM EUROPE/VIENNA    +000 00:03:39        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        02-FEB-25 10.02.28.221273 PM EUROPE/VIENNA    +000 00:05:10        SUCCEEDED           0
auto optimizer stats collection          MONDAY_WINDOW        03-FEB-25 10.00.02.140007 PM EUROPE/VIENNA    +000 00:53:47        SUCCEEDED           0
auto optimizer stats collection          TUESDAY_WINDOW       04-FEB-25 10.00.02.268355 PM EUROPE/VIENNA    +000 00:58:48        SUCCEEDED           0
auto optimizer stats collection          WEDNESDAY_WINDOW     05-FEB-25 10.00.02.105067 PM EUROPE/VIENNA    +000 00:56:27        SUCCEEDED           0
auto optimizer stats collection          THURSDAY_WINDOW      06-FEB-25 10.00.02.271994 PM EUROPE/VIENNA    +000 00:51:01        SUCCEEDED           0
auto optimizer stats collection          FRIDAY_WINDOW        07-FEB-25 10.00.02.141611 PM EUROPE/VIENNA    +000 00:54:37        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      08-FEB-25 06.00.02.100437 AM EUROPE/VIENNA    +000 00:31:31        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      08-FEB-25 10.04.07.647752 AM EUROPE/VIENNA    +000 00:02:55        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      08-FEB-25 02.04.46.931043 PM EUROPE/VIENNA    +000 00:07:49        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      08-FEB-25 06.05.25.280237 PM EUROPE/VIENNA    +000 00:02:49        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      08-FEB-25 10.06.03.268771 PM EUROPE/VIENNA    +000 00:35:10        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        09-FEB-25 06.00.02.154818 AM EUROPE/VIENNA    +000 00:36:54        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        09-FEB-25 10.08.00.168727 AM EUROPE/VIENNA    +000 00:04:22        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        09-FEB-25 02.08.39.366606 PM EUROPE/VIENNA    +000 00:06:50        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        09-FEB-25 06.09.18.565542 PM EUROPE/VIENNA    +000 00:02:27        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        09-FEB-25 10.09.58.133107 PM EUROPE/VIENNA    +000 00:03:45        SUCCEEDED           0
auto optimizer stats collection          MONDAY_WINDOW        10-FEB-25 10.00.01.061017 PM EUROPE/VIENNA    +000 00:49:01        SUCCEEDED           0
auto optimizer stats collection          TUESDAY_WINDOW       11-FEB-25 10.00.02.090952 PM EUROPE/VIENNA    +000 00:50:57        SUCCEEDED           0
auto optimizer stats collection          WEDNESDAY_WINDOW     12-FEB-25 10.00.02.109709 PM EUROPE/VIENNA    +000 01:09:29        SUCCEEDED           0
auto optimizer stats collection          THURSDAY_WINDOW      13-FEB-25 10.00.02.118497 PM EUROPE/VIENNA    +000 00:54:47        SUCCEEDED           0
auto optimizer stats collection          FRIDAY_WINDOW        14-FEB-25 10.00.01.031294 PM EUROPE/VIENNA    +000 00:45:50        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      15-FEB-25 06.00.01.691384 AM EUROPE/VIENNA    +000 00:19:52        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      15-FEB-25 10.01.49.703370 AM EUROPE/VIENNA    +000 00:07:25        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      15-FEB-25 02.02.12.188622 PM EUROPE/VIENNA    +000 00:04:27        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      15-FEB-25 06.02.33.463346 PM EUROPE/VIENNA    +000 00:05:05        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      15-FEB-25 10.02.58.583606 PM EUROPE/VIENNA    +000 00:44:40        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        16-FEB-25 06.00.02.412556 AM EUROPE/VIENNA    +000 00:51:02        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        16-FEB-25 10.04.10.094803 AM EUROPE/VIENNA    +000 00:05:39        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        16-FEB-25 02.04.35.425045 PM EUROPE/VIENNA    +000 00:05:13        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        16-FEB-25 06.04.58.896427 PM EUROPE/VIENNA    +000 00:05:07        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        16-FEB-25 10.05.22.494430 PM EUROPE/VIENNA    +000 00:04:44        SUCCEEDED           0
auto optimizer stats collection          MONDAY_WINDOW        17-FEB-25 10.00.02.033349 PM EUROPE/VIENNA    +000 00:50:32        SUCCEEDED           0
auto optimizer stats collection          TUESDAY_WINDOW       18-FEB-25 10.00.01.987854 PM EUROPE/VIENNA    +000 00:45:13        SUCCEEDED           0
auto optimizer stats collection          WEDNESDAY_WINDOW     19-FEB-25 10.00.02.208539 PM EUROPE/VIENNA    +000 03:12:19        SUCCEEDED           0
auto optimizer stats collection          THURSDAY_WINDOW      20-FEB-25 10.00.02.541976 PM EUROPE/VIENNA    +000 00:48:44        SUCCEEDED           0
auto optimizer stats collection          FRIDAY_WINDOW        21-FEB-25 10.00.01.440892 PM EUROPE/VIENNA    +000 00:55:42        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      22-FEB-25 06.00.02.255533 AM EUROPE/VIENNA    +000 00:23:05        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      22-FEB-25 10.02.17.260535 AM EUROPE/VIENNA    +000 00:08:39        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      22-FEB-25 02.02.29.521844 PM EUROPE/VIENNA    +000 00:04:47        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      22-FEB-25 06.02.40.526516 PM EUROPE/VIENNA    +000 00:02:32        SUCCEEDED           0
auto optimizer stats collection          SATURDAY_WINDOW      22-FEB-25 10.02.52.452264 PM EUROPE/VIENNA    +000 00:37:16        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        23-FEB-25 06.00.02.497059 AM EUROPE/VIENNA    +000 00:50:08        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        23-FEB-25 10.03.30.785736 AM EUROPE/VIENNA    +000 00:04:37        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        23-FEB-25 02.03.45.323118 PM EUROPE/VIENNA    +000 00:06:38        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        23-FEB-25 06.03.58.690141 PM EUROPE/VIENNA    +000 00:02:44        SUCCEEDED           0
auto optimizer stats collection          SUNDAY_WINDOW        23-FEB-25 10.04.12.003046 PM EUROPE/VIENNA    +000 00:04:42        SUCCEEDED           0
auto optimizer stats collection          MONDAY_WINDOW        24-FEB-25 10.00.00.705692 PM EUROPE/VIENNA    +000 01:03:20        SUCCEEDED           0
auto optimizer stats collection          TUESDAY_WINDOW       25-FEB-25 10.00.02.620535 PM EUROPE/VIENNA    +000 00:51:34        SUCCEEDED           0

62 rows selected.

set lines 200
col WINDOW_NAME for a25
col WINDOW_NEXT_TIME for a45
select WINDOW_NAME,WINDOW_NEXT_TIME,OPTIMIZER_STATS,WINDOW_ACTIVE,AUTOTASK_STATUS from DBA_AUTOTASK_WINDOW_CLIENTS;


WINDOW_NAME               WINDOW_NEXT_TIME                              OPTIMIZE WINDO AUTOTASK
------------------------- --------------------------------------------- -------- ----- --------
MONDAY_WINDOW             03-MAR-25 10.00.00.000000 PM EUROPE/VIENNA    ENABLED  FALSE ENABLED
TUESDAY_WINDOW            04-MAR-25 10.00.00.000000 PM EUROPE/VIENNA    ENABLED  FALSE ENABLED
WEDNESDAY_WINDOW          26-FEB-25 10.00.00.000000 PM EUROPE/VIENNA    ENABLED  FALSE ENABLED
THURSDAY_WINDOW           27-FEB-25 10.00.00.000000 PM EUROPE/VIENNA    ENABLED  FALSE ENABLED
FRIDAY_WINDOW             28-FEB-25 10.00.00.000000 PM EUROPE/VIENNA    ENABLED  FALSE ENABLED
SATURDAY_WINDOW           01-MAR-25 06.00.00.000000 AM EUROPE/VIENNA    ENABLED  FALSE ENABLED
SUNDAY_WINDOW             02-MAR-25 06.00.00.000000 AM EUROPE/VIENNA    ENABLED  FALSE ENABLED

7 rows selected.

SET LINES 300
SET PAGES 999
COL WINDOW_NAME FOR A18
COL REPEAT_INTERVAL FOR A72
COL WINDOW_DURATION FOR A32
COL ENABLED FOR A10
SELECT WINDOW_NAME,
REPEAT_INTERVAL,
TO_CHAR(EXTRACT(DAY FROM DURATION),'90') || ' Days ' ||
TO_CHAR(EXTRACT(HOUR FROM DURATION),'90') || ' Hours ' ||
TO_CHAR(EXTRACT(MINUTE FROM DURATION),'90') || ' Minutes '
WINDOW_DURATION,
ENABLED
FROM
DBA_SCHEDULER_WINDOWS;
  
  
WINDOW_NAME        REPEAT_INTERVAL                                                          WINDOW_DURATION                  ENABLED
------------------ ------------------------------------------------------------------------ -------------------------------- ----------
MONDAY_WINDOW      freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0                      0 Days   4 Hours   0 Minutes   TRUE
TUESDAY_WINDOW     freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0                      0 Days   4 Hours   0 Minutes   TRUE
WEDNESDAY_WINDOW   freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0                      0 Days   4 Hours   0 Minutes   TRUE
THURSDAY_WINDOW    freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0                      0 Days   4 Hours   0 Minutes   TRUE
FRIDAY_WINDOW      freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0                      0 Days   4 Hours   0 Minutes   TRUE
SATURDAY_WINDOW    freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0                       0 Days  20 Hours   0 Minutes   TRUE
SUNDAY_WINDOW      freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0                       0 Days  20 Hours   0 Minutes   TRUE
WEEKNIGHT_WINDOW   freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0      0 Days   8 Hours   0 Minutes   FALSE
WEEKEND_WINDOW     freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0                        2 Days   0 Hours   0 Minutes   FALSE

Amit's Oracle DBA Blog

Disclaimer

Wednesday, 26 February 2025

auto jobs in Oracle database

No comments:

Post a Comment

ORA-07445: exception encountered: core dump [qcpibfa()+235] [SIGSEGV] [ADDR:0x7FFF6A9C7C88] [PC:0x4BF0E4B] [Address not mapped to object

Labels