欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-04-09 19:42:02
在Oracle数据库中,我们能否找到未提交事务(uncommit transactin)的SQL语句或其他相关信息呢? 关于这个问题,我们先来看看实验测试吧。实践出真知。 首先,我们在会话1(SID=63)中构造一个未提交的事务,如下所: SQL> create table test 2 as 3 ... ......

 

在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