FROM Metalink:
Problem Description
-------------------
When using DBMS_UTILITY.COMPILE_SCHEMA, you receive the
following error:
ORA-01436: CONNECT BY loop in user data
Solution Description
--------------------
There can also be other reasons for using another way of compiling,
for example, you may wish to compile objects for more than one schema.
Use this statement:
SELECT 'alter ' || object_type || ' ' || object_name || ' compile;'
from USER_OBJECTS
where status!='VALID'
and object_type not in ('PACKAGE BODY', 'INDEX', 'SYNONYM', 'TABLE', 'DATABASE LINK');
Spool the results to the script file, then run it manually. If it is necessary to compile
objects from more than one schema, change the USER_OBJECTS to ALL_OBJECTS or DBA_OBJECTS. It is necessary to add column OWNER to the SELECT command:
SELECT 'alter ' || object_type || ' ' || owner || '.' || object_name || ' compile;'
from ALL_OBJECTS
where status!='VALID'
and object_type not in ('PACKAGE BODY', 'INDEX', 'SYNONYM', 'TABLE', 'DATABASE LINK');
Note that object type PACKAGE BODY needs to be compiled individually.
Explanation
-----------
This is due to [BUG:895238].
Compiling objects one by one takes a lot of time.
References
----------
[BUG:895238] DBMS_UTILITY.COMPILE_SCHEMA can fail with ORA-1436 in 8.1
Additional Search Words
-----------------------
compile, user_objects