Hi Tom,

I ran into the following problems when trying to call DBMS_ADVISOR.TUNE_MVIEW.

At first, account sqltune_admin is created as follows.

create user sqltune_admin identified by sqltune_admin; 
grant select any dictionary to sqltune_admin;
grant create session to sqltune_admin;
grant oem_advisor to sqltune_admin; 
grant advisor to sqltune_admin; 

1. Case 1

DECLARE
  v_tname VARCHAR2(30) := 'MVIEW_DUMMY_TASK';
BEGIN
  DBMS_ADVISOR.TUNE_MVIEW(
      task_name      => v_tname
     ,mv_create_stmt => 
       'CREATE MATERIALIZED VIEW omv REFRESH WITH ROWID AS SELECT ''x'' dummy FROM dual');
END;
/

ORA-13600: error encountered in Advisor
QSM-03113: Cannot tune the MATERIALIZED VIEW statement
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.PRVT_ACCESS_ADVISOR", line 205
ORA-06512: at "SYS.PRVT_TUNE_MVIEW", line 1223
ORA-06512: at "SYS.DBMS_ADVISOR", line 828
ORA-06512: at line 4

Question: Is it supposed to have such an error if mview cannot be tuned?

2. Case 2

DECLARE
  v_tname VARCHAR2(30) := 'fed_vam_attached_mv';
BEGIN
DBMS_ADVISOR.TUNE_MVIEW(v_tname, '
create materialized view fs_fleet.fed_vam_attached_mv refresh fast on commit with primary key
as
select d.id from fs_ead_admin.ii_documentations d
');
END;
/

ORA-13600: error encountered in Advisor
QSM-03112: Invalid CREATE MATERIALIZED VIEW statement
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.PRVT_ACCESS_ADVISOR", line 205
ORA-06512: at "SYS.PRVT_TUNE_MVIEW", line 461
ORA-06512: at "SYS.PRVT_TUNE_MVIEW", line 1132
ORA-06512: at "SYS.DBMS_ADVISOR", line 828
ORA-06512: at line 4

Question: What privilege(s) does sqltune_admin need in order to prevent the "insufficient privileges" error?

Thanks,
Charles