Check the Statement of the Oracle transaction and the statement of the oracle transaction.

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 #';