compile_schema eror

[utkinpol]

Env: Oracle 8.1.6+context, Sparc Solaris.
No one schema could be compiled... I tried it from sqlplus, from svrmgrl - result is the same:

SQL> exec dbms_utility.compile_schema('SYS');
BEGIN dbms_utility.compile_schema('SYS'); END;

*
ERROR at line 1:
ORA-01436: CONNECT BY loop in user data
ORA-06512: at "SYS.DBMS_UTILITY", line 195
ORA-06512: at line 1

All views are VALID. Some packages are INVALID, I`m trying to compile them manually, but it could take a lot of time...
Can anyone tell me - what could be a reason of that error?

[shwa]

Just a guess but dbms_utility belongs to sys - it might have some trouble trying to compile itself during execution. You might want to try something like:

select 'alter '||
DECODE(Object_Type,
'PACKAGE BODY',
'PACKAGE',Object_Type)||
' '||Owner ||'.'|| Object_Name ||
' compile '||
DECODE(Object_Type,
'PACKAGE BODY','BODY',null)||';'
from DBA_OBJECTS
where Object_Type in
('PROCEDURE','PACKAGE','FUNCTION','TRIGGER',
'VIEW','PACKAGE BODY')
and Owner = 'SYS'
and Status != 'VALID'

[sambavan]

I'm not sure whether you could do a compile on the SYS, because it is mostly a one time creation.

Sam

[utkinpol]

[QUOTE][i]Originally posted by sambavan [/i]
[B]I'm not sure whether you could do a compile on the SYS, because it is mostly a one time creation.

There ara a _lot_of_ invalid packages in SYS schema - mostly packages. I don`t know why - that were done before I came here...
That`s the problem...
But I don`t understand, that could be wrong with dbms_utility,
It do not want compile no one schema - with the same error message...

[utkinpol]

[QUOTE][i]Originally posted by shwa [/i]
[B]Just a guess but dbms_utility belongs to sys - it might have some trouble trying to compile itself during execution. You might want to try something like:

Thanks - but I have no problems with manually compile...
I did it - SYS and SYSTEM objects are all compiled.
but this
SQL> exec dbms_utility.compile_schema('PGWIZ');
BEGIN dbms_utility.compile_schema('PGWIZ'); END;

*
ERROR at line 1:
ORA-01436: CONNECT BY loop in user data
ORA-06512: at "SYS.DBMS_UTILITY", line 195
ORA-06512: at line 1

doesn`t work for all schemes - PGWIZ, PGWIZB, SYS,SYSTEM .. And I afraid to run catproc an etc. - it`s a production system, 24x7... :(

[sambavan]

Catproc would not cause any damage I beleive. It would just go ahead and rebuilt the packages. If you are running any stats pack then you might loose the data. for the betterness, if you have oracle support create a tar and they would be able to give you the asurity on it. I have run catproc every time I put a patch. It would just go ahead and rebuild all the procedures. By building them individually you might cause some inconsistencies.

Sam

[utkinpol]

[QUOTE][i]Originally posted by sambavan [/i]
[B]Catproc would not cause any damage I beleive. It would just go ahead and rebuilt the packages. If you are running any stats pack then you might loose the data. for the betterness, if you have oracle support create a tar and they would be able to give you the asurity on it. I have run catproc every time I put a patch. It would just go ahead and rebuild all the procedures. By building them individually you might cause some inconsistencies.


It becames interesting... Even after catproc I still cannot compile that:
alter PACKAGE SYS.DBMSOBJG compile BODY;
alter PACKAGE SYS.DBMS_ASYNCRPC_PUSH compile BODY;
alter PACKAGE SYS.DBMS_DEFER_INTERNAL_SYS compile BODY;
alter PACKAGE SYS.DBMS_DEFER_SYS_PART1 compile BODY;
alter FUNCTION SYS.NameFromLastDDL compile ;

Strange...

[sambavan]

Now run the catrep.sql to compile the oracle replication packages if you have the oracle replication installed.

In order to execute those pacakges you might have to shut down the instance. and have to try reexecuting the catproc through the svrmgrl.

Sam

[Prof]

I think that need verify database file ( dbv ). And you compile schema as user sys ( DBA privileges )?