3 Replies Latest reply: Sep 25, 2013 1:20 PM by MuthukumaranK RSS

    Not able to execute DBMS_AUTO_TASK_ADMIN.ENABLE

    MuthukumaranK

      Hi,

       

      I am getting following error, when I tried to enable the DBMS_AUTO_TASK_ADMIN package for 'auto optimizer stats collection'.

       

      My user account has DBA, IMP_FULL_DATABASE, DATAPUMP_IMP_FULL_DATABASE roles. I am not a SYS user.

       

      There was a grants and synonyms exist for the above package.

       

      BEGIN

        DBMS_AUTO_TASK_ADMIN.ENABLE(

          client_name => 'auto optimizer stats collection',

          operation => NULL,

          window_name => NULL);

      end;

      /

       

      PLS-00201: identifier 'DBMS_AUTO_TASK_ADMIN' must be declared

      PL/SQL: Statement ignored

      06550. 00000 -  "line %s, column %s:\n%s"

      *Cause:    Usually a PL/SQL compilation error.

       

      SELECT * FROM V$VERSION;

      SELECT GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE FROM DBA_TAB_PRIVS WHERE TABLE_NAME = 'DBMS_AUTO_TASK_ADMIN';

      SELECT OWNER, SYNONYM_NAME, TABLE_OWNER, TABLE_NAME FROM DBA_SYNONYMS WHERE TABLE_NAME    = 'DBMS_AUTO_TASK_ADMIN';

       

      BANNER                                                                          

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

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

      PL/SQL Release 11.2.0.2.0 - Production                                          

      CORE 11.2.0.2.0 Production                                                        

      TNS for Linux: Version 11.2.0.2.0 - Production                                  

      NLSRTL Version 11.2.0.2.0 - Production                                          

       

      GRANTEE                        OWNER                          TABLE_NAME                     GRANTOR                        PRIVILEGE                               

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

      DATAPUMP_IMP_FULL_DATABASE     SYS                            DBMS_AUTO_TASK_ADMIN           SYS                            EXECUTE                                 

      IMP_FULL_DATABASE              SYS                            DBMS_AUTO_TASK_ADMIN           SYS                            EXECUTE                                 

      DBA                            SYS                            DBMS_AUTO_TASK_ADMIN           SYS                            EXECUTE                                 

       

       

      OWNER                          SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME                    

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

      PUBLIC                         DBMS_AUTO_TASK_ADMIN           SYS                            DBMS_AUTO_TASK_ADMIN          

       

      I have similar account in other databases. And successfully enabled auto stats collection except this one.

      I am having execute privs to this package through roles as same as other database where I enabled.

      Any idea what I am missing.