Check the Statement of the Oracle transaction and the statement of the oracle transaction.
SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */ 2 KTUXESTA Status, KTUXECFL Flags ,KTUXESIZ 3 FROM x$ktuxe 4 WHERE ktuxesta!='INACTIVE'; 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 release status flags running ---------- release failed ---------- 13 5 47447 active dead 2033516 35 29 502 active none 1 SQL>/release KTUXESLT release STATUS FLAGS running ------------ release failed ---------- 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 onSQL> declare 2 l_start number; 3 l_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.
SQL statements for Oracle-defined transactions
SQL> select * from test_main;
ID VALUE
------------------------------
2 TWO
3 THREE
1 ONE
SQL> BEGIN
2 -- insert two identical data records to repeat the primary key and roll back the transaction after an error is thrown.
3 insert into test_main (id, value) VALUES (4, 'four ');
4 insert into test_main (id, value) VALUES (4, 'four ');
5 COMMIT;
6 EXCEPTION
7 WHEN OTHERS THEN
8 dbms_output.put_line ('error Code = '| TO_CHAR (SQLCODE ));
9 dbms_output.put_line ('error Message = '| SQLERRM );
10 -- roll back the transaction
11 ROLLBACK;
12 END;
13/
PL/SQL procedure successfully completed.
SQL> select * from test_main;
ID VALUE
------------------------------
2 TWO
3 THREE
1 ONE
SQL>
How to kill a query statement in oracle that has been executing
Select sid, serial #, SQL _text, executions from v $ SQL join v $ session on v $ SQL. SQL _id = v $ session. SQL _id where cpu_time> 20000;
Check the detected session. If SQL _text is correct, write down sid and serial # And run the following statement to kill
Alter system kill session 'sid, serial #';