Discussion: how to view the actual SQL statements executed by a process in Oracle Database

Check how a process executes related SQL statements in the Oracle database.
Copy codeThe Code is 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 front-end.
Copy codeThe Code is 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 sessions by SPID
Copy codeThe Code is as follows:
SELECT * FROM v $ session WHERE paddr IN
(SELECT addr FROM v $ process WHERE spid = & spid );

Query process by SID
Copy codeThe Code is as follows:
SELECT * FROM v $ process WHERE addr IN
(SELECT paddr FROM v $ session WHERE sid = & sid );

How does DBA query operations performed by other users?
Copy codeThe Code is 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 by process
Copy codeThe Code is as follows:
SELECT SQL _text
FROM v $ sqltext
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;

Others
Run the following command
Copy codeThe Code is as follows:
Sqlplus/as sysdba <EOF
Create tablespace test datafile '/data/test01.dbf' size 10240 M;
Quit;
EOF

Run the ps-ef | grep sqlplus command to obtain the process id of the Command executed above: 12345
(1) v $ process
If the following SQL statement is executed, no relevant information is found:
Select * from v $ process where spid = '000000 ';
Because this spid field does not correspond to the process id we query from the system using the ps command, but the process id of the current SQL executed by the process,
That is, the process id corresponding to "create tablespace test datafile '/data/test01.dbf' size 10240 M;" in the preceding command. if you want
To view the corresponding information from the process id queried by the ps command, you must use the following statement:
Select spid, sid, process, SQL _address from v $ session where process = '2013'
In the preceding SQL statement, process is the process id viewed through ps, and spid is the process id corresponding to the SQL statement.
You can also use SQL _address to view the content of the SQL statement being executed:
Select SQL _text from v $ sqlarea s, v $ session ses where s. address = ses. SQL _address and ses. process = '2016 ';
(2) v $ session
When querying the v $ session view, we decode each field based on the internal representation of the command field. This is useful when we need to quickly find out the internal situation of their Oracle system.
Copy codeThe Code is as follows:
Select
Substr (s. username,) username, substr (s. program,) 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

(3) Several related SQL statements
-- View the information of the system process
Copy codeThe Code is 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 codeThe Code is 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 of a session
Copy codeThe Code is 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'