Oracle-Flashback使用 -02

4.UNDO

alter tablespace undotbs1 retentionguarantee[noguarantee]

使用到的视图：

select*fromgv$rollname

select*fromgv$rollstat

select*fromdba_rollback_segs

select*fromdba_undo_extents

 

(a).strings命令得到回滚段的信息：

[oracle@deer ~]$ strings /u01/oradata/deer/system01.dbf | grep _SYSSMU|cut -d $ -f 1 |sort -u

             and substr(drs.segment_name,1,7) != '_SYSSMU'

D'             and substr(drs.segment_name,1,7) != ''_SYSSMU'' ' );

_SYSSMU10_3550978943

_SYSSMU10_3904554333

_SYSSMU11_286947212

_SYSSMU12_3068564564

_SYSSMU13_2761193625

_SYSSMU1_3780397527

_SYSSMU14_2421411996

_SYSSMU15_1683924174

_SYSSMU16_2313212396

_SYSSMU17_2041439332

_SYSSMU1_783380902

_SYSSMU18_2800789714

_SYSSMU19_53723967

_SYSSMU20_3850939844

_SYSSMU2_2232571081

_SYSSMU2_3138176977

_SYSSMU3_1645411166

_SYSSMU3_2097677531

_SYSSMU4_1152005954

_SYSSMU4_870421980

_SYSSMU5_1527469038

_SYSSMU5_2525172762

_SYSSMU6_2443381498

_SYSSMU6_3753507049

_SYSSMU7_1260614213

_SYSSMU7_3286610060

_SYSSMU8_2012382730

_SYSSMU8_2806087761

_SYSSMU9_1424341975

_SYSSMU9_973944058

 

(b)查询正处于事务的undo信息：

create view user_undo_stat

as

select s.USERNAME,t.START_TIME,rn.name,t.UBAFIL,t.UBABLK,log_io,phy_io from

v$transaction t,v$rollname rn,v$rollstat rs,v$session s

where t.XIDUSN=rn.usn and t.SES_ADDR=s.SADDR and rs.USN=rn.usn

and s.SID=(select sid from v$mystat where rownum=1)

 

create public synonym user_undo_stat for user_undo_stat

 

grant select on user_undo_stat to public

 

(c).删除AUM下的回滚段

SQL> alter rollback segment "_SYSSMU1_3780397527$" offline;

Rollback segment altered.

SQL> select name,status from v$rollname,v$rollstat where v$rollstat.usn=v$rollname.usn;

NAME                          STATUS

------------------------------ ---------------

SYSTEM                        ONLINE

_SYSSMU1_3780397527$          ONLINE

_SYSSMU2_2232571081$          ONLINE

_SYSSMU3_2097677531$          ONLINE

_SYSSMU4_1152005954$          ONLINE

_SYSSMU5_1527469038$          ONLINE

_SYSSMU6_2443381498$          ONLINE

_SYSSMU7_3286610060$          ONLINE

_SYSSMU8_2012382730$          ONLINE

_SYSSMU9_1424341975$          ONLINE

_SYSSMU10_3550978943$         ONLINE

SQL> drop rollback segment "_SYSSMU1_3780397527$"

 2 ;

drop rollback segment "_SYSSMU1_3780397527$"

*

ERROR at line 1:

ORA-30025: DROP segment '_SYSSMU1_3780397527$' (in undo tablespace) not allowed

AUM情况下，offline不能直接删除UNDO segment

需要修改隐藏参数：

SQL> select ksppinm,ksppdesc from x$ksppi where ksppinm like '%_smu_debug_mode%';

KSPPINM             KSPPDESC

-------------------- ----------------------------------------------------------------------------------------------------

_smu_debug_mode     <debug-flag> - set debug event for testing SMU operations

SQL>alter system set "_smu_debug_mode"=4;

System altered.

SQL> alter rollback segment "_SYSSMU1_3780397527$" offline;

Rollback segment altered.

查询v$rollstat发现offline的segment不存在了(11g)。

 

SQL> select name,status from v$rollname,v$rollstat where v$rollstat.usn=v$rollname.usn;

NAME                          STATUS

------------------------------ ---------------

SYSTEM                        ONLINE

_SYSSMU2_2232571081$          ONLINE

_SYSSMU3_2097677531$          ONLINE

_SYSSMU4_1152005954$          ONLINE

_SYSSMU5_1527469038$          ONLINE

_SYSSMU6_2443381498$          ONLINE

_SYSSMU7_3286610060$          ONLINE

_SYSSMU8_2012382730$          ONLINE

_SYSSMU9_1424341975$          ONLINE

_SYSSMU10_3550978943$         ONLINE

 

SQL> drop rollback segment "_SYSSMU1_3780397527$";

Rollback segment dropped.

 

_offline_rollback_segments和_corrupted_rollback_segments

_offline_rollback_segments，oracle会假设这个回滚段还是可以用的，会尝试利用回滚段中的内容完成事务的回滚。

_corrupted_rollback_segments，oracle不会做这种假设，也不会做尝试，而是直接把食物标志成提交。

Alter system set “_offline_rollback_segments”=”rollback_name” scope=spfile

Alter system set “_corrupted_rollback_segments”=”rollback_name” scope=spfile