still don't understand why export gets ORA-01000
Beth, September 25, 2003 - 7:41 pm UTC
Hi Tom,
I have been receiving the same ORA-00604, ORA-01000 stack, as in this question, during a nightly export.
I have been reading all about open cursors on your site and still have a few questions.
1) Why exactly does the export get max open cursors exceeded? Does the exp utility open one cursor per table? I can't tell if there is a correlation, because I don't know how far the export gets before it fails. We have open_cursors = 220, and there are 454 tables in the entire database. (This is a full export that it is failing on.)
2) I understand you stress to set open_cursors to at least 500, preferably 1000, because there is no harm done in allocating an array of 1000 pointers (not yet eating up memory) and it solves the bigger pain-in-the-butt problem of having to repeatedly bounce the database to fix this error, if you only increment it by a small amount each time, right?
a) You also said, if there is a memory leak in some code, you will find it then (how? I am not exactly sure, but if it can suck up the bulk of 1000 cursors I guess you can find out what in v$open_cursor and find out who in v$sesstat...) Regardless, if this bad code got implemented in a prod db, wouldn't it then start to allocate memory for those 1000 array pointers, and depending on the amt of memory available for Oracle on your server, couldn't that be a bad thing, to let it run wild?
b) or do you just have to keep in mind the 'OS limit for open cursors'? Is there one? This was a phrase from some TAR. If it exists, how do you know what that would be? The Ref manual says 4294967295 is the max, but that is number of cursors...would I need to find out the size of each cursor and compare to RAM on the box?
3) Lastly, or perhaps repeating myself, if there is a threshold of max cursors being set too high....how do you proactively figure it out?
Thanks as always!
September 25, 2003 - 11:38 pm UTC
what version of the database/exp are we talking about here.
version
Beth, September 26, 2003 - 11:55 am UTC
8.1.7.0.0
version
Beth, September 26, 2003 - 11:57 am UTC
817 for both the database *and* the exp utility
September 26, 2003 - 2:47 pm UTC
contact support and reference:
Hdr: 2194182 8.1.7.2 RDBMS 8.1.7.2 SPACE PRODID-5 PORTID-59 ORA-604
Abstract: ORA-604 FOLLOWED BY ORA-1000 DURING SELECTS ON THE DD VIEWS
patched in 8173 and 8174
Thanks!
Beth, September 26, 2003 - 2:58 pm UTC
Wow, thanks! Sorry to bother you with a bug. I inherited the unpatched db, and know i need to fix it, but didn't think this was a bug!
But anyway, for my understanding...what about 2a, b?
September 26, 2003 - 8:06 pm UTC
2a) you would find that in TEST would you not? if you say no, then prod is error prone anyway...
2b) it is a function of RAM available
exp is using 600 cursors
Manish Upadhyay, January 28, 2005 - 10:45 am UTC
Hi ,
My database 9.2.0.6 on AIX with open_cursors=1000. I am running full database export on this database and at the same time I query to see how many cursors were opened by exp session.
select sid, count(*) from V$OPEN_CURSOR group by sid;
SID COUNT(*)
---------- ----------
10 594
11 1
15 8
22 1
25 4
28 10
30 22
Sid 10 is the export session.
This database has XML database too. ( if this has any effect)
Do you think that exp might be leaking cursors? or just normal for exp to use that many cursors.?
January 28, 2005 - 2:31 pm UTC
cursors will be cached by plsql automagically and for good purpose.
No probablems here.
when using Export i'm getting these error and can't do the export
Rahul Chaudhari, February 15, 2005 - 11:47 pm UTC
EXP-00008: ORACLE error 604 encountered
ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 8
ORA-00904: invalid column name
EXP-00000: Export terminated unsuccessfully
February 16, 2005 - 7:55 am UTC
umm, sort of falls into "insufficient data"
but why didn't you contact support?
EXP-00008: ORACLE error 604 encountered
Rahul Chaudhari, February 20, 2005 - 10:45 pm UTC
While Exporting any schema through one of service name on one of our server the following were the errors
EXP-00008: ORACLE error 604 encountered
ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 8
ORA-00904: invalid column name
EXP-00000: Export terminated unsuccessfully
Solution :--
Solved it by running CATALOG.SQL as it was found that incompatibility with the catalog.
Thankx a lot Tom...
Rahul chaudhari