oracle密码剩余天数,oracle检测回滚剩余时间-Oracle

oracle检测回滚剩余时间

操作oracle数据库时，有一个大事务要回滚，非常慢，所以要看一下需要多少时间才能回滚完。 在网上找到这样的SQL语句：

select * from v$transaction;

但我执行的时候返回：ORA-00942: 表或视图不存在

原来是这样滴：普通用户下视图v$不可见，conn /as sysdba一下就好了。

再次执行返回：未选定行

下面找的的一段可以看回滚还要用多长时间。

when transaction will finish rollback

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

--

-- Script: rolling_back.sql

-- Purpose: to predict when transactions will finish rolling back

-- For: 9.0+

--

-- Copyright: (c) Ixora Pty Ltd

-- Author: Steve Adams

--

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

@save_sqlplus_settings

set serveroutput on

set feedback off

prompt

prompt Looking for transactions that are rolling back ...

prompt

declare

cursor tx is

select

s.username,

t.xidusn,

t.xidslot,

t.xidsqn,

x.ktuxesiz

from

sys.x$ktuxe x,

sys.v_$transaction t,

sys.v_$session s

where

x.inst_id = userenv('Instance') and

x.ktuxesta = 'ACTIVE' and

x.ktuxesiz 1 and

t.xidusn = x.ktuxeusn and

t.xidslot = x.ktuxeslt and

t.xidsqn = x.ktuxesqn and

s.saddr = t.ses_addr;

user_name varchar2(30);

xid_usnnumber;

xid_slot number;

xid_sqnnumber;

used_ublk1 number;

used_ublk2 number;

begin

open tx;

loop

fetch tx into user_name, xid_usn, xid_slot, xid_sqn, used_ublk1;

exit when tx%notfound;

if tx%rowcount = 1

then

sys.dbms_lock.sleep(10);

end if;

select

sum(ktuxesiz)

into

used_ublk2

from

sys.x$ktuxe

where

inst_id = userenv('Instance') and

ktuxeusn = xid_usn and

ktuxeslt = xid_slot and

ktuxesqn = xid_sqn and

ktuxesta = 'ACTIVE';

if used_ublk2 < used_ublk1

then

sys.dbms_output.put_line(

user_name ||

'''s transaction ' ||

xid_usn || '.' ||

xid_slot || '.' ||

xid_sqn ||

' will finish rolling back at approximately ' ||

to_char(

sysdate + used_ublk2 / (used_ublk1 - used_ublk2) / 6 / 60 / 24,

'HH24:MI:SS DD-MON-YYYY'

)

);

end if;

end loop;

if user_name is null

then

sys.dbms_output.put_line('No transactions appear to be rolling back.');

end if;

end;

/

prompt

@restore_sqlplus_settings