Problem solve Get help with specific problems with your technologies, process and projects.

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?

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

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.