Home » RDBMS Server » Server Administration » SYS objects and Catproc Invalid (10.2.0.5/AIX)
SYS objects and Catproc Invalid [message #500634] Tue, 22 March 2011 09:18 Go to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
Hello Everybody,

I am getting below error while connecting to sqlplus.

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 22 12:47:48 2011

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

ERROR:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_OUTPUT.DISABLE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

ERROR:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_APPLICATION_INFO.SET_MODULE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Error accessing package DBMS_APPLICATION_INFO
SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level


Executed the below scripts but it didnt resolve the issue, whereas some of the SYS objects and catproc got invalid...

dbmsotpt.sql
dbmsapin.sql


Now even after reexecuting the catproc.sql and utlrp...Sys objects and the catproc status is still INvalid.

I tried to manually compile the sys objects, but it didnt work.

OWNER                SUBSTR(OBJECT_NAME,1,40)                 OBJECT_TYPE
-------------------- ---------------------------------------- --------------------
SYS                  DBMS_XPLAN                               PACKAGE BODY
SYS                  AQ$AQ_SRVNTFN_TABLE                      VIEW
SYS                  DBMS_LOGREP_DEF_PROC                     PACKAGE
SYS                  DBMS_LOGREP_DEF_PROC                     PACKAGE BODY
SYS                  INITJVMAUX                               PACKAGE BODY
SYS                  XMLDOM                                   SYNONYM
SYS                  XMLPARSER                                SYNONYM
SYS                  XSLPROCESSOR                             SYNONYM
SYS                  SQLJUTL                                  PACKAGE
SYS                  XMLVALIDATE                              PROCEDURE
SYS                  DBMS_XQUERYINT                           PACKAGE BODY

OWNER                SUBSTR(OBJECT_NAME,1,40)                 OBJECT_TYPE
-------------------- ---------------------------------------- --------------------
SYS                  _DBA_REGISTERED_ARCHIVED_LOG             VIEW
SYS                  KU$_XMLSCHEMA_VIEW                       VIEW
SYS                  AQ$_SCHEDULER$_JOBQTAB_F                 VIEW
SYS                  AQ$SCHEDULER$_JOBQTAB                    VIEW
SYS                  AQ$SCHEDULER$_JOBQTAB_R                  VIEW
SYS                  AQ$_SCHEDULER$_EVENT_QTAB_F              VIEW
SYS                  AQ$SCHEDULER$_EVENT_QTAB_R               VIEW
SYS                  AQ$_AQ$_MEM_MC_F                         VIEW
SYS                  AQ$_ALERT_QT_F                           VIEW
SYS                  AQ$ALERT_QT_R                            VIEW
SYS                  PRVT_ADVISOR                             PACKAGE BODY

OWNER                SUBSTR(OBJECT_NAME,1,40)                 OBJECT_TYPE
-------------------- ---------------------------------------- --------------------
SYS                  DBMS_SQLTUNE_INTERNAL                    PACKAGE BODY
SYS                  DBMS_SQLTUNE                             PACKAGE BODY
SYS                  AQ$_AQ_SRVNTFN_TABLE_F                   VIEW
SYS                  SQLJUTL                                  PACKAGE BODY
SYS                  UTL_DBWS                                 PACKAGE
SYS                  UTL_DBWS                                 PACKAGE BODY
SYS                  DBMS_XQUERYINT                           PACKAGE
SYS                  SET_TABLESPACE                           PROCEDURE
SYS                  DBMS_EPG                                 PACKAGE BODY
SYS                  DBA_XML_SCHEMAS                          VIEW
SYS                  USER_XML_SCHEMAS                         VIEW

OWNER                SUBSTR(OBJECT_NAME,1,40)                 OBJECT_TYPE
-------------------- ---------------------------------------- --------------------
SYS                  ALL_XML_INDEXES                          VIEW
SYS                  USER_XML_INDEXES                         VIEW
SYS                  DBMS_REGXDB                              PACKAGE BODY
SYS                  DBMS_SQLTCB_INTERNAL                     PACKAGE BODY
SYS                  DBMS_SQLDIAG                             PACKAGE BODY



Pls provide your expertise, how to go about making the SYS objects and catproc VALID and resolve the error which i mentioned above.

Thanks,
Bryan.

Report message to a moderator

Re: SYS objects and Catproc Invalid [message #500636 is a reply to message #500634] Tue, 22 March 2011 09:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What did you do to make them invalid?
Were they valid at any point in time?

Regards
Michel

Report message to a moderator

Re: SYS objects and Catproc Invalid [message #500637 is a reply to message #500636] Tue, 22 March 2011 09:46 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, the usual way to make objects valid is to fix all errors in them, of course something highly irregular must have happened to make the SYS objects invalid.

With what user did you actually run those scripts? Where there any errors while you did run them? Has anything happened that made the sys objects invalid?

Then you could also have a look what the actual errors are:
SELECT * FROM all_errors

[Updated on: Tue, 22 March 2011 09:47]

Report message to a moderator

Re: SYS objects and Catproc Invalid [message #500639 is a reply to message #500637] Tue, 22 March 2011 10:20 Go to previous messageGo to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
Hi Michel,

They were Valid, before I executed the script dbmsotpt.sql and dbmsapin.sql to fix the below error, the error wasnt fixed, however invalidated the sys and catproc. 


ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_OUTPUT.DISABLE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

ERROR:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_APPLICATION_INFO.SET_MODULE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Error accessing package DBMS_APPLICATION_INFO
SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level



Hi ThomasG

I executed above mentioned the scipts using SYS, and there were no errors during execution of the scripts


Pls Suggest!

Bryan
Thanks

Report message to a moderator

Re: SYS objects and Catproc Invalid [message #500641 is a reply to message #500639] Tue, 22 March 2011 10:41 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
ThomasG wrote on Tue, 22 March 2011 15:46
Then you could also have a look what the actual errors are:
SELECT * FROM all_errors


Report message to a moderator

Re: SYS objects and Catproc Invalid [message #500642 is a reply to message #500641] Tue, 22 March 2011 11:05 Go to previous messageGo to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
Rebooted the server and executed the catproc and Utlrp, all the objects are Valid now..

But I am back to the original prob now.

[aix]/home/oracle$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Mar 23 00:02:01 2011

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
[aix]/home/oracle$ sqlplus /

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Mar 23 00:02:09 2011

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

[b][color=red]ERROR:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_APPLICATION_INFO.SET_MODULE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


Error accessing package DBMS_APPLICATION_INFO[/color][/b]Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

SQL>



Pls suggest on this error.
Thanks

Report message to a moderator

Re: SYS objects and Catproc Invalid [message #500646 is a reply to message #500642] Tue, 22 March 2011 11:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
a flawed implementation of a LOGON trigger exists.

LOGON trigger do NOT get fired for SYS user.

Report message to a moderator

Re: SYS objects and Catproc Invalid [message #500647 is a reply to message #500642] Tue, 22 March 2011 11:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
 
select owner, object_name, object_type, status
from dba_objects
where ( object_name like 'DBMS%' or object_name like 'DBA%' )
  and owner not in ('SYS','PUBLIC','DMSYS','EXFSYS','MDSYS','WMSYS','XDB')
order by 1, 2, 3
/

Regards
Michel

Report message to a moderator

Re: SYS objects and Catproc Invalid [message #500656 is a reply to message #500634] Tue, 22 March 2011 12:02 Go to previous messageGo to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
Now all the objects are Valid..

SQL> select owner, object_name, object_type, status
from dba_objects
where ( object_name like 'DBMS%' or object_name like 'DBA%' )
  and owner not in ('SYS','PUBLIC','DMSYS','EXFSYS','MDSYS','WMSYS','XDB')
order by 1, 2, 3  2    3    4    5  
  6  ;

OWNER                          OBJECT_NAME          OBJECT_TYPE         STATUS
------------------------------ -------------------- ------------------- -------
RMAN                           DBMS_RCVCAT          PACKAGE             VALID
RMAN                           DBMS_RCVCAT          PACKAGE BODY        VALID
RMAN                           DBMS_RCVMAN          PACKAGE             VALID
RMAN                           DBMS_RCVMAN          PACKAGE BODY        VALID
SYSTEM                         DBMS_REPCAT_AUTH     PACKAGE             VALID
SYSTEM                         DBMS_REPCAT_AUTH     PACKAGE BODY        VALID

6 rows selected.



But the error still persists.

[aix]/home/oracle$ sqlplus /

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Mar 23 00:02:09 2011

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

[b][color=red]ERROR:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_APPLICATION_INFO.SET_MODULE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


Error accessing package DBMS_APPLICATION_INFO[/color][/b]Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

SQL>

Report message to a moderator

Re: SYS objects and Catproc Invalid [message #500662 is a reply to message #500656] Tue, 22 March 2011 12:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I advise you to export your schemas and recreate the database, it will take far less time and is far more sure than trying to fix it.

Regards
Michel

[Updated on: Tue, 22 March 2011 12:56]

Report message to a moderator

Re: SYS objects and Catproc Invalid [message #500665 is a reply to message #500662] Tue, 22 March 2011 12:52 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member
Paste output of,

select owner, object_type from all_objects where object_name='DUAL';

Report message to a moderator

Re: SYS objects and Catproc Invalid [message #500710 is a reply to message #500665] Wed, 23 March 2011 00:52 Go to previous messageGo to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
SQL> select owner, object_type from all_objects where object_name='DUAL';

OWNER OBJECT_TYPE
------------------------------ -------------------
SYS TABLE
PUBLIC SYNONYM

Report message to a moderator

Re: SYS objects and Catproc Invalid [message #500720 is a reply to message #500710] Wed, 23 March 2011 02:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
See it is almost 24h you started this topic and you have no solution (and will have no safe one).
Do you think that recreating the database would take more?

Regards
Michel

Report message to a moderator

Re: SYS objects and Catproc Invalid [message #500759 is a reply to message #500720] Wed, 23 March 2011 07:52 Go to previous messageGo to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
Hi Michael,
Recreating DB option is always wide and open.
However want to sort out this issue, without recreating the database.

Looking for some expertise on this issue..as i am very sure that it is not only me in the oracle world who has faced this kind of error..

Thanks,
Bryan

Report message to a moderator

Re: SYS objects and Catproc Invalid [message #500767 is a reply to message #500759] Wed, 23 March 2011 08:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
However want to sort out this issue, without recreating the database

You can't.

Quote:
as i am very sure that it is not only me in the oracle world who has faced this kind of error..

Who knows? Maybe only you screwed had screwed his database in this way (but you refuse to tell us what you happened).

Regards
Michel

[Updated on: Wed, 23 March 2011 08:02]

Report message to a moderator

Re: SYS objects and Catproc Invalid [message #500778 is a reply to message #500767] Wed, 23 March 2011 08:20 Go to previous messageGo to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
@Michel,

Quote:
Who knows? Maybe only you screwed had screwed his database in this way (but you refuse to tell us what you happened).


All the story is written in bold and clear in this thread, what caused the issue..however you refuse to accept that...and close your eyes by pretending that you saw nothing.

Nyways, I am sure that there is a way to sort out this issue..
Will soon be back with solution.

Bryan

Report message to a moderator

Re: SYS objects and Catproc Invalid [message #500779 is a reply to message #500778] Wed, 23 March 2011 08:26 Go to previous messageGo to next message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
To recreate DBMS_APPLICATION_INFO you need to run TWO scripts:
1. dbmsapin.sql
2. prvtapin.plb

Same with DBMS_OUTPUT:
1. dbmsotpt.sql
2. prvtotpt.plb

If you get errors while recreating them, please post the details here.

Report message to a moderator

Re: SYS objects and Catproc Invalid [message #500781 is a reply to message #500778] Wed, 23 March 2011 08:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
All the story is written in bold and clear in this thread

I doubt that executing the 2 scripts you mentioned is the whole story.

Regards
Michel

Report message to a moderator

Re: SYS objects and Catproc Invalid [message #500785 is a reply to message #500779] Wed, 23 March 2011 09:08 Go to previous messageGo to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
Hi Frank,

Pls see the below output.
SQL> @?/rdbms/admin/dbmsapin.sql

Package created.

create public synonym dbms_application_info for sys.dbms_application_info
                      *
ERROR at line 1:
ORA-00955: name is already used by an existing object



Grant succeeded.

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

Package body created.

SQL>

Report message to a moderator

Re: SYS objects and Catproc Invalid [message #500789 is a reply to message #500785] Wed, 23 March 2011 09:20 Go to previous messageGo to next message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
Excellent! Now, please run the following commands from SQL*Plus to test it and post the result:

SQL> exec DBMS_APPLICATION_INFO.SET_MODULE('SQL*plus', 'Testing DBMS_APPLICATION_INFO');

SQL> exec DBMS_OUTPUT.DISABLE;


If you get error SP2-0575, run "set flagger off" and retry the above statements.

Report message to a moderator

Re: SYS objects and Catproc Invalid [message #500794 is a reply to message #500789] Wed, 23 March 2011 10:35 Go to previous messageGo to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
Please see the below output


SQL> exec DBMS_APPLICATION_INFO.SET_MODULE('SQL*plus', 'Testing DBMS_APPLICATION_INFO');
BEGIN DBMS_APPLICATION_INFO.SET_MODULE('SQL*plus', 'Testing DBMS_APPLICATION_INFO'); END;

*
ERROR at line 1:
ORA-04067: not executed, package body "OPS$BECK.DBMS_APPLICATION_INFO" does
not exist
ORA-06508: PL/SQL: could not find program unit being called:
"OPS$BECK.DBMS_APPLICATION_INFO"
ORA-06512: at line 1


SQL> exec DBMS_OUTPUT.DISABLE;

PL/SQL procedure successfully completed.

Report message to a moderator

Re: SYS objects and Catproc Invalid [message #500796 is a reply to message #500794] Wed, 23 March 2011 11:00 Go to previous messageGo to next message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
DBMS_APPLICATION_INFO should be in SYS and not in OPS$BECK. Try this:

exec SYS.DBMS_APPLICATION_INFO.SET_MODULE('SQL*plus', 'Testing DBMS_APPLICATION_INFO');


And even:
DROP PACKAGE OPS$BECK.DBMS_APPLICATION_INFO;
exec DBMS_APPLICATION_INFO.SET_MODULE('SQL*plus', 'Testing DBMS_APPLICATION_INFO');

Report message to a moderator

Re: SYS objects and Catproc Invalid [message #500798 is a reply to message #500634] Wed, 23 March 2011 11:14 Go to previous messageGo to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
Pls see the below


SQL> exec SYS.DBMS_APPLICATION_INFO.SET_MODULE('SQL*plus', 'Testing DBMS_APPLICATION_INFO');

PL/SQL procedure successfully completed.

SQL> DROP PACKAGE OPS$BECK.DBMS_APPLICATION_INFO;;

Package dropped.

SQL> exec DBMS_APPLICATION_INFO.SET_MODULE('SQL*plus', 'Testing DBMS_APPLICATION_INFO');

PL/SQL procedure successfully completed.

SQL>
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
[saixu117]/home/oracle$ sqlplus /

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Mar 24 00:11:30 2011

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

ERROR:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_APPLICATION_INFO.SET_MODULE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


Error accessing package DBMS_APPLICATION_INFO

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

Report message to a moderator

Re: SYS objects and Catproc Invalid [message #500799 is a reply to message #500798] Wed, 23 March 2011 11:25 Go to previous messageGo to next message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
Is that a connection to a different user?
Does it also contain a copy DBMS_APPLICATION_INFO that should be dropped?

Report message to a moderator

Re: SYS objects and Catproc Invalid [message #500800 is a reply to message #500799] Wed, 23 March 2011 11:31 Go to previous messageGo to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
 
/home/oracle$ sqlplus /

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Mar 24 00:27:15 2011

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

ERROR:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_APPLICATION_INFO.SET_MODULE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


Error accessing package DBMS_APPLICATION_INFO

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

SQL> show user
USER is "OPS$ORA10"
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
/home/oracle$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Mar 24 00:27:30 2011

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production

SQL>

Report message to a moderator

Re: SYS objects and Catproc Invalid [message #500801 is a reply to message #500800] Wed, 23 March 2011 11:36 Go to previous messageGo to next message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
Please run:
DROP PACKAGE OPS$ORA10.DBMS_APPLICATION_INFO;
exec DBMS_APPLICATION_INFO.SET_MODULE('SQL*plus', 'Testing DBMS_APPLICATION_INFO');

Report message to a moderator

Re: SYS objects and Catproc Invalid [message #500802 is a reply to message #500801] Wed, 23 March 2011 11:43 Go to previous messageGo to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
 
SQL> DROP PACKAGE OPS$ORA10.DBMS_APPLICATION_INFO;
DROP PACKAGE OPS$ORA10.DBMS_APPLICATION_INFO
*
ERROR at line 1:
ORA-04043: object DBMS_APPLICATION_INFO does not exist

 
SQL> exec DBMS_APPLICATION_INFO.SET_MODULE('SQL*plus', 'Testing DBMS_APPLICATION_INFO');

PL/SQL procedure successfully completed.

SQL>

Report message to a moderator

Re: SYS objects and Catproc Invalid [message #500804 is a reply to message #500802] Wed, 23 March 2011 11:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I bet you still have some wrong synonym.

So, I was right, what you said was NOT ALL what you did. Nowhere you mentioned you executed the script with another user than SYS.

Regards
Michel

[Updated on: Wed, 23 March 2011 11:56]

Report message to a moderator

Re: SYS objects and Catproc Invalid [message #500805 is a reply to message #500804] Wed, 23 March 2011 12:00 Go to previous messageGo to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
I never executed any script other than SYS user.

Report message to a moderator

Re: SYS objects and Catproc Invalid [message #500806 is a reply to message #500805] Wed, 23 March 2011 12:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So check this:

select owner, object_type, status 
from dba_objects 
where object_name='DBMS_APPLICATION_INFO'
/


Regards
Michel

[Updated on: Wed, 23 March 2011 12:04]

Report message to a moderator

Re: SYS objects and Catproc Invalid [message #500808 is a reply to message #500806] Wed, 23 March 2011 12:09 Go to previous messageGo to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
 
SQL> select owner, object_type, status 
from dba_objects 
where object_name='DBMS_APPLICATION_INFO'  2    3  
  4  /

OWNER                          OBJECT_TYPE         STATUS
------------------------------ ------------------- -------
SYS                            PACKAGE             VALID
SYS                            PACKAGE BODY        VALID
PUBLIC                         SYNONYM             VALID

SQL>

Report message to a moderator

Re: SYS objects and Catproc Invalid [message #500813 is a reply to message #500808] Wed, 23 March 2011 12:30 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
select owner, table_owner, table_name from dba_synonyms where synonym_name = 'DBMS_APPLICATION_INFO';


post results from SQL above

Report message to a moderator

Re: SYS objects and Catproc Invalid [message #500814 is a reply to message #500813] Wed, 23 March 2011 12:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, this was the next step I wanted to ask (after being sure there is a synonym).

Regards
Michel

Report message to a moderator

Re: SYS objects and Catproc Invalid [message #500837 is a reply to message #500814] Wed, 23 March 2011 20:53 Go to previous messageGo to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
 
SQL> select owner, table_owner, table_name from dba_synonyms where synonym_name = 'DBMS_APPLICATION_INFO';

OWNER                          TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------ ------------------------------
PUBLIC                         OPS$BECK		              DBMS_APPLICATION_INFO

Report message to a moderator

Re: SYS objects and Catproc Invalid [message #500838 is a reply to message #500837] Wed, 23 March 2011 21:10 Go to previous messageGo to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
Recreated the public synonym which pointed towards the sys object DBMS_APPLICATION_INFO, and issue seems to be fixed.

Report message to a moderator

Re: SYS objects and Catproc Invalid [message #500839 is a reply to message #500838] Wed, 23 March 2011 21:40 Go to previous messageGo to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
Big Big Thanks to everyone for their efforts in resolving the issue.

Cheers
Bryan

Report message to a moderator

Re: SYS objects and Catproc Invalid [message #500843 is a reply to message #500839] Thu, 24 March 2011 00:29 Go to previous message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just an advice: check ALL your public synonyms.

Regards
Michel

Report message to a moderator

Previous Topic: Unable to start second node in RAC
Next Topic: Introducing McAfee Anti virus on Oracle Database
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 19 09:51:49 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.