Oracle回滚当前循环,oracle查看回滚段

1.监控PGA的视图：

v$sql_workarea_active、v$sql_workarea、v$sesstat、v$process、v$sysstat、v$sql_workarea_histogram等。

2.使用下面的视图查看Oracle建议的评估设置:

Select pga_target_for_estimate/1024/1024 ||'M' "Estimate PGA Target"

,estd_pga_cache_hit_percentage "Cache Hit(%)"

,estd_extra_bytes_rw/1024/1024 ||'M' "Extra Read/Write"

,estd_overalloc_count "Over alloc count"

From v$pga_target_advice

3.查看数据文件的SCN号： select file#, status, fuzzy, checkpoint_change# from v$datafile_header;

4.查看UNDO表空间上回滚段得状态

select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks  from v$rollstat order by rssize;

5.更改回滚段得状态：ALTER ROLLBACK SEGMENT rollback_segment OFFLINE;(select * from v$rollname) 或者查询SELECT segment_name,tablespace_name,owner,status FROM dba_rollback_segs;

回收会滚段：ALTER ROLLBACK SEGMENT rbs01 SHRINK TO 2M(不知道大小就写TO integer);

删除回滚段：DROP ROLLBACK SEGMENT rollback_segment    ----对应的数据字典DBA_ROLLBACK_SEGS

创建一个新的UNDO 表空间：create undo tablespace undotbs2 datafile '/u01/oradata/untbs2.dbf' size 10M

切换UNDO 表空间：alter system set undo_tablespace=undotbs2 scope=both;

加一个数据文件：alter tablespace undotbs2 add datafile '/dev/vgdata01/rlv_ora_log12';裸设备

6.回滚段当前活动的事物：SELECT s.username,t.xidusn,t.ubafil,t.ubablk,t.used_ublk  FROM v$session s,v$transaction t

————————————

alter tablespace undotbs1 retention guarantee;

当把UNDO_RENTION设置为0时，开启undo retention 自动优化，为了保证最长事务的UNDO，系统自动最大限制地不去增加EXTENT