oracle查看未提交SQL

c213021806

做这个测试之前，先熟悉以下的动态性能表(可参考10gR2文档)
v$session (lists session information for each current session)
v$transaction (lists the active transactions in the system)
v$process (contains information about the currently active processes)
v$sql (lists statistics on shared SQL area without the GROUP BY clause and contains one row for each child of the original SQL text entered)

SQL> conn scott/tiger
已连接。
SQL> create table m (id number(5),name char(10));
表已创建。
SQL> insert into m values (0,'test');
已创建 1 行。
--此时不要commit提交。
SQL> select addr,ses_addr from v$transaction;
ADDR SES_ADDR
-------- --------
28967184 29F056AC
SQL> select saddr,sid,paddr,username,status from v$session where username is not null;
SADDR SID PADDR USERNAME STATUS
-------- ---------- -------- ------------------------------ --------
29F056AC 154 29E1DA40 SCOTT INACTIVE
29F0B430 159 29E1C370 SYS ACTIVE
SQL> select sid,prev_sql_addr,username,status from v$session
2 where username is not null;
SID PREV_SQL USERNAME&nb

sp; STATUS
---------- -------- ------------------------------ --------
154 26A365C8 SCOTT INACTIVE
159 26A35DD0 SYS ACTIVE
SQL> select sql_text,address from v$sql
2 where address='26A365C8';
SQL_TEXT
--------------------------------------------------------------------------------
ADDRESS
--------
insert into m values (0,'test')
26A365C8
到此查到了正在进行transaction的sql语句。
附：
select addr,ses_addr from v$transaction;
select saddr,sid,serial#,username,status from v$session where username is not null;
select saddr,sid,serial#,username,status,prev_sql_addr,prev_hash_value from v$session
where username is not null;
select addr,sid,username,s.status,process,program from v$transaction t,v$session s
where t.ses_addr=s.saddr;
select addr,pid,spid,program from v$process;
select saddr,sid,paddr,username,status from v$session where username is not null;
select sql_text,address,hash_value from v$sql q,v$session s
where s.sid=154 and s.prev_sql_addr=q.address;