ORACLE中如何找到未提交事务的SQL语句详解

在oracle数据库中，我们能否找到未提交事务（uncommIT transactin）的SQL语句或其他相关信息呢？ 关于这个问题，我们先来看看实验测试吧。实践出真知。

首先，我们在会话1（SID=63)中构造一个未提交的事务，如下所：

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语句。如下所示：

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      : selectgrantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))from objauth$ whereobj#=:1 group by grantee#,privilege#,nvl(col#,0) order by grantee#-----------------SQL_TEXT      : SELECT /* OPT_DYN_SamP */ /*+ ALL_ROWSIGNORE_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# isnot null group by privilege#, col#, grantee# order by col#, grantee#-----------------SQL_TEXT      : selecttyPE#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr,NVL(spare1,0),NVL(scanhint,0),NVL(bitmapranges,0) from seg$ where ts#=:1 andfile#=:2 and block#=:3-----------------PL/SQL procedure successfully completed.

如上所示，这个SQL我们会查出很多不相关的SQL语句，接下来我们可以用下面的SQL查询（改用SQL Developer展示，因为SQL*Plus，不方便展示），如下所示，这个SQL倒不会查出不相关的SQL。但是这个SQL能胜任任何场景吗？ 答案是否定的。

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的情况。如下所示：

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操作语句，都未提交事务。

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

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对的支持。

• ORACLE数据库事务隔离级别介绍
• oracle 合并查询 事务 sql函数小知识学习
• Oracle中死事务的检查语句
• MSSQL与Oracle数据库事务隔离级别与锁机制对比
• Java与Oracle实现事务（JDBC事务）实例详解

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！