Oracle 动态视图6 V$PROCESS

一、视图包含当前系统oracle运行的所有进程信息。常用于将session与进程（oracle进程，操作系统进程）之间建立联系。

Column	Datatype	Description
ADDR	RAW(4 | 8)	进程对象地址
PID	NUMBER	oracle进程ID
SPID	VARCHAR2(12)	操作系统进程ID
USERNAME	VARCHAR2(15)	Operating system process username. Any two-task user coming across the network has "-T" appended to the username.
SERIAL#	NUMBER	Process serial number
TERMINAL	VARCHAR2(30)	操作系统terminal identifier(e.g., computer name)
PROGRAM	VARCHAR2(48)	进程正在执行的程序(e.g., ORACLE.EXE (ARC0))
TRACEID	VARCHAR2(255)	Trace file identifier
BACKGROUND	VARCHAR2(1)	1代表oracle background process，null代表normal process
LATCHWAIT	VARCHAR2(8)	Address of latch the process is waiting for; NULL if none
LATCHSPIN	VARCHAR2(8)	Address of the latch the process is spinning on; NULL if none
PGA_USED_MEM	NUMBER	当前进程所使用的PGA内存
PGA_ALLOC_MEM	NUMBER	当前分配的PGA内存大小 (including free PGA memory not yet released to the operating system by the server process)
PGA_FREEABLE_MEM	NUMBER	空闲PGA
PGA_MAX_MEM	NUMBER	Maximum PGA memory ever allocated by the process

示例：

--Eygle大师写了一段sql脚本getsql.sql，用来获取指定pid正在执行的sql语句
--REM getsql.sql
--REM author eygle
--REM 在windows上,已知进程ID,得到当前正在执行的语句
--REM 在windows上,进程ID为16进制,需要转换,在UNIX直接为10进制

SELECT /*+ ORDERED */
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 = TO_NUMBER ('&pid', 'xxxx'))
)
ORDER BY piece ASC
/