DBMS_UTILITY
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: DBMS_UTILITY

Hybrid View

  1. #1
    Join Date
    Jun 2000
    Location
    Nagpur, Maharashtra, India
    Posts
    10
    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

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Nov 2000
    Posts
    212

    Talking

    if some privileges are still missing, can try this:

    grant all privileges to <schema>


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width