Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to recycle and switch UNDO tablespaces. The article is rich in content and analyzes and describes it from a professional point of view. I hope you can get something after reading this article.

1. View undo related parameters

Show parameter undo

NAME TYPE VALUE

Undo_management string AUTO

Undo_retention integer 900

Undo_tablespace string UNDOTBS

two。 View the data file for the current undo tablespace

Select file_name,bytes from dba_data_files where tablespace_name = 'UNDOTBS1'

3. Maximum number of transactions per undo segment (10g has been deprecated)

10g default is one undo segment, at most one transaction. If there is not enough undo segment, the undo segment will be created automatically.

When the undo table space is insufficient, the undo segment can contain more than one transaction (a wait may occur in the undo segment header)

Show parameter rollback

NAME TYPE VALUE

Transactions_per_rollback_segment integer 5

4. View the status of undo rollback segment

Select * from dba_rollback_segs

Select a.usn, b.name, a.status, a.xacts, a.rssize/1024/1024/1024, a.hwmsize/1024/1024/1024, a.shrinks from v$rollstat a, v$rollname b where a.usn = b.usn order by a.rssize

5. Create a new rollback tablespace

Create undo tablespace undo datafile'/ usr/lib/oracle/xe/oradata/XE/undo.dbf' size 10m

6. Toggle undo tablespace

Alter system set undo_tablespace = undotbs scope = both

7. Confirm the status of the rollback segment

Repeatedly confirm the status of undo rollback segments until all rollback segments are in online state, and there is no pending_offline status. It is best to delete the rollback segment after reaching the time limit of undo_retention.

Select a.usn, b.name, a.status, a.xacts, a.rssize/1024/1024/1024, a.hwmsize/1024/1024/1024, a.shrinks from v$rollstat a, v$rollname b where a.usn = b.usn order by a.rssize

8. Delete the original rollback tablespace

Drop tablespace undotbs including contents and datafiles

The above is the editor for you to share how to carry out UNDO tablespace recycling and switching, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.