Tuesday, 31 July 2018

ORA-04063: package body "IMADVISOR.DBMS_INMEMORY_ADVISOR_PRVT" has errors | Oracle 12c

Unable to Configure Oracle In-Memory Advisor in Oracle 12c - Failing with ORA-04063 ORA-06508 ORA-06512


Oracle Database 12c In Memory Advisor

ORA-04063: package body "IMADVISOR.DBMS_INMEMORY_ADVISOR_PRVT" has errors
ORA-06508: PL/SQL: could not find program unit being called:
"IMADVISOR.DBMS_INMEMORY_ADVISOR_PRVT"


This error occured to me while running @instimadv.sql to configure Oracle In-Memory Advisory on Production Database. Below mentioned is the complete error description,

Warning: Package Body created with compilation errors.

Errors for PACKAGE BODY DBMS_INMEMORY_ADVISOR_PRVT:

LINE/COL ERROR
-------- -----------------------------------------------------------------
10310/23 PL/SQL: Item ignored
10310/23 PLS-00201: identifier 'UTL_FILE' must be declared
10312/5  PL/SQL: Statement ignored
10312/5  PLS-00320: the declaration of the type of this expression is
         incomplete or malformed

10313/5  PL/SQL: Statement ignored
10313/22 PLS-00320: the declaration of the type of this expression is
         incomplete or malformed
Parvinder Rathore PM 03:31:
BEGIN
*
ERROR at line 1:
ORA-04063: package body "IMADVISOR.DBMS_INMEMORY_ADVISOR_PRVT" has errors
ORA-06508: PL/SQL: could not find program unit being called:
"IMADVISOR.DBMS_INMEMORY_ADVISOR_PRVT"
ORA-06512: at line 8

 Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options


Now after looking into the same, I found that the above mentioned error is considered as a Bug - 25918275 which is normally seen in 12.1.0.2 Database Release.

For a quick workaround, please follow the below mentioned steps:


CREATE USER c##imadvisor IDENTIFIED BY secure-password DEFAULT TABLESPACE perm-tablespace TEMPORARY TABLESPACE temp-tablespace;

GRANT EXECUTE ON utl_file TO c##imadvisor;

After this, run @instimadv.sql

I hope this helps !!

No comments:

Post a comment