PLS-00201: identifier ‘SYS.DBMS_CUBE_EXP’ must be declared

PLS-00201: identifier ‘SYS.DBMS_CUBE_EXP’ must be declared

While performing Data pump export (expdp) the following errors were seen in the log file. The Oracle RDBMS version was 19.3.0.0.0 

BEGIN "SYS"."DBMS_CUBE_EXP".SCHEMA_CALLOUT(:1,0,1,'19.00.00.00.00'); END;
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06550: line 1, column 8:
PLS-00201: identifier 'SYS.DBMS_CUBE_EXP' must be declared
ORA-06550: line 1, column 8:
PL/SQL: Statement ignored

According to Oracle the cause is that OLAP objects remain existing in data dictionary while OLAP is not installed or was de-installed. https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-state=hhnfrabhd_4&id=1328829.1

To verify this issue this query

$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 10 15:41:31 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> SELECT * FROM SYS.EXPPKGACT$ WHERE PACKAGE = 'DBMS_CUBE_EXP';

PACKAGE            SCHEMA   CLASS     LEVEL#
---------------------------------------------------------------------------------------                                                                                                                                
DBMS_CUBE_EXP      SYS      2       1050                                                                                                                                 
DBMS_CUBE_EXP      SYS      4       1050                                                                                                                                    
DBMS_CUBE_EXP      SYS      6       1050

SOLUTION

Connect as sysdba

$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 10 15:41:31 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>

Take backup of the table SYS.EXPPKGACT$ before deleting the row.

SQL> CREATE TABLE SYS.EXPPKGACT$_BKP AS SELECT * FROM SYS.EXPPKGACT$;
      Table created.

Delete DBMS_CUBE_EXP from SYS.EXPPKGACT$

SQL> DELETE FROM SYS.EXPPKGACT$ WHERE PACKAGE = 'DBMS_CUBE_EXP' AND SCHEMA= 'SYS';
3 rows deleted.
SQL> commit;
Commit complete.

Run the data pump (expdp) Command again.

Tags: ,