Problems with the undo tablespace are usually of the following nature:

ORA-01555: snapshot too old

ORA-30036: unable to extend segment by … in undo tablespace ‘UNDOTBS1’

The prior errors can be caused by many different issues, such as incorrect sizing of the undo tablespace or poorly written SQL or PL/SQL code.

A snapshot being too old can also occur during exports because of updates to very large tables and is normally seen when either the undo retention or the size is not properly set.

For an export, it is an easy fix to rerun at a quieter time. So, sometimes it is just rerunning the job or transaction on the database. If these happen too often, it might be because of the undo tablespace size.

Or if the system is sharing the undo tablespace in the CDB, you can create the undo tablespace in each of the PDBs.

Determining Whether the Undo Is Correctly Sized

Suppose you have a long-running SQL statement that is throwing an ORA-01555: snapshot too old error, and you want to determine whether adding space to the undo tablespace might help alleviate the issue.

Run this next query to identify potential issues with your undo tablespace. Make sure you are in the PDB that you are checking the undo tablespace. The query checks for issues that have occurred within the last day:

SQL> select to_char(begin_time,’MM-DD-YYYY HH24:MI’) begin_time ,ssolderrcnt                             ORA_01555_cnt, nospaceerrcnt                             no_space_cnt,txncount ,expiredblks
max_num_txns, maxquerylen    max_query_len blck_in_expired from v$undostat where begin_time > sysdate – 1 order by begin_time;

The ORA_01555_CNT column indicates the number of times your database has encountered the ORA-01555: snapshot too old error. If this column reports a nonzero value, you need to do one or more of the following tasks:

•     Ensure that code does not contain COMMIT statements within cursor loops

•     Tune the SQL statement throwing the error so that it runs faster

•     Ensure that you have good statistics (so that your SQL runs efficiently)

•     Increase the UNDO_RETENTION initialization parameter

The NO_SPACE_CNT column displays the number of times space was requested in the undo tablespace. In this example, there were no such requests. If the NO_SPACE_CNT is reporting a nonzero value, however, you may need to add more space to your undo tablespace.

A maximum of 4 days worth of information is stored in the V$UNDOSTAT view. The statistics are gathered every 10 minutes, for a maximum of 576 rows in the table. If you have stopped and started your database within the last 4 days, this view will contain information only from the time you last started your database.

Another way to get advice on the undo tablespace sizing is to use the Oracle Undo

Advisor, which you can invoke by querying the PL/SQL DBMS_UNDO_ADV package from a SELECT statement. The following query displays the current undo size and the recommended size for an undo retention setting of 900 seconds:

SQL> select sum(bytes)/1024/1024             cur_mb_size ,dbms_undo_adv.required_undo_size(900) req_mb_size from dba_data_files where tablespace_name = (select value from v$parameter where name = ‘undo tablespace’);

Here is some sample output:

CUR_MB_SIZE REQ_MB_SIZE 36864                 20897

The output shows that the undo tablespace currently has 36GB allocated to it. In the prior query, you used 900 seconds as the amount of time to retain information in the undo tablespace. To retain undo information for 900 seconds, the Oracle Undo

Advisor estimates that the undo tablespace should be 20.4GB. In this example, the undo tablespace is sized adequately. If it were not sized adequately, you would have to either add space to an existing data file or add a data file to the undo tablespace.