[Oracle] v $ session and v $ process

View the sid and serial of the current session #:

SYS @ ORCL> select sid, serial #, status from v $ session where sid = userenv ('sid ');

Sid serial # STATUS

----------------------------

89 3 ACTIVE

View the spid corresponding to the current session:

SYS @ ORCL> select spid from v $ process p, v $ session s where s. sid = userenv ('sid ') and s. paddr = p. addr;

SPID

------------

18871

View the path of the trace file of the current session:

SYS @ ORCL> select p. value | '/' | t. instance | '_ ora _' | ltrim (to_char (p. spid, 'fm99999') | '. trc'

2 from v $ process p, v $ session s, v $ parameter p, v $ thread t

3 where p. addr = s. paddr and s. audsid = userenv ('sessionid') and p. name = 'user _ dump_dest ';

P. VALUE | '/' | T. INSTANCE | '_ ORA _' | LTRIM (TO_CHAR (P. SPID, 'fm99999') | '. trc'

--------------------------------------------------------------------------------

/U01/app/oracle/admin/ORCL/udump/ORCL_ora_18871.trc

If spid is known, view the SQL statement currently being executed or the last time it was executed

SYS @ ORCL> select/* + ordered */SQL _text

2 from v $ sqltext SQL

3 where (SQL. hash_value, SQL. address) in (

4 select decode (SQL _hash_value, 0, prev_hash_value, SQL _hash_value), decode (SQL _hash_value, 0, prev_ SQL _addr, SQL _address)

5 from v $ session s

6 where s. paddr = (select addr from v $ process p where p. spid = to_number ('& pid ')));

Enter value for pid: 18871

Old 6: where s. paddr = (select addr from v $ process p where p. spid = to_number ('& pid ')))

New 6: where s. paddr = (select addr from v $ process p where p. spid = to_number ('123 ')))

SQL _TEXT

----------------------------------------------------------------

From v $ process p where p. spid = to_number ('123 ')))

SQL _address) from v $ session s where s. paddr = (select addr

Ash_value, SQL _hash_value), decode (SQL _hash_value, 0, prev_ SQL _addr,

Value, SQL. address) in (select decode (SQL _hash_value, 0, prev_h

Select/* + ordered */SQL _text from v $ sqltext SQL where (SQL. hash _

View the lock and wait:

SYS @ ORCL> col user_name format a10

SYS @ ORCL> col owner format a10

SYS @ ORCL> col object_name format a15

SYS @ ORCL> col sid format 999999

SYS @ ORCL> col serial # format 999999

SYS @ ORCL> col spid format a6

SYS @ ORCL> SELECT/* + rule */lpad ('', decode (l. xidusn, 0, 3, 0) | l. oracle_username User_name, o. owner, o. object_name, o. object_type, s. sid, s. serial #, p. spid

2 FROM v $ locked_object l, dba_objects o, v $ session s, v $ process p

3 WHERE l. object_id = o. object_id

4 AND l. session_id = s. sid and s. paddr = p. addr

5 order by o. object_id, xidusn DESC;

No rows selected

Queries the session information and process id of a specified system user in oracle. Assume that the operating system user is: oracle

SYS @ ORCL> select s. sid, s. SERIAL #, s. username, p. spid

2 from v $ session s, v $ process p

3 where s. osuser = 'oracle'

4 and s. PADDR = p. ADDR;

In Windows

In linux, you can view the process information through ps, including the pid. In windows, the PID of the task manager cannot correspond to the pid in v $ process one by one. This section is not found in oracleDocument, later, google gave me some information, saying that windows is a multi-threaded server, and each process contains a series of threads. This is different from unix. Each Oralce process in Unix exists independently, and all threads in Nt are derived from the Oralce process.
To display the oracle-related process pid in windows, we can use a simple SQL statement.
SELECT s. SID, p. pid, p. spid signaled, s. osuser, s. program
FROM v $ process p, v $ session s
WHERE p. addr = s. paddr;

You can also query the name of the background process through a connection with v $ bgprocess:
SELECT s. SID, p. spid threadid, p. program processname, bg. NAME
FROM v $ process p, v $ session s, v $ bgprocess bg
WHERE p. addr = s. paddr
AND p. addr = bg. paddr
AND bg. paddr <> '00 ';

Master Eygle wrote an SQL script getsql. SQL to obtain the SQL statement being executed by the specified pid. This is also a note here.

REM getsql. SQL
REM author eygle
REM on windows, the process ID is known to get the statement currently being executed
REM in windows, the process ID is in hexadecimal format and needs to be converted. in UNIX, SELECT/* + ORDERED */is in hexadecimal format */
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 = TO_NUMBER ('& pid', 'xxxx ')))
Order by piece ASC
/