重建undo表空间

undo表空间扩展很大的需要缩容，或出现undo块部分故障的时候，可以考虑对undo表空间进行重建

1. 查询undo表空间及数据文件

-- 查询undo表空间（2钟方式）
select * from v$tablespace where name like '%UNDO%';
select * from  dba_tablespaces where contents='UNDO';

-- 一般默认UNDOTBS1 ，ts#为2
SELECT * FROM v$datafile  where TS#=<ts#>；-- 按需
复制

2. 查看undo表空间是否在使用中

-- 确认UNDOTBS1上没有活动的事务，若有，则等待
select usn,xacts,status from v$rollstat where xacts != 0;

-- 查看正在使用UNDO的段
select * from v$rollname;

-- 查看undo表空间当前状态
select tablespace_name,status,sum(bytes)/1024/1024 MB
from dba_undo_extents
group by tablespace_name,status;

-- 查询正在使用undo的段，若有数据，不允许切换undo表空间
select s.username, u.name ,s.SQL_ID,s.MACHINE
from v$transaction t,v$rollstat r, v$rollname u,v$session s
where s.taddr=t.addr
and t.xidusn=r.usn
and r.usn=u.usn
order by s.username;
复制

3. 若undo在使用，可以通过sql_id查看到执行的语句，进而考虑是否等待完成还是结束会话

-- 正在执行的SQL
SELECT to_char(SQL_FULLTEXT) 正在执行的SQL
FROM v$process a, v$session b, v$sqlarea c
WHERE a.addr = b.paddr
   AND b.sql_hash_value = c.hash_value
   and b.STATUS='ACTIVE'
  and c.SQL_ID='<sql_id>';  -- 替换查找正在使用undo的sql_id
复制

新增临时undo表空间（若嫌麻烦，可以直接用这个，不在转换）

-- 新增
create undo tablespace undotbs3 datafile
'/mnt/oradata/UNDOTBS31.dbf' size 512m  AUTOEXTEND ON NEXT 512M MAXSIZE 32767M;

-- 扩容
alter  tablespace undotbs3 add datafile 
'/mnt/oradata/UNDOTBS32.dbf' size 5120m  AUTOEXTEND ON NEXT 512M MAXSIZE 32767M;
复制

确认无使用时候，切换当前表空间(这里，若spfile值为空，会报错)

alter system set undo_tablespace=UNDOTBS3 scope=both;

-- 若spfile值为空，使用下面的，后续重启数据库时候的时候，注意检查undo表空间
alter system set undo_tablespace=UNDOTBS3 ;
复制

4. 确认都没有用原先旧的undo后，删除undo表空间

drop tablespace UNDOTBS1   including contents and datafiles;
复制

5.可能会遇到的问题

1. 若还有未完成的事务,是不允许删除的

SQL> drop tablespace UNDOTBS1   including contents and datafiles;
drop tablespace UNDOTBS1   including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
复制

2.在日志文件中也可以看到，且可以看到切换完成输出

Wed Jun 14 17:25:04 2023
drop tablespace UNDOTBS1   including contents and datafiles
ORA-30013 signalled during: drop tablespace UNDOTBS1   including contents and datafiles...

Wed Jun 14 17:25:34 2023
[8496] Undo Tablespace 2 successfully switched out.
复制

PS：若有需要，重复上面步骤，可与将undo表空间换回是 UNDOTBS1