Oracle rollback segment and rollback tablespace

Last night, a large batch of deletion was performed, and the delete operation was used. It took about six hours, causing the rollback segment to automatically expand to nearly 30 GB. (Remember that you must use scripts to Perform Batch deletion and submit transactions in batches. In this way, it will not occupy too many UNDO tablespaces !) I searched for a common method from the Internet to change

Last night, a large batch of deletion was performed, and the delete operation was used. It took about six hours, causing the rollback segment to automatically expand to nearly 30 GB. (Remember that you must use scripts to Perform Batch deletion and submit transactions in batches. In this way, it will not occupy too many UNDO tablespaces !) I searched for a common method from the Internet to change

Last night, a large batch of deletion was performed, and the delete operation was used. It took about six hours, causing the rollback segment to automatically expand to nearly 30 GB. (Remember that you must use scripts to Perform Batch deletion and submit transactions in batches. In this way, it will not occupy too many UNDO tablespaces !)

I found a common method on the Internet to replace the New UNDO tablespace, delete the original UNDO tablespace, and release the disk capacity.

The specific method is as follows: (refer to the online materials)

1. query rollback segment information: The status is ONLINE, and the current UNDO tablespace is undotbs1

SQL> select segment_name, owner, tablespace_name, status from dba_rollback_segs;

SEGMENT_NAME OWNER TABLESPACE_NAME STATUS

----------------------------------------------------------------------------------

SYSTEM SYS SYSTEM ONLINE

_ SYSSMU1 $ PUBLIC UNDOTBS1 ONLINE

_ SYSSMU2 $ PUBLIC UNDOTBS1 ONLINE

_ SYSSMU3 $ PUBLIC UNDOTBS1 ONLINE

_ SYSSMU4 $ PUBLIC UNDOTBS1 ONLINE

_ SYSSMU5 $ PUBLIC UNDOTBS1 ONLINE

_ SYSSMU6 $ PUBLIC UNDOTBS1 ONLINE

_ SYSSMU7 $ PUBLIC UNDOTBS1 ONLINE

_ SYSSMU8 $ PUBLIC UNDOTBS1 ONLINE

_ SYSSMU9 $ PUBLIC UNDOTBS1 ONLINE

_ SYSSMU10 $ PUBLIC UNDOTBS1 ONLINE

11 rows selected.

2. Create a New rollback segment:

SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE 'd:/oracle/oradata/oradev/UNDOTBS2.dbf 'size 50 M

Note: UNDOTBS2 is the name of the new rollback segment. 'D:/oracle/oradata/oradev/UNDOTBS2.dbf is the tablespace data file address, which can be set as needed.

3. Switch the rollback segment:

SQL> alter system set undo_tablespace = undotbs2 scope = both;

By default, the UNDO tablespace is the newly created undotbs2.

4. After restarting the database, you can delete the original rollback segment to release disk space.

SQL> drop rollback segment undotbs1;

SQL> drop tablespace undotbs1 including contents and datafiles;

5. The size of the rollback segment can be adjusted as needed or changed to automatic scaling.

The following is a tip provided by netizens:

When imp imports data, the system prompts an error indicating that the rollback segment cannot be expanded.

Solution: add the commit = y parameter to imp for direct submission to avoid occupying large rollback segments.