Undotbs01.dbf file too large solution

Undotbs01.dbf file too large solution

Reduce the size of the UNDOTBS01.DBF file in the Oracle directory

Log on to Oracle using the sys user

Method 1: reset the tablespace size

Run

Alter database datafile '/opt/oracle/oradata/res/undotbs01.dbf' RESIZE 100 M;

In Windows, execute alter database datafile 'e: \ ORACLE \ ORADATA \ undotbs01.dbf' RESIZE 100 M;

'/Opt/oracle/oradata/res/undotbs01.dbf' indicates the storage path of the undotbs01.dbf file in your system, and 100M indicates the size After resetting. Modify the size as needed.

If method 1 cannot be executed, use method 2.

Method 2: Create an UNDO tablespace and replace the original UNDO tablespace.

1. Create a new small undo tablespace

Run the following command in Linux:

Create undo tablespace undotbs2 datafile '/opt/oracle/oradata/res/undotbs02.dbf' size 100 M reuse autoextend on;

'/Opt/oracle/oradata/res/undotbs02.dbf' indicates the location where the tablespace file is to be stored. 100M indicates the initial size of the new tablespace.

Run the following command in Windows:

Create undo tablespace UNDOTBS2 DATAFILE 'e: \ oradata \ undotbs02.dbf' SIZE 100 m reuse autoextend on;

2. Set the new tablespace to the system's Undo tablespace.

Run

Alter system set UNDO_TABLESPACE = UNDOTBS2;

3. Restart the database

You can use the sys user to log on from sqlplus, execute the startup force Command, and force restart

4. Delete the old Undo tablespace

Log On with the sys user and run

Drop tablespace UNDOTBS01 including contents and datafiles;

So far, the UNDOTBS01.DBF file has been reduced. To restore the original tablespace name, run method 2 again.