OEM 13.5 -Tuning Advisor giving ORA-01031: insufficient privileges - with DBSNMP user

Sol : (Replace XXXX with DBSNMP in my case or change accordingly)

GRANT OEM_ADVISOR TO XXXX;

GRANT CONNECT TO XXXX;

GRANT OEM_MONITOR TO XXXX;

ALTER USER XXXX DEFAULT ROLE OEM_ADVISOR, OEM_MONITOR;

GRANT CREATE SESSION TO XXXX;

GRANT SELECT ANY DICTIONARY TO XXXX;

GRANT ADMINISTER SQL TUNING SET TO XXXX;

GRANT EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY TO XXXX;

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

Issue :

---------Run below queries to confirm the issue

---Use DBSNMP user to connect and run below  , replace sql_id with latest from your environment.

1) 

SET SERVEROUTPUT ON

declare

stmt_task VARCHAR2(40);

begin

stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(

sql_id => '6xdyxvdxkmuj3',

scope => DBMS_SQLTUNE.scope_comprehensive,

time_limit => 10000, --<<< number of seconds

task_name => '6xdyxvdxkmuj3_tuning_task',

description => 'Tuning task for statement 6xdyxvdxkmuj3');

DBMS_OUTPUT.put_line('task_id: ' || stmt_task );

end;

/

****

BEGIN

DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => '6xdyxvdxkmuj3_tuning_task');

end;

/

-- See the suggestions of the TUNING TASK.

SET LONG 100000

SET LONGCHUNKSIZE 1000

SET LINESIZE 200

set pagesize 500

--***************************

--TO GET DETAILED INFORMATION

--***************************

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('1djr35t9304ns_tuning_task','TEXT','ALL','ALL') FROM DUAL;

###>>>>>Check the results of the detailed query. <<<<<###

SQL> SET LONG 100000

SET LONGCHUNKSIZE 1000

SET LINESIZE 200

set pagesize 500

SQL>

SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('6xdyxvdxkmuj3_tuning_task','TEXT','ALL','ALL') FROM DUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK('6XDYXVDXKMUJ3_TUNING_TASK','TEXT','ALL','ALL')

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

GENERAL INFORMATION SECTION

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

Tuning Task Name : 6xdyxvdxkmuj3_tuning_task

Tuning Task Owner : DBSNMP

Tuning Task ID : 375975

Workload Type : Single SQL Statement

Execution Count : 1

Current Execution : EXEC_343213

Execution Type : TUNE SQL

Scope : COMPREHENSIVE

Time Limit(seconds): 10000

Completion Status : COMPLETED

Started at : 10/20/2021 08:35:54

Completed at : 10/20/2021 08:35:56

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

Schema Name: APPS

SQL ID : 6xdyxvdxkmuj3

SQL Text : SELECT

'PO' PO_RELEASE_FLAG,

POH.CLM_DOCUMENT_NUMBER PO_NUM,

POHA.ACCEPTANCE_DUE_DATE,

POH.APPROVED_DATE,

NVL(POH.AUTHORIZATION_STATUS,'INCOMPLETE')

AUTHORIZATION_STATUS,

POH.CLOSED_DATE,

POHA.COMMENTS,

POH.FIRM_DATE,

POH.NOTE_TO_AUTHORIZER,

POH.NOTE_TO_RECEIVER,

POH.NOTE_TO_VENDOR,

POH.PRINT_COUNT,

POH.PRINTED_DATE,

POH.RATE,

POH.RATE_DATE,

POH.RATE_TYPE,

POH.REVISED_DATE,

POH.REVISION_NUM,

POH.AGENT_ID,

POH.BILL_TO_LOCATION_ID,

POH.FROM_HEADER_ID,

POH.PO_HEADER_ID,

POH.SHIP_TO_LOCATION_ID,

POH.TERMS_ID,

POH.VENDOR_CONTACT_ID,

POH.VENDOR_ID,

POH.VENDOR_SITE_ID,

NVL(POH.CLOSED_CODE, 'OPEN') CLOSED_CODE,

POH.CURRENCY_CODE,

NVL(POH.FIRM_STATUS_LOOKUP_CODE,'N')

FIRM_STATUS_LOOKUP_CODE,

POH.FOB_LOOKUP_CODE,

POH.FREIGHT_TERMS_LOOKUP_CODE,

POH.SHIP_VIA_LOOKUP_CODE,

POH.TYPE_LOOKUP_CODE,

POCR.REQUEST_LEVEL (+)= 'HEADER' AND

POCR.CHANGE_ACTIVE_FLAG (+)= 'Y'

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

ERRORS SECTION

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

- ORA-01031: insufficient privileges

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