ORACLE中能否找到未提交事务的SQL语句

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

SQL> create table test as select * from dba_objects;
SQL> select userenv('sid') from dual;
SQL> delete from test where object_id=12;

然后我们在会话2（SID=70)中，我们使用下面SQL查询未提交的SQL语句。如下所示：

SQL> select userenv('sid') from dual;
USERENV('SID')
--------------
            70
 
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');

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或者其值为Null。

所以结合上面实验，我们基本上可以给出结论，我们不一定能准确找出未提交事务的SQL语句，这个要视情况或场景而定。存在这不确定性。