Home » RDBMS Server » Server Utilities » ORA-01436 during export on Oracle 8i (Oracle 8.1.7.4.1, Windows server 2003 R2)
ORA-01436 during export on Oracle 8i [message #603194] Thu, 12 December 2013 02:28 Go to next message
oraclebabe
Messages: 7
Registered: December 2013
Junior Member
Hello,

I am trying to do a schema export on 8i (exp system/***@dbname file=xyz.dmp log=xyz.log owner=xyz), but the export ends with the following error:

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

This error occurs while exporting the views. The tables are exported successfully.
On MOS, I found DocID 6936881.8, which states that this is caused by 2 views with foreign keys that cross-reference each other and the solution is to disable those contraints.

First of all, is it even possible to have primary and foreign keys directly on views???

And how can I find the views with foreign keys that cross-reference each other? Does anyone have some sort of query for this?

Could it be that the foreign keys that cross-reference each other are actually on the tables? Coz from dba_contraints, I haven't seen any views with pk's and fk's.
In this case, I would expect the export of the tables to give the error message, not the export of the views...

Is there another way to solve this export problem?

Thanks in advance.
Re: ORA-01436 during export on Oracle 8i [message #603203 is a reply to message #603194] Thu, 12 December 2013 02:53 Go to previous messageGo to next message
ThomasG
Messages: 3092
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
What I would try is to re-compile the views and see if the error shows up during that.

You can run
SELECT 'alter view ' || view_name || ' compile;' FROM user_views;


as user xyz and then run the resulting script to compile them all.
Re: ORA-01436 during export on Oracle 8i [message #603205 is a reply to message #603203] Thu, 12 December 2013 02:59 Go to previous messageGo to next message
oraclebabe
Messages: 7
Registered: December 2013
Junior Member
If the compilation does give an error, then certain views will become invalid and I probably won't be able to get them valid again...right?
Re: ORA-01436 during export on Oracle 8i [message #603208 is a reply to message #603205] Thu, 12 December 2013 03:07 Go to previous messageGo to next message
ThomasG
Messages: 3092
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
That might happen, yes.
Re: ORA-01436 during export on Oracle 8i [message #603211 is a reply to message #603208] Thu, 12 December 2013 03:12 Go to previous message
oraclebabe
Messages: 7
Registered: December 2013
Junior Member
It's a production db, so I don't really want to take that chance. Is there a query I can use just to find the views (or tables) with the cross-referenced foreign keys?
Previous Topic: importing tables....
Next Topic: IMPDP in Oracle 11g
Goto Forum:
  


Current Time: Fri Aug 01 21:00:49 CDT 2014

Total time taken to generate the page: 0.08311 seconds