What if the Oracle undo tablespace is full?

This article mainly introduces the full Oracle undo table space how to do, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor with you to understand.

Resolution steps:

1. Start SQLPLUS and log in to the database using sys.

# su-oracle

$> sqlplus / as sysdba

two。 Find the UNDO tablespace name of the database and determine which UNDO tablespace is being used by the current routine:

Show parameter undo_tablespace .

3. Confirm UNDO tablespace

SQL > select name from vested tablespace; NAME-- UNDOTBS1

4. Check the database UNDO table space occupancy and data file storage location

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, and if there is a user, it is best to replace it (especially in the production environment).

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

6. Check UNDO Segment status

SQL > select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinksfrom v$rollstat order by rssize; Thank you for reading this article carefully. I hope the article "what to do when the Oracle undo tablespace is full" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!