Re: EXP INCTYPE problem in ORACLE7

In a previous article, jl34778_at_corp02.d51.lilly.com () says:

>
> $ EXP / FILE=FULL_EXPORT.DMP FULL=Y INCTYPE=COMPLETE
>
>I get the following error at the end of the export.
>EXP-00008: ORACLE error 1 encountered
>ORA-00001: unique constraint (SYS.I_INCEXP) violated
>
 

  You mean in 7.0.13 they STILL haven't fixed this.  

I've had the same problem since 7.0.11 on our Sun network. It took me several days of poking around to find the problem. You're gonna love this one...  

   In Oracle7, when you create a view "WITH CHECK OPTION" a "CHECK" constraint is created in the data dictionary. When, for whatever reason, one of these views is recompiled (explicitly by ALTER VIEW v COMPILE, or automatically by the DBMS - say if the base table is changed) the constraint is dropped and then re-created. Herein lies the problem.  

   Said constraint is represented by one row in each of the SYS.CON$ and SYS.CDEF$ tables. When the constraint is dropped, the row in CON$ is deleted but the row in CDEF$ isn't (but should be). What you end up with is garbage rows in CDEF$ every time you recompile the view.  

   Now the views used by the incremental export perform an outer join between CON$ and CDEF$ and so exp is trying to export the constraint more than once...hence the unique constraint violation.  

   If you haven't re-built your base yet, try the following query to see if this is your problem (I don't have my "at work" system handy so apologies if some of the column names are off)...  

   SELECT COUNT(*) FROM SYS.CDEF$
      WHERE CON# NOT IN (SELECT CON# FROM SYS.CON$);      If you get a result > 0 and are not too sqeuamish about mucking around in these tables, you can correct the corruption with  

   DELETE SYS.CDEF$ WHERE CON# NOT IN (SELECT CON# FROM SYS.CON$);      I actually have this done automatically just before every export with no ill effects (yet) except for...

   WARNING...WARNING...WARNING      Don't get the CON$ and CDEF$ tables mixed up or BAD things will happen (Speaking from experience... You will delete the _NEXT_CONSTRAINT row from CON$ and NOTHING WILL WORK).      In fact, since I'm doing all of this from memory... please double check everything I've written!!!  

   Hope this helps...    

• Doug Harris DBA System Development Division Statistics Canada --
• Doug