检查Oracle 中死事务的语句，oracle死事务语句

检查Oracle 中死事务的语句，oracle死事务语句

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<----该值没减小。
        39          0        502 ACTIVE           NONE                              1
        43         45        480 ACTIVE           NONE                              0

查询v$px_session和v$fast_start_servers,显示很多并行进程在rollback，根据以往的工程经验：

于是改为

SQL>alter system set fast_start_parallel_rollback=false scope=both;

之后，再次运行

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                        2033516
       35         29        502 ACTIVE           NONE                              1
 
SQL> /
 
 KTUXEUSN   KTUXESLT   KTUXESQN STATUS           FLAGS                      KTUXESIZ
---------- ---------- -------------------------- ------------------------ ----------
       13          5      47447 ACTIVE           DEAD                        2033433<---该值不断变小。
       35         29        502 ACTIVE           NONE                              1

使用如下脚本查看回滚完毕的预计时间（以天为单位）：

SQL> set serveroutput on
SQL> 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小时。即：预计5.04小时后回滚完毕。

另外注意：在其他环境使用时，请注意替换KTUXEUSN=13和KTUXESLT=5

SQL> select * from test_main;

ID VALUE
---------- --------------------
2 TWO
3 THREE
1 ONE

SQL> BEGIN
2 -- 插入2条同样的数据，使主键重复，引发错误后回滚事务.
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 -- 回滚事务
11 ROLLBACK;
12 END;
13 /

PL/SQL procedure successfully completed.

SQL> select * from test_main;

ID VALUE
---------- --------------------
2 TWO
3 THREE
1 ONE

SQL>
 

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;
检查查出来的会话，如果sql_text正确的话，记下sid和serial#，执行以下语句杀死
alter system kill session ‘sid,serial#';