# 创建撤销表空间
SQL> create undo tablespace undotbs1
2 datafile ‘/oradata/fgedudb/undotbs01.dbf’
3 size 100M
4 autoextend on
5 next 100M
6 maxsize 10G;Tablespace created.

# 查看撤销表空间
SQL> select
tablespace_name,
contents,
status,
logging,
extent_management,
allocation_type
from dba_tablespaces
where contents = ‘UNDO’;TABLESPACE_NAME CONTENTS STATUS LOGGING EXTENT_MAN ALLOCATIO
—————————— ——— ——— ——— ———- ———
UNDOTBS1 UNDO ONLINE LOGGING LOCAL SYSTEM

# 设置默认撤销表空间
SQL> alter system set undo_tablespace = undotbs1 scope=both;System altered.

# 查看当前撤销表空间
SQL> show parameter undo_tablespace;NAME TYPE VALUE
———————————— ———– ——————————
undo_tablespace string UNDOTBS1

# 为撤销表空间添加数据文件
SQL> alter tablespace undotbs1
2 add datafile ‘/oradata/fgedudb/undotbs02.dbf’
3 size 100M
4 autoextend on
5 next 100M
6 maxsize 10G;Tablespace altered.

# 修改数据文件大小
SQL> alter database datafile ‘/oradata/fgedudb/undotbs01.dbf’
2 resize 200M;Database altered.

# 启用数据文件自动扩展
SQL> alter database datafile ‘/oradata/fgedudb/undotbs01.dbf’
2 autoextend on
3 next 100M
4 maxsize 10G;Database altered.

# 切换撤销表空间
SQL> create undo tablespace undotbs2
2 datafile ‘/oradata/fgedudb/undotbs03.dbf’
3 size 100M
4 autoextend on
5 next 100M
6 maxsize 10G;Tablespace created.

SQL> alter system set undo_tablespace = undotbs2 scope=both;System altered.

# 删除旧的撤销表空间（确保没有事务使用）
SQL> drop tablespace undotbs1 including contents and datafiles;Tablespace dropped.

# 查看撤销表空间总大小和使用情况
SQL> select
tablespace_name,
sum(bytes)/1024/1024 as total_mb,
sum(decode(status, ‘ACTIVE’, bytes, 0))/1024/1024 as active_mb,
sum(decode(status, ‘UNEXPIRED’, bytes, 0))/1024/1024 as unexpired_mb,
sum(decode(status, ‘EXPIRED’, bytes, 0))/1024/1024 as expired_mb,
sum(decode(status, ‘ACTIVE’, bytes, 0))/sum(bytes)*100 as active_pct
from dba_undo_extents
group by tablespace_name;TABLESPACE_NAME TOTAL_MB ACTIVE_MB UNEXPIRED_MB EXPIRED_MB ACTIVE_PCT
——————– ———- ———- ———— ———- ———-
UNDOTBS1 100 10 50 40 10

# 查看撤销表空间数据文件使用情况
SQL> select
file_name,
tablespace_name,
bytes/1024/1024 as size_mb,
autoextensible,
maxbytes/1024/1024 as max_mb
from dba_data_files
where tablespace_name like ‘%UNDO%’;FILE_NAME TABLESPACE_NAME SIZE_MB AUT MAX_MB
—————————————— —————- ———- — ———-
/oradata/fgedudb/undotbs01.dbf UNDOTBS1 100 YES 10240

# 查看当前撤销统计信息
SQL> select
to_char(begin_time, ‘YYYY-MM-DD HH24:MI:SS’) as begin_time,
to_char(end_time, ‘YYYY-MM-DD HH24:MI:SS’) as end_time,
undotsn,
undoblks,
txncount,
maxquerylen,
maxconcurrency,
unxpstealcnt,
expstealcnt
from v$undostat
order by begin_time desc;BEGIN_TIME END_TIME UNDOTSN UNDOBLKS TXNCOUNT MAXQUERYLEN MAXCONCURRENCY UNXPSTEALCNT EXPSTEALCNT
——————- ——————- ———- ———- ———- ———– ————– ———— ———–
2026-03-31 10:00:00 2026-03-31 10:10:00 1 1000 500 600 10 0 0
2026-03-31 09:50:00 2026-03-31 10:00:00 1 1200 600 700 12 0 0

# 查看回滚段状态
SQL> select
segment_name,
tablespace_name,
status,
instance_num,
shrink,
wraps,
extends,
aveshrink,
hwmsize
from v$rollstat;SEGMENT_NAME TABLESPACE_NAME STATUS INSTANCE_NUM SHRINK WRAPS EXTENDS AVESHRINK HWMSIZE
——————– ——————– ———- ———— —— ———- ———- ———- ———-
SYSTEM SYSTEM ONLINE 0 NO 100 10 0 2097152
_SYSSMU1_123456789$ UNDOTBS1 ONLINE 0 NO 200 20 0 4194304
_SYSSMU2_234567890$ UNDOTBS1 ONLINE 0 NO 150 15 0 3145728

# 查看回滚段详细信息
SQL> select
r.segment_name,
r.tablespace_name,
r.status,
r.initial_extent,
r.next_extent,
r.min_extents,
r.max_extents,
rs.xacts,
rs.writes,
rs.gets,
rs.optsize
from dba_rollback_segs r, v$rollstat rs
where r.segment_id = rs.usn
order by r.segment_name;SEGMENT_NAME TABLESPACE_NAME STATUS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS XACTS WRITES GETS OPTSIZE
——————– ——————– ———- ————– ———– ———– ———– ———- ———- ———- ———-
SYSTEM SYSTEM ONLINE 524288 524288 2 101 0 10000 20000
_SYSSMU1_123456789$ UNDOTBS1 ONLINE 524288 524288 2 101 5 20000 40000 1048576
_SYSSMU2_234567890$ UNDOTBS1 ONLINE 524288 524288 2 101 3 15000 30000 1048576

# 查看使用回滚段的事务
SQL> select
r.segment_name,
s.sid,
s.serial#,
s.username,
s.program,
s.machine,
t.start_time,
t.used_ublk,
t.used_urec
from v$rollstat r,
v$transaction t,
v$session s
where r.usn = t.xidusn
and t.addr = s.taddr
order by t.start_time;SEGMENT_NAME SID SERIAL# USERNAME PROGRAM MACHINE START_TIME USED_UBLK USED_UREC
——————– ———- ———- ———- —————– ————- ——————– ———- ———-
_SYSSMU1_123456789$ 123 45678 FGAPP_USER app.exe server1 03/31/26 10:00:00 5 10

# 查看当前RETENTION设置
SQL> select
tablespace_name,
retention
from dba_tablespaces
where contents = ‘UNDO’;TABLESPACE_NAME RETENTION
—————————— ———–
UNDOTBS1 NOGUARANTEE

# 启用RETENTION GUARANTEE
SQL> alter tablespace undotbs1 retention guarantee;Tablespace altered.

# 验证RETENTION GUARANTEE已启用
SQL> select
tablespace_name,
retention
from dba_tablespaces
where contents = ‘UNDO’;TABLESPACE_NAME RETENTION
—————————— ———–
UNDOTBS1 GUARANTEE

# 禁用RETENTION GUARANTEE
SQL> alter tablespace undotbs1 retention noguarantee;Tablespace altered.

# 设置UNDO_RETENTION参数
SQL> alter system set undo_retention = 3600 scope=both;System altered.

# 查看UNDO_RETENTION参数
SQL> show parameter undo_retention;NAME TYPE VALUE
———————————— ———– ——————————
undo_retention integer 3600