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

 

After patching 19.26 , we received complain from Application team that they were facing below error while executing one particular SQL_ID 

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

[root@rac01 ]$ cat /etc/security/limits.conf ---Before 

oracle   soft   nofile    1024

oracle   hard   nofile    65536

oracle   soft   nproc    16384

oracle   hard   nproc    16384

oracle   soft   stack    10240-------------------------unlimited

oracle   hard   stack    32768

oracle   hard   memlock    134217728

oracle   soft   memlock    134217728

oracle   soft   data    unlimited

oracle   hard   data    unlimited

oracle   soft   core    unlimited

oracle   hard   core    unlimited

 

grid   soft   nofile    1024

grid   hard   nofile    65536

grid   soft   nproc    16384

grid   hard   nproc    16384

grid   soft   stack    10240------------------------unlimited

grid   hard   stack    32768

grid   hard   memlock    134217728

grid   soft   memlock    134217728

grid   soft   data    unlimited

grid   hard   data    unlimited

grid   soft   core    unlimited

grid   hard   core    unlimited

We had # hash the value of stack for Grid and Oracle users and added * (unlimited)

[root@rac01 ]$ cat /etc/security/limits.conf ---After 

oracle   soft   nofile    1024

oracle   hard   nofile    65536

oracle   soft   nproc    16384

oracle   hard   nproc    16384

#oracle   soft   stack    10240

#oracle   hard   stack    32768

oracle   hard   memlock    134217728

oracle   soft   memlock    134217728

oracle   soft   data    unlimited

oracle   hard   data    unlimited

oracle   soft   core    unlimited

oracle   hard   core    unlimited

 

grid   soft   nofile    1024

grid   hard   nofile    65536

grid   soft   nproc    16384

grid   hard   nproc    16384

#grid   soft   stack    10240

#grid   hard   stack    32768

grid   hard   memlock    134217728

grid   soft   memlock    134217728

grid   soft   data    unlimited

grid   hard   data    unlimited

grid   soft   core    unlimited

grid   hard   core    unlimited

 

*       soft   stack    unlimited

*       hard   stack    unlimited 

 

[oracle@rac01 limits.d]$ cat oracle-database-preinstall-19c.conf

 

oracle   soft   nofile    1024

oracle   hard   nofile    65536

oracle   soft   nproc    16384

oracle   hard   nproc    16384

oracle   soft   stack    unlimited

oracle   hard   stack    unlimited

oracle   hard   memlock    134217728

oracle   soft   memlock    134217728

oracle   soft   data    unlimited

oracle   hard   data    unlimited

[oracle@rac01 limits.d]$ cd /etc/security/limits.d

[oracle@rac01 limits.d]$ ls -lrt

total 8

-rw-r--r-- 1 root root 1205 Feb 24 07:42 oracle-database-preinstall-19c.conf_24feb2025

-rw-r--r-- 1 root root 1213 Feb 24 07:43 oracle-database-preinstall-19c.conf ----------------Before

[oracle@bms04dbamd03p limits.d]$ cat oracle-database-preinstall-19c.conf

oracle   soft   nofile    1024

oracle   hard   nofile    65536

oracle   soft   nproc    16384

oracle   hard   nproc    16384

oracle   soft   stack    10240 ---Before

oracle   hard   stack    10240 ---Before

oracle   hard   memlock    134217728

oracle   soft   memlock    134217728

oracle   soft   data    unlimited

oracle   hard   data    unlimited

 [oracle@rac01 limits.d]$ ls -lrt

total 8

-rw-r--r-- 1 root root 1205 Feb 24 07:42 oracle-database-preinstall-19c.conf_24feb2025

-rw-r--r-- 1 root root 1213 Feb 24 07:43 oracle-database-preinstall-19c.conf ----------------After

[oracle@bms04dbamd03p limits.d]$ cat oracle-database-preinstall-19c.conf

oracle   soft   nofile    1024

oracle   hard   nofile    65536

oracle   soft   nproc    16384

oracle   hard   nproc    16384

oracle   soft   stack    unlimited ---After

oracle   hard   stack    unlimited ---After

oracle   hard   memlock    134217728

oracle   soft   memlock    134217728

oracle   soft   data    unlimited

oracle   hard   data    unlimited

 

 =======================================

 

We have also made the change in the s_crsconfig_rac01_env.txt file for CRS_LIMIT_STACK value from 2048 to 16384

 

 

[grid@rac01 install]$ cat s_crsconfig_rac01_env.txt

#########################################################################

#This file can be used to set values for the NLS_LANG and TZ environment

#variables and to set resource limits for Oracle Clusterware and

#Database processes.

#1. The NLS_LANG environment variable determines the language and

#   characterset used for messages. For example, a new value can be

#   configured by setting NLS_LANG=JAPANESE_JAPAN.UTF8

#2. The Time zone setting can be changed by setting the TZ entry to

#   the appropriate time zone name. For example, TZ=America/New_York

#3. Resource limits for stack size, open files and number of processes

#   can be specified by modifying the appropriate entries.

#

#Do not modify this file except as documented above or under the

#direction of Oracle Support Services.

#########################################################################

TZ=Europe/Berlin

NLS_LANG=AMERICAN_AMERICA.AL32UTF8

CRS_LIMIT_STACK=2048----------------------------we had this value 

CRS_LIMIT_OPENFILE=65536

CRS_LIMIT_NPROC=65536

TNS_ADMIN=

 

[root@rac01 ~]$ cd /applications/oracle/g19.3.0/grid_home/crs/install

[root@rac01 install]$

[root@rac01 install]$

[root@rac01 install]$

[root@rac01 install]$ ls -lrt *s_*

-rwxr-xr-x. 1 root oinstall   3593 Dec 30  2014 s_crsconfig_defs

-rwxr-xr-x  1 root oinstall   1000 Sep 19 16:46 s_crsconfig_rac01_env.txt_bkp_25022025

-rwxr-xr-x. 1 root oinstall   7348 Jan 20 14:03 s_oraolr.pm

-rwxr-xr-x. 1 root oinstall  19742 Jan 20 14:03 s_oraocr.pm

-rwxr-xr-x. 1 root oinstall   3917 Jan 20 14:03 s_orachm.pm

-rwxr-xr-x. 1 root oinstall 141610 Jan 20 14:03 s_crsutils.pm

-rwxr-xr-x  1 root oinstall   1001 Feb 25 15:53 s_crsconfig_rac01_env.txt

[oracle@rac01 install]$ cat s_crsconfig_rac01_env.txt

#########################################################################

#This file can be used to set values for the NLS_LANG and TZ environment

#variables and to set resource limits for Oracle Clusterware and

#Database processes.

#1. The NLS_LANG environment variable determines the language and

#   characterset used for messages. For example, a new value can be

#   configured by setting NLS_LANG=JAPANESE_JAPAN.UTF8

#2. The Time zone setting can be changed by setting the TZ entry to

#   the appropriate time zone name. For example, TZ=America/New_York

#3. Resource limits for stack size, open files and number of processes

#   can be specified by modifying the appropriate entries.

#

#Do not modify this file except as documented above or under the

#direction of Oracle Support Services.

#########################################################################

TZ=Europe/Berlin

NLS_LANG=AMERICAN_AMERICA.AL32UTF8

CRS_LIMIT_STACK=16384 -------------------------------------------------------------> Changed this value from 2048 to 16384 

CRS_LIMIT_OPENFILE=65536

CRS_LIMIT_NPROC=65536

TNS_ADMIN=

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

HOW TO RESCHEDULE THE AUTOMATIC OPTIMIZER STATISTICS COLLECTION JOB

 

Ensure that the Automatic Optimizer Statistics Collection Job is ENABLED.

SET LINES 300 
SET PAGES 999
COL CLIENT_NAME FOR A35
COL CONSUMER_GROUP FOR A30 
COL WINDOW_GROUP FOR A20 
COL STATUS FOR A10 

SELECT CLIENT_NAME,
       STATUS,
       CONSUMER_GROUP,
       CLIENT_TAG,
       WINDOW_GROUP 
FROM 
       DBA_AUTOTASK_CLIENT;
	   
	   
CLIENT_NAME                         STATUS     CONSUMER_GROUP       CL WINDOW_GROUP
----------------------------------- ---------- -------------------- -- --------------------
sql tuning advisor                  ENABLED    ORA$AUTOTASK         SQ ORA$AT_WGRP_SQ
auto optimizer stats collection     ENABLED    ORA$AUTOTASK         OS ORA$AT_WGRP_OS
auto space advisor                  ENABLED    ORA$AUTOTASK         SA ORA$AT_WGRP_SA

Here is the Automatic Optimizer Statistics Collection Job is ENABLED and assigned to the ORA$AT_WGRP_OS window group.

Logically a window group is like a container with different schedules. By default the ORA$AT_WGRP_OS group has schedules which called as WINDOW NAME.

SET LINES 300 
COL WINDOW_GROUP_NAME FOR A20
COL WINDOW_NAME FOR A20

SELECT * FROM DBA_SCHEDULER_WINGROUP_MEMBERS WHERE WINDOW_GROUP_NAME='ORA$AT_WGRP_OS';

WINDOW_GROUP_NAME    WINDOW_NAME
-------------------- --------------------
ORA$AT_WGRP_OS       MONDAY_WINDOW
ORA$AT_WGRP_OS       TUESDAY_WINDOW
ORA$AT_WGRP_OS       WEDNESDAY_WINDOW
ORA$AT_WGRP_OS       THURSDAY_WINDOW
ORA$AT_WGRP_OS       FRIDAY_WINDOW
ORA$AT_WGRP_OS       SATURDAY_WINDOW
ORA$AT_WGRP_OS       SUNDAY_WINDOW

7 rows selected.

Every WINDOW NAME is linked to a specific day of the week and runs at a specific period of time. 

The  view contains schedule details about every WINDOW NAME

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

9 rows selected.

As it can be seen from the above output the Monday-To-Friday WINDOW NAME start on Weekdays at 10 PM / 22  and last for 4 hours, 

and the Saturday-To-Sunday WINDOW NAME start on Weekends at 6 AM and last for 20 hours.

set lines 200
col WINDOW_NAME for a24
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-24 10.00.00.000000 PM EUROPE/VIENNA    ENABLED  FALSE ENABLED
TUESDAY_WINDOW            04-MAR-24 10.00.00.000000 PM EUROPE/VIENNA    ENABLED  FALSE ENABLED
WEDNESDAY_WINDOW          26-FEB-24 10.00.00.000000 PM EUROPE/VIENNA    ENABLED  FALSE ENABLED
THURSDAY_WINDOW           27-FEB-24 10.00.00.000000 PM EUROPE/VIENNA    ENABLED  FALSE ENABLED
FRIDAY_WINDOW             28-FEB-24 10.00.00.000000 PM EUROPE/VIENNA    ENABLED  FALSE ENABLED
SATURDAY_WINDOW           01-MAR-24 06.00.00.000000 AM EUROPE/VIENNA    ENABLED  FALSE ENABLED
SUNDAY_WINDOW             02-MAR-24 06.00.00.000000 AM EUROPE/VIENNA    ENABLED  FALSE ENABLED

 

There are  options to schedule the Automatic Optimizer Statistics Collection Job at another time. 

Both options include creating a new WINDOW NAME. 

In this demo I modify the schedule of the job without touching original WINDOW NAME schedules. 

It might be useful if you want to have the ability to reset all changes fast.

 

Option 1 : Creates a  using a Named Schedule Object

           a. Disable the Automatic Optimizer Statistics Collection Job [optional]

           b. Create a new Named Schedule Object 

           c. Create a new Window Name and assign a created Named Scheduled Object (b) to it

           d. Assign a created Windows Name (c) to the default Maintenance Group

           e. Enable the Automatic Optimizer Statistics Collection Job with a newly created Windows Name 

           f. Disable default Window Names

Option 2 : Creates a  using an Inlined Schedule

           A. Disable the Automatic Optimizer Statistics Collection Job [optional]

           B. Create a new Windows Name with required repeat interval 

           C. Assign a created Windows Name (B) to the default Maintenance group 

           D. Enable the Automatic Optimizer Statistics Collection Job with a newly created Windows Name 

           E. Disable default Window Names

 

Option 1 : Creates a Window Name using a Named Schedule Object

****************************************************************************

a. Disable the Automatic Optimizer Statistics Collection Job [optional]

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

-- Disable the Auto Optimizer Stats Collection job

BEGIN

   DBMS_AUTO_TASK_ADMIN.DISABLE(

   CLIENT_NAME => 'auto optimizer stats collection', 

   OPERATION   => NULL, 

   WINDOW_NAME => NULL);

END;

/

-- Confirm that it's been disabled

SET LINES 300 
SET PAGES 999
COL CLIENT_NAME FOR A35
COL CONSUMER_GROUP FOR A30 
COL WINDOW_GROUP FOR A20 
COL STATUS FOR A10 

SELECT CLIENT_NAME, STATUS, CONSUMER_GROUP,
       CLIENT_TAG, WINDOW_GROUP 
FROM   DBA_AUTOTASK_CLIENT 
WHERE  CLIENT_NAME='auto optimizer stats collection';
CLIENT_NAME                         STATUS     CONSUMER_GROUP                 CLIENT WINDOW_GROUP
----------------------------------- ---------- ------------------------------ ------ --------------------
auto optimizer stats collection     DISABLED   ORA$AUTOTASK_STATS_GROUP       OS     ORA$AT_WGRP_OS

b. Create a new Named Schedule Object.

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

In this example, the schedule will start at  from Monday to Friday. 
A name of a new Named Schedule Object (see SCHEDULE_NAME parameter) can be anything you want.

-- Define any name for a new Named Schedule Object

DEFINE SCHEDULE_NAME = 'GATHER_STATS_SCHEDULE'


-- Create a new Named Schedule Object

BEGIN 
   DBMS_SCHEDULER.CREATE_SCHEDULE(
   SCHEDULE_NAME   => '&SCHEDULE_NAME',
   START_DATE      => SYSTIMESTAMP,
   REPEAT_INTERVAL => 'freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=10;byminute=0; bysecond=0',
   END_DATE        => NULL, 
   COMMENTS        => 'REPEATS DAILY (MON-FRI) AT 10AM FOREVER.');
END;
/



-- To remove the created Named Scheduled Object

/*
EXEC DBMS_SCHEDULER.DROP_SCHEDULE(SCHEDULE_NAME => '&SCHEDULE_NAME');
*/



-- Query repeat interval for the created Named Scheduled Object 

SET LINES 300
SET PAGES 999
COL OWNER FOR A5 
COL SCHEDULE_NAME FOR A23
col START_DATE for a40 
col REPEAT_INTERVAL for a70 
col END_DATE for a10 
col COMMENTS for a40

SELECT OWNER, SCHEDULE_NAME, REPEAT_INTERVAL 
FROM   DBA_SCHEDULER_SCHEDULES 
WHERE  SCHEDULE_NAME='&SCHEDULE_NAME';

OWNER SCHEDULE_NAME           REPEAT_INTERVAL
----- ----------------------- ----------------------------------------------------------------------
SYS   GATHER_STATS_SCHEDULE   freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=10;byminute=0; bysecond=0





-- Query when the created Named Schedule Object will be started

COL START_DATE FOR A20
COL END_DATE FOR A20

SELECT TO_CHAR(START_DATE,'DD-MON-YYYY HH24:MI:SS') START_DATE, 
       TO_CHAR(END_DATE,'DD-MON-YYYY HH24:MI:SS') END_DATE, 
       COMMENTS 
FROM  
       DBA_SCHEDULER_SCHEDULES 
WHERE 
       SCHEDULE_NAME='&SCHEDULE_NAME';

START_DATE           END_DATE             COMMENTS
-------------------- -------------------- ----------------------------------------
24-SEP-2024 01:38:11                      REPEATS DAILY (MON-FRI) AT 10AM FOREVER.

c. Create a new Window Name and assign a created Named Scheduled Object (b) to it.

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

A name of a new Window Name (see WINDOW_NAME parameter) can be anything you want. In this example, the duration of a new Window Name is  hours.

-- Define any name for a new Window Name

DEFINE WINDOW_NAME = 'GATHERSTATISTICS_MON_FRI'

-- Create a new Window Name with the GATHER_STATS_SCHEDULE assigned to it

BEGIN   
	DBMS_SCHEDULER.CREATE_WINDOW ( 
	WINDOW_NAME     => '&WINDOW_NAME',
	RESOURCE_PLAN   => 'DEFAULT_MAINTENANCE_PLAN',
	SCHEDULE_NAME   => 'GATHER_STATS_SCHEDULE',
	DURATION        => interval '4' hour, 
	WINDOW_PRIORITY => 'LOW',
	COMMENTS        => 'Weekdays Gather Statistic Job. Used instead of default window names.');
END;
/







-- To remove the created Window Name

/*
EXEC DBMS_SCHEDULER.DROP_WINDOW (window_name => '&WINDOW_NAME');
*/





-- Confirm that the Windows Name is created

SET LINES 300 
SET PAGES 999
COL WINDOW_NAME for a24
COL REPEAT_INTERVAL FOR A15
COL DURATION FOR A30
COL SCHEDULE_NAME FOR A21
COL ENABLED FOR A7

SELECT WINDOW_NAME,
       REPEAT_INTERVAL,
       TO_CHAR(EXTRACT(DAY FROM DURATION),'90') || ' Days' || 
       TO_CHAR(EXTRACT(HOUR FROM DURATION),'00') || ' Hours' ||
       TO_CHAR(EXTRACT(MINUTE FROM DURATION),'00') || ' Minutes ' 
       DURATION,
       SCHEDULE_NAME,
       ENABLED 
FROM   
       DBA_SCHEDULER_WINDOWS 
WHERE  
       WINDOW_NAME=UPPER('&WINDOW_NAME');
old   3: WHERE  WINDOW_NAME=UPPER('&WINDOW_NAME')
new   3: WHERE  WINDOW_NAME=UPPER('GATHERSTATISTICS_MON_FRI')

WINDOW_NAME               REPEAT_INTERVAL DURATION                       SCHEDULE_NAME         ENABLED
------------------------- --------------- ------------------------------ --------------------- -------
GATHERSTATISTICS_MON_FRI                    0 Days 04 Hours 00 Minutes   GATHER_STATS_SCHEDULE TRUE

d. Assign a created Windows Name (c) to the default Maintenance Group.

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

The default Maintenance Group is used for automated tasks

-- Add the created Window Name to the Window Group
-- DEFINE WINDOW_NAME = 'GATHERSTATISTICS_MON_FRI'

BEGIN   
   DBMS_SCHEDULER.ADD_GROUP_MEMBER(
   GROUP_NAME => 'MAINTENANCE_WINDOW_GROUP',
   MEMBER     => '&WINDOW_NAME');
END;
/


-- To exclude the created Window Name from the Window Group

/*
BEGIN   
  DBMS_SCHEDULER.REMOVE_GROUP_MEMBER(
  GROUP_NAME => 'MAINTENANCE_WINDOW_GROUP',
  MEMBER     => '&WINDOW_NAME');
END;
/
*/

e. Enable the Automatic Optimizer Statistics Collection Job with a newly created Windows Name

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

-- Enable the job
-- DEFINE WINDOW_NAME = 'GATHERSTATISTICS_MON_FRI'

BEGIN
   DBMS_AUTO_TASK_ADMIN.ENABLE(
   CLIENT_NAME => 'auto optimizer stats collection', 
   OPERATION   => NULL, 
   WINDOW_NAME => '&WINDOW_NAME');
END;
/

-- Confirm that it is enabled

SET LINES 300 
SET PAGES 999
COL CLIENT_NAME FOR A31
COL CONSUMER_GROUP FOR A24
COL WINDOW_GROUP FOR A15 
COL STATUS FOR A7 

SELECT CLIENT_NAME,STATUS,CONSUMER_GROUP,CLIENT_TAG,WINDOW_GROUP 
FROM   DBA_AUTOTASK_CLIENT 
WHERE  CLIENT_NAME='auto optimizer stats collection';

CLIENT_NAME                     STATUS  CONSUMER_GROUP            CLIENT WINDOW_GROUP
------------------------------- ------- ------------------------- ------ ---------------
auto optimizer stats collection ENABLED ORA$AUTOTASK_STATS_GROUP  OS     ORA$AT_WGRP_OS




-- Verify that created Window Name are in the list of the Window Group

COL WINDOW_GROUP_NAME for a20 
SELECT * from DBA_SCHEDULER_WINGROUP_MEMBERS 
WHERE WINDOW_GROUP_NAME='ORA$AT_WGRP_OS';

WINDOW_GROUP_NAME    WINDOW_NAME
-------------------- ------------------------------
ORA$AT_WGRP_OS       MONDAY_WINDOW
ORA$AT_WGRP_OS       TUESDAY_WINDOW
ORA$AT_WGRP_OS       WEDNESDAY_WINDOW
ORA$AT_WGRP_OS       THURSDAY_WINDOW
ORA$AT_WGRP_OS       FRIDAY_WINDOW
ORA$AT_WGRP_OS       SATURDAY_WINDOW
ORA$AT_WGRP_OS       SUNDAY_WINDOW
ORA$AT_WGRP_OS       GATHERSTATISTICS_MON_FRI



-- Statuses of all available Window Names in the Window Group

COL WINDOW_NAME FOR A24
COL WINDOW_NEXT_TIME for a24
COL STATUS FOR A15
COL OPTIMIZER_STATS FOR A15 

SELECT WINDOW_NAME,
       TO_CHAR(WINDOW_NEXT_TIME,'DD-MON-YYYY HH24:MI:SS') WINDOW_NEXT_TIME,
       AUTOTASK_STATUS STATUS,
       OPTIMIZER_STATS 
FROM 
       DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME               WINDOW_NEXT_TIME          STATUS          OPTIMIZER_STATS
------------------------- ------------------------- --------------- ---------------
MONDAY_WINDOW             26-SEP-2024 22:00:00      ENABLED         ENABLED
TUESDAY_WINDOW            27-SEP-2024 22:00:00      ENABLED         ENABLED
WEDNESDAY_WINDOW          28-SEP-2024 22:00:00      ENABLED         ENABLED
THURSDAY_WINDOW           29-SEP-2024 22:00:00      ENABLED         ENABLED
FRIDAY_WINDOW             30-SEP-2024 22:00:00      ENABLED         ENABLED
SATURDAY_WINDOW           24-SEP-2024 06:00:00      ENABLED         ENABLED
SUNDAY_WINDOW             24-SEP-2024 06:00:00      ENABLED         ENABLED
GATHERSTATISTICS_MON_FRI  26-SEP-2024 10:00:00      ENABLED         ENABLED

8 rows selected.

f. Disable default Window Names

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

-- Disable defaults through the LOOP 
-- DEFINE WINDOW_NAME = 'GATHERSTATISTICS_MON_FRI'

BEGIN 
FOR W IN (SELECT WINDOW_NAME FROM DBA_AUTOTASK_WINDOW_CLIENTS 
          WHERE WINDOW_NAME!='&WINDOW_NAME')
LOOP
    BEGIN
       dbms_auto_task_admin.disable(
       client_name => 'auto optimizer stats collection', 
       operation   => NULL, 
       window_name => W.WINDOW_NAME);
    END;
END LOOP;
END;
/

-- Confirm that all default Window Names are disabled

COL WINDOW_NAME FOR A24
COL WINDOW_NEXT_TIME for a24
COL STATUS FOR A15
COL OPTIMIZER_STATS FOR A15 

SELECT WINDOW_NAME,
       TO_CHAR(WINDOW_NEXT_TIME,'DD-MON-YYYY HH24:MI:SS') WINDOW_NEXT_TIME,
       AUTOTASK_STATUS STATUS,
       OPTIMIZER_STATS 
FROM 
       DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME               WINDOW_NEXT_TIME          STATUS          OPTIMIZER_STATS
------------------------- ------------------------- --------------- ---------------
MONDAY_WINDOW             26-SEP-2024 22:00:00      ENABLED         DISABLED
TUESDAY_WINDOW            27-SEP-2024 22:00:00      ENABLED         DISABLED
WEDNESDAY_WINDOW          28-SEP-2024 22:00:00      ENABLED         DISABLED
THURSDAY_WINDOW           29-SEP-2024 22:00:00      ENABLED         DISABLED
FRIDAY_WINDOW             30-SEP-2024 22:00:00      ENABLED         DISABLED
SATURDAY_WINDOW           24-SEP-2024 06:00:00      ENABLED         DISABLED
SUNDAY_WINDOW             24-SEP-2024 06:00:00      ENABLED         DISABLED
GATHERSTATISTICS_MON_FRI  26-SEP-2024 10:00:00      ENABLED         ENABLED

8 rows selected.



To RESET all changes made in the Option 1

-- Names for the Named Schedule Object and Window Name

DEFINE SCHEDULE_NAME = 'GATHER_STATS_SCHEDULE'
DEFINE WINDOW_NAME = 'GATHERSTATISTICS_MON_FRI'

-- Drop the Named Schedule Object and Window Name

EXEC DBMS_SCHEDULER.DROP_WINDOW(WINDOW_NAME => '&WINDOW_NAME');
EXEC DBMS_SCHEDULER.DROP_SCHEDULE(SCHEDULE_NAME => '&SCHEDULE_NAME');


-- Enable default Window Names 

BEGIN 
FOR W IN (SELECT WINDOW_NAME FROM DBA_AUTOTASK_WINDOW_CLIENTS)
LOOP
    BEGIN
       DBMS_AUTO_TASK_ADMIN.ENABLE(
       CLIENT_NAME => 'auto optimizer stats collection', 
       OPERATION   => NULL, 
       WINDOW_NAME => W.WINDOW_NAME);
    END;
END LOOP;
END;
/

-- Confirm that all default Window Names are enabled

COL WINDOW_NAME FOR A24
COL WINDOW_NEXT_TIME for a24
COL STATUS FOR A15
COL OPTIMIZER_STATS FOR A15 

SELECT WINDOW_NAME,
       TO_CHAR(WINDOW_NEXT_TIME,'DD-MON-YYYY HH24:MI:SS') WINDOW_NEXT_TIME,
       AUTOTASK_STATUS STATUS,
       OPTIMIZER_STATS 
FROM 
       DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME               WINDOW_NEXT_TIME          STATUS          OPTIMIZER_STATS
------------------------- ------------------------- --------------- ---------------
MONDAY_WINDOW             26-SEP-2024 22:00:00      ENABLED         ENABLED
TUESDAY_WINDOW            27-SEP-2024 22:00:00      ENABLED         ENABLED
WEDNESDAY_WINDOW          28-SEP-2024 22:00:00      ENABLED         ENABLED
THURSDAY_WINDOW           29-SEP-2024 22:00:00      ENABLED         ENABLED
FRIDAY_WINDOW             30-SEP-2024 22:00:00      ENABLED         ENABLED
SATURDAY_WINDOW           24-SEP-2024 06:00:00      ENABLED         ENABLED
SUNDAY_WINDOW             24-SEP-2024 06:00:00      ENABLED         ENABLED

7 rows selected.
 

Option 2 : Creates a Window Name using an Inlined Schedule

*******************************************************************

A. Disable the Automatic Optimizer Statistics Collection Job [optional]

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

-- Disable the Auto Optimizer Stats Collection job

BEGIN
   DBMS_AUTO_TASK_ADMIN.DISABLE(
   CLIENT_NAME => 'auto optimizer stats collection', 
   OPERATION   => NULL, 
   WINDOW_NAME => NULL);
END;
/

-- Confirm that it's been disabled

SET LINES 300 
SET PAGES 999
COL CLIENT_NAME FOR A35
COL CONSUMER_GROUP FOR A30 
COL WINDOW_GROUP FOR A20 
COL STATUS FOR A10 

SELECT CLIENT_NAME, STATUS, CONSUMER_GROUP,
       CLIENT_TAG, WINDOW_GROUP 
FROM   DBA_AUTOTASK_CLIENT 
WHERE  CLIENT_NAME='auto optimizer stats collection';
CLIENT_NAME                         STATUS     CONSUMER_GROUP                 CLIENT WINDOW_GROUP
----------------------------------- ---------- ------------------------------ ------ --------------------
auto optimizer stats collection     DISABLED   ORA$AUTOTASK_STATS_GROUP       OS     ORA$AT_WGRP_OS

B. Create a new Windows Name with required REPEAT_INTERVAL.

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

 

In this example a new Windows Name will start at  and last for  hours

-- Define any name for a new Window Name

DEFINE WINDOW_NAME = 'GATHERSTATISTICS_MON_FRI_OP2'

BEGIN   
   DBMS_SCHEDULER.CREATE_WINDOW ( 
   WINDOW_NAME     => '&WINDOW_NAME',
   RESOURCE_PLAN   => 'DEFAULT_MAINTENANCE_PLAN',
   START_DATE      => SYSTIMESTAMP,
   REPEAT_INTERVAL => 'freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=4;byminute=0; bysecond=0',
   END_DATE        => NULL,
   DURATION        => interval '5' hour, 
   WINDOW_PRIORITY => 'LOW',
   COMMENTS        => 'Custom Window Name for the Gather Statistics Job. Starts at 4am and last for 5 hours.');
END;
/




-- To remove the created Window Name

/*
EXEC DBMS_SCHEDULER.DROP_WINDOW (window_name => '&WINDOW_NAME');
*/



-- Confirm that the Windows Name is created

SET LINES 300 
SET PAGES 999
COL WINDOW_NAME FOR A28
COL REPEAT_INTERVAL FOR A70 
COL DURATION FOR A15
COL SCHEDULE_NAME FOR A30

SELECT WINDOW_NAME,REPEAT_INTERVAL,DURATION,ENABLED 
FROM   DBA_SCHEDULER_WINDOWS
WHERE  WINDOW_NAME=UPPER('&WINDOW_NAME');
old   3: WHERE  WINDOW_NAME=UPPER('&WINDOW_NAME')
new   3: WHERE  WINDOW_NAME=UPPER('GATHERSTATISTICS_MON_FRI_OP2')

WINDOW_NAME                  REPEAT_INTERVAL                                                        DURATION        ENABLED
---------------------------- ---------------------------------------------------------------------- --------------- -------
GATHERSTATISTICS_MON_FRI_OP2 freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=4;byminute=0; bysecond=0   +000 05:00:00   TRUE

C. Assign a created Windows Name (B) to the default Maintenance group.

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

 

The default Maintenance Group is used for automated tasks

-- Add the created Window Name to the Window Group
-- DEFINE WINDOW_NAME = 'GATHERSTATISTICS_MON_FRI_OP2'

BEGIN   
   DBMS_SCHEDULER.ADD_GROUP_MEMBER(
   GROUP_NAME => 'MAINTENANCE_WINDOW_GROUP',
   MEMBER     => '&WINDOW_NAME');
END;
/


-- To exclude the created Window Name from the Window Group

/*
BEGIN   
  DBMS_SCHEDULER.REMOVE_GROUP_MEMBER(
  GROUP_NAME => 'MAINTENANCE_WINDOW_GROUP',
  MEMBER     => '&WINDOW_NAME');
END;
/
*/

D. Enable the Automatic Optimizer Statistics Collection Job with a newly created Windows Name.

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

-- Enable the job
-- DEFINE WINDOW_NAME = 'GATHERSTATISTICS_MON_FRI_OP2'

BEGIN
   DBMS_AUTO_TASK_ADMIN.ENABLE(
   CLIENT_NAME => 'auto optimizer stats collection', 
   OPERATION   => NULL, 
   WINDOW_NAME => '&WINDOW_NAME');
END;
/

-- Confirm that it is enabled

SET LINES 300 
SET PAGES 999
COL CLIENT_NAME FOR A31
COL CONSUMER_GROUP FOR A24
COL WINDOW_GROUP FOR A15 
COL STATUS FOR A7 

SELECT CLIENT_NAME,STATUS,CONSUMER_GROUP,CLIENT_TAG,WINDOW_GROUP 
FROM   DBA_AUTOTASK_CLIENT 
WHERE  CLIENT_NAME='auto optimizer stats collection';

CLIENT_NAME                     STATUS  CONSUMER_GROUP            CLIENT WINDOW_GROUP
------------------------------- ------- ------------------------- ------ ---------------
auto optimizer stats collection ENABLED ORA$AUTOTASK_STATS_GROUP  OS     ORA$AT_WGRP_OS

-- Verify that created Window Name are in the list of the Window Group

COL WINDOW_GROUP_NAME for a20 
SELECT * from DBA_SCHEDULER_WINGROUP_MEMBERS 
WHERE WINDOW_GROUP_NAME='ORA$AT_WGRP_OS';

WINDOW_GROUP_NAME    WINDOW_NAME
-------------------- ----------------------------
ORA$AT_WGRP_OS       MONDAY_WINDOW
ORA$AT_WGRP_OS       TUESDAY_WINDOW
ORA$AT_WGRP_OS       WEDNESDAY_WINDOW
ORA$AT_WGRP_OS       THURSDAY_WINDOW
ORA$AT_WGRP_OS       FRIDAY_WINDOW
ORA$AT_WGRP_OS       SATURDAY_WINDOW
ORA$AT_WGRP_OS       SUNDAY_WINDOW
ORA$AT_WGRP_OS       GATHERSTATISTICS_MON_FRI_OP2

8 rows selected.

-- Statuses of all available Window Names in the Window Group

COL WINDOW_NAME FOR A30
COL WINDOW_NEXT_TIME for a24
COL STATUS FOR A15
COL OPTIMIZER_STATS FOR A15 

SELECT WINDOW_NAME,
       TO_CHAR(WINDOW_NEXT_TIME,'DD-MON-YYYY HH24:MI:SS') WINDOW_NEXT_TIME,
       AUTOTASK_STATUS STATUS,
       OPTIMIZER_STATS 
FROM 
       DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME                    WINDOW_NEXT_TIME          STATUS          OPTIMIZER_STATS
------------------------------ ------------------------- --------------- ---------------
MONDAY_WINDOW                  26-SEP-2024 22:00:00      ENABLED         ENABLED
TUESDAY_WINDOW                 27-SEP-2024 22:00:00      ENABLED         ENABLED
WEDNESDAY_WINDOW               28-SEP-2024 22:00:00      ENABLED         ENABLED
THURSDAY_WINDOW                29-SEP-2024 22:00:00      ENABLED         ENABLED
FRIDAY_WINDOW                  30-SEP-2024 22:00:00      ENABLED         ENABLED
SATURDAY_WINDOW                24-SEP-2024 06:00:00      ENABLED         ENABLED
SUNDAY_WINDOW                  24-SEP-2024 06:00:00      ENABLED         ENABLED
GATHERSTATISTICS_MON_FRI_OP2   26-SEP-2024 04:00:00      ENABLED         ENABLED

8 rows selected.

E. Disable default Window Names.

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

-- Disable defaults through the LOOP 
-- DEFINE WINDOW_NAME = 'GATHERSTATISTICS_MON_FRI_OP2'

BEGIN 
FOR W IN (SELECT WINDOW_NAME FROM DBA_AUTOTASK_WINDOW_CLIENTS 
          WHERE WINDOW_NAME!='&WINDOW_NAME')
LOOP
    BEGIN
       dbms_auto_task_admin.disable(
       client_name => 'auto optimizer stats collection', 
       operation   => NULL, 
       window_name => W.WINDOW_NAME);
    END;
END LOOP;
END;
/

-- Confirm that all default Window Names are disabled

COL WINDOW_NAME FOR A30
COL WINDOW_NEXT_TIME for a24
COL STATUS FOR A15
COL OPTIMIZER_STATS FOR A15 

SELECT WINDOW_NAME,
       TO_CHAR(WINDOW_NEXT_TIME,'DD-MON-YYYY HH24:MI:SS') WINDOW_NEXT_TIME,
       AUTOTASK_STATUS STATUS,
       OPTIMIZER_STATS 
FROM 
       DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME                    WINDOW_NEXT_TIME          STATUS          OPTIMIZER_STATS
------------------------------ ------------------------- --------------- ---------------
MONDAY_WINDOW                  26-SEP-2024 22:00:00      ENABLED         DISABLED
TUESDAY_WINDOW                 27-SEP-2024 22:00:00      ENABLED         DISABLED
WEDNESDAY_WINDOW               28-SEP-2024 22:00:00      ENABLED         DISABLED
THURSDAY_WINDOW                29-SEP-2024 22:00:00      ENABLED         DISABLED
FRIDAY_WINDOW                  30-SEP-2024 22:00:00      ENABLED         DISABLED
SATURDAY_WINDOW                24-SEP-2024 06:00:00      ENABLED         DISABLED
SUNDAY_WINDOW                  24-SEP-2024 06:00:00      ENABLED         DISABLED
GATHERSTATISTICS_MON_FRI_OP2   26-SEP-2024 04:00:00      ENABLED         ENABLED

8 rows selected.




To RESET all changes made in the Option 2

-- Names for the Named Schedule Object and Window Name

DEFINE WINDOW_NAME = 'GATHERSTATISTICS_MON_FRI_OP2'

-- Drop the Window Name

EXEC DBMS_SCHEDULER.DROP_WINDOW(WINDOW_NAME => '&WINDOW_NAME');


-- Enable default Window Names 

BEGIN 
FOR W IN (SELECT WINDOW_NAME FROM DBA_AUTOTASK_WINDOW_CLIENTS)
LOOP
    BEGIN
       DBMS_AUTO_TASK_ADMIN.ENABLE(
       CLIENT_NAME => 'auto optimizer stats collection', 
       OPERATION   => NULL, 
       WINDOW_NAME => W.WINDOW_NAME);
    END;
END LOOP;
END;
/

-- Confirm that all default Window Names are enabled

COL WINDOW_NAME FOR A24
COL WINDOW_NEXT_TIME for a24
COL STATUS FOR A15
COL OPTIMIZER_STATS FOR A15 

SELECT WINDOW_NAME,
       TO_CHAR(WINDOW_NEXT_TIME,'DD-MON-YYYY HH24:MI:SS') WINDOW_NEXT_TIME,
       AUTOTASK_STATUS STATUS,
       OPTIMIZER_STATS 
FROM 
       DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME               WINDOW_NEXT_TIME          STATUS          OPTIMIZER_STATS
------------------------- ------------------------- --------------- ---------------
MONDAY_WINDOW             26-SEP-2024 22:00:00      ENABLED         ENABLED
TUESDAY_WINDOW            27-SEP-2024 22:00:00      ENABLED         ENABLED
WEDNESDAY_WINDOW          28-SEP-2024 22:00:00      ENABLED         ENABLED
THURSDAY_WINDOW           29-SEP-2024 22:00:00      ENABLED         ENABLED
FRIDAY_WINDOW             30-SEP-2024 22:00:00      ENABLED         ENABLED
SATURDAY_WINDOW           24-SEP-2024 06:00:00      ENABLED         ENABLED
SUNDAY_WINDOW             24-SEP-2024 06:00:00      ENABLED         ENABLED

7 rows selected.

To get historic details about all the Automatic Optimizer Statistics Collection Job executions

-- Specify how many rows you want the query to returns

DEFINE ROWNUM = 5

SET LINES 300
SET PAGES 999
COL WINDOW_NAME FOR A15
COL WINDOW_DURATION FOR A35 
COL JOB_NAME FOR A22 
COL WINDOW_START_TIME FOR A20 
COL JOB_DURATION FOR A23
COL JOB_INFO FOR A2 
COL JOB_STATUS FOR A10
COL JOB_START_TIME FOR A20

SELECT * FROM (
       SELECT WINDOW_NAME,
              TO_CHAR(WINDOW_START_TIME,'DD-MM-YYYY HH24:MI:SS') WINDOW_START_TIME,
              TO_CHAR(EXTRACT(DAY FROM WINDOW_DURATION),'00') || ' Days ' || 
              TO_CHAR(EXTRACT(HOUR FROM WINDOW_DURATION),'00') || ' Hours ' ||
              TO_CHAR(EXTRACT(MINUTE FROM WINDOW_DURATION),'00') || ' Minutes ' 
              WINDOW_DURATION,
              JOB_NAME,
              JOB_STATUS,
              TO_CHAR(JOB_START_TIME,'DD-MM-YYYY HH24:MI:SS') JOB_START_TIME,
              TO_CHAR(EXTRACT(DAY FROM JOB_DURATION),'90') || ' D' || 
              TO_CHAR(EXTRACT(HOUR FROM JOB_DURATION),'90') || ' H' ||
              TO_CHAR(EXTRACT(MINUTE FROM JOB_DURATION),'90') || ' M' ||
              TO_CHAR(EXTRACT(SECOND FROM JOB_DURATION),'90') || ' S' 
              JOB_DURATION,
              JOB_ERROR 
       FROM DBA_AUTOTASK_JOB_HISTORY 
       WHERE CLIENT_NAME='auto optimizer stats collection' 
      ORDER BY JOB_START_TIME DESC
) WHERE ROWNUM < '&ROWNUM';
WINDOW_NAME     WINDOW_START_TIME    WINDOW_DURATION                     JOB_NAME               JOB_STATUS JOB_START_TIME       JOB_DURATION             JOB_ERROR
--------------- -------------------- ----------------------------------- ---------------------- ---------- -------------------- ----------------------- ----------
SATURDAY_WINDOW 24-09-2024 13:04:45   00 Days  12 Hours  46 Minutes      ORA$AT_OS_OPT_SY_586   SUCCEEDED  24-09-2024 13:04:58    0 D  0 H  0 M 27 S             0
MONDAY_WINDOW   19-09-2024 22:00:00   04 Days  15 Hours  04 Minutes      ORA$AT_OS_OPT_SY_566   SUCCEEDED  19-09-2024 22:00:02    0 D  0 H  0 M 52 S             0
SUNDAY_WINDOW   11-09-2024 11:47:01   07 Days  02 Hours  18 Minutes      ORA$AT_OS_OPT_SY_564   SUCCEEDED  11-09-2024 15:51:21    0 D  0 H  0 M 24 S             0
SUNDAY_WINDOW   11-09-2024 11:47:01   07 Days  02 Hours  18 Minutes      ORA$AT_OS_OPT_SY_561   SUCCEEDED  11-09-2024 11:47:44    0 D  0 H  0 M 31 S             0