complete_rollback_script.pdf
40墨值下载
Knowing how much time left to complete rollback
operation.
Author: David Gornshtein
WisdomForce Technologies, Inc
http://www.wisdomforce.com
Note*: The latest copy of this document is available at
http://www.wisdomforce.com/dweb/resources/docs/complete_rollback_script.pdf
The script below is written for Oracle 9+.
You can rewrite it for version 8+, but please note it comes without support for killed session
lookup. Also lookup for rollbacks continues after issuing of "shutdown abort".
*Note: There is known issue with Oracle prior 9i version, that there is a lack of support for "left
outer join" statement defined by SQL standard.
Preliminaries:
fixed view sys.x$ktuxe
view: x$ktuxe
[k]ernel layer
[t]ransaction layer
[u]ndo
transaction [e]ntry
This view holds entry for each active undo slot.
DECLARE
CURSOR tx
IS
SELECT /*+ USE_NL(S,T,X) */
NVL
(s.username,
'session no more exists or running on the other node of RAC'
),
x.ktuxeusn, x.ktuxeslt, x.ktuxesqn, x.ktuxesiz
FROM ((sys.x_$ktuxe x LEFT JOIN sys.gv_$transaction t ON
t.xidusn = x.ktuxeusn AND t.xidslot = x.ktuxeslt AND
t.xidsqn = x.ktuxesqn AND x.inst_id = t.inst_id) LEFT JOIN
sys.gv_$session s ON
s.saddr = t.ses_addr AND
s
.inst_id = t.inst_id)
WHERE x.ktuxesta = 'ACTIVE' AND x.ktuxesiz > 1;
user_name VARCHAR2 (80);
xid_usn NUMBER;
xid_slot NUMBER;
xid_sqn NUMBER;
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 (120
);
SELECT SUM (ktuxesiz)
INTO used_ublk2
FROM sys.x_$ktuxe
WHERE 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 ('session (' || user_name || ')');
sys.DBMS_OUTPUT.put_line
( 'transaction '
|| xid_usn
|| '.'
|| xid_slot
|| '.'
|| xid_sqn
|| ' will finish rolling back at approximately '
|| TO_CHAR ( SYSDATE
+ used_ublk2
/ (used_ublk1 - used_ublk2)
/
30
/ 24,
'HH24:MI:SS DD-MON-YYYY'
)
);
END IF;
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;
Assume the following situation - you have several unique indexes but want automatically create
the apropriate unique constraints. See the following script :
of 4
40墨值下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
评论