Statement used to query dead transactions in Oracle
Statement used to query dead transactions in Oracle
SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN,/* Transaction ID */
2 KTUXESTA Status, KTUXECFL Flags, KTUXESIZ
3 FROM x $ ktuxe
4 WHERE ktuxesta! = 'Inactivity ';
KTUXEUSN KTUXESLT KTUXESQN STATUS FLAGS KTUXESIZ
--------------------------------------------------------------------------------
13 5 47447 active dead 2819919
39 0 502 active none 1
43 45 480 active none 0
SQL>/
KTUXEUSN KTUXESLT KTUXESQN STATUS FLAGS KTUXESIZ
--------------------------------------------------------------------------------
13 5 47447 active dead 2819919 <---- the value is not reduced.
39 0 502 active none 1
43 45 480 active none 0
Query v $ px_session and v $ fast_start_servers. It shows that many parallel processes are in rollback. Based on previous engineering experience:
So change
SQL> alter system set fast_start_parallel_rollback = false scope = both;
Then run again
SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN,/* Transaction ID */
2 KTUXESTA Status, KTUXECFL Flags, KTUXESIZ
3 FROM x $ ktuxe
4 WHERE ktuxesta! = 'Inactivity ';
KTUXEUSN KTUXESLT KTUXESQN STATUS FLAGS KTUXESIZ
--------------------------------------------------------------------------------
13 5 47447 active dead 2033516
35 29 502 active none 1
SQL>/
KTUXEUSN KTUXESLT KTUXESQN STATUS FLAGS KTUXESIZ
--------------------------------------------------------------------------------
13 5 47447 active dead 2033433 <--- the value keeps decreasing.
35 29 502 active none 1
Run the following script to check the Estimated rollback time (in days ):
SQL> set serveroutput on
SQL> declare
2 Rochelle start number;
3 Rochelle end number;
4 begin
5 select ktuxesiz into l_startfrom x $ ktuxe where KTUXEUSN = 13 and KTUXESLT = 5;
6 dbms_lock.sleep (60 );
7 select ktuxesiz into l_endfrom x $ ktuxe where KTUXEUSN = 13 and KTUXESLT = 5;
8 dbms_output.put_line ('time estDay: '| round (l_end/(l_start-l_end)/60/24, 2 ));
9 end;
10/
Time est Day:. 21
24*0.21 = 5.04 hours. That is, the rollback is expected to be completed within 5.04 hours.
Note: in other environments, replace KTUXEUSN with KTUXESLT = 5.
--------------------------------------------------------------------------------
Installing Oracle 12C in Linux-6-64
Install Oracle 11gR2 (x64) in CentOS 6.4)
Steps for installing Oracle 11gR2 in vmwarevm
Install Oracle 11g XE R2 In Debian
--------------------------------------------------------------------------------