l
For the allocation and management of Oracle's returning segments, it is actually not so complicated. Of course, if we are from the principle and even from the perspective of Oracle Internal, it is not easy. But for our ordinary developers and DBA, it is important to understand, principitate, and is familiar with it. To do this, it is not a difficult thing. The root of the problem is that there are very few Chinese articles to return to the segments to make a clear introduction and organizing, and those who have to gradually read them slowly, although I have to admit that if you read it seriously Oracle Concepts About the Rollback Segment section, most of this is already very familiar with you, but I still willing to let go of Oracle Document to talk about my understanding, in reality, what kind of problem we meet in the reality. In trivial documents, the key part of can't catch the problem is the biggest obstacle to our reading. It is also because it is too trivial to make it difficult to grasp in macro, so that we adhere to our confidence. So, I also made me an idea to write this text, some of which were repeated the content in the Oracle document, but it is only possible to be different from the document. In this article, I intend to explain related content from a few aspects of the role, principles, allocation and management, diagnosis, and common issues.
l What is a rollback segment?
If you want to define a rollback segment according to mathematical definition, I think it is bored, abstract. For example, it can be said to be a disk storage area for saving data change pre-imaged images to provide a single disk storage area for transaction integrity. People who do applications are often not concerned about concepts or definitions, but they care about the specific use, which is better than we manage the use of databases every day, but they can't remember the definition of the database. In fact, our work does not require us to remember the database definition. Importantly, for our database, how should we understand it, how to use and manage it.
We probably know that the rollback segment is a space on the disk. When a transaction begins, first write the data and changes before the change in the change, first write the log buffer, then write the data before the change Segment, finally modify the data in the data buffer (log buffer content may be written after meeting a certain condition, but the log must be written to disk during transaction, and data in the data buffer depends on checkpoints The occurrence of the DBWR process is not discussed here. For example, when we do the following
SQL> Update T set Object_id = '0' where object_id = '12344';
1 row updated.
SQL> commit;
COMMIT COMPLETE.
At this time, the database first includes data '0' and '12344' to write the log buffer, and then writes the data '12344' and some related information, and finally modify the '0' to the data buffer. Area. When the submission command is issued, if the log buffer content has not been written to the log file, you must write into the log file, and the rollback segment is marked as submitted. These transactions in the data buffer are also marked as already submitted ( In the case of a large transaction, if the block has been written into a disk or the block of the transaction, the transaction change is 10% of the data buffer total size, which will not have submitted, which will affect us. Next, read this block next time). Of course, if we returns this transaction, the database will read the data buffer in the returning segment (the data buffer has been modified to '0'), and the change in this rolling itself is also written. Log, this is the process of retreat. We can see that if transaction is large, transaction data in the data buffer is already written to disk, that is, it is a very expensive operation. So in general, we think that the retreat rate of transactions in a system should be lower, otherwise it should be checked whether the system is normal or whether the program design is problematic. We can measure the transaction retort since the database starts, if you find that Transaction Rollbacks / (Transaction Rollbacks User Commits) is too high must pay attention to. SQL> SELECT NAME, VALUE from V $ sysstat where name in ('user commits',' transaction rollbacks);
Name Value
-------------------------------------------------- ------------------------
User commits 12532
Transaction Rollbacks 21
SQL>
Here we have to point out the data about the rollback segment store, if it is a delete operation, the returning segment will record the entire line of data. If it is Update, the returned segment only records the changed field. Data (pre-image), that is, the field that is not modified is not recorded. If it is insert, the rollback segment only records the RowID of the recorded record. This way, if the transaction is submitted, the transaction has been submitted in that the transaction is submitted; if it is a rolling, if the operation is delete, turn back the data in the back segment back to the data block, if it is Update, Then change the data before the change, and the operation is deleted according to the recorded ROWID. This process can be regarded as the integrity of the affairs, and the guarantee data is not lost.
Here we introduce consistency read, English is consistent reads. For Oracle, the result set of queries is determined based on the time point. Oracle inside the system change number SCN as a standard of relative time points, any change in the database generates SCN, this positive integer, the change corresponding to the change in the data block is recorded in the block. Assuming the SCN is T when the query starts, in the data block scanned, if the Commital SCN of the data is less than T, the query accepts the data. If the Commit SCN is greater than T, if there is no COMMIT SCN, the query will be Try to go back to the segment to find data. This is to ensure the consistency of the time point of the data read, so the consistency read. When obtaining data in the rollover segment, it is essentially a copy of the data block in the data buffer, and then restores the content recorded in the backgrou to the block, then query the use of this block to read . The number of such blocks If you are interested, you can log in to the database via the SYS user and then get it.
SQL> SELECT Count (*) from x $ bh where state = 3;
Count (*)
------------
15
SQL>
We are likely to produce such a confusion, whether the block of the rollover segment is in SGA? If there is any existence, why don't you see parameter settings? The real situation is that the block of the rollback segment is the same as the block of other data files, and it is also a part in the data buffer. Similarly, for Oracle8 or more, we can determine how much the block occupied by querying X $ BH. We can refer to the V $ ROLLSTAT TO and X $ BH table, in the X $ BH, the class field, if it is a roll-to-segment block, assume the return segment USN is N, then the rollback segment head Class is 11 2N, back The roll block is 12 2n. Let's take a look at the actual example
SQL> SELECT USN FROM V $ ROLLSTAT;
USN
------------
0
1
2
3
4
5
6
Seduce
9
9 rows selected.
SQL>
From the backgrou number we can see the number of numbered 8 has been deleted, a total of 9 rebounds.
SQL> SELECT CLASS, Count (*) from x $ bh where class> 10 group by class;
Class count (*)
---------- ----------
11 1
12 2
13 1
14 1
15 1
16 1
17 1
18 982
19 1
20 1
21 1
Class count (*)
---------- ----------
22 1
23 1
24 1
25 1
26 1
29 1
30 1
18 rows selected.
SQL>
From here we can see that the Class of the return segment number 8 should be 27,28 blocks. The rollback section is always a block, and the block of the retraction of class = 18 (USN = 3) is more because my database is just started, and the following statement is just implemented.
SQL> Delete from T;
25374 rows.
SQL> commit;
COMMIT COMPLETE.
SQL>
l The assignment of the rollback segment and use When the transaction is generated, the database will assign a rollover segment to the transaction. Of course we can specify a transaction to use a rollover segment. In my test, there is a sloppy segment
SQL> SELECT segment_id, segment_name from dba_rollback_segs;
Segment_id segment_name
---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0 system
1 RBS0
2 RBS1
3 RBS2
4 RBS3
5 rbs4
6 rbs5
7 rbs6
9 RBS12
9 rows selected.
SQL>
If we want to specify a returning segment, you are as follows
SQL> Set Transaction Use Rollback Segment RBS6;
Transaction set.
SQL> INSERT INTO T SELECT * ALL_OBJECTS;
Rows created.
SQL> commit;
COMMIT COMPLETE.
SQL>
If we do not use which rollback segments you use, the database will trade according to the transaction in the rollback segment so that transaction pressure in all returns will be as sharp as possible. We consider the situation in which non-system returns (this is also the case of most systems, unless the DBA is serious) only exists in the system rollback segment, in which case our transaction will not be used. The system returns, when the system returning segment is only used for system-level usage, such as CREATE, DROP, TRUNCATE, and the rollback records of system-level data dictionary. The database selection non-system returns for our transactions, the same transaction cannot sprinkle the grouse, that is, even other returns are still very idle, the big business can only use the assigned rollback segment even if the return Glute extension.
Next, we will study the problem of use, expansion, and retraction within a single returns. A rollback segment contains at least 2 Extent. Each rollback section has a rollback segment header, the rollback section is a block, which mainly records the transaction table information. When a transaction is generated, a message is recorded in the transaction table of the rollback segment head, which includes information such as transaction marks, transaction status, and number of rolled segments used. We assume that there is a newly created rollover segment extent 1, 2, 3, 4, 5. When the first transaction is assigned to the returned segment, in addition to the transaction table information, the transaction starts using the second block of Extent 1, and the transaction is continuous and submitted, suppose we have already used Extent. 5 at the end. At this time, the content needs to be written again, then the second block of Extent 1 is reused. This cycle uses the rollover segment space. The transaction table of the same back to the head is also used cyclically. The number of cycles can be obtained by querying
SQL> SELECT USN, WRAPS from V $ ROLLSTAT;
USN WRAPS
---------- ----------
0 0
1 15
2 15
3 15
4 15
5 12
6 15
7 17
9 12
9 rows selected.
SQL>
Then we will have a problem, that is, since the return segment is cycled, why will it expand? Note that in the example above, all transactions are quickly submitted. Then we now assume such a situation, there is a transaction, assuming that there is a transaction in Extent 3 has not submitted, and then returning to the rollway to use Extent 2. When Extent 2 is used, it is found that there is no transaction in Extent 3, which is even if the transactions in Extent 4, 5, 1 have been submitted, and the current transaction cannot be used by Extent 3 to use the available extent, this When the returning segment expands new extent, assumes Extent 2-1. Between the EXTENT that actually returns a split segment in the database is a one-way loop of a one-way loop of the pointer. When expanding, it is equivalent to inserting a node Extent 2-1 in the linked list, but the next extent of node 2-1 is still extent 3. If 2-1 is used, it is found that Extent 3 still has uncommitted transaction returns will continue to expand. . We do an example, running in SQLPlus 1 (we querying the query of USN = 5 in V $ ROLLSTAT and DBA_ROLLBACK_SEGS, and the roll band of USN = 5 corresponds to the name RBS4)
SQL> SELECT A.USN, B.SEGMENT_NAME FROM V $ ROLLSTAT A, DBA_ROLLBACK_SEGS B
2 where a.usn = b.segment_id;
USN segment_name
---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0 system
1 RBS0
2 RBS1
3 RBS2
4 RBS3
5 rbs4
6 rbs5
7 rbs6
9 RBS12
9 rows selected.
SQL>
SQL> SELECT USN, RSSIZE "Rollback Segment Size" from V $ ROLLSTAT WHERE USN = 5;
USN ROLLBACK Segment Size
---------- ---------------------
5 4186112
SQL> SET Transaction Use Rollback Segment RBS4;
Transaction set.
SQL> Update T_Small Set Object_ID = 1;
100 rows updated.
Open SQLUS 2 run
Begin
For i in 1..1000 loop
Set Transaction Use Rollback Segment RBS4;
Update t set object_id = I where rownum <101;
COMMIT;
End loop;
END;
SQL> SELECT USN, RSSIZE "Rollback Segment Size" from V $ ROLLSTAT WHERE USN = 5;
USN ROLLBACK Segment Size
---------- ---------------------
5 55042048
Now let's see the following queries in a separate session and compare the rollback segments before and after the query.
SQL> SELECT USN, RSSIZE "Rollback Segment Size" from V $ ROLLSTAT WHERE USN = 4;
USN ROLLBACK Segment Size
---------- ---------------------
4 4186112
SQL> Begin2 for i in 1..1000 loop
3 set Transaction use rollback segment rbs3;
4 Update T set Object_id = I where rownum <101;
5 commit;
6 End loop;
7 End;
8 /
PL / SQL Procedure SuccessFully Completed.
SQL> SELECT USN, RSSIZE "Rollback Segment Size" from V $ ROLLSTAT WHERE USN = 4;
USN ROLLBACK Segment Size
---------- ---------------------
4 4186112
SQL>
In these two examples, we clearly see the contrast of the returning segment extension. So, after completing the second example, I will return to the head to query the relatively large returns of the original extension, and I found it into 4M.
SQL> SELECT USN, RSSIZE "Rollback Segment Size" from V $ ROLLSTAT WHERE USN = 5;
USN ROLLBACK Segment Size
---------- ---------------------
5 4186112
SQL>
The rollback segment is to be retracted after the extension is. Assuming the rollback segments Current N, when using Extent N 1 (Extent N 1 no active transaction), if Optimal is set, if the Optimal is set and the slide is larger than the size of the Optimal setting, check whether EXTENT N 2 is in EXTENT N 2 There is no transaction, if not, recycle Extent N 2, essentially, is extracted from Extent N 2 on the linked list of rollback segments, then continue to check that EXTENT N 3 determines whether it is recycled, which repeats this action. Optimal setting determines the size after the rollback segment finally recovered, and the slot segment retraction is as close as possible to Optimal settings. If the above example is 4M, the number of times the SHRINKS indicates the number of retraction of the rollback segment, actually v $ ROLLSTAT provides a lot of return segment information, you can refer to Oracle Document)
SQL> SELECT USN, OPTSIZE, SHRINKS from V $ ROLLSTAT;
USN OPTSIZE SHRINKS
---------- --------------------
0 0
1 4194304 0
2 4194304 0
3 4194304 0
4 4194304 0
5 4194304 10
6 4194304 0
7 4194304 0
90
9 rows selected.
l System returning segments and delayed rollback segments
The SYSTEM returning segment is created in the system tablespace, mainly for system-level transactions and assigning normal transactions on other returns. You must first create a system rollback segment before you need to create a regular return segment. According to Oracle Documentation, things that use the system return segment when ordinary transactions are abnormal. However, under normal circumstances, the system returning segment is mainly used for two aspects. One is system transaction, such as TRUNCATE TABLE and DROP TABLE for operations for data dictionaries. If there is no success during the truncate table or drop table, the system is retracted according to the data dictionary operation information in the system rollback segment. Another aspect is the delay rollback segment. The delayed rollback segment is indicated that when we make a table space OFFLINE (Exeample: ALTER TABLESPACE OFFLINE), since the table space is not available (cannot be read and written), this time if the transaction data is located in the table space and executed. Roll back the command, rollback to the client, which looks back for the client, but for the database, the rollback is not really completed, this time the database writes the rollover information into the system rollback segment ( This is the latency rollback segment), when the table space is re-online, the database writes the rollback information from the system rollback to the table space. l Setting and managing the rollback segment
In fact, for the personal opinion, the management of the rollback segment should not be treated as a complex task of DBA, because the management of the rollback segment can make it simple. Almost all systems have a problem of returning segments, nothing more than a rollover segment is not enough, and the number of rollbacks is too small. 9i previous versions Because for us, it is nothing more than these two questions.
Regarding the number of table spaces, returning segment data files, the expansion of the segment data file, the expansion of the rollback segment, the parameter problem specified, I want to refer to the creation of the syntax, there is no need to go to entanglement Max EXTENTS is 100 or 200 You will find that these parameters do not have substantive meaning. All all settings, I only need to ask a few questions, it is enough:
1: What is the number of system concurrent transactions?
2: Is there a big query or a transaction? Are you frequent?
3: How much is the disk space that can provide a split segment space for the system?
There is a parameter transactions_per_rollback_segment and transactions that will be initialized in the initialization parameter file, which determines the number of largest rollback segments online, which determines the maximum number of transactions in the same time. 2 initialization parameters mentioned herein
Max_rollback_segments Maximum number of rollies allowed
Rollback_segments This parameter is a parameter list. If you are a PUBLIC type when you create a rollback segment, you have nothing to do with this parameter. If it is a private type, you must make the rollback segment appear inside the parameter list, otherwise these backs The segment does not automatically online (manual). In the OPS / RAC, the PUBLIC type rollback segment indicates that all instances can be online (but only one instance online), which is equivalent to a public returning segment pool.
When the instance is started, the instance attempts to set the rollback segment set in the online rollback_segments until the number is min (CEIL (Transactions / Transactions_Per_Rollback_Segment), Max_Rollback_Segments. If this value is not reached, the instance tries to try online back to roll in the PUBLIC type, until the value is reached or no longer returned to the block. Whether the number of online returns segments is sufficient after a instance is started, the number of concurrent transactions is related to the excessive events on each rollback segment may lead to serious retracement.
Here, there is a problem related to the problem, let's look at the following two questions. Due to the expansion and recycling of the rollback segment is an operation of expensive costs, usually we are avoided. If there is a big query, even if you don't write back, but you can also refer to the previous content, know if the busy returns of the transaction is overwritten, and a famous ORA-01555 error . Also because the transaction is generated unless of which a return segment is used to use, the transaction is transparent for us to apply, and we can specify which return segment used but does not stop other transactions Something, so we must realize that the rollback segment is set to size is inconsistent, almost meaningless, because bottlenecks always determine the minimum rollback segment (this is similar to the principle of wooden bucket What decisive water is determined by the shortest piece). So we should unify the size of the grouse. That usually, for a system, hundredsm of disk space or even a few g of disk space is not a problem, so we have no reason to study the rollback segment here to use 4M size or 10m size, we can provide disk space Estimation, you can set the size of 50m / 100m or even greater, which is mainly determined in the amount of transaction generation on each rollback segment during large query operation, as well as rollback data that may be generated in a single transaction. size. If the system occasionally exists in bulk jobs, a rollback segment may be extended to 1G, but usually our split segment size does not have a retreating phenomenon at 50m. At this particular time, if the database is not busy, we can create a few big returns, then other returns of the segments OFFLINE, wait for the batch job to complete, other returns, make the big back to the ruler OFFLINE . Of course, it may also specify a batch job using a large rollback segment. Alternatively, we can set an Optimal to 50M for all returning segments, and the specific time extension is then retracted (note that all the Optimal of all returns must set the same size).
In addition to the system status estimation, the returning segment is deleted, it is necessary to pay back the rollback segment and offline, we have to pay attention to if the rollover segment is online and there is an active transaction, if you want to make The rollback segment is offline, then the rollback segment is in a suspended state, that is, the new transaction will not use the rollback segment, and the original transaction continues to exist, waiting for the returning segment. After the transaction is complete, the rollback segment becomes offline.
l 9i Undo TableSpace
Starting with Oracle9i, recommend UNDO TABLESPACE, allowing the system to automatically manage the rollback segment.
SQL> Show Parameters Undo
Name Type Value
------------------------------------- --------
undo_management string auto
Undo_retention integer 10800
Undo_suppress_errors boolean falseundo_tablespace string undotbs1
SQL>
Initialization parameter undo_management decides whether the returning segment used by the database uses automatic management mode. Auto expresses automatic management, manual means manual management, which is the same as 8i. Undo_tableSpace Specifies which tablespace used in Automatic Management mode, undo_retentity indicates how long it is retained before the data is covered in automatic management mode, and the unit is seconds. This parameter should determine the length of time for some big queries in the system to avoid ORA-01555 errors. Of course, due to the flashback function provided by 9i, the length of time settings can be determined as needed. Setting UNDO_RETENTION to estimate the size of the rollback data generated in such a long time, combined with the disk space provided by the hardware to consider. UNDO_SUPPRESS_ERRORS parameter If set to true in automatic management mode, if we try to create a rollback segment, do not return an error message, but actually invalid, set to false, try to create a rollback segment returns an error message.
The rollback segment has become automatic management. On the one hand, the problem of the management of the rollback segments mentioned earlier is not a complex issue. On the other hand, automatic management makes us feel at a loss, almost artificially difficult to control it. For example, the undo table space is large, we can't shrink. At this time we can consider creating a new Undo table space, then change to new table space, even if there is a transaction in the UNDO table space, it can be switched, but you can't immediately delete the table space. After switching, wait until the original table space After all transaction is processed and reached the decline in undo_retrion, you can DROP the original undo table space. This can solve the problem that the UNDO tablespace changes is too large and cannot be reduced. The command is as follows:
SQL> ALTER system set undo_tablespace = undotbs1;
SYSTEM altered.
SQL>
At this point, you should pay attention to if the undo table space should be switched, the PFILE or SPFILE should be modified next time to start the application new UNDO table space.
The number of returning segments in the UNDO table space in automatic management mode is changed, and the rollback segments still have the case of online and offline, but it is only the system management. If there is an active transaction in the rollback segment at the time of offline, there is also a state in which a hanging state mentioned above. At this time, the system may generate a TRACE file for the prompt information, which is nothing to do, and there is no impact on the system.
Create a non-auto-managed rollback segment without using the undo table space, set undo_management to manual, then create a rollback segment in the system table space (note that this is must), create your own rollback segment table Space, at which time you can create a rollover segment in the rollback segment table space, create a returning segment in the system table space.
By the way, the undo table space is used when doing the LOB type data, if the amount of data is too large (maybe more than 5g) may have bug, oracle9.2. If Undo is ASSM, if the undo_retrion is not properly The expansion is too large, and bugs will also cause the system crash to be recovered. Of course, it is recommended to manage DBAs in the production database to browse Metalinks, understand the features and bugs of various versions.
l back to the famous ORA-01555 issue
About consistency reading has been introduced, but here we have to generate a question, and when you read the acquisition, you find that the rollback segment has been covered and you can't find the image, that is, when the commit SCN is greater than t query, try returning Retrieved data in the segment, but found that the roll band has been covered (because the rollback segment is cycled), a famous ORA-01555 error will appear. Another situation is about block cleaning, which involves a block clearance of Oracle, which means that the data has been submitted, requiring flags to be submitted, so that the later session is accessed It is no longer generated to read the block directly. If the block has been written in the disk when the transaction is submitted, the block is not cleared, and the Delay Block Cleanout is required. When the submitted but no way and the flag is submitted, the session checks if the session is read in the next session, it will check if the latest transaction status is active, and if it is not an activity, modify the transaction flag. This will no longer generate unnecessary unwanted reads after doing this. But this situation only appears in: The transaction is very likely to produce this situation, the transaction has already been submitted, the rollback segment has been overwritten, and the block is not marked whether it is submitted, and the current query SCN T is recorded in the current rollback segment. The smallest SCN is small (query has been running a longer time rollback segment has been overwritten). At this time, the database cannot determine the size relationship between the SCN T between the current query and the COMMIT SCN of the block, so returns an error. In fact, this situation is rare, and it is more difficult to understand. Usually we can not consider this situation.
Of course, the above is description from the perspective of the database. From the application perspective, there are several possibilities:
1: The query execution time is too long. So at this time, the work we must do is to optimize the query, then consider whether you can run the query when the database is not busy, and finally consider increasing the rollback segment.
2: Excessive frequent submission. If you can submit a transaction, we may be submitted, you should consider submitting the entire process, or segment submitting instead of single submission.
3: Use consistent = y when EXP. This parameter is mainly to ensure that all spreads have been consistent at the time point when EXP, which avoids the integrity of the data due to inconsistency in different table time points. It is recommended that the operation is performed when the system is idle.
4: Due to the case of returning segments, the returning segment has not been recycled, and there is only the case where the data is found in the rollback segment, that can only be increased to increase the Optimal settings.
Other reasons why others such as returning segments cannot be expanded, and no longer be described.
l Strike Sound Signation and Some Useful Script
For the monitoring of the rollback segment, the most common is to view the V $ ROLLSTAT dynamic view
SQL> Desc v $ ROLLSTAT
Name NULL? TYPE
------------------------------------- ------------------
USN Number
Extents Number
RSSIZE NUMBER
Writes Number
XACTS Numbergets Number
Waits Number
Optsize Number
HWMSIZE NUMBER
SHRINKS NUMBER
Wraps Number
EXTENDS NUMBER
Aveshrink Number
Aveactive Number
Status varchar2 (15)
Curext Number
Curblk Number
SQL>
In this view, if you find Shrinks, you can think that the return segment is too small or the Optimal setting is too small. If it is Waits, it can be considered to be a number of returns. I think you often search for http://tahiti.Oracle.com is a good habit, there are almost all of the basic concepts, a lot of Oracle Documents, these view information can be queried here, all about Oracle's basic problem can almost look at the answer here.
Another view that helps us diagnose problems
SQL> SELECT * FROM V $ WaitStat;
Class Count Time
------------------------------------
Data Block 341 0
Sort Block 0 0
Save undo block 0 0
Segment header 0 0
Save undo header 0 0
Free List 0 0
EXTENT MAP 0 0
Bitmap Block 0 0
Bitmap Index Block 0 0
Unused 0 0
SYSTEM undo header 0 0
SYSTEM undo Block 0 0
Undo header 4 0
Undo Block 81 014 Rows SELECTED.
SQL>
From this view, you can see the battle for returning segments and rollback segments. If you are serious, you can also consider adding the number of rollies and increase the size of the rollover segment.
Now give some useful script
Below this script is what session blocks others when there is a lock waiting in the system. (Note that the DBA_BLockers table is provided in the Oracle9.2.0 version to display the session information of the other session, while DBA_Waiters displays the SESSION information)
SQL> SELECT UserName,
2 V $ LOCK.SID,
3 Trunc (ID1 / Power (2,16)) RBS,
4 Bitand (ID1, TO_NUMBER ('FFFF', 'XXXX')) 0 slot,
5 ID2 SEQ,
6 lmode,
7 Request
8 from V $ LOCK, V $ session
9 WHERE V $ LOCK.TYPE = 'TX'
10 and v $ lock.sid = v $ session.sid
11 and v $ session.username = user;
UserName Sid Rbs Slot SEQ LMODE Request
------------------------------------------------------------------------------------------------------------------------------------------ ---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Rainy 8 7 45 300 0 6
Rainy 13 7 45 300 6 0
From here we can see that the block of lmode = 6 is the session of lmode = 0 (LMODE = 6 means the session has a lock, the type of lock is 6, which means exclusive).
There is a detailed description of the Script, which provides a detailed description of a very intuitive data format.
Also introduce one how to query the rollback segment used by the database current SESSION's transaction
SQL> SELECT B.SID,
2 a.xidusn,
3 a.USED_UBLK
4 from V $ Transaction A, V $ Session B
5 WHERE A.Addr = B. TADDR;
Sid Xidusn Used_UBLK
---------- --------------------
8 1 3
SQL>
The table content provided by Oracle9.2.0 is as follows, and Holding_SESSION and WAITING_SESSION represent the SID, which can be combined according to V $ SESSION inquiry information.
SQL> DESC DBA_BLOCKERS
Name NULL? TYPE
----------------------------------------------------------------------------------------------------------
Holding_Session Number
SQL> DESC DBA_WAITERSNAME NULL? TYPE
----------------------------------------------------------------------------------------------------------
Waiting_Session Number
Holding_Session Number
LOCK_TYPE VARCHAR2 (26)
Mode_held varchar2 (40)
Mode_Requested Varchar2 (40)
LOCK_ID1 NUMBER
LOCK_ID2 NUMBER
SQL>
Of course, in fact, you can also combine SQL_ADDRESS, SQL_HASH_VALUE in combination with V $ session, so I know what SQL is running, through V $ LOCK, DBA_OBJECTS, V $ locate_object Query What locks, locked what objects. As long as you understand the content of Oracle's view, the so-called these queries become the same as the needs of queries in ordinary applications, solve the problem is just a matter of belief.
