ORA-01436: CONNECT BY loop in user data

[simply_dba]

if you are in 10g try this:

Select 'DWPROD', owner, object_type, object_name, object_id, status
    from sys.ALL_OBJECTS
    where object_name like upper('%ORDER') and object_type != 'SYNONYM'
    union
    Select 'DWPROD', owner, object_type, object_name, object_id, status
    from sys.ALL_OBJECTS
    where object_id in (Select object_id
                        from public_dependency
                        connect by nocycle prior object_id = referenced_object_id
                       start with referenced_object_id in
                                                        (Select object_id
                                                         from sys.ALL_OBJECTS
                                                         where upper(object_name) like upper('%ORDER')));