Dbms_Utility.Compile_Schema Returns ORA-20000 Even Though You Have Proper Privileges (Doc ID 329842.1)

Last updated on FEBRUARY 04, 2022

Applies to:

Symptoms

   -  You are connected as SYSDBA
   -  You have "analyze any" system  privilege 
   -  The schema name is in uppercase

Note: You also get the same error while running dbms_utility.compile_schema as schema owner.

SQL>exec dbms_utility.compile_schema('TEST');
BEGIN dbms_utility.compile_schema('TEST'); END;
*
ERROR at line 1:
ORA-20000: You have insufficient privileges for an object in this schema.
ORA-06512: at "SYS.DBMS_UTILITY", line 282
ORA-06512: at line 1

When you set the errorstack for "ORA-20000" in the following way and then execute dbms_utility:

SQL> alter session set events '20000 trace name errorstack level 3';

Then execute:
SQL> exec dbms_utility.compile_schema('TEST');

The trace file generated in user_dump_destination(udump) will show:

ksedmp: internal or fatal error
ORA-20000: Unable to compile PACKAGE BODY "TEST"."TEST_PACK1",
insufficient privileges or does not exist
Current SQL statement for this session:
BEGIN dbms_utility.compile_schema('TEST'); END;
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
35c97784        89  package body SYS.DBMS_DDL
39864ec0       279  package body SYS.DBMS_UTILITY
3d82a33c         1  anonymous block

-----------------------
Failing functions will be:
-----------------------
ksddoa ksdpcg ksdpec ksfpec kgerev kserec1 kkxerae 
pfrrun peicnt kkxexe opiexe opiall0 kpoal8

When you try to compile the package body manually, you receive:

SQL> alter package   TEST.TEST_PACK1 compile body;
Warning: Package Body altered with  compilation errors.

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.