The mailing list for listmasters using Sympa

["Jahnke-Zumbusch, Dirk" <address@concealed>]

Hi all,

I still hit the following problem with Oracle 10gR2 as our database
(SYMPA 5.1.2):

wwsympa[26968]: 
 Unable to prepare SQL statement : 
 ORA-00604: error occurred at recursive SQL level 1 ORA-01000: maximum
open cursors exceeded 
 (DBD ERROR: error possibly near <*> indicator at char 540 in 
  'SELECT user_admin "email", comment_admin "gecos", reception_admin
"reception", .........

So I had a look which statements are holding cursors and never
give them back:

select count(sql_text),sql_text 
 from gv$open_cursor 
 where user_name = 'SYMPA' 
 group by sql_text 
 order by 1 desc;

resulting in:

9180    SELECT user_admin "email", comment_admin "gecos", reception_
153     SELECT count(*) FROM subscriber_table WHERE list_subscriber 
139     SELECT user_subscriber "email", comment_subscriber "gecos", 
49      SELECT count(*) FROM subscriber_table WHERE (list_subscriber
9       SELECT email_user "email", gecos_user "gecos", password_user
7       select SYSDATE from DUAL /* ping */
...

We have ~1,100 lists and about 300 of them are including subscriber
addresses from "include_sql_query" and some fewer also "owner_include"
by parametrized SQL-queries.

The very high number of oipen cursors in the first row of the
open cursor statistic points to a problem with owner_include,
as *_admin columns are addressed.

Does anybody notice the same problem?

Where is the difference in handling "owner_include"s and
"include_sql_query" ?  There are by 10 more open cursors
for the owner_include than for the include_sql_queries.

Best regards

--
Dirk Jahnke-Zumbusch              Deutsches Elektronen-Synchrotron DESY
IT Information Fabrics              Member of the Helmholtz Association
D-22603 Hamburg                        Notkestrasse 85  / 22607 Hamburg
T: +49-40-899.81760   F: +49-40-899.41760  address@concealed