Shulou(Shulou.com)06/01 Report--

Phenomenon description

When we make a large query, we often encounter this error:

The rollback record version is too old to get the user record

How can we solve this kind of problem?

There are three ways.

Choose the machine to execute

Adjust undo_retention appropriately

Consider enabling the ENABLE_IGNORE_PURGE_REC parameter

Treatment method

Treatment method 1:

When the data involved is unaltered, perform the corresponding operation (query, or query table creation, etc.)

It can be simply interpreted as (not equivalent, but roughly): after you start executing this statement, the data is modified by someone else and submitted. The database retains an up-to-date value, which is the modified value. Based on transaction visibility, the statement you execute starts before the modification, and the modified data should not be queried. What you should query is to modify the previous data-- at this time, the modified old value is in the rollback segment.

However, the database is not a machine that can be stored indefinitely, ah, when the value in the rollback segment and the corresponding transaction has been committed, it should have been cleaned up, but in order not to report errors in this kind of query we are currently executing, we also need to keep it properly.

So, for how long to stay, there is a parameter of undo_retention that determines that the default value for versions after 2017 is 300s (the previous default is 900s)

How to view this value:

`select * from v$dm_ini where para_name LIKE 'UNDO_RETENTION' `

That is to say, if the data involved in the execution of the sql is modified by someone else, and it is more than 300s, you may encounter an error (rolling back the record version for too long).

Then, as long as we execute when no one modifies the relevant data, no matter how many 300s we execute, we will not report an error.

Treatment method 2:

Temporarily modify undo_retention, for example, if we expect that this statement needs to execute 30min, then we can temporarily change this parameter to 30060 = 1800

After execution, change back to the original default value.

This is the sql statement modified to 18000, and the parameter is adjusted by executing the statement directly by executing sql.

`Now sp_set_para_value (1).

Accordingly, this is modified back to 300s.

`Now sp_set_para_value (1)

Before you modify it, make sure how much it was before. So as not to correct the error (too big or too small) to affect the use of other people or other applications. (excessive value has a negative impact on performance)

Treatment method 3:

When we know what's going on, we can also know that the database provides us with such a parameter. After all, it is better for the performance of the database to keep undo_retention small. Individual queries can actually ignore that piece of data and do not affect our execution expectations. Then, we can enable this parameter (and it is also a dynamic parameter)

The default value of ENABLE_IGNORE_PURGE_REC is 0 dynamic, session level

Processing policy when an EC_RN_NREC_PURGED (- 7120) error is returned (the version of the rollback record is too old to get the user record); 0: error; 1: ignore this record and continue execution

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.