ORA-04063: package body “SYS.DBMS_DATAPUMP“ has errors

本文链接：https://blog.csdn.net/jycjyc/article/details/118339254

由于手工修改编译了sys.DBMS_DATAPUMP的start_job里cluster_ok的默认参数值，1为0，编译成功后，导致导出报错。

ORA-04063: package body "SYS.DBMS_DATAPUMP" has errors

SQL> CREATE TABLE "test"
2 ( " " VARCHAR2(255), "YPID" VARCHAR2(255));

Table created.

SQL> insert into "test" values('aa','bb');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

--------------------------------------------------------------------------------
YPID
--------------------------------------------------------------------------------
aa
bb

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

[oracle@oem ~]$ expdp jyc/jyc@jyc dumpfile=t.dmp logfile=t.log directory=dmp tables=jyc.test

Export: Release 19.0.0.0.0 - Production on Tue Jun 29 16:54:57 2021
Version 19.3.0.0.0

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

UDE-04063: operation generated ORACLE error 4063
ORA-04063: package body "SYS.DBMS_DATAPUMP" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_DATAPUMP"
ORA-06512: at line 1

[oracle@oem ~]$ sqlplus jyc/jyc@jyc

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 29 16:57:00 2021
Version 19.3.0.0.0

Last Successful login time: Tue Jun 29 2021 16:54:57 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select * from dba_errors where OWNER='SYS' and NAME='DBMS_DATAPUMP';

OWNER
--------------------------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
TYPE SEQUENCE LINE POSITION
-------------------------------------- ---------- ---------- ----------
TEXT
--------------------------------------------------------------------------------
ATTRIBUTE MESSAGE_NUMBER
------------------ --------------
SYS
DBMS_DATAPUMP
PACKAGE BODY 1 7294 17

SQL>

select * from dba_objects where status = 'INVALID'

select owner, name, text, message_number from all_errors where owner='SYS';

解决办法：

在cdb下执行重建dbms_datapump

cd $ORACLE_HOME/rdbms/admin
sqlplus / as sysdba
@dbmsdp.sql;
@utlrp.sql;
exit

重导正常：

[oracle@oem admin]$ expdp jyc/jyc@jyc dumpfile=t.dmp logfile=t.log directory=dmp tables=jyc.test

Export: Release 19.0.0.0.0 - Production on Thu Jul 1 10:29:24 2021
Version 19.3.0.0.0

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
FLASHBACK automatically enabled to preserve database integrity.
Starting "JYC"."SYS_EXPORT_TABLE_01": jyc/********@jyc dumpfile=t.dmp logfile=t.log directory=dmp tables=jyc.test
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "JYC"."TEST" 0 KB 0 rows
Master table "JYC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for JYC.SYS_EXPORT_TABLE_01 is:
/home/oracle/dmp/t.dmp
Job "JYC"."SYS_EXPORT_TABLE_01" successfully completed at Thu Jul 1 10:29:32 2021 elapsed 0 00:00:08