ORACLE 未提交事务与语句查询

最新推荐文章于 2025-04-24 17:50:50 发布

原创最新推荐文章于 2025-04-24 17:50:50 发布 · 7.4k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#ORACLE 未提交事务与语句查询 #未提交事务与语句 #未提交事务 #未提交语句

ORACLE 专栏收录该内容

6 篇文章

订阅专栏

在Oracle数据库中，未提交的事务可能影响到查询结果。通过示例展示了如何在SQL*Plus环境中插入一条记录但不提交，并在另一个会话中查询到这个未提交的事务状态，揭示了事务管理和并发查询的原理。

插入一条记录，不提交

sys@EDIPD> select sid from v$mystat where rownum<=1;

SID
----------
771

sys@EDIPD> insert into edi.master_trackingno_table(ID,CARRIER_ID,TRACKING_NO,STATUS) values (edi.MASTER_TRACKINGNO_TABLE_SEQ.nextval,'1','11083229377990','1');

1 row created.

Elapsed: 00:00:00.01

窗口二查询未提交事务与语句

sys@EDIPD> select s.sid, s.serial#, s.username,s.osuser, s.program, s.event, to_char(s.LOGON_TIME,'yyyymmdd-hh24:mi:ss') ,to_char(t.START_DATE,'yyyymmdd-hh24:mi:ss'),s.last_call_et as last_ct,s.BLOCKING_SESSION block_sess, 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 ;

SID SERIAL# USERNAME OSUSER PROGRAM EVENT TO_CHAR(S.LOGON_T TO_CHAR(T.START_D LAST_CT BLOCK_SESS STATUS
------ ------- ---------- ---------- ------------------------------- ---------------------------- ----------------- ----------------- ---------- ---------- ---------
SQL_TEXT
----------------------------------------------------------------------------------------------------
771 63711 SYS oracle sqlplus@cnedidp1 (TNS V1-V3) SQL*Net message from client 20151102-17:07:22 20151102-17:35:51 19 INACTIVE
insert into edi.master_trackingno_table(ID,CARRIER_ID,TRACKING_NO,STATUS) values (edi.MASTER_TRACKIN
GNO_TABLE_SEQ.nextval,'1','11083229377990','1')