FW: "ORA-01436: CONNECT BY loop in user data" during export.

From: <babu.nagarajan_at_mail.iflexsolutions.com>
Date: Tue, 8 Aug 2000 16:49:28 +0530
Message-Id: <10583.114049@fatcity.com>

you can run this script and depending on the number returned you might have a corrption in sys.dependency$ view in d_obj# = <number returned>. you may need to delete the row.

take care and call oracle support

babu

the script below can be used to detect 'loops' in DEPENDENCY$.

cut here
rem rem Usage: execute cycle.deep or exec cycle.deep(true) rem drop table graph; create table graph as select * from sys.dependency$; set serveroutput on size 200000

create or replace package CYCLE as
procedure DEEP(trc boolean default false); type CharType is table of char(5) index by binary_integer; type NumType is table of binary_integer index by binary_integer; end CYCLE;
/
create or replace package body CYCLE as
procedure DEEPER(n IN binary_integer);
color CharType;
parent NumType;
bfr NumType;
aft NumType;
stamp binary_integer :=0;
trc1 boolean := false;
cursor c1 is
   select d_obj# obj, d_timestamp timestamp from graph    order by 2 desc;
--

procedure DEEP(trc boolean default false) is u binary_integer;
begin
trc1:=trc;
stamp:=0;
for urec in c1 loop
   u:=urec.obj;
   color(u):='WHITE';
   parent(u):=NULL;
end loop;
for urec in c1 loop
   u:=urec.obj;
   if color(u)='WHITE' then
     deeper(u);
   end if;
end loop;
end;
procedure DEEPER(n IN binary_integer) is v binary_integer;
begin
if trc1 then
   dbms_output.put_line('path: '||to_char(n)); end if;
color(n):='GRAAY';
stamp:=stamp+1;
bfr(n):=stamp;
for vrec in (select d_obj# adj from graph where p_obj#=n) loop    v:=vrec.adj;

   if color(v)='WHITE' then
     parent(v):=n;
     deeper(v);

end if;
if color(v)='GRAAY' then --back edge

     dbms_output.put_line('--------------------------');
     dbms_output.put_line('Cycle Detected at: '||to_char(v));
     dbms_output.put_line('--------------------------');

end if;
end loop;
color(n):='BLACK';
stamp:=stamp+1;
aft(n):=stamp;
end;
end CYCLE;
/
---------------------- cut here

Example Output (testing):

SQL> desc graph

 Name                            Null?    Type
 ------------------------------- -------- ----
 D_OBJ#                          NOT NULL NUMBER
 D_TIMESTAMP                     NOT NULL DATE
 ORDER#                          NOT NULL NUMBER
 P_OBJ#                          NOT NULL NUMBER
 P_TIMESTAMP                     NOT NULL DATE
 D_OWNER#                        NOT NULL NUMBER

SQL> exec cycle.deep

Cycle Detected at: -16271

SQL> exec cycle.deep(true)

path: -16271
path: 347
path: 366
path: 383
path: 389
path: 399
path: 405
path: 409
path: 431
path: 1464


--------------------------

Cycle Detected at: -16271

Babu Nagarajan
i-Flex, Bangalore
#91-80-5597600-3381

-----Original Message-----

From: Grabowy, Chris [mailto:cgrabowy_at_fcg.com] Sent: Monday, August 07, 2000 11:16 PM
To: Fatcity's Oracle ListServ; LazyDBA's Oracle ListServ Subject: "ORA-01436: CONNECT BY loop in user data" during export.

I receive the following error when I export a schema on a Oracle 8.1.6 database.

EXP-00056: ORACLE error 1436 encountered 
ORA-01436: CONNECT BY loop in user data 
EXP-00000: Export terminated unsuccessfully

One of the schemas on that database originally came from an Oracle 7.3.4 database. I have opened up "yet another unresponsive" iTAR. There is a known bug involving views corrupting the dictionary, causing exports to fail. OWS responded with a series of scripts to identify and cleanup the dictionary, but the "identify" script also returns ORA-01436. Which means the "cleanup" script has nothing to cleanup.

So I was wondering if anyone else has encountered this problem, and what has been done??

TIA Chris Grabowy
"May Oracle be with you...always"

If you're bored, then visit the list's website: http://www.lazydba.com (updated daily) Received on Tue Aug 08 2000 - 06:19:28 CDT