在Oracle数据库中，我们能否找到未提交事务（uncommit transactin）的SQL语句或其他相关信息呢？ 关于这个问题，我们先来看看实验测试吧。实践出真知。
首先，我们在会话1（SID=63)中构造一个未提交的事务，如下所：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SQL> create table test 2 as 3 select * from dba_objects;
Table created. SQL> select userenv( 'sid' ) from dual;
USERENV( 'SID' ) -------------- 63
SQL> delete from test where object_id=12;
1 row deleted.
SQL> |
然后我们在会话2（SID=70)中，我们使用下面SQL查询未提交的SQL语句。如下所示：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
SQL> select userenv( 'sid' ) from dual;
USERENV( 'SID' ) -------------- 70
SQL> SQL> SET SERVEROUTPUT ON SIZE 99999; SQL> EXECUTE PRINT_TABLE( 'SELECT SQL_TEXT FROM V$SQL S,V$TRANSACTION T WHERE S.LAST_ACTIVE_TIME=T.START_DATE' ); SQL_TEXT : delete from test where object_id=12 ----------------- SQL_TEXT : select grantee#,privilege#,nvl(col#,0), max (mod(nvl( option $,0),2)) from objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by grantee# ----------------- SQL_TEXT : SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param( 'parallel_execution_enabled' , 'false' ) NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL( SUM (C1),0), NVL( SUM (C2),0) FROM ( SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL( "TEST" ) FULL ( "TEST" ) NO_PARALLEL_INDEX( "TEST" ) */ 1 AS C1, CASE WHEN "TEST" . "OBJECT_ID" =12 THEN 1 ELSE 0 END AS C2 FROM "TEST" SAMPLE BLOCK (6.134372 , 1) SEED (1) "TEST" ) SAMPLESUB ----------------- SQL_TEXT : select col#, grantee#, privilege#, max (mod(nvl( option $,0),2)) from objauth$ where obj#=:1 and col# is not null group by privilege#, col#, grantee# order by col#, grantee# ----------------- SQL_TEXT : select type#,blocks,extents,minexts,maxexts,extsize,extpct, user #,iniexts,NVL(lists,6553 5),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),NVL(scanhint,0),NVL(bitmapranges,0) from seg$ where ts#=:1 and file#=:2 and block#=:3 ----------------- PL/SQL procedure successfully completed. |
如上所示，这个SQL我们会查出很多不相关的SQL语句，接下来我们可以用下面的SQL查询（改用SQL Developer展示，因为SQL*Plus，不方便展示），如下所示，这个SQL倒不会查出不相关的SQL。但是这个SQL能胜任任何场景吗？ 答案是否定的。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SELECT S.SID ,S.SERIAL# ,S.USERNAME ,S.OSUSER ,S.PROGRAM ,S.EVENT ,TO_CHAR(S.LOGON_TIME, 'YYYY-MM-DD HH24:MI:SS' ) ,TO_CHAR(T.START_DATE, 'YYYY-MM-DD HH24:MI:SS' ) ,S.LAST_CALL_ET ,S.BLOCKING_SESSION ,S.STATUS ,( SELECT Q.SQL_TEXT FROM V$SQL Q WHERE Q.LAST_ACTIVE_TIME=T.START_DATE AND ROWNUM<=1) AS SQL_TEXT FROM V$SESSION S, V$ TRANSACTION T WHERE S.SADDR = T.SES_ADDR; |
我们知道，在ORACLE里第一次执行一条SQL语句后，该SQL语句会被硬解析，而且执行计划和解析树会被缓存到Shared Pool里。方便以后再次执行这条SQL语句时不需要再做硬解析。但是Shared Pool的大小也是有限制的，不可能无限制的缓存所有SQL的执行计划，它使用LRU算法管理库高速缓存区。所以有可能你要找的SQL语句已经不在Shared Pool里面了，它从Shared Pool被移除出去了。如下所示，我们使用sys.dbms_shared_pool.purge人为构造SQL被移除出Shared Pool的情况。如下所示：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SQL> col sql_text for a80; SQL> select sql_text 2 ,sql_id 3 ,version_count 4 ,executions 5 ,address 6 ,hash_value 7 from v$sqlarea where sql_text 8 like 'delete from test%' ;
SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS ADDRESS HASH_VALUE ------------------------------------ ------------- ------------- ---------- ---------------- ---------- delete from test where object_id=12 5xaqyzz8p863u 1 1 0000000097FAE648 3511949434
SQL> exec sys.dbms_shared_pool.purge( '0000000097FAE648,3511949434' , 'C' );
PL/SQL procedure successfully completed.
SQL> |
此时我们查询到的SQL语句，是一个不相关的SQL或者其值为Null。
接下来我们回滚SQL语句，然后继续新的实验测试，如下所示，在会话1（SID=63)里面执行了两个DML操作语句，都未提交事务。
|
1 2 3 4 5 6 7 8 9 |
SQL> delete from test where object_id=12;
1 row deleted.
SQL> update test set object_name= 'kkk' where object_id=14;
1 row updated.
SQL> |
接下来，我们使用SQL语句去查找未提交的SQL，发现只能捕获最开始执行的DELETE语句，不能捕获到后面执行的UPDATE语句。这个实验也从侧面印证了，我们不一定能准确的找出未提交事务的SQL语句。
所以结合上面实验，我们基本上可以给出结论，我们不一定能准确找出未提交事务的SQL语句，这个要视情况或场景而定。存在这不确定性。
参考资料：
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9523503800346688981
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对服务器之家的支持。
原文链接：https://www.cnblogs.com/kerrycode/p/10930004.html
查看更多关于ORACLE中如何找到未提交事务的SQL语句详解的详细内容...