Managing Undo Data

Undo segments purpose

 

  .Transaction rollback

  .Transaction recovery

  .Read consistency

 

Automatic undo management

  .parameters

    --undo_management [auto]        <---initialization parameter

    --undo_tablespace [undotbs]

    --undo_retention  [10800]

    --undo_suppress_error [false]

 

 

Creating undo tablespace[假设原来undo为undotbs1,现新建为undotbs2]

 

  create undo tablespace tablespace_name  [datafile_clause] [extent_clause]

   

1.新建undo tablespace
create undo tablespace undotbs2 datafile 'd:\oracle9i\oradata\undotbs2.dbf' size 5m extent management local; 

2.设置参数
alter system set undo_tablespace=undotbs2;

3.查看原来undotbs1中是否还有事务
select a.name,b.*
from v$rollname a,v$rollstat b
where a.usn=b.usn
  and a.name in (select segment_name
                 from dba_rollback_segs
                 where tablespace_name='UNDOTBS2')

如果查询没有返回行,则可以删除undotbs1表空间

4.删除undo表空间
drop tablespace untotbs1 including contents and datafiles;

 

Resizing undo datafiles

 

alter database datafile 'D:\oracle9i\oradata\rhce\undo02.dbf' resize 4m;

alter database datafile 'D:\oracle9i\oradata\rhce\undo02.dbf' autoextend on next 512k maxsize 3m;

alter tablespace undo02
add datafile 'D:\oracle9i\oradata\rhce\undo22.dbf' size 1m