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 : 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能胜任任何场景吗？ 答案是否定的。
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