The ORA-30036 error is a common issue that can occur in an Oracle Database when the system is unable to extend a segment by a specific number of blocks in the undo tablespace.
This error can be caused by a variety of factors, including a lack of free space in the undo tablespace, a corrupted undo tablespace, or a system configuration issue. Some of the key reasons are listed below:
1. Check the undo tablespace for free space: The first step in resolving the ORA-30036 error is to check the undo tablespace for free space. If the undo tablespace is full or nearly full, you will need to add more space to it. You can do this by adding another datafile to the undo tablespace or by increasing the size of an existing datafile.
2. Check for a corrupted undo tablespace: A corrupted undo tablespace can also cause the ORA-30036 error. To check for a corrupted undo tablespace, you can run the following command:
SQL> SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES;
If the status of the undo tablespace is ‘CORRUPT’, you will need to restore the undo tablespace from a backup.
3. Check the system configuration: The ORA-30036 error can also be caused by a system configuration issue. For example, if the Oracle Database is configured with a small undo tablespace, it may not have enough space to handle the undo segments. In this case, you will need to increase the size of the undo tablespace or configure the database with a larger undo tablespace.
4. Check if any long-running queries or transactions: if you see any long-running queries or transactions that are holding undo blocks, the undo tablespace fills up quickly. In such cases, you can try to kill the long-running queries or transactions and check if that resolves the issue.
5. Check if Auto-extend is on: This as such is also related to space issues. If the auto-extend option is turned off for the undo tablespace, you will need to manually add space to the undo tablespace when it becomes full. To check if auto-extend is on, you can run the following command:
SQL> SELECT TABLESPACE_NAME, AUTOEXTENSIBLE FROM DBA_DATA_FILES;
If auto-extend is not enabled, you will need to enable it and increase the size of the undo tablespace.
6. Consider Archiving: If none of the above steps work, you may consider archiving the undo tablespace. Archiving involves moving the undo data that is no longer needed for recovery to an archive location. This will free up space in the undo tablespace and prevent the ORA-30036 error from occurring.
In our case, in one specific scenario, we saw that UNDO tablespace was not releasing space and we had to use below hidden parameters (ONLY after Oracle confirmed in SEV1 SR):
SQL> alter system set "_undo_autotune"=FALSE scope=BOTH SID='*'; SQL> alter system set "_smu_debug_mode"=33554432 scope=BOTH SID='*';
Please note that hidden parameters are to be used only after confirmation from Oracle Support.
So, the ORA-30036 error is a common issue that can occur in an Oracle Database. By checking some of the above things we can resolve this error.
- Oracle Database Service for Azure (ODSA) 3 – Database Services - May 1, 2023
- Oracle Database Service for Azure (ODSA) 2 – Interconnect Details - April 30, 2023
- Oracle Database Service for Azure (ODSA) 1 – Fundamentals - April 29, 2023