How to Fix Oracle Datapatch Errors: DBMS_AW, HTTPURITYPE, and UTL_TCP

Oracle Database patching is an essential activity for maintaining database security, stability, and performance. However, after installing a Release Update (RU) or another database patch, administrators may encounter SQL patching errors when executing the datapatch utility.

These errors can occur in the Container Database (CDB) or individual Pluggable Databases (PDBs) because of missing privileges, unavailable database objects, invalid dependencies, or component-related issues.

In this article, we examine a real-world Oracle Database patching scenario involving patch 39034528. We investigate errors related to SYS.DBMS_AW, SYS.HTTPURITYPE, and SYS.UTL_TCP, explain how to troubleshoot them, and demonstrate how to validate the patching results.

Environment: Oracle Database 19c multitenant architecture
Utility: datapatch -verbose
Patch ID: 39034528
Affected containers: CDB$ROOT, PDB1, and PDB2

Note: The troubleshooting steps below are based on the reported errors. Verify the database version, patch README, object status, and applicable Oracle Support guidance before implementing changes in production.

1. Understanding the Datapatch Errors

After the database patch installation, the following output appeared:

Patch installation complete. Total patches installed: 4

Validating logfiles...done

Patch 39034528 apply (pdb CDB$ROOT): WITH ERRORS

Although the patch installation process completed, SQL patching reported errors in several containers.

The patch results were:

Container	Patch status	Main issue
CDB$ROOT	WITH ERRORS	Oracle OLAP initialization and DBMS_AW errors
PDB$SEED	SUCCESS	No errors reported
PDB1	WITH ERRORS	Oracle Text and Semantic component compilation errors
PDB2	WITH ERRORS	Oracle Text and Semantic component compilation errors

This demonstrates an important point: successful binary patch installation does not guarantee successful SQL patching in every container.

Let’s examine the errors individually.

2. Error 1: ORA-37002 and ORA-04067 in CDB$ROOT

The root container reported errors while executing the following SQL script:

olap/admin/xoqawmd.sql

The main errors were:

ORA-37002: Oracle OLAP failed to initialize.
Please contact Oracle OLAP technical support.

ORA-04067: not executed, package "SYS.DBMS_AW" does not exist

Why does this happen?

The Oracle OLAP patching script requires the SYS.DBMS_AW package. The error indicates that the package could not be used when the script attempted to execute it.

Possible causes include:

• The package is missing or invalid.
• Required database component objects are unavailable.
• The required execution privileges are missing.
• An earlier component installation or compilation problem has affected the package.

The error alone does not establish which condition is responsible.

Step 1: Check the DBMS_AW object

Connect to the database as a privileged administrator:

sqlplus / as sysdba

Confirm the current container:

SHOW CON_NAME;

Check the object:

SELECT owner,
       object_name,
       object_type,
       status
FROM dba_objects
WHERE owner = 'SYS'
  AND object_name = 'DBMS_AW';

If the package exists, verify whether it is valid.

If it is missing or invalid, investigate the Oracle OLAP component and relevant patch logs before proceeding.

Step 2: Check existing privileges

Run:

SELECT owner,
       table_name,
       grantee,
       privilege
FROM dba_tab_privs
WHERE owner = 'SYS'
  AND table_name = 'DBMS_AW';

In the reported scenario, the following grant was used as part of the corrective action:

GRANT EXECUTE ON SYS.DBMS_AW TO PUBLIC;

Important: Apply this grant only after confirming that the package exists and that the grant is appropriate for your environment. Granting EXECUTE does not create a missing package or repair an invalid package.

3. Error 2: PLS-00201 for SYS.HTTPURITYPE in PDB1 and PDB2

The affected PDBs reported errors in the Oracle Text script:

ctx/admin/dr0stat.pkh

The primary error was:

PLS-00201: identifier 'SYS.HTTPURITYPE' must be declared

Additional errors followed:

PLS-00304: cannot compile body of 'TEXTOPTSTATS'
without its specification

PLS-00905: object CTXSYS.TEXTOPTSTATS is invalid

Why does this happen?

The Oracle Text script attempts to compile a type that depends on SYS.HTTPURITYPE.

When Oracle cannot resolve or access the required type, compilation fails. Dependent objects may subsequently become invalid.

The PLS-00304 and PLS-00905 messages are consequences of the initial compilation problem and should be investigated in that order.

Step 1: Check the HTTPURITYPE object

Connect to the affected PDB:

ALTER SESSION SET CONTAINER = PDB1;

Verify the current container:

SHOW CON_NAME;

Check the object:

SELECT owner,
       object_name,
       object_type,
       status
FROM dba_objects
WHERE owner = 'SYS'
  AND object_name = 'HTTPURITYPE';

Check the existing privileges:

SELECT owner,
       table_name,
       grantee,
       privilege
FROM dba_tab_privs
WHERE owner = 'SYS'
  AND table_name = 'HTTPURITYPE';

Step 2: Apply the required grant

In the reported scenario, the following statement was used:

GRANT EXECUTE ON SYS.HTTPURITYPE TO PUBLIC;

If the grant is confirmed to be the appropriate corrective action, execute it in the affected container.

Switch to the second PDB:

ALTER SESSION SET CONTAINER = PDB2;

Repeat the object and privilege checks. Apply the grant there only if it is required and appropriate.

Note: If SYS.HTTPURITYPE is missing or invalid, investigate the relevant Oracle-supplied object and component installation. A privilege grant alone will not fix an absent or invalid type.

4. Error 3: PLS-00201 for UTL_TCP

Another error appeared while executing:

md/admin/catsem.sql

The patching log reported:

PLS-00201: identifier 'UTL_TCP' must be declared

This indicates that the script could not resolve or access the UTL_TCP identifier.

Possible causes include a missing direct execution privilege, an unavailable object, or another dependency issue.

Step 1: Check the UTL_TCP package

Run the following query in the affected PDB:

SELECT owner,
       object_name,
       object_type,
       status
FROM dba_objects
WHERE owner = 'SYS'
  AND object_name = 'UTL_TCP';

Check the existing privileges:

SELECT owner,
       table_name,
       grantee,
       privilege
FROM dba_tab_privs
WHERE owner = 'SYS'
  AND table_name = 'UTL_TCP';

Step 2: Grant EXECUTE if required

In the reported scenario, the following statement was used:

GRANT EXECUTE ON SYS.UTL_TCP TO PUBLIC;

Apply it only after confirming that the package exists and the privilege is appropriate.

Because UTL_TCP provides network communication functionality, consider the security implications of granting access to PUBLIC. Where feasible, grant privileges to specific users or schemas instead.

If the object is missing or invalid, investigate the underlying database component or dependency problem.

5. Applying the Grants in CDB$ROOT and PDBs

In a multitenant Oracle Database environment, it is important to understand which container is active when executing administrative commands.

A privilege grant executed in CDB$ROOT does not automatically resolve every local privilege or object issue in all PDBs.

In CDB$ROOT

Connect as SYSDBA and confirm the current container:

SHOW CON_NAME;

After verifying that the objects exist and the grants are appropriate, execute the required statements:

GRANT EXECUTE ON SYS.DBMS_AW TO PUBLIC;

GRANT EXECUTE ON SYS.HTTPURITYPE TO PUBLIC;

GRANT EXECUTE ON SYS.UTL_TCP TO PUBLIC;

In PDB1

Switch to the first affected PDB:

ALTER SESSION SET CONTAINER = PDB1;

Confirm the container:

SHOW CON_NAME;

Verify the required objects and privileges. Where the same grants are required and supported, execute:

GRANT EXECUTE ON SYS.DBMS_AW TO PUBLIC;

GRANT EXECUTE ON SYS.HTTPURITYPE TO PUBLIC;

GRANT EXECUTE ON SYS.UTL_TCP TO PUBLIC;

In PDB2

Switch to the second affected PDB:

ALTER SESSION SET CONTAINER = PDB2;

Repeat the verification and apply only the required grants:

GRANT EXECUTE ON SYS.DBMS_AW TO PUBLIC;

GRANT EXECUTE ON SYS.HTTPURITYPE TO PUBLIC;

GRANT EXECUTE ON SYS.UTL_TCP TO PUBLIC;

Security and configuration note: These are broad grants. Do not automatically execute every statement in every container without checking object availability, the existing privilege state, and Oracle’s patch-specific guidance. The required correction may differ between containers.

6. Check the Database Component Status

If errors continue after the privilege checks, investigate the relevant database components.

Run:

SELECT comp_id,
       comp_name,
       version,
       status
FROM dba_registry
ORDER BY comp_name;

Review the status of the Oracle OLAP, Oracle Text, and other relevant components.

An invalid or inconsistent component may require additional investigation beyond granting execution privileges.

You can also check invalid objects:

SELECT owner,
       object_type,
       COUNT(*) AS invalid_count
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY owner, object_type
ORDER BY owner, object_type;

For the affected Oracle Text type, run:

SELECT owner,
       object_name,
       object_type,
       status
FROM dba_objects
WHERE owner = 'CTXSYS'
  AND object_name = 'TEXTOPTSTATS';

If the object remains invalid, inspect its compilation errors:

SELECT owner,
       name,
       type,
       line,
       position,
       text
FROM dba_errors
WHERE owner = 'CTXSYS'
  AND name = 'TEXTOPTSTATS'
ORDER BY sequence;

Investigate the first underlying compilation error before attempting to recompile dependent objects.

7. Rerun Datapatch

After addressing the identified issues, rerun the SQL patching utility from the correct Oracle home.

First, verify the environment:

echo $ORACLE_HOME
echo $ORACLE_SID

Then execute:

$ORACLE_HOME/OPatch/datapatch -verbose

The -verbose option provides detailed output during patch installation and validation.

Review the output for each container.

For example:

Patch installation complete.

Validating logfiles...done

Patch 39034528 apply (pdb PDB1): SUCCESS

The output above is an illustration of the expected success status, not a claim that the patch has already succeeded in your environment.

If the patch still reports WITH ERRORS, review the newly generated log files and investigate the first underlying error.

Do not assume that a previous grant resolved all issues simply because the utility completed execution.

8. Validate the SQL Patch Status

After datapatch finishes, check the SQL patch registry.

Run:

SELECT patch_id,
       patch_type,
       action,
       status,
       action_time,
       description
FROM dba_registry_sqlpatch
WHERE patch_id = 39034528
ORDER BY action_time DESC;

Check the patch status across containers using CDB_REGISTRY_SQLPATCH:

SELECT con_id,
       patch_id,
       patch_type,
       action,
       status,
       action_time,
       description
FROM cdb_registry_sqlpatch
WHERE patch_id = 39034528
ORDER BY con_id, action_time DESC;

Confirm that the expected successful status is recorded for CDB$ROOT, PDB1, and PDB2.

Also verify the PDB$SEED result.

If the patch was recorded as failed or the relevant container still reports errors, investigate before declaring the patching activity complete.

9. Review the Datapatch Log Files

Oracle stores SQL patching logs under the Oracle diagnostic configuration directory.

In this scenario, the log files were located under:

/u01/app/oracle/cfgtoollogs/sqlpatch/

The original root-container log was:

/u01/app/oracle/cfgtoollogs/sqlpatch/39034528/28776809/39034528_apply_ATMPR_CDBROOT_2026Oct29_14_59_29.log

Use the corresponding log files for the affected containers to investigate any remaining errors.

For example, search for common Oracle errors:

grep -nE 'ORA-|PLS-|SP2-' \
/u01/app/oracle/cfgtoollogs/sqlpatch/39034528/28776809/*.log

Review the full context around each error rather than relying only on the summary output.

This is especially important when multiple compilation errors occur in sequence.

10. Common Mistakes to Avoid

Mistake 1: Assuming every PLS-00201 error is caused by missing privileges

The error can indicate that an object is missing, invalid, inaccessible, or not resolvable in the current context.

Check the object before applying grants.

Mistake 2: Applying grants only in the root container

Always verify the state of the required objects and privileges in each affected PDB.

Mistake 3: Ignoring dependent compilation errors

Errors such as PLS-00304 and PLS-00905 may be consequences of an earlier failure.

Investigate the original error first.

Mistake 4: Assuming that patch installation means patching is complete

The database binary patch and the SQL patch are separate stages.

Validate the SQL patch status for each container before declaring success.

Mistake 5: Granting EXECUTE to PUBLIC without reviewing security

A grant to PUBLIC gives broad access to the specified object.

Review the security impact and use the narrowest appropriate privilege scope.

Mistake 6: Repeating datapatch without addressing the underlying problem

Repeated execution alone will not resolve a missing object, invalid component, or unresolved dependency.

Investigate the underlying issue before retrying the patch.

11. Final Validation Checklist

Before closing the patching activity, confirm the following:

• The binary patch installation completed successfully.
• The correct Oracle home was used.
• All affected containers were identified.
• The required Oracle-supplied objects were checked.
• Existing privileges were reviewed.
• The required corrective actions were applied.
• datapatch -verbose completed without unresolved errors.
• Patch 39034528 has the expected status in DBA_REGISTRY_SQLPATCH.
• The patch status was verified across all relevant containers.
• Previously failing objects were checked.
• No unresolved component or compilation issues remain.

Conclusion

Oracle datapatch failures can occur when SQL patch scripts encounter missing objects, unavailable privileges, or invalid dependencies.

In this example, patch 39034528 reported Oracle OLAP errors involving SYS.DBMS_AW, Oracle Text compilation errors involving SYS.HTTPURITYPE, and a Semantic component error involving UTL_TCP.

The troubleshooting process focused on verifying the required objects, checking execution privileges, reviewing the affected containers, and applying the appropriate corrective actions.

The next step was to rerun datapatch -verbose and validate the SQL patch registry across the root container and affected PDBs.

The most important lesson is simple: identify the underlying error, verify the affected container, apply a targeted fix, and validate the patch status before declaring the patching activity successful.

For production databases, always review the patch README and applicable Oracle Support recommendations before modifying Oracle-supplied objects or granting privileges to PUBLIC.

12.1: datapatch -verbose Fails with Following Error”PLS-00201: identifier ‘UTL_FILE’ must be declared” (Doc ID 2265408.1):