DB Version:10gR2
Since dbms_utility.compile_schema(user); was hanging all the time. I decided to use UTL_RECOMP.recomp_parallel . I could log in as SYS and recompile objects in SCOTT user without any issues .But i want the developers to run this command from their own schema. So i tried to test this by logging in as SCOTT and executing UTL_RECOMP.recomp_parallel. But i was getting errors.
Logging in as SYS
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> EXEC UTL_RECOMP.recomp_parallel(4, 'SCOTT');
PL/SQL procedure successfully completed.
SQL> conn scott/tiger
Connected.
SQL> EXEC UTL_RECOMP.recomp_parallel(4);
BEGIN UTL_RECOMP.recomp_parallel(4); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'UTL_RECOMP.RECOMP_PARALLEL' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
SQL> conn sys/mypassword@db8 as sysdba
Connected.
SQL> grant execute on UTL_RECOMP to scott;
Grant succeeded.
SQL> conn scott/tiger
Connected.
SQL> EXEC UTL_RECOMP.recomp_parallel(4);
BEGIN UTL_RECOMP.recomp_parallel(4); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'UTL_RECOMP.RECOMP_PARALLEL' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Any idea why i am getting this error?