OraFAQ Forum: Server Utilities » Invalid Objects after done import

Home » RDBMS Server » Server Utilities » Invalid Objects after done import (Oracle 10.2.0.3 Sun OS)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Invalid Objects after done import [message #441110]

Thu, 28 January 2010 21:22

madhavamanohar
Messages: 58
Registered: February 2009

Member

Hello friends,

There is a problem with Invalid obejcts.

Plase find the brief abt problem
---------------------------------
I have imported the dump into database, It was completed with warnings.

Then after I could see the aroung 280 invalid objects , I execute the utlrp.sql to recompile the invalid objects , but still there are aroung 40 invalid sysman objects and 2 sys user's views and some other also..

I tyied to complile them by manually but I got warnings.
There are no invalid objects in source database ( From which I took export).

IN ONE ARTICAL I SAW THAT , IF WE EXECUTE THE catproc.sql script again it will complie all the invalid objects.

I executed that catproc.sql ..it was hung in middle of execution.

----------------------------
My doubt is... is there any problem by exectuting the catproc.sql after import has done ?

What is the best way to recompile the INVALID objects.

Please suggest me on this..

Thanks

Manoar

Report message to a moderator

Re: Invalid Objects after done import [message #441113 is a reply to message #441110]

Thu, 28 January 2010 21:34

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

Quote:

I have imported the dump into database, It was completed with warnings.

we don`t have your import log file to see what are errors you got?
More over we don`t know how you imported(using which user? And we don`t know if there are already invalid object before import?

sriram Smile

Smile

Report message to a moderator

Re: Invalid Objects after done import [message #441115 is a reply to message #441113]

Thu, 28 January 2010 21:38

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

Quote:

What is the best way to recompile the INVALID objects.

compile-invalid-objects

Quote:

I tyied to complile them by manually but I got warnings.

what type of warnings?

for Quote:

catproc.sql

It depends on what are invalid objects you have in sys...

sriram Smile

Smile

[Updated on: Thu, 28 January 2010 21:39]

Report message to a moderator

Re: Invalid Objects after done import [message #441117 is a reply to message #441113]

Thu, 28 January 2010 21:47

madhavamanohar
Messages: 58
Registered: February 2009

Member

Hi

By using system user only I imported..

All ARE COMPILATION ERRORS. AND THERE WAS NO INVALID OBJECTS IN THE EXISTING DATABASE.

Thanks

Manohar

Report message to a moderator

Re: Invalid Objects after done import [message #441118 is a reply to message #441115]

Thu, 28 January 2010 21:52

madhavamanohar
Messages: 58
Registered: February 2009

Member

Hi Sriram,

All are compilation errors onaly and for sys user we have only VIEWS which are in INVALID.

My Question is " SHELL I EXECUTE catproc.sql AFTER IMPORT DONE ?"

Please suggest me on this..

Thanks

Mahesh..

Report message to a moderator

Re: Invalid Objects after done import [message #441119 is a reply to message #441115]

Thu, 28 January 2010 21:53

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

ramoradba wrote on Fri, 29 January 2010 09:08

Quote:
I tyied to complile them by manually but I got warnings.

what type of warnings?
It depends on what are the invalid objects you have in sys...

Report message to a moderator

Re: Invalid Objects after done import [message #441132 is a reply to message #441119]

Thu, 28 January 2010 23:24

madhavamanohar
Messages: 58
Registered: February 2009

Member

SQL> ALTER PACKAGE SYSMAN.MGMT_LOCK_UTIL COMPILE;

Warning: Package altered with compilation errors.

getting that type of errors for all objects..

Thanks
Manohar

Report message to a moderator

Re: Invalid Objects after done import [message #441134 is a reply to message #441132]

Thu, 28 January 2010 23:31

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Usage: SHOW ERRORS [{ FUNCTION | PROCEDURE | PACKAGE |
   PACKAGE BODY | TRIGGER | VIEW
   | TYPE | TYPE BODY | DIMENSION
   | JAVA SOURCE | JAVA CLASS } [schema.]name]

Regards
Michel

Report message to a moderator

Re: Invalid Objects after done import [message #441135 is a reply to message #441132]

Thu, 28 January 2010 23:40

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

select owner||','||OBJECT_NAME||','||OBJECT_TYPE||','||STATUS
from dba_objects
where STATUS <> 'VALID';

if any package is invalid compile its body.

alter package <package name> compile body;

any warning...

show err

check for dependent objects also(if they were invalid).
How you performed this full import.

Invalid objects

sriram Smile

Smile

[Updated on: Thu, 28 January 2010 23:47]

Report message to a moderator

Re: Invalid Objects after done import [message #441138 is a reply to message #441113]

Thu, 28 January 2010 23:51

madhavamanohar
Messages: 58
Registered: February 2009

Member

SQL> show error
Errors for PACKAGE BODY SYSMAN.MGMT_LOCK_UTIL:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/14 PLS-00304: cannot compile body of 'MGMT_LOCK_UTIL' without its
specification

1/14 PLS-00905: object SYSMAN.MGMT_LOCK_UTIL is invalid
SQL> alter alter PACKAGE sysman.MGMT_LOCK_UTIL compile;
alter alter PACKAGE sysman.MGMT_LOCK_UTIL compile
*
ERROR at line 1:
ORA-00940: invalid ALTER command

SQL> alter PACKAGE sysman.MGMT_LOCK_UTIL compile;

Warning: Package altered with compilation errors.

SQL> show error
Errors for PACKAGE SYSMAN.MGMT_LOCK_UTIL:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/30 PL/SQL: Declaration ignored
4/49 PLS-00201: identifier 'DBMS_LOCK' must be declared
5/30 PL/SQL: Declaration ignored
5/49 PLS-00201: identifier 'DBMS_LOCK' must be declared
6/30 PL/SQL: Declaration ignored
6/49 PLS-00201: identifier 'DBMS_LOCK' must be declared
7/30 PL/SQL: Declaration ignored
7/49 PLS-00201: identifier 'DBMS_LOCK' must be declared
8/30 PL/SQL: Declaration ignored
8/49 PLS-00201: identifier 'DBMS_LOCK' must be declared
9/30 PL/SQL: Declaration ignored

LINE/COL ERROR
-------- -----------------------------------------------------------------
9/49 PLS-00201: identifier 'DBMS_LOCK' must be declared

Please look at the above eror and please suggest me on this

Report message to a moderator

Re: Invalid Objects after done import [message #441139 is a reply to message #441138]

Thu, 28 January 2010 23:58

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

you did n`t answer My questions?in the previous topic?right?

select owner||','||OBJECT_NAME||','||OBJECT_TYPE||','||STATUS
from dba_objects
where STATUS <> 'VALID';

And you how you executed the scripts(utlrp and utlproc) By connecting of which user?it should be sys.
and By which user you are executing this alter commands?

sriram Smile

Smile

Report message to a moderator

Re: Invalid Objects after done import [message #441143 is a reply to message #441139]

Fri, 29 January 2010 00:37

madhavamanohar
Messages: 58
Registered: February 2009

Member

SQL> select OBJECT_NAME,OWNER,OBJECT_TYPE from dba_objects where STATUS='INVALID';

OBJECT_NAME OWNER OBJECT_TYPE
------------------------------ ------------------------------ -------------------
ALL_USERS KI VIEW
MGMT_LOCK_UTIL SYSMAN PACKAGE
EMD_LOADER SYSMAN PACKAGE BODY
MGMT_JOB_ENGINE SYSMAN PACKAGE BODY
MGMT_BLACKOUT_ENGINE SYSMAN PACKAGE BODY
MGMT_JOBS SYSMAN PACKAGE BODY
MGMT_USER SYSMAN PACKAGE BODY
MGMT_LOCK_UTIL SYSMAN PACKAGE BODY
STATSPACK PERFSTAT PACKAGE BODY

9 rows selected.

SQL> alter package EMD_LOADER compile body;
alter package EMD_LOADER compile body
*
ERROR at line 1:
ORA-04043: object EMD_LOADER does not exist

SQL> alter package sysman.EMD_LOADER compile body;

Warning: Package Body altered with compilation errors.

SQL> show error
Errors for PACKAGE BODY SYSMAN.EMD_LOADER:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1360/3 PL/SQL: Statement ignored
1360/3 PLS-00201: identifier 'DBMS_REDEFINITION' must be declared
1366/3 PL/SQL: Statement ignored
1366/3 PLS-00201: identifier 'DBMS_REDEFINITION' must be declared
1449/3 PL/SQL: Statement ignored
1449/3 PLS-00201: identifier 'DBMS_REDEFINITION' must be declared
1455/3 PL/SQL: Statement ignored
1455/3 PLS-00201: identifier 'DBMS_REDEFINITION' must be declared
1540/3 PL/SQL: Statement ignored
1540/3 PLS-00201: identifier 'DBMS_REDEFINITION' must be declared
1546/3 PL/SQL: Statement ignored

LINE/COL ERROR
-------- -----------------------------------------------------------------
1546/3 PLS-00201: identifier 'DBMS_REDEFINITION' must be declared
3110/5 PL/SQL: Statement ignored
3111/11 PLS-00905: object SYSMAN.MGMT_LOCK_UTIL is invalid
3124/5 PL/SQL: Statement ignored
3125/11 PLS-00905: object SYSMAN.MGMT_LOCK_UTIL is invalid

By Connecteing to SYS user I am executing ..

But I did not executed utlproc ..shall I execute it..?

Thanks

Manohar..

Report message to a moderator

Re: Invalid Objects after done import [message #441144 is a reply to message #441138]

Fri, 29 January 2010 00:38

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

8/49 PLS-00201: identifier 'DBMS_LOCK' must be declared
9/30 PL/SQL: Declaration ignored

SYSMAN has not the privilege to execute this package.
If the installation was correct, SYSMAN would have this privilege.

Regards
Michel

[Updated on: Fri, 29 January 2010 00:50]

Report message to a moderator

Re: Invalid Objects after done import [message #441148 is a reply to message #441144]

Fri, 29 January 2010 00:45

madhavamanohar
Messages: 58
Registered: February 2009

Member

Hi Michel ,

Thanks fot the replay.

Is there an ywork aroung for this...

Please suggest me ..Istallation has done perfectly..

Thanks

Manohar

Report message to a moderator

Re: Invalid Objects after done import [message #441152 is a reply to message #441148]

Fri, 29 January 2010 00:56

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

(Re)Install OEM on target then you have a correct SYSMAN schema.

Something like:
emca dbcontrol db -repos drop
emca dbcontrol db -repos create
But I'm not an expert in EM which I don't use.

Regards
Michel

Report message to a moderator

Re: Invalid Objects after done import [message #441154 is a reply to message #441148]

Fri, 29 January 2010 01:15

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

Quote:

SQL> select OBJECT_NAME,OWNER,OBJECT_TYPE from dba_objects where STATUS='INVALID';

OBJECT_NAME OWNER OBJECT_TYPE
------------------------------ ------------------------------ -------------------
ALL_USERS KI VIEW

Just curious Who`s this owner KI here?

What ever the object invalid you have are dependent on each with one another.And even whats your source version?

Try with Michel solution...But normally while trying this we will drop and recrete the roles and users not the Packages...So i dont think it`s a solution.
sriram /forum/fa/7379/0/

/forum/fa/7379/0/

Attachment: self-distruction.gif
(Size: 20.35KB, Downloaded 7044 times)

[Updated on: Fri, 29 January 2010 01:48]

Report message to a moderator

Previous Topic:	Sql Loader Starting Problem
Next Topic:	import of indexes using impdp

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jan 14 07:18:55 CST 2025