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