常用sql语句-回滚段状态

回滚段状态（检查活动、过期、未使用段大小）

select a.owner,a.tablespace_name,a.status,a.use_mb,
      round(a.use_mb / b.total_mb * 100) use_precent
  from (select owner,tablespace_name,status,
              sum(bytes) / 1024 / 1024 use_mb
          from dba_undo_extents
        where status='ACTIVE'
        group by owner, tablespace_name, status) a,
      (select tablespace_name, sum(bytes) / 1024 / 1024 total_mb
          from dba_data_files
        where tablespace_name like '%UNDO%'
        group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by 1, 2, 3;

回滚段状态（筛选使用超过500MB单个进程）

select a.inst_id,a.username,a.sql_id,a.prev_sql_id,b.name,c.used_ublk*8192/1024/1024 use_mb
  from gv$session a, v$rollname b, v$transaction c
where a.saddr = c.ses_addr
  and b.usn = c.xidusn
  and c.used_ublk * 8192 / 1024 / 1024 > 50
order by 5;