Generic Operational Error OR Operational error () detected in EM OR Process 0x0xc0000013d3dbf638 appears to be hung in Auto SQL Tuning task

Reason:

The Automatic SQL Tuning Task (SYS_AUTO_SQL_TUNING_TASK)  has been over-running, therefore it has auto killed to protect the system from harm caused by such over-running.This is a protective measure purely to avoid the task from over-running its time limit because of a single task and  protects the system from harm caused by such over-running.

Solution:

To disable this job and messages will not appear anymore. Eventually manually execute it when needed.   

BEGIN

   DBMS_AUTO_TASK_ADMIN.DISABLE(

   client_name => ‘sql tuning advisor’,

   operation => NULL,

   window_name => NULL);

 END;

 /

 BEGIN

   DBMS_AUTO_TASK_ADMIN.ENABLE(

   client_name => ‘sql tuning advisor’,

   operation => NULL,

   window_name => NULL);

 END;

 /

Implementation:

oracle@dcpdb1 [/home/oracle]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jun 26 14:39:25 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL>

SQL>

SQL> SELECT client_name, status from dba_autotask_client;

CLIENT_NAME                                                      STATUS

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

auto optimizer stats collection                                  ENABLED

auto space advisor                                               ENABLED

sql tuning advisor                                               ENABLED

SQL>

SQL>

SQL>

SQL>

SQL> BEGIN

DBMS_AUTO_TASK_ADMIN.DISABLE(

  2    3  client_name => 'sql tuning advisor',

  4  operation => NULL,

  5  window_name => NULL);

  6  END;

  7  /

PL/SQL procedure successfully completed.

SQL>

SQL>

SQL> SELECT client_name, status from dba_autotask_client;

CLIENT_NAME                                                      STATUS

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

auto optimizer stats collection                                  ENABLED

auto space advisor                                               ENABLED

sql tuning advisor                                               DISABLED

SQL>

SQL>

SQL>