1.查找数据库的UNDO表空间名 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
select name from v$tablespace; |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
2.检查数据库UNDO表空间占用空间情况以及数据文件存放位置; |
|
|
|
|
|
|
|
select file_name, bytes/1024/1024 from dba_data_files; |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
3.查看回滚段的使用情况,哪个用户正在使用回滚段的资源,如果有用户最好更换时间。 |
select s.username, u.name, d.tablespace_name |
|
|
|
|
|
|
|
|
|
|
|
from v$transaction t,v$rollstat r, v$rollname u, v$session s, dba_rollback_segs d |
where s.taddr=t.addr and t.xidusn=r.usn and r.usn=u.usn and r.usn=d.SEGMENT_ID order by s.username; |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
4.检查UNDO Segment状态 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
select usn, xacts, rssize/1024/1024/1024, hwmsize/1024/1024/1024, shrinks |
from v$rollstat order by rssize; |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
5.创建新的UNDO表空间,并设置自动扩展参数; |
|
|
|
|
|
|
|
|
|
|
|
|
create undo tablespace undotbs2 datafile '/home/oracle/product/11.2.0/oradata/srcl/undotbs02.dbf' size 1000m reuse autoextend on next 800m maxsize 10G; |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
6.动态更改spfile配置文件; |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
alter system set undo_tablespace=undotbs2 scope=both; |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
7.等待原UNDO表空间所有UNDO SEGMENT OFFLINE; |
|
|
|
|
|
|
|
|
|
|
|
select segment_name, tablespace_name, status from dba_rollback_segs; |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
8.再执行看UNDO表空间所有UNDO SEGMENT ONLINE; |
|
|
|
|
|
|
|
|
|
|
|
select segment_name, tablespace_name, status from dba_rollback_segs; |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
9.删除原有的UNDO表空间; |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
drop tablespace undotbs1 including contents; |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
10.确认删除是否成功; |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
select name from v$tablespace; |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
11.册除原UNDO表空间的数据文件,其文件名为步骤中执行的结果。 |
|
|
|
|
|
|
#rm /home/oracle/product/11.2.0/oradata/srcl/undotbs01.dbf |
|
|
|