SYS objects and Catproc Invalid [message #500634] |
Tue, 22 March 2011 09:18 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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.
|
|
|
|
Re: SYS objects and Catproc Invalid [message #500637 is a reply to message #500636] |
Tue, 22 March 2011 09:46 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ThomasG
Messages: 3212 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:
[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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
Re: SYS objects and Catproc Invalid [message #500642 is a reply to message #500641] |
Tue, 22 March 2011 11:05 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
Re: SYS objects and Catproc Invalid [message #500647 is a reply to message #500642] |
Tue, 22 March 2011 11:14 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68676 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
|
|
|
Re: SYS objects and Catproc Invalid [message #500656 is a reply to message #500634] |
Tue, 22 March 2011 12:02 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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>
|
|
|
|
|
|
|
Re: SYS objects and Catproc Invalid [message #500759 is a reply to message #500720] |
Wed, 23 March 2011 07:52 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
Re: SYS objects and Catproc Invalid [message #500767 is a reply to message #500759] |
Wed, 23 March 2011 08:00 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68676 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
Re: SYS objects and Catproc Invalid [message #500779 is a reply to message #500778] |
Wed, 23 March 2011 08:26 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Frank Naude
Messages: 4580 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.
|
|
|
|
Re: SYS objects and Catproc Invalid [message #500785 is a reply to message #500779] |
Wed, 23 March 2011 09:08 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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>
|
|
|
Re: SYS objects and Catproc Invalid [message #500789 is a reply to message #500785] |
Wed, 23 March 2011 09:20 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Frank Naude
Messages: 4580 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.
|
|
|
Re: SYS objects and Catproc Invalid [message #500794 is a reply to message #500789] |
Wed, 23 March 2011 10:35 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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.
|
|
|
Re: SYS objects and Catproc Invalid [message #500796 is a reply to message #500794] |
Wed, 23 March 2011 11:00 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Frank Naude
Messages: 4580 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');
|
|
|
Re: SYS objects and Catproc Invalid [message #500798 is a reply to message #500634] |
Wed, 23 March 2011 11:14 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
Re: SYS objects and Catproc Invalid [message #500800 is a reply to message #500799] |
Wed, 23 March 2011 11:31 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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>
|
|
|
|
Re: SYS objects and Catproc Invalid [message #500802 is a reply to message #500801] |
Wed, 23 March 2011 11:43 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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>
|
|
|
|
|
|
Re: SYS objects and Catproc Invalid [message #500808 is a reply to message #500806] |
Wed, 23 March 2011 12:09 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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>
|
|
|
|
|
Re: SYS objects and Catproc Invalid [message #500837 is a reply to message #500814] |
Wed, 23 March 2011 20:53 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
|
|