Home » RDBMS Server » Server Utilities » Invalid Objects after done import (Oracle 10.2.0.3 Sun OS)
Invalid Objects after done import [message #441110] Thu, 28 January 2010 21:22 Go to next message
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


Re: Invalid Objects after done import [message #441113 is a reply to message #441110] Thu, 28 January 2010 21:34 Go to previous messageGo to next message
ramoradba
Messages: 2456
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
Re: Invalid Objects after done import [message #441115 is a reply to message #441113] Thu, 28 January 2010 21:38 Go to previous messageGo to next message
ramoradba
Messages: 2456
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

[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 Go to previous messageGo to next message
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
Re: Invalid Objects after done import [message #441118 is a reply to message #441115] Thu, 28 January 2010 21:52 Go to previous messageGo to next message
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..
Re: Invalid Objects after done import [message #441119 is a reply to message #441115] Thu, 28 January 2010 21:53 Go to previous messageGo to next message
ramoradba
Messages: 2456
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...

Re: Invalid Objects after done import [message #441132 is a reply to message #441119] Thu, 28 January 2010 23:24 Go to previous messageGo to next message
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
Re: Invalid Objects after done import [message #441134 is a reply to message #441132] Thu, 28 January 2010 23:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Invalid Objects after done import [message #441135 is a reply to message #441132] Thu, 28 January 2010 23:40 Go to previous messageGo to next message
ramoradba
Messages: 2456
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

[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 Go to previous messageGo to next message
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
Re: Invalid Objects after done import [message #441139 is a reply to message #441138] Thu, 28 January 2010 23:58 Go to previous messageGo to next message
ramoradba
Messages: 2456
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
Re: Invalid Objects after done import [message #441143 is a reply to message #441139] Fri, 29 January 2010 00:37 Go to previous messageGo to next message
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..
Re: Invalid Objects after done import [message #441144 is a reply to message #441138] Fri, 29 January 2010 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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
Re: Invalid Objects after done import [message #441152 is a reply to message #441148] Fri, 29 January 2010 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Invalid Objects after done import [message #441154 is a reply to message #441148] Fri, 29 January 2010 01:15 Go to previous message
ramoradba
Messages: 2456
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/

[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:
  


Current Time: Fri Mar 29 04:12:16 CDT 2024