探讨:Oracle数据库查看一个进程是如何执行相关的实际SQL语句

复制代码 代码如下:

SELECT b.sql_text, sid, serial#, osuser, machine
FROM v$session a, v$sqlarea b
WHERE a.sql_address = b.address;

复制代码 代码如下:

select user_name,sql_text
from v$open_cursor
where sid in
(select sid from (select sid,serial#,username,program from v$session where status='ACTIVE'));

复制代码 代码如下:

SELECT * FROM v$session WHERE paddr IN
(SELECT addr FROM v$process WHERE spid=&spid);

复制代码 代码如下:

SELECT * FROM v$process WHERE addr IN
(SELECT paddr FROM v$session WHERE sid=&sid);

复制代码 代码如下:

SELECT sql_text
FROM v$sql t1, v$session t2
WHERE t1.address = t2.sql_address
AND t2.sid = &sid;

复制代码 代码如下:

SELECT sql_text
FROM v$sqltext a
WHERE (a.hash_value, a.address) IN (
SELECT DECODE (sql_hash_value,0, prev_hash_value,sql_hash_value ),
DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
FROM v$session b
WHERE b.paddr = (SELECT addr
FROM v$process c
WHERE c.spid = '$processID'))
ORDER BY piece ASC;

复制代码 代码如下:

sqlplus / as sysdba <<EOF
create tablespace test datafile '/data/test01.dbf' size 10240M;
quit;
EOF

复制代码 代码如下:

select
substr(s.username,1,18) username,substr(s.program,1,15) program,p.spid,s.process,
decode(s.command,
0,'No Command',
1,'Create Table',
2,'Insert',
3,'Select',
6,'Update',
7,'Delete',
9,'Create Index',
15,'Alter Table',
21,'Create View',
23,'Validate Index',
35,'Alter Database',
39,'Create Tablespace',
41,'Drop Tablespace',
40,'Alter Tablespace',
53,'Drop User',
62,'Analyze Table',
63,'Analyze Index',
s.command||': Other') command
from
v$session s,
v$process p,
v$transaction t,
v$rollstat r,
v$rollname n
where s.paddr = p.addr
and s.taddr = t.addr (+)
and t.xidusn = r.usn (+)
and r.usn = n.usn (+)
order by username

复制代码 代码如下:

select se.saddr,se.sid,se.serial#,p.pid,se.paddr,s.sql_id,s.sql_text
from v$session se ,v$process p, v$sqlarea s
where se.paddr=p.addr and se.sql_address=s.address and se.process='&1'
and se.username is not null

复制代码 代码如下:

select se.username,se.saddr,se.sid,se.serial#,se.process,s.sql_id
from v$session se,v$sqlarea s
where se.sql_address=s.address

复制代码 代码如下:

select se.username,se.process,s.sql_text
from v$session se,v$sqlarea s
where se.sql_address=s.address and s.sql_id='&1'