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:
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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.