Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Juan.

Asked: January 23, 2023 - 4:05 pm UTC

Last updated: January 26, 2023 - 4:53 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

ORA-04063: package body "SYS.DBMS_NETWORK_ACL_ADMIN" has errors,
how do I reinstall this package?

and Connor said...

First I would check DBA_OBJECTS to see if anything else has errors, but that seems an odd issue (those packages should never be invalid).

Then run $ORACLE_HOME/rdbms/admin/utlrp.sql to see if it just needed a recompile

If not, then in 11g you'll need to run

$ORACLE_HOME/rdbms/admin/dbmsnacl.sql
$ORACLE_HOME/rdbms/admin/prvtnacl.plb

as sysdba

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

the domain index is marked as LOADING/FAILED/UNUSABLE

Juan Pablo, January 25, 2023 - 4:15 pm UTC

Hello, the DBMS_NETWORK_ACL_ADMIN package body problem is fixed, but I'm trying to create an ACL list and I get this error


ORA-29861: the domain index is marked as LOADING/FAILED/UNUSABLE
ORA-06512: en "SYS.DBMS_NETWORK_ACL_ADMIN", línea 258
ORA-06512: en línea 2
29861. 00000 - "domain index is marked LOADING/FAILED/UNUSABLE"
*Cause: An attempt has been made to access a domain index that is
being built or is marked failed by an unsuccessful DDL
or is marked unusable by a DDL operation.
*Action: Wait if the specified index is marked LOADING
Drop the specified index if it is marked FAILED
Drop or rebuild the specified index if it is marked UNUSABLE.

Chris Saxon
January 26, 2023 - 4:53 pm UTC

This looks bad.

As Connor says, this package should always be valid. If you're running into further issues it suggests someone's been tampering with them/your database.

You could use this query to find which indexes are invalid:

select index_name, status 
from   dba_indexes
where  index_type = 'DOMAIN'
and    status <> 'VALID';


But sorting this out could be complicated - it's worth figuring out how this package became invalidated in the first place to see if you can undo whatever those actions were.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library