oracle报错ORA-04068 DBMS_SPACE_ADMIN

业务人员反馈创建所以会报错，报错为ORA-04068 DBMS_SPACE_ADMIN。看名字应该是与空间管理相关的包存在问题

一、检查对象

SQL> set linesize 1000
SQL> col object_name for a30
SQL> SELECT object_name, object_type, owner, status, last_ddl_time FROM dba_objects WHERE object_name = 'DBMS_SPACE_ADMIN';

OBJECT_NAME                    OBJECT_TYPE         OWNER                          STATUS  LAST_DDL_TIM
------------------------------ ------------------- ------------------------------ ------- ------------
DBMS_SPACE_ADMIN               PACKAGE             SYS                            VALID   26-MAY-23
DBMS_SPACE_ADMIN               PACKAGE BODY        SYS                            INVALID 26-MAY-23
DBMS_SPACE_ADMIN               SYNONYM             PUBLIC                         VALID   24-AUG-13

检查发现DBMS_SPACE_ADMIN属于sys用户下的包

二、检查依赖

SQL> SELECT object_name, object_type, owner, status, last_ddl_time FROM dba_objects WHERE ( object_name, object_type ) IN ( SELECT referenced_name, referenced_type FROM dba_dependencies WHERE name = 'DBMS_SPACE_ADMIN' );

OBJECT_NAME                    OBJECT_TYPE         OWNER                          STATUS  LAST_DDL_TIM
------------------------------ ------------------- ------------------------------ ------- ------------
DBMS_SPACE_ADMIN               PACKAGE             SYS                            VALID   26-MAY-23
STANDARD                       PACKAGE             SYS                            VALID   24-AUG-13

三、检查报错

SQL> SELECT name, type, text FROM dba_errors WHERE name = 'DBMS_SPACE_ADMIN';

NAME                           TYPE         TEXT
------------------------------ ------------ ----------------------------------------------------------------------------------------------------
DBMS_SPACE_ADMIN               PACKAGE BODY PLS-00753: malformed or corrupted wrapped unit

四、重建包体

之前尝试重新编译package DBMS_SPACE_ADMIN,编译后依然失效，只能重建包体

SQL> @?/rdbms/admin/prvtspad.plb

Package body created.

SQL> SELECT object_name, object_type, owner, status, last_ddl_time FROM dba_objects WHERE object_name = 'DBMS_SPACE_ADMIN';

OBJECT_NAME                    OBJECT_TYPE         OWNER                          STATUS  LAST_DDL_TIM
------------------------------ ------------------- ------------------------------ ------- ------------
DBMS_SPACE_ADMIN               PACKAGE             SYS                            VALID   26-MAY-23
DBMS_SPACE_ADMIN               PACKAGE BODY        SYS                            VALID   26-MAY-23
DBMS_SPACE_ADMIN               SYNONYM             PUBLIC                         VALID   24-AUG-13