Ask the Expert

Details of UNDO space, restricitng transaction usage

I have two questions:

I would like to how to get the details of UNDO space occupied by each session. I tried with v$session and v$transaction. I don't understand some of the columns in v$transaction. Can you explain the important columns.

And another query, I tried to restrict the number of transaction usage of the rollback segment using TRANSACTIONS_PER_ROLLBACK_SEGMENT=5 I connected to six sessions and set the same rbs to all segments using SET TRANS... I did some transactions in all sessions. But I didn't get the error message from Oracle as the session is > 5. What could be the reason for this effect?

    Requires Free Membership to View

To answer your first question, I will refer you to the Oracle documentation for V$SESSION and V$TRANSACTION. You can get a detailed explanation of each of these view's columns with the following URLs:

If you look at the docs for V$TRANSACTION, you will see that the USED_UBLK column indicates the number of used UNDO blocks. And the USED_UREC indicates the number of used UNDO records. Furthermore, the START_UBAFIL, START_UBABLK, START_UBASQN, and START_UBAREC points to the exact UNDO location, namely the undo file, block, sequence number and record number, respectively.

To answer your second question, please note that according to the Oracle docs, TRANSACTIONS_PER_ROLLBACK_SEGMENT is "the number of concurrent transactions you expect each rollback segment to have". This does not mean that each transaction is limited to this number of transactions. There is no way of limiting the total number of transactions a rollback segment can have at any given time. This INIT.ORA parameter is used, in conjunction with TRANSACTIONS, to determine the minimum number of rollback segments to bring online on instance startup. The minimum number of rollback segments on startup = TRANSACTIONS/TRANSACTIONS_PER_ROLLBACK_SEGMENT.

For More Information

This was first published in May 2003

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: