show parameter optimizer_capture_sql_plan_baselines

-- sould be FALSE for automatic SPM

col parameter_name for a40
col parameter_value for a20

SELECT parameter_name,parameter_value
FROM dba_sql_management_config;

-- check for parameter_name='AUTO_SPM_EVOLVE_TASK'

col task_name for a40

SELECT task_name,enabled
FROM dba_autotask_schedule_control
WHERE  dbid = sys_context('userenv','con_dbid');

-- check for task_name = 'Auto SPM Task';

------------
-- to ENABLE 
------------
BEGIN
  DBMS_SPM.CONFIGURE('AUTO_SPM_EVOLVE_TASK','ON');
END;
/

-- For non-autonomous systems only, in the relevant PDB
-- execute the following as SYS to ensure the correct plan source
-- and ACCEPT_PLANS has its default value, TRUE,
BEGIN
   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
      task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
      parameter => 'ALTERNATE_PLAN_SOURCE',
      value     => 'SQL_TUNING_SET');
END;
/
BEGIN
   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
      task_name => 'SYS_AUTO_SPM_EVOLVE_TASK' ,
      parameter => 'ACCEPT_PLANS',
      value     => 'TRUE');
END;
/

-------------
-- to DISABLE 
-------------
BEGIN
  DBMS_SPM.CONFIGURE('AUTO_SPM_EVOLVE_TASK','OFF');
END;
/

-- For non-autonomous systems only, 
-- execute the following as SYS if you want to return
-- parameters to 'manual' SPM values - for example
BEGIN 
   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( 
      task_name => 'SYS_AUTO_SPM_EVOLVE_TASK' ,
      parameter => 'ALTERNATE_PLAN_BASELINE', 
      value     => 'EXISTING');
END; 
/
BEGIN
  DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(
	task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
	parameter => 'ALTERNATE_PLAN_SOURCE',
	value => 'AUTO');
END;
/