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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.