Oracle database View How a process is executing the relevant actual SQL statements
Copy Code code as follows:
SELECT b.sql_text, sid, Serial#, Osuser, machine
From V$session A, V$sqlarea b
WHERE a.sql_address = b.address;
Query the SQL statements issued by the foreground.
Copy Code code as follows:
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 '));
query session based on SPID
Copy Code code as follows:
SELECT * from V$session WHERE paddr
(SELECT addr from v$process WHERE spid=&spid);
query process based on SID
Copy Code code as follows:
SELECT * from v$process WHERE addr
(SELECT paddr from v$session WHERE sid=&sid);
how DBAs query What other users are doing
Copy Code code as follows:
SELECT Sql_text
From V$sql T1, V$session T2
WHERE t1.address = t2.sql_address
and T2.sid = &sid;
query SQL statements based on process
Copy Code code as follows:
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;
Other
Execute the following command statement
Copy Code code as follows:
Sqlplus/as SYSDBA <<eof
Create tablespace test datafile '/data/test01.dbf ' size 10240M;
Quit
Eof
The process ID of the command executed above with the Ps-ef|grep sqlplus command is: 12345
(1) About v$process
The following SQL is not found to be relevant information:
SELECT * from v$process where spid= ' 12345 ';
Because this SPID field corresponds not to the process ID we queried from the system with the PS command, but to the current SQL process ID that the process executes.
The "Create tablespace test datafile '/data/test01.dbf ' size 10240M" in the above command. The corresponding process ID, if you want to
By using the PS command to view the corresponding information from the process ID that is queried from the system, you must use the following statement:
Select Spid,sid,process,sql_address from v$session where process= ' 12345 '
The processes in SQL above are the process IDs viewed through PS, and the SPID is the process ID of the SQL statement inside.
You can also view the contents of an executing SQL statement through the sql_address above:
Select Sql_text from V$sqlarea s,v$session ses where s.address=ses.sql_address and ses.process= ' 12345 ';
(2) About V$session
When querying the v$session view, we decode each field according to the command field's internal representation, which is useful when we need to quickly identify the internal situation of their Oracle system.
Copy Code code as follows:
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 ',
' Alter Table ',
, ' Create View ',
, ' Validate Index ',
' Alter Database ',
The ' Create tablespace ',
A, ' Drop tablespace ',
, ' Alter tablespace ',
, ' Drop User ',
The ' Analyze Table ',
, ' 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
(3) Several related SQL
--View the information for the system process
Copy Code code as follows:
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
--View all sessions
Copy Code code as follows:
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
--View the SQL content for the session
Copy Code code as follows:
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 '
