Solution to insufficient disk space caused by the large Oracle 10g UNDO tablespace

InOracle 10gIn the database applicationUNDO tablespaceThe disk space is too large to crash. After analyzing the problem, the main reasons for the problem are summarized as follows:

1. There is a large volume of transactions that allow Oracle Undo to automatically expand, resulting in excessive disk space occupation;

2. There are large transactions that have not been contracted or are not committed by the system;

Note:This problem is normal in Oracle system management. Pay attention to disk space monitoring during routine maintenance.

Oracle 10 Gb has the feature of Automatic Undo Retention Tuning. The Set undo_retention parameter is just a guiding value. The default value is 900 seconds, Oracle automatically adjusts Undo (beyond the time set by undo_retention) to ensure that there is no Ora-1555 error .. Query V $ UNDOSTAT. This view records the usage of the UNDO tablespace within four days. You can query the DBA_HIST_UNDOSTAT view for more than four days.) The tuned_undoretention field is available in version 10 GB and 9I is unavailable) if the transaction volume is not extensible, the Oracle will consider the remaining space.) The optimal retenton time is automatically calculated after sampling ..

1) query retention Value

Show parameter undo_retention

The query automatically calculates the optimal retenton time.

Select tuned_undoretention, maxquerylen, maxqueryid from v $ undostat;

2) Change the retention Value

Alter system set undo_retention = 10800 SCOPE = BOTH;

This will lead to potential problems for a database with uneven transaction volume distribution-Undo may run out during batch processing, and this state will continue and will not be released.

There are three methods to cancel 10 Gb auto UNDO Retention Tuning:

(1) There is a patch for 10.2.0.2/10.2.0.3. This bug has been fixed in 10.2.0.4. We recommend that you fix the patch when it is stopped.

(2) set the implicit parameter _ smu_debug_mode = 33554432 and correct the tuned_undoretention Value algorithm to max (maxquerylen secs + 300, undo_retention ), it is not recommended to use SQL> Alter system set "_ smu_debug_mode" = 33554432;

(3) set the implicit parameter _ undo_autotune = false to disable automatic undo retention adjustment. We do not recommend that you use SQL> Alter system set "_ undo_autotune" = false; from metalink 420525.1: automatic Tuning of Undo_retention Causes Space Problems.

Solution:

1. Start SQLPLUS and log on to the database with sys.

 
 

  
  
#su - oracle  
  
  
$>sqlplus / as sysdba 
 
 

2. Find the UNDO tablespace name of the database and determine the UNDO tablespace used by the current routine: Show parameter undo_tablespace.

3. Confirm the UNDO tablespace;

 
 

  
  
SQL> select name from v$tablespace;  
  
  
NAME  
  
  
------------------------------  
  
  
.......  
  
  
UNDOTBS1 
 
 

4. Check the space occupied by the UNDO tablespace in the database and the storage location of the data files;

SQL> select file_name, bytes/1024/1024 from dba_data_files where tablespace_name like 'undotbs % ';

5. Check the usage of the rollback segment. Which user is using the resources of the rollback segment? If there is a user, it is best to change the time, especially in the production environment ).

 
 

  
  
SQL> select s.username, u.name from v$transaction t,v$rollstat r, v$rollname u,v$session s  
  
  
where s.taddr=t.addr and t.xidusn=r.usn and r.usn=u.usn order by s.username; 
 
 

6. Check the UNDO Segment status;

 
 

  
  
SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize; 
 
 

7. Create a New UNDO tablespace and set automatic expansion parameters;

 
 

  
  
SQL> create undo tablespace undotbs2 datafile '/opt/oracle/oradata/ge01/UNDOTBS2.dbf' size 100m reuse autoextend on next 50m maxsize 5000m; 
  
  
Tablespace created.
 
 

8. Modify the spfile configuration file dynamically;

 
 

  
  
SQL> alter system set undo_tablespace=undotbs2 scope=both;  
  
  
System altered. 
 
 

9. Wait for all undo segment offline of the original UNDO tablespace;

 
 

  
  
select usn,xacts,status,rssize/1024/1024,hwmsize/1024/1024, shrinks from   v$rollstat order by rssize; 
 
 

10. Run the following command to check whether all UNDO segments in the UNDO tablespace are ONLINE;

 
 

  
  
select usn,xacts,status,rssize/1024/1024,hwmsize/1024/1024, shrinks from v$rollstat order by rssize; 
 
 

11. Delete the original UNDO tablespace;

 
 

  
  
SQL> drop tablespace undotbs1 including contents;  
  
  
Tablespace dropped. 
 
 

12. confirm whether the deletion is successful;

 
 

  
  
SQL> select name from v$tablespace;  
  
  
NAME  
  
  
------------------------------  
  
  
.......  
  
  
UNDOTBS2  
  
  
12 rows selected. 
 
 

13. Update pfile

 
 

  
  
SQL> create pfile from spfile;  
  
  
File created. 
 
 

14. The data file in the volume except the original UNDO tablespace is named as the result of execution in the step.

 
 

  
  
#rm $ORACLE_BASE/oradata/$ORACLE_SID/undotbs01.dbf 
 
 

The solution to the insufficient disk space caused by the large Oracle 10g UNDO tablespace is described here. I hope this introduction will help you gain some benefits!