ORA-01436: CONNECT BY loop in user data

We have been continuously getting the following error [ORA-01436: CONNECT BY loop in user data] whenever we run a refresh via a refresh group with more than 1 mview. This is happening on several different schemas.

The data refreshes fine as individual refreshes (COMPLETE)

Full Error

ORA-12012: error on auto execute of job 5839709
ORA-01436: CONNECT BY loop in user data
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2545
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2751
ORA-06512: at "SYS.DBMS_IREFRESH", line 685
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1

The Following has been attempted:

Refresh of mviews manually – WORKS

Refresh of a refresh group with only 1 mview – WORKS

Refresh of a refresh group with more than 1 mview – ERRORs

Workaround

This looks to be caused by some sort of dependency loop with object_id’s for mviews. use the SQL below to locate offending objects which have a looping dependency

SELECT    p_obj# p, CONNECT_BY_ISCYCLE "Cycle",
        CONCAT(SYS_CONNECT_BY_PATH(d_obj#,'/'), '/') x 
FROM ( SELECT p_obj#, d_obj# 
FROM sys.dependency$, sys.obj$ p, sys.obj$ d 
WHERE p_obj# = p.obj# AND d_obj# = d.obj# AND p.type# = 42 AND d.type# = 42) d
CONNECT BY NOCYCLE PRIOR d.p_obj# = d.d_obj# ;

Locate the row where Cycle is 1, in that row we will get the object ids of the involved objects (P column is parent object, X column is the chain of dependent objects)

Then find the offending objects by running the following:

select * from dba_objects
where object_id in (17679693, 17679697);

Where object_ID’s are where Cycle was equal to 1 in the previous query.

Drop and Recreate any mviews that are returned. This will alleviate the problem. This can be confirmed by running the following and no rows will be returned.

SELECT    p_obj# p, CONNECT_BY_ISCYCLE "Cycle",
        CONCAT(SYS_CONNECT_BY_PATH(d_obj#,'/'), '/') x 
FROM ( SELECT p_obj#, d_obj# 
FROM sys.dependency$, sys.obj$ p, sys.obj$ d 
WHERE p_obj# = p.obj# AND d_obj# = d.obj# AND p.type# = 42 AND d.type# = 42) d
WHERE  CONNECT_BY_ISCYCLE = 1
CONNECT BY NOCYCLE PRIOR d.p_obj# = d.d_obj# ;

Further Info

ORA-01436 While Refreshing a Refresh Group [ID 793843.1]

OERR: ORA 1436 CONNECT BY loop in user data [ID 18840.1]

Advertisements

One Response to ORA-01436: CONNECT BY loop in user data

  1. KuKu says:

    Well, you have saved my life 🙂 thx

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: