V $ rollstat -- records the statistics of each rollback segment!

Rollback segment Overview:

Rollback segments are used to store values before data modification (including locations and values before data modification ). A transaction can use only one rollback segment to store its rollback information, while a rollback segment can store the rollback information of multiple transactions.

The role of rollback segments:

1. Transaction rollback: when the transaction modifies the data in the table, the value before the data modification (that is, the former image) will be stored in the rollback segment, when you roll back a transaction, Oracle uses the data pre-image in the ROLLBACK segment to restore the modified data to the original value.

2. transaction recovery: when the transaction is being processed, the routine fails and the rollback segment information is saved in the redo log file, ORACLE will use rollback to restore uncommitted data the next time it opens the database.

3. read consistency: When a session is modifying data, other sessions will not be able to see the modifications not submitted by the session. In addition, when a statement is being executed, the statement cannot see uncommitted modifications (statement-level read consistency) after the statement is executed ). When ORACLE executes a SELECT statement, ORACLE ensures that any uncommitted changes earlier than the current SCN are not processed by the current system change NUMBER-SCN. It can be imagined that when a long query is being executed, if other sessions change the data block to be queried for this query, ORACLE uses the data pre-image of the rollback segment to construct a read consistency view.

1. SQL>DescV $ rollstat;
2. Is the name empty? Type
3.  -----------------------------------------------------------------------------
4. USN NUMBER
5. LATCH NUMBER
6. NUMBER of partitions in the extents number rollback segment
7. Rssize number the size of the rollback segment in bytes
8. NUMBER of bytes written by writes number to the rollback segment
9. NUMBER of transactions processed by the xacts number Activity
10. NUMBER obtained by the gets number Title
11. Waits number of headers waiting
12. Optsize number the optimal size of the rollback segment
13. Hwmsize number high level mark of the rollback segment size
14. Multiple of shrinks number rollback segments
15. Multiples of the wraps number rollback segment Winding
16. Extends number: a multiple of the size extension of the rollback segment
17. Aveshrink number average shrinkage size
18. Aveactive number current size of the activity area on average over time
19. STATUS VARCHAR2 (15) STATUS of the rollback segment
20. Curext number Current Zone
21. Curblk number current Block

First, you can use u1 to delete a large table and construct a rollback operation:

1. SQL> showUser
2. USERIs"U1"
3. SQL>Delete FromT;
5. 402344 rows have been deleted.
7. SQL>Rollback;

View the rollback information as a sys User:

1. SQL> showUser
2. USERIs"SYS"
3. SQL>SelectN.Name, S. rssize, s. xacts, s. statusFromV $ rollname n, v $ rollstat sWHEREN. usn = s. usnAndS. xacts> 0;
5. NAMERSSIZE XACTS STATUS
6. -----------------------------------------------------------------
7. _ SYSSMU8 $100786176 1 ONLINE

The size of the rollback segment is 100786176/1024/1024 = 96 M.

1. SQL>SelectS. username, u.Name FromV $TransactionT, v $ rollstat r, v $ rollname u, v $ session sWhereS. taddr = t. addrAndT. xidusn = r. usnAndR. usn = u. usnOrder ByS. username;
3. USERNAMENAME
4. ------------------------------------------------------------
5. U1 _ SYSSMU8 $

You can see that u1 users are using rollback segment Resources.