Oracle重建UNDO表空间
Oracle重建UNDO表空间
采⽤delete的⽅法删除⼀个100G表中的部分历史数据，结果删除过程出现了ORA-30036:⽆法按8扩展段的错误，原因是undo表空间满了。
1.使⽤如下语句检查表空间，发现表空间已经到了原来的最⼤设定值，99.7%已经被使⽤；
select b.tablespace_name as "表空间",      b.file_name as "物理⽂件名",
b.bytes / 1024 / 1024 as "当前⼤⼩(M)",
打动人心爱情表白句子(b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 as "已使⽤(M)",
substr((b.bytes - sum(nvl(a.bytes, 0))) / (b.bytes) * 100, 1, 5) as "使⽤率(%)",空调的清洗
case b.autoextensible
when 'YES' then '是'
else '否'
end as "是否⾃增",
b.maxbytes / 1024 / 1024 as "⾃增最⼤容量(M)"
from dba_free_space a, dba_data_files b
where a.file_id = b.file_id
and a.tablespace_name in ('UNDOTBS1')
group by b.tablespace_name, b.file_name, b.bytes, b.autoextensible, b.maxbytes
人外有人order by b.tablespace_name;
2.创建undo表空间UNDOTBS2
萧蔷整容前后create undo tablespace undotbs2 datafile 'D:\ORACLEDB\ORADATA\QQS\UNDOTBS02.DBF' size 1024M reuse autoextend on next 100M maxsize unlimited;
alter system set undo_tablespace=undotbs2 scope=both;
3.更spfile配置⽂件
alter  system set undo_tablespace=undotbs2 scope=both;
4.检查系统是否已经切换到undotbs2上，确认undotbs1已经offline
select  segment_name, tablespace_name, status from dba_rollback_segs;
5.删除原来的undotbs1
二维码链接泡椒鸡爪drop  tablespace undotbs1 including  contents;
6.删除⽂件UNDOTBS01.DBF

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。