How to get or set UNDO_RETENTION parameter - Oracle

Updated on:

I have recently encountered an issue while trying to export the Oracle DB using the expdp command and my database size was huge, therefore I got an error stating that Snapshot is too old

When I researched further I noticed that my UNDO_RETENTION was too less and set to 900 seconds.

This is how I have managed to get or set the value to this parameter in Oracle

 

How to know the current value of UNDO_RETENTION in Oracle

to know the current or configured value of UNDO_RETENTION parameter in Oracle you can use the following SQL query

In order to be able to execute this SQL command, you must have SQL dba privileges or some specific grants to make you an elevated user

SQL> show parameters undo_retention;

NAME				     TYPE	 VALUE
---------------------------------- – – ------- – – ----------------------------
undo_retention			     integer	 10600

 

How to modify/set this UNDO_RETENTION value in Oracle

to modify or to set this UNDO_RETENTION value in oracle. you can use the following sql query.

Remember that the value of this parameter is mentioned in seconds.

SQL> ALTER SYSTEM SET UNDO_RETENTION = 10600;

System altered.

You can use the previous SQL statement to make sure that the value is changed or not.

Now you can go ahead and re-run the expdp and hopefully it should be fine.

Good luck

 

Thanks

Sarav

Follow me on Linkedin My Profile
Follow DevopsJunction onFacebook orTwitter
For more practical videos and tutorials. Subscribe to our channel

Buy Me a Coffee at ko-fi.com

Signup for Exclusive "Subscriber-only" Content

More from Middleware Inventory

  • Oracle Weblogic Basic Authentication

    Overview   Oracle WebLogic Server authentication is enabled by default. However, this configuration prevents Oracle WebLogic Server from using application managed authentication. You must disable Oracle WebLogic Server authentication by setting the enforce-valid-basic-auth-credentials parameter to false. Procedure  To disable Oracle WebLogic Server authentication: In a text editor, open the xmlfile from the domain folder. The config.xml file is in the Oracle/Middleware/user_projects/domains/domain_name/config directory. Locate the <security-configuration> Add the…

  • Weblogic 12c Oracle Datasource TNS Exception

    Problem While creating a Oracle Data source in weblogic 12c, If you are encountering the below error message upon TestConnection. Though all the SID(Database name) and Host and Port are Correct Listener refused the connection with the following error: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor…

  • How to perform Oracle Weblogic Deployments - Console

    Overview Oracle WebLogic Server is a scalable, enterprise-ready Java Platform, Enterprise Edition 7 (Java EE 7) application server. Oracle WebLogic Server is the industry's best application server for building and deploying enterprise Java EE applications with support for new features for lowering the cost of operations, improving performance, enhancing scalability…

  • What is ORACLE_HOME,MW_HOME,WL_HOME in Weblogic 12c

    The Objective In this post, we are going to see what are the different types of home directories available in weblogic and the various options or methods to find the ORACLE_HOME, MW_HOME and WL_HOME of your weblogic 12c installation. The commands and the methods are mostly given for the LINUX…

  • Apache Clickjack
    ROBOT Return of Bleichenbacher Oracle Threat - How to Fix

    Overview ROBOT is the return of a 19-year-old vulnerability that allows performing RSA decryption and signing operations with the private key of a TLS server. In 1998, Daniel Bleichenbacher discovered that the error messages given by SSL servers for errors in the PKCS #1 v1.5 padding allowed an adaptive-chosen ciphertext…