Re: Failure to extend rollback segment N because of 1000 condition

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Sat, 15 May 2010 05:42:46 +0000 (UTC)
Message-ID: <hslc8m$bvk$1_at_news.eternal-september.org>



On Fri, 14 May 2010 22:22:15 -0700, ca111026 wrote:

> Thanks. Yes, I checked V$SESSTAT for STATISTIC# = 3 ('open cursors
> current'). In this database parameter open_cursors is set to 2,000.
>
> Most sessions had less than 10 opened cursors, with following
> exceptions:
> - one session with 1,999
> - two sessions with 2,000
> - one session with 32,430
> - one session with 33,877
>
> How a session could have 32,000 opened cursors when limit is 2,000?

It can't. There is probably a problem with the stats. Check the V$OPEN_CURSOR table and do counts. Here is what the table looks like

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> desc v$open_cursor

 Name                                      Null?    Type
 ----------------------------------------- -------- 
----------------------------
 SADDR                                              RAW(4)
 SID                                                NUMBER
 USER_NAME                                          VARCHAR2(30)
 ADDRESS                                            RAW(4)
 HASH_VALUE                                         NUMBER
 SQL_ID                                             VARCHAR2(13)
 SQL_TEXT                                           VARCHAR2(60)
 LAST_SQL_ACTIVE_TIME                               DATE
 SQL_EXEC_ID                                        NUMBER
 CURSOR_TYPE                                        VARCHAR2(64)

SQL>

-- 
http://mgogala.byethost5.com
Received on Sat May 15 2010 - 00:42:46 CDT

Original text of this message