What if the undo tablespace is full?

This article will explain in detail what to do when the undo table space is full. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Ora_30036 errors are sometimes reported when inserting data or update, resulting in data insertion failure

The reason is that the undo table space of the database is insufficient, and there are two ways to solve the problem of full undo table space.

Method 1:

Add data files for undo tablespaces

Alter tablespace undo add datafile'/ u01 size reuse undo02.dbf' oracle 100m

Method 2:

Toggle undo tablespace

1. Create a new tablespace undotbs3

Undo03.dbf' create undo tablespace undotbs3 datafile'/ u01amp oracle

2. Switch to the new Undo tablespace

Alter system set undo_tablespace=undotbs3 scope=both

3. Delete the original undo tablespace. If you can't delete it, you can restart the library and then delete it.

Drop tablespace undo including contents and datafiles cascade constraints

Note: if only the drop tablespace undo deletes the records of the control file, the file will not be physically deleted, and the undo can only be deleted if it is not in use.

The default Undo_retention is only 15 minutes, and this default value generally does not meet the needs of the system.

The general suggestion is to change it to three hours, so as to give more time to prepare in case of a situation.

-- modify the retention time method of undo_retention rollback segment:

The retention time of the rollback segment is displayed first, in seconds.

Show parameter undo_retention

The default time for undo_retention in oracle is 900s.

If you want to change the default time of undo_retention, modify it with the following sentence:

ALTER SYSTEM SET undo_retention=10800 SCOPE=BOTH

This is the end of the article on "what to do when the undo table space is full". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it out for more people to see.