Running Tuning Adviser from sqlplus

-- Get the SQL ID
5s1uq1cdc2rzp

-- get the explain plan
select plan_table_output from table(dbms_xplan.display_cursor('5s1uq1cdc2rzp',null,'basic'));


-- Create tuning taks for the SQL ID
DECLARE
  v_tune_taskid  VARCHAR2(100);
BEGIN
  v_tune_taskid := dbms_sqltune.create_tuning_task (
                          sql_id      => '5s1uq1cdc2rzp',
                          scope       => dbms_sqltune.scope_comprehensive,
                          time_limit  => 30,
                          task_name   => 'sqltune_5s1uq1cdc2rzp',
                          description => 'Tuning task sql_id 5s1uq1cdc2rzp');
  dbms_output.put_line('taskid = ' || v_tune_taskid);
END;
/

-- Run the Tuning Task
exec dbms_sqltune.execute_tuning_task(task_name => 'sqltune_5s1uq1cdc2rzp');

-- Check the status for the task
select task_name, status
from dba_advisor_log
where owner = '&OWNER_YOU_RAN_TASK_AS';   -- sys in my case


-- Get the report
set long 10000;
set pagesize 1000
set linesize 220
set pagesize 24
select dbms_sqltune.report_tuning_task('sqltune_5s1uq1cdc2rzp') as output
from dual;