oracle 6

监控事务回滚进度：
select n.USED_UBLK*x.VALUE/1024/1024 ,s.*
  from gv$transaction n, gv$session s,
       v$parameter   x
 where n.INST_ID = s.INST_ID  
   and n.ADDR = s.TADDR   
   and x.name = 'db_block_size'   
   and n.INST_ID = 2;
--
select ((select (nvl(sum(bytes), 0))
           from dba_undo_extents
          where tablespace_name  = 'UNDOTBS1'                
            and status in ('ACTIVE'/*, 'UNEXPIRED'*/ )) * 100) /
       (select sum(bytes)
          from dba_data_files
         where tablespace_name    = 'UNDOTBS1')
                  "PCT_INUSE"
  from dual;










Script to Monitor SMON Rollback Progress [ID 1352046.1]
Master Note: Troubleshooting Database Transaction Recovery (文档 ID 1494886.1)
Rollback Is Taking A Long Time After Session Killed (文档 ID 1060831.6)


V$FAST_START_TRANSACTIONS displays information about the progress of the transactions that Oracle is recovering.




评估事务回滚完成时间
select inst_id,
usn,
state,
undoblockstotal "Total",
undoblocksdone "Done",
undoblockstotal - undoblocksdone "ToDo",
decode(cputime,
0,
'unknown',
to_char(sysdate + (((undoblockstotal - undoblocksdone) /
(undoblocksdone / cputime)) / 86400),'yyyy-mm-dd hh24:mi:ss')) "Estimated time to complete"
from gv$fast_start_transactions;






select min(s.snap_id), max(s.snap_id)
From dba_hist_snapshot s
where s.end_interval_time >=
to_date('2014-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')


根据XID找出造成回滚的语句 
select /*+ parallel(8) */
distinct h.sql_id
from gv$fast_start_transactions st, dba_hist_active_sess_history h where st.STATE = 'RECOVERING'
and st.XID = h.xid
and st.INST_ID = h.instance_number 
and h.snap_id >= 60197
and h.snap_id <= 60330;


《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《《


查看权限：
select * from user_sys_privs where username in  ('PERFSTAT');
select * from user_role_privs where username in ('PERFSTAT');
  
select * from user_TAB_PRIVS where grantee in   ('PERFSTAT');
select * from dba_role_privs where grantee in   ('PERFSTAT');

select * from dba_sys_privs where grantee in    ('PERFSTAT');