Solution for insufficient space of rollback segments due to the release of temporary Oracle table cursors

Multiple ORA-01650 errors are reported when the Oracle temporary table cursor is not released, resulting in insufficient rollback segment space, causing the database to fail. This article mainly introduces the solution to this problem. Next let's take a look at this part.

Analyze the background. The program that generates an error is a data processing module. Every day, the content in the flat file passed by other systems is processed and placed into the database, resulting in a large volume of transactions. Our system is 9i, divided into 16 rollback segments, two of which are large batch rollback segments. Each batch rollback segment has 6 GB of space. The data processing module specifies the use of batkers in transactions.

Analyze the error reporting module. Check related data transaction processing. Because the business needs to maintain data consistency, it is necessary to process one file before submission. If an error occurs in the middle, it is necessary to roll back all data transactions. After confirmation, this part of the Code has not been modified for a long time. Then confirm the data volume. You can confirm that today (Friday) is the day with the minimum file content within a week. That is to say, if an error occurs due to the amount of data, the probability of other times should be higher.

However, pay attention to this. The specified rollback segment is for transactions, not for rollback segments. That is to say, we can specify that a transaction only uses a specific rollback segment, but it cannot be guaranteed that This rollback segment is only used by this transaction. When a transaction application uses a rollback segment, if it is not specified by itself, oracle will use the rollback segments at that time according to the usage of each rollback segment, allocate the most suitable rollback segment for this transaction. Therefore, transactions that report insufficient rollback segments may not necessarily be those that cause insufficient rollback segments. Another possibility is that the rollback segment specified by the firm is occupied by other firms.

Therefore, we will check whether other transactions occupy the rollback segment.

SELECT s. sid, s. username, s. osuser, s. machine, s. program,
T. xidusn, t. ubafil, t. ubablk, t. used_ublk, t. log_io, t. phy_io, t. cr_get, t. cr_change,
R. name, q. SQL _text
FROM v $ session s, v $ transaction t, v $ RollName r, v $ sqlarea q
WHERE s. saddr = t. ses_addr
And t. xidusn = r. usn
And s. SQL _address = q. address (+)
And s. SQL _hash_value = q. hash_value (+) And r, name = 'rbs _ batting ';
We found that five transactions are occupying batkers. However, the session status is INACTIVE. This means that they didn't run the INSERT/UPDATE/DELETE Statement (once run, the transaction is not finished), but instead they rolled back the segment resource hung.

Check the objects that are hung in batchcompute,

Select l. session_id, l. OS _user_name, l. oracle_username, o. owner, o. object_name, t. xidusn, t. ubafil, t. ubablk, t. used_ublk, t. used_urec,
T. log_io, t. phy_io, t. cr_get, t. cr_change, r. name
From v $ locked_object l, dba_objects o, v $ transaction t, v $ RollName r
Where l. object_id = o. object_id
And l. xidusn = t. xidusn
And l. xidslot = t. xidslot
And l. xidsqn = t. xidsqn
And t. xidusn = r. usn;
Well, they are all the same object: "TMP_CNT_GRP ". Through SID confirmation, it is true that the above transactions all put this object hung in RBS.

After checking, the object is a temporary table. We know that temporary table objects do not usually have data. Only when a session uses a temporary table and inserts data into the table can oracle create its data objects in the temporary tablespace. The reason why temporary table data is only seen by the called session is that a separate data object is created in each session with its own data object label. Therefore, although it is a temporary table, each session only copies a table structure and creates different data objects, so that there will be no data interference between sessions. In a session, the processing of temporary table data objects is basically the same as that of common data objects, including the rollback of temporary table objects during transaction data changes.

Go back to our issue. We found through V $ SESSION and V $ SQL _AREA that these sessions call a PLSQL function and are all called through java.

The Review Code finally found a potential problem: the result of this function is to return a cursor, And the cursor is associated with this temporary table.

Insert into TMP_CNT_GRP...
SELECT ...... open v_cursor
Select TMP. CDE,
CAR. ID,
CAR. NME,
COUNT (distinct tmp. NUM) TOTAL_CNT
From TMP_CNT_GRP TMP,
CSS_CAR
Where tmp. ID = CAR. ID
Group by tmp. CDE, CAR. ID, CAR. NME;
 
RETURN v_cursor;
 
DELETE TMP_CNT_GRP;
(This code still has a problem, that is, the last DELETE statement will not be called at all)

From this code, we can see that in fact, in the entire function, the data in the temporary table will not be released at all; and no transaction is committed or rolled back (although this is a session-level temporary table ). The occupied rollback segments will not be released. This has the potential problem. If the caller does not close the session or commit/roll back the transaction, the rollback segments it occupies will not be released. In fact, after the java developer checks the code, it is found that the client has not closed after the session is opened, knowing that the client itself has ended.

Solution:

1. Because this is a session-level temporary table, data is retained after the transaction is committed. Therefore, add commit after the insert statement in the PLSQL function;

2. the Java code closes the session after the cursor is used.

This article introduces the problem that the temporary table cursor of the Oracle database is not released and the rollback segment space is insufficient. We hope this article will help you.