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:
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.comReceived on Sat May 15 2010 - 00:42:46 CDT