DBMS_UTILITY

[Gopinath]

Hi
I had just imported a schema with a lot of procedures and packages, and due to the dependencies, many of them show a status of invalid.
Instead of manually recompiling all the procedures etc, i tried using the command from sqlplus:
execute dbms_utility.compile_schema('SCHEMA_NAME');
However, i got the following error message:

SQL> execute dbms_utility.compile_schema('OLDNAG');
begin dbms_utility.compile_schema('OLDNAG'); end;

*
ERROR at line 1:
ORA-20000: You have insufficient privileges for an object in this schema.
ORA-06512: at "SYS.DBMS_UTILITY", line 219
ORA-06512: at line 1

What could be the possible reason?
I tried executing this first as user system, then from the same schema after granting it execute privelege on DBMS_UTILITY, but the error message remains the same.
Thanks

[jmodic]

DBMS_UTILITY is PL/SQL package and when you are runing it all your roles are disabled! Although SYSTEM has DBA role assigned, this role is useless while running PL/SQL procedures, functions, packages, triggers and views. You must grant *explicit* privilege to user to be valid inside PL/SQL.

Why your schema owner got the same error? Because probably some of the procedures depend on procedures/functions/packages/views from some other schema, which are also invalid. When DBMS_UTILITY.COMPILE_SCHEMA tries to compile procedure owned by OLDNAG and this procedure depends on some procedure in schema SCOTT it has first to recompile SCOTT''s procedure. For this to work OLDNAG has to be granted ALTER PROCEDURE object privilege on that procedure from SCOTT, or he has to be granted ALTER ANY PROCEDURE system privilege.

In your case I would grant:

GRANT ALTER ANY PROCEDURE TO oldnag; -- for procs, funcs, and packages
GRANT ALTER ANY TRIGGER TO oldnag; -- for triggers recompilation
GRANT ALTER ANY TABLE TO oldnag; -- for views recompilation

If those privileges are too powerfull for this schema you could revoke them after the recompilation succeds.

HTH,

[LND]

if some privileges are still missing, can try this:

grant all privileges to <schema>