Thursday, July 2, 2009

ORA-04063: package body "SYS.DBMS_INTERNAL_LOGSTDBY" has errors;ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_INTERNAL_LOGSTD

E:\export>expdp "'sys/*******@dbname as sysdba'" parfile=E:\export\expdp.par

Export: Release 10.2.0.4.0 - Production on Tuesday, 30 June, 2009 10:56:10

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-04063: package body "SYS.DBMS_INTERNAL_LOGSTDBY" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_INTERNAL_LOGSTDBY"
ORA-06512: at "SYS.KUPV$FT", line 842
ORA-04063: package body "SYS.DBMS_INTERNAL_LOGSTDBY" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_INTERNAL_LOGSTDBY"

Tried to compile that package and got the below error.

PACKAGE BODY SYS.DBMS_INTERNAL_LOGSTDBY On line: 3583 PL/SQL: ORA-00942: table or view does not exist

So tried to find which objects it is using/dependent with the below query.

SELECT owner, object_type, object_name, object_id, status
FROM SYS.dba_objects
where object_name = 'DBMS_INTERNAL_LOGSTDBY';

SELECT B.*
FROM PUBLIC_DEPENDENCY A, SYS.DBA_OBJECTS B
WHERE A.OBJECT_ID = 7331 AND A.REFERENCED_OBJECT_ID = B.OBJECT_ID AND OBJECT_TYPE = 'TABLE'
ORDER BY OWNER, OBJECT_NAME;

OBJECT_NAME

AUDIT_ACTIONS
COL$
DUAL
IND$
INDPART$
JOB$
OBJ$
TAB$
TS$
USER$
LOGMNR_DICTSTATE$
LOGMNR_LOG$
LOGMNR_SESSION$
LOGMNR_UID$
LOGSTDBY$APPLY_MILESTONE
LOGSTDBY$EVENTS
LOGSTDBY$HISTORY
LOGSTDBY$PARAMETERS
LOGSTDBY$SCN
LOGSTDBY$SKIP
LOGSTDBY$SKIP_TRANSACTION

Found that one table is missing by comparing the package with other database which is same version 10.2.0.4.

Table LOGSTDBY$SKIP_SUPPORT is missing in the system schema which the package is refering.

Created the script for that missing table from the database and created in the other database which is giving error.

The problem solved and export completed sucessfully with no errors.


reference:
------------

http://pandazen.wordpress.com/2009/01/03/cannot-drop-jobs-because-sysdbms_internal_logstdby-error/


http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=32765 - solution

2 comments:

  1. Could you post the script that was used in creating the logstdby$skip_support table?

    ReplyDelete