|Applies To||Affected Versions: All Versions|
This summary information regarding the Oracle Undo Tablespace is available from many online Oracle documentation sites and is repeated here as reference. For detailed specifics, please refer to the official Oracle Documentation.
The information presented here assumes the use of an ACM appliance with the default ASM partition. The basic idea behind undo tablespace usage is the same regardless if a local filesystem or ASM partion is used, however, the directory path/device designation will be different. In both cases, the amount of available 'free' space should be ascertained and understood before adding new tablespace extents.
Undo records are used to:
The amount of space required (and ultimately the filesize of the undo tablespace file(s), depends on how much transaction activity is taking place at any given time for a particular database. The existing Aveksa ACM database is configured to use up to a maximum filesize of 32 GB.
Typically, this is more than enough space, as the undo tablespace is re-used during normal processing. However, an event may occur which requires additional undo space, and if this space is not currently available, an ORA-30036 error may occur. Although this error implies that only 8 additional bytes are required, those 8 bytes were only the initial request. There is no way to determine at this point how much addictional space will be necessary to complete the current transactions. A standard ROT ('Rule of Thumb") guildeline is to increase the current maxsize (in our case, 32GB) by 10-15%, which is approximately 5GB.
How to workaround:
Based on our 10%-15% ROT, the recommended command to add an additional 2nd UNDO tablespace data file, is shown below. This command adds a 2nd file to the UNDO tablespace, starting with an initial size of 5GB, incremented as needed by 5GB, up to a max 32GB size. This will make a grand total of 64GB for your UNDO tablespace. Note that these values can be changed for each system, as appropriate. Additional datafiles can also be added from the Oracle OEM UI, but this sql command (executed as sysdba) is more direct.
ALTER TABLESPACE "UNDOTBS1" ADD DATAFILE '+DG01' SIZE 5120M AUTOEXTEND ON NEXT 5120M MAXSIZE 32767M;
Long Term Consequences:
Once an additional datafile is added to the Undo Tablespace, the ORA-30036 error should not been seen. The actual instance use of the new 'space' can be seen by accessing the Tablespace options from the Oracle OEM UI display. The new 'size' as well as datafile usage can be seen from this UI.
Note that once the Undo Tablespace is increased in size, there is really no way to physically 'shrink' it. There is a way to 'functionally' shrink this tablespace, and that is by creating a new, smaller tablespace. Since only 1 tablespace can be active at any one time, the original 'large' file would be disabled and the new 'smaller' file would be enabled, thus effectively making it 'smaller'. Unless current diskspace allocation becomes dysfunctionally small, there is typically no need to change or reduce the file size of the Undo Tablespace datafile(s), unless there were special creation considerations to begin with. If this is a special case, please contact Aveksa Technical Support for guidance.