SYS.DBMS_ASSERT issue resolution

Error information:

SQL> grant execute on dbms_assert to public;
grant execute on dbms_assert to public
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 58, column 51:
PLS-00201: identifier 'SYS.DBMS_ASSERT' must be declared
ORA-06550: line 58, column 7:
PL/SQL: Statement ignored
ORA-06550: line 62, column 40:
PLS-00201: identifier 'SYS.DBMS_ASSERT' must be declared
ORA-06550: line 62, column 9:
PL/SQL: Statement ignored 

Issue details and steps taken to resolve:

The primary reason for the issue is because of some of the privileges on the internal dictionary views revoked from the PUBLIC. This has caused the DBMS_ASSERT not to function as expected and caused issues for almost all the DDLs that touch the database dictionary.

The default installation of the Oracle Database requires that certain privileges be granted to PUBLIC.  Grants to PUBLIC are inherited by all database users.

In order to get the dependencies for the UTL_FILE and other components, we can use the below script as provided by metalink.

   -- Example script: dependency report for UTL_FILE

   col owner for a15

   col references for a10

   set feedback off

   set verify off

   def SYSTEM_PACKAGE=UTL_FILE

   select 'Schemas with dependencies on &&SYSTEM_PACKAGE'

   "Dependency Report" from dual;

   select owner, count(name) objects, referenced_owner references,

          referenced_type type

           from dba_dependencies

               where

   referenced_owner in ('SYS','PUBLIC')     and

                     referenced_type in ('PACKAGE','SYNONYM')  and

                     referenced_name = '&&SYSTEM_PACKAGE' and

                     owner <> 'SYS' and

                     owner <> 'PUBLIC'

   group by owner, referenced_owner, referenced_type

   order by owner, referenced_owner, referenced_type;

   undef SYSTEM_PACKAGE

The dependencies on all the PUBLIC objects and the dictionary views can be obtained by querying the dba_dependencies view and the sample SQL is given below.

    Select 'grant execute on '||

       referenced_name||' to '||owner||';' statements

    from dba_dependencies

               where

    referenced_owner in ('SYS','PUBLIC')     and

                     referenced_type in ('PACKAGE','SYNONYM')  and

                     referenced_name in

    ('DBMS_RANDOM','DBMS_EXPORT_EXTENSION','UTL_FILE',

     'DBMS_JOB','DBMS_LOB','UTL_SMTP','UTL_TCP','UTL_HTTP') and

                     owner <> 'SYS' and

                     owner <> 'PUBLIC')

The above will generate a dynamic SQL which can be used to restore the privileges and permissions. This should fix the issue in most of the cases but if this also doesn’t fix the problem, then the only option is to restore the database from a previous successful backup.

References : Database team worked with self in this issue.

Thanks.

--------------------------------------------- End of Document ---------------------------------------------------------