| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: ORA-1554: Out of transaction slots
This is pure conjecture with a little bit of old 'knowledge' tossed in, but
it does explain the scenario. Any and all corrections to my
knowledge/reasoning are VERY welcome. I could test, but that would take too
long to provide the answer in a timely manner.
Explanation of test scenario:
I recall that at one point, Oracle would allow user transactions to use
the system rbs if all other rbs were 'full'. Not sure how it determined this
in that a transaction is bound to an rbs before it tries to locate an open
tx slot. If it finds all other rbss full, it tries to bind to the system
rbs, but cannot because of a new rule preventing that binding, so it invokes
the "Unable to use system..." error.
Dan Fink
-----Original Message-----
Sent: Tuesday, October 08, 2002 4:24 PM
To: Multiple recipients of list ORACLE-L
Anjo,
To simulate the ORA-1554, I tried the following, which I think, is along the same lines you mention :
1. Created a database with block size 2K. 2. Created one rollback segment in the tablespace RBS. 3. Started 21 session all updating different tables, and not commiting. 4. v$rollstat at this point showed me xacts = 21 and status = ONLINE 5. Started the 22 session and tried an update statement. 6. Oracle reported the "Unable to use system rollback segment for nonsystem ..." error.
The reason I am trying to simulate this error, is that the application logs
reported the error:
ORA-1595: error freeing extent (3) of rollback segment (2). #### Yes,
optsize is set.
ORA-1554: Out of transaction slots in transaction tables.
I am trying to investigate why? Metalink could not give me a definite answer, and soft closed the tar.
Thanks
Raj
Anjo Kolk
<anjo_at_oraperf To: Multiple recipients of list
ORACLE-L <ORACLE-L_at_fatcity.com>
.com> cc:
Sent by: Subject: Re: ORA-1554: Out of
transaction slots
root_at_fatcity.
com
October 08,
2002 04:54 PM
Please
respond to
ORACLE-L
Rajesh,
Your database has probably only the system rollback segment. Oracle will probably leave a slot open so in that rollback segment. Create an additional rollback segment and you will probably see the error.
Anjo.
Rajesh.Rao_at_jpmchase.com wrote:
>
> Sorry if I did not phrase my question correctly. I do know that for a
> database with block size 2K, the number of max transaction slots is 21.
> When I was running my tests, I was expecting to see an "Out of
transaction
> slots" on the 22nd session. Instead Oracle tried to use the system
rollback
> segment, and gave me an "Unable to use system rollback segment for a non
> system .... " error.
>
> My question is I want to simulate the "Out of transaction slot" error?
What
> should I do to have Oracle report this message back to me?
>
> Thanks
> Raj
>
>
> Jared.Still_at_r
> adisys.com To: ORACLE-L_at_fatcity.com
> cc: Rajesh Rao/CHASE_at_CHASE
> October 08, Subject: Re: ORA-1554: Out
of transaction slots
> 2002 02:44 PM
>
>
>
> It's dependant on block size, or at least it was the last time I checked,
> which was a few years ago.
>
> You can see the number of slots by doing a block dump the rollback
> segment.
>
> Jared
>
> Rajesh.Rao_at_jpmchase.com
> Sent by: root_at_fatcity.com
> 10/08/2002 11:34 AM
> Please respond to ORACLE-L
>
> To: Multiple recipients of list ORACLE-L
<ORACLE-L_at_fatcity.com>
> cc:
> Subject: ORA-1554: Out of transaction slots
>
> Hello Folks,
>
> I carried out a test, whereby I had just one rollback segment online with
> a
> block size of 2K, and then running updates against the database thru
> different sessions without commiting any of them? As expected, it
accepted
> the first 21 transactions, and on the 22nd transaction, threw an "Unable
> to
> use system rollback segment for a non system .... " error.
>
> When does Oracle report an "ORA-1554: Out of transaction slots" for a
> rollback segment?
>
> Thanks
> Raj
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: Rajesh.Rao_at_jpmchase.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk INET: anjo_at_oraperf.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Rajesh.Rao_at_jpmchase.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fink, Dan INET: Dan.Fink_at_mdx.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Tue Oct 08 2002 - 18:13:41 CDT
 |
 |