SoFunction
Updated on 2025-04-13

Methods to adjust UNDO_RETENTENT parameter of Oracle database

UNDO_RETENTIONThe parameter is used to specify the minimum time (in seconds) for Oracle database to retain undo (UNDO) data. In some scenarios, you may need to adjust this parameter to meet the business's need for the revocation of data retention time. The following is a detailed introduction to how to adjust this parameter:

1. Check the current UNDO_RETENTTION parameter value

Before adjusting the parameters, you can check the current oneUNDO_RETENTIONSetting value. You can use the following SQL statements to view:

SHOW PARAMETER undo_retention;

or

SELECT name, value
FROM v$parameter
WHERE name = 'undo_retention';

2. Dynamic adjustment of UNDO_RETENTENT parameter

If you want to adjust without restarting the databaseUNDO_RETENTIONParameters, can be usedALTER SYSTEMSentence. The parameters adjusted in this way are still valid after the database instance is restarted because it will modify the initialization parameter file.

-- For example UNDO_RETENTION Set as 3600 Second（Right now 1 Hour）
ALTER SYSTEM SET undo_retention = 3600 SCOPE = BOTH;

Code explanation:

  • ALTER SYSTEM SETUsed to modify system-level parameters.
  • undo_retention = 3600Indicates that it willUNDO_RETENTIONThe value of the parameter is set to 3600 seconds.
  • SCOPE = BOTHIt means that the parameter value and initialization parameter file of the current instance are modified at the same time, so that the settings will still take effect after the database is restarted.

3. Static adjustment UNDO_RETENTTION parameter

Static adjustment requires modification of the initialization parameter file (such asor), and restart the database to make the settings take effect. This method is suitable for some special cases, such as when dynamic adjustment parameters are invalid.

3.1 Modify the file

If you are usingFile, you can edit the file directly, add or modify itundo_retentionParameter line:

undo_retention = 3600

After the modification is completed, save the file and restart the database:

-- by Oracle User ID login，Close the database
sqlplus / as sysdba
SHUTDOWN IMMEDIATE;
-- Start the database
STARTUP;

3.2 Modify the file

If you are usingFiles can be usedALTER SYSTEMThe statement writes the parameter value tospfile, and then restart the database:

ALTER SYSTEM SET undo_retention = 3600 SCOPE = SPFILE;

Then restart the database to make the modification take effect:

-- by Oracle User ID login，Close the database
sqlplus / as sysdba
SHUTDOWN IMMEDIATE;
-- Start the database
STARTUP;

4. Things to note

  • Parameter influence: increaseUNDO_RETENTIONThe value increases the usage of the undo tablespace, so you need to make sure that the undo tablespace has enough space to store the undo data for a longer period of time.
  • Test verification: Before adjusting this parameter in a production environment, it is recommended to test it in a test environment first, and observe the impact of the adjustment on database performance and undo tablespace usage.

This is the article about how to adjust the UNDO_RETENTION parameters of Oracle database. For more related content on Oracle UNDO_RETENTION parameters, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!