SQL> CREATE UNDO TABLESPACE undo
2 DATAFILE ‘/u01/app/oracle/oradata/ORCL/undo01.dbf’ SIZE 100M
3 AUTOEXTEND ON NEXT 20M MAXSIZE 1000M;Tablespace created.

SQL> ALTER SYSTEM SET undo_tablespace = ‘UNDO’ SCOPE=BOTH;System altered.

SQL> ALTER SYSTEM SET undo_retention = 900 SCOPE=BOTH;System altered.

— 1. 查看回滚表空间信息
SELECT tablespace_name, status, contents, extent_management
FROM dba_tablespaces
WHERE contents = ‘UNDO’;– 2. 查看回滚表空间数据文件
SELECT tablespace_name, file_name, bytes/1024/1024 AS size_mb,
autoextensible, maxbytes/1024/1024 AS max_size_mb
FROM dba_data_files
WHERE tablespace_name LIKE ‘%UNDO%’;– 3. 修改回滚表空间大小
ALTER TABLESPACE undo
ADD DATAFILE ‘/u01/app/oracle/oradata/ORCL/undo02.dbf’ SIZE 100M
AUTOEXTEND ON NEXT 20M MAXSIZE 1000M;– 4. 调整回滚表空间自动扩展
ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/ORCL/undo01.dbf’
AUTOEXTEND ON NEXT 40M MAXSIZE 2000M;– 5. 重命名回滚表空间
ALTER TABLESPACE undo_old RENAME TO undo_new;– 6. 删除回滚表空间
DROP TABLESPACE undo_old INCLUDING CONTENTS AND DATAFILES;– 7. 切换回滚表空间
ALTER SYSTEM SET undo_tablespace = ‘UNDO_NEW’ SCOPE=BOTH;– 8. 查看回滚段信息
SELECT segment_name, tablespace_name, status, bytes/1024/1024 AS size_mb
FROM dba_rollback_segs;– 9. 查看回滚表空间的使用情况
SELECT tablespace_name, current_bytes/1024/1024 AS current_mb,
max_bytes/1024/1024 AS max_mb,
current_scn, retention_timeout
FROM v$undo_space;

— 1. 监控回滚表空间使用情况
SELECT tablespace_name,
SUM(bytes)/1024/1024 AS total_mb,
SUM(bytes – free_bytes)/1024/1024 AS used_mb,
SUM(free_bytes)/1024/1024 AS free_mb,
ROUND((SUM(bytes – free_bytes)/SUM(bytes))*100, 2) AS used_percent
FROM (
SELECT tablespace_name, bytes,
CASE WHEN autoextensible = ‘YES’ THEN maxbytes ELSE bytes END – bytes AS free_bytes
FROM dba_data_files
WHERE tablespace_name LIKE ‘%UNDO%’
) GROUP BY tablespace_name;– 2. 监控回滚段使用情况
SELECT segment_name, tablespace_name, status, bytes/1024/1024 AS size_mb,
extents, initial_extent/1024/1024 AS initial_mb,
next_extent/1024/1024 AS next_mb
FROM dba_rollback_segs;– 3. 监控回滚表空间的事务信息
SELECT usn, slot, sequence#, status, xidsqn, ubablk, ubafil,
ubaobj, used_ublk, used_urec
FROM v$transaction;– 4. 查看回滚表空间的统计信息
SELECT * FROM v$undostat
ORDER BY begin_time DESC;– 5. 检查回滚表空间的保留时间
SELECT name, value FROM v$parameter WHERE name = ‘undo_retention’;– 6. 检查回滚表空间的自动优化
SELECT * FROM v$parameter WHERE name LIKE ‘undo%’;– 7. 查看回滚表空间的历史使用情况
SELECT * FROM dba_hist_undostat
ORDER BY snap_id DESC;

— 案例：为大型事务系统配置回滚表空间

— 1. 创建主回滚表空间
CREATE UNDO TABLESPACE undo
DATAFILE ‘/u01/app/oracle/oradata/ORCL/undo01.dbf’ SIZE 500M
AUTOEXTEND ON NEXT 50M MAXSIZE 5000M;– 2. 创建大型回滚表空间
CREATE UNDO TABLESPACE undo_large
DATAFILE ‘/u01/app/oracle/oradata/ORCL/undo_large01.dbf’ SIZE 2G
AUTOEXTEND ON NEXT 100M MAXSIZE 10G;– 3. 设置默认回滚表空间
ALTER SYSTEM SET undo_tablespace = ‘UNDO’ SCOPE=BOTH;– 4. 设置回滚表空间的保留时间
ALTER SYSTEM SET undo_retention = 1800 SCOPE=BOTH; — 30分钟

— 5. 为特定用户分配大型回滚表空间
— 注意：回滚表空间是系统级设置，不能为单个用户分配不同的回滚表空间

— 6. 监控回滚表空间使用情况
SELECT tablespace_name,
SUM(bytes)/1024/1024 AS total_mb,
SUM(bytes – free_bytes)/1024/1024 AS used_mb,
SUM(free_bytes)/1024/1024 AS free_mb,
ROUND((SUM(bytes – free_bytes)/SUM(bytes))*100, 2) AS used_percent
FROM (
SELECT tablespace_name, bytes,
CASE WHEN autoextensible = ‘YES’ THEN maxbytes ELSE bytes END – bytes AS free_bytes
FROM dba_data_files
WHERE tablespace_name LIKE ‘%UNDO%’
) GROUP BY tablespace_name;– 7. 监控回滚表空间的事务信息
SELECT usn, slot, sequence#, status, xidsqn, ubablk, ubafil,
ubaobj, used_ublk, used_urec
FROM v$transaction;– 8. 查看回滚表空间的统计信息
SELECT * FROM v$undostat
ORDER BY begin_time DESC;

— 1. 回滚表空间不足问题
— 查看回滚表空间使用情况
SELECT tablespace_name,
SUM(bytes)/1024/1024 AS total_mb,
SUM(bytes – free_bytes)/1024/1024 AS used_mb,
SUM(free_bytes)/1024/1024 AS free_mb,
ROUND((SUM(bytes – free_bytes)/SUM(bytes))*100, 2) AS used_percent
FROM (
SELECT tablespace_name, bytes,
CASE WHEN autoextensible = ‘YES’ THEN maxbytes ELSE bytes END – bytes AS free_bytes
FROM dba_data_files
WHERE tablespace_name LIKE ‘%UNDO%’
) GROUP BY tablespace_name
ORDER BY used_percent DESC;– 解决方案：添加数据文件
ALTER TABLESPACE undo
ADD DATAFILE ‘/u01/app/oracle/oradata/ORCL/undo03.dbf’ SIZE 200M
AUTOEXTEND ON NEXT 50M MAXSIZE 2000M;– 2. 回滚表空间保留时间不足问题
— 查看回滚表空间的保留时间
SELECT name, value FROM v$parameter WHERE name = ‘undo_retention’;– 解决方案：增加保留时间
ALTER SYSTEM SET undo_retention = 3600 SCOPE=BOTH; — 60分钟

— 3. 回滚表空间事务过多问题
— 查看当前事务
SELECT usn, slot, sequence#, status, xidsqn, ubablk, ubafil,
ubaobj, used_ublk, used_urec
FROM v$transaction;– 查看长时间运行的事务
SELECT s.sid, s.serial#, s.username, t.start_time,
(SYSDATE – t.start_time) * 24 * 60 AS minutes_running
FROM v$session s, v$transaction t
WHERE s.saddr = t.saddr
ORDER BY minutes_running DESC;– 解决方案：终止长时间运行的事务
ALTER SYSTEM KILL SESSION ‘sid,serial#’ IMMEDIATE;– 4. 回滚表空间碎片问题
— 检查回滚表空间碎片
SELECT tablespace_name,
COUNT(*) AS extent_count,
MAX(blocks) AS max_blocks,
MIN(blocks) AS min_blocks,
AVG(blocks) AS avg_blocks
FROM dba_extents
WHERE tablespace_name LIKE ‘%UNDO%’
GROUP BY tablespace_name;– 解决方案：重建回滚表空间
CREATE UNDO TABLESPACE undo_new
DATAFILE ‘/u01/app/oracle/oradata/ORCL/undo_new01.dbf’ SIZE 500M
AUTOEXTEND ON NEXT 50M MAXSIZE 5000M;ALTER SYSTEM SET undo_tablespace = ‘UNDO_NEW’ SCOPE=BOTH;DROP TABLESPACE undo INCLUDING CONTENTS AND DATAFILES;CREATE UNDO TABLESPACE undo
DATAFILE ‘/u01/app/oracle/oradata/ORCL/undo01.dbf’ SIZE 500M
AUTOEXTEND ON NEXT 50M MAXSIZE 5000M;ALTER SYSTEM SET undo_tablespace = ‘UNDO’ SCOPE=BOTH;DROP TABLESPACE undo_new INCLUDING CONTENTS AND DATAFILES;

— 1. 回滚表空间无法扩展问题
— 检查回滚表空间数据文件
SELECT tablespace_name, file_name, bytes/1024/1024 AS size_mb,
autoextensible, maxbytes/1024/1024 AS max_size_mb
FROM dba_data_files
WHERE tablespace_name LIKE ‘%UNDO%’;– 解决方案：检查文件系统空间，添加数据文件

— 2. 回滚表空间事务回滚失败问题
— 查看当前事务
SELECT usn, slot, sequence#, status, xidsqn, ubablk, ubafil,
ubaobj, used_ublk, used_urec
FROM v$transaction;– 解决方案：检查事务状态，必要时重启数据库

— 3. 回滚表空间损坏问题
— 检查回滚表空间状态
SELECT tablespace_name, status
FROM dba_tablespaces
WHERE contents = ‘UNDO’;– 解决方案：重建回滚表空间
CREATE UNDO TABLESPACE undo_new
DATAFILE ‘/u01/app/oracle/oradata/ORCL/undo_new01.dbf’ SIZE 500M
AUTOEXTEND ON NEXT 50M MAXSIZE 5000M;ALTER SYSTEM SET undo_tablespace = ‘UNDO_NEW’ SCOPE=BOTH;DROP TABLESPACE undo INCLUDING CONTENTS AND DATAFILES;– 4. 回滚表空间使用过高问题
— 查看回滚表空间的使用情况
SELECT tablespace_name, current_bytes/1024/1024 AS current_mb,
max_bytes/1024/1024 AS max_mb,
current_scn, retention_timeout
FROM v$undo_space;– 查看长时间运行的事务
SELECT s.sid, s.serial#, s.username, t.start_time,
(SYSDATE – t.start_time) * 24 * 60 AS minutes_running
FROM v$session s, v$transaction t
WHERE s.saddr = t.saddr
ORDER BY minutes_running DESC;– 解决方案：终止长时间运行的事务，增加回滚表空间大小