Home » SQL & PL/SQL » SQL & PL/SQL » Getting lot of invalid object (10.2.0.1 linux)
Getting lot of invalid object [message #342850] Mon, 25 August 2008 05:54 Go to next message
ranvijaidba
Messages: 71
Registered: May 2008
Location: Bangalore
Member
Database version-10.2.0.1
O.S Linux

I am getting invalid package in system schema and one other schema.

SQL> @find_invalid.sql

OWNER OBJECT_TYPE OBJECT_NAME STATUS
---------- ------------------- -------------------- -------
HIPERGATE_ TRIGGER K_TR_INS_COURSE_BOOK INVALID
DEV ING

HIPERGATE_ TRIGGER K_TR_UPD_COURSE_BOOK INVALID
DEV ING

SYS PACKAGE BODY DBMS_REPCAT_AUTH INVALID
SYSTEM PACKAGE BODY INITJVMAUX INVALID
SYSTEM PACKAGE BODY JVMFCB INVALID
SYSTEM PACKAGE BODY RMJVM INVALID

when i am trying to compile the package body INITJVMAUX with Oracle sql developer it is showing following error

PACKAGE BODY SYSTEM.INITJVMAUX@server_42_crcl_system
Error(45,5): PL/SQL: SQL Statement ignored
Error(45,28): PL/SQL: ORA-00942: table or view does not exist
Error(52,5): PL/SQL: SQL Statement ignored
Error(52,57): PL/SQL: ORA-00942: table or view does not exist
Error(62,9): PL/SQL: SQL Statement ignored
Error(63,14): PL/SQL: ORA-00942: table or view does not exist
Error(120,1): PLS-00201: identifier 'DBMS_REGISTRY' must be declared
Error(120,1): PL/SQL: Statement ignored
Error(127,1): PLS-00201: identifier 'DBMS_REGISTRY' must be declared
Error(127,1): PL/SQL: Statement ignored
Error(135,3): PLS-00201: identifier 'DBMS_REGISTRY' must be declared
Error(135,3): PL/SQL: Statement ignored
Error(137,3): PLS-00201: identifier 'DBMS_REGISTRY' must be declared
Error(137,3): PL/SQL: Statement ignored
Error(141,8): PL/SQL: Item ignored
Error(141,24): PLS-00201: identifier 'DBMS_REGISTRY' must be declared
Error(143,3): PL/SQL: Statement ignored
Error(143,6): PLS-00320: the declaration of the type of this expression is incomplete or malformed
Error(144,3): PL/SQL: Statement ignored
Error(144,10): PLS-00320: the declaration of the type of this expression is incomplete or malformed
Warning(1,1): Only first 20 issues are reported


The first two error Error(45,5),Error(45,28).I am getting at this code.showing that v$parameter is not exist in system schema.

begin
select num into x from v$parameter
where name='undo_management' and value='AUTO';
return;
exception when no_data_found then null;
end;

The Error (52,5) and Error(52,57) getting at this line

select segment_name into rollback_segment_name from dba_rollback_segs

The Error (62,9),(63,14) getting at this line:

select segment_name into deallocate_rollback_name


I checked dbms_registry package its owner is sys.

Note:I have attached the package body.Please check it.

Thanks in advance.
Ranvijai

[Updated on: Mon, 25 August 2008 06:17]

Report message to a moderator

Re: Getting lot of invalid object [message #342868 is a reply to message #342850] Mon, 25 August 2008 07:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
SYSTEM PACKAGE BODY INITJVMAUX INVALID
SYSTEM PACKAGE BODY JVMFCB INVALID
SYSTEM PACKAGE BODY RMJVM INVALID

These should be in SYS and not SYSTEM schema.
Your installation is wrong.

Regards
Michel
Re: Getting lot of invalid object [message #342870 is a reply to message #342868] Mon, 25 August 2008 07:19 Go to previous messageGo to next message
ranvijaidba
Messages: 71
Registered: May 2008
Location: Bangalore
Member
The installation is done by another DBA.I recently join.So there is any solution to overcome this issue Or we have to do fresh Installation.
Re: Getting lot of invalid object [message #342881 is a reply to message #342870] Mon, 25 August 2008 07:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is only safe way I see is to recreate the database.

Regards
Michel
Re: Getting lot of invalid object [message #342898 is a reply to message #342881] Mon, 25 August 2008 08:09 Go to previous messageGo to next message
ranvijaidba
Messages: 71
Registered: May 2008
Location: Bangalore
Member
sorry if this qestion is useless.It may be possible to export this invalid object from system to sys schema?.

I thinks DBMS_REGISTRY package is created by catproc.sql.Correct me if i am wrong.So it may be possible that this script is run under wrong user.because most of the error relating to DBMS_REGISTRY.
Re: Getting lot of invalid object [message #342941 is a reply to message #342898] Mon, 25 August 2008 10:39 Go to previous message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can try what you want but the ONLY safe way is to recreate the database.

Regards
Michel
Previous Topic: Either Joins1 or join2 but not both
Next Topic: getting counts of multiple tables with one SQL stmt
Goto Forum:
  


Current Time: Sat Nov 02 13:38:00 CDT 2024