一 什么是用户进程
用户进程是指用户到Oracle数据库服务器的链接,用户进程处理用户输入并通过Oracle程序接口与Oracle服务器进程通信。用户进程还负责显示用户请求的信息,必要时可以将信息处理成更有用的形式。用户通过客户端,比如SQL Plus、SQL Developer、PL SQL Developer、Toad等工具连接上服务器而产生的进程。
二 怎样查看用户进程
通过视图v$session进行查看。示例:
[oracle@localhost 桌面]$ lsnrctl start
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 27-MAY-2013 19:33:25
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Starting /oracle/oracle//product/10.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /oracle/oracle//product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /oracle/oracle//product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 27-MAY-2013 19:33:27
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/oracle//product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /oracle/oracle//product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@localhost 桌面]$ sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 27 19:33:34 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 71305460 bytes
Database Buffers 92274688 bytes
Redo Buffers 2973696 bytes
sDatabase mounted.
Database opened.
SQL> show parameter user_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
license_max_users integer 0
user_dump_dest string /oracle/oracle/product/10.2.0/
db_1/admin/orcl/udump
SQL> desc v$session;
Name Null? Type
----------------------------------------- -------- ----------------------------
SADDR RAW(4)
SID NUMBER
SERIAL# NUMBER
AUDSID NUMBER
PADDR RAW(4)
USER# NUMBER
USERNAME VARCHAR2(30)
COMMAND NUMBER
OWNERID NUMBER
TADDR VARCHAR2(8)
LOCKWAIT VARCHAR2(8)
STATUS VARCHAR2(8)
SERVER VARCHAR2(9)
SCHEMA# NUMBER
SCHEMANAME VARCHAR2(30)
OSUSER VARCHAR2(30)
PROCESS VARCHAR2(12)
MACHINE VARCHAR2(64)
TERMINAL VARCHAR2(30)
PROGRAM VARCHAR2(48)
TYPE VARCHAR2(10)
SQL_ADDRESS RAW(4)
SQL_HASH_VALUE NUMBER
SQL_ID VARCHAR2(13)
SQL_CHILD_NUMBER NUMBER
PREV_SQL_ADDR RAW(4)
PREV_HASH_VALUE NUMBER
PREV_SQL_ID VARCHAR2(13)
PREV_CHILD_NUMBER NUMBER
MODULE VARCHAR2(48)
MODULE_HASH NUMBER
ACTION VARCHAR2(32)
ACTION_HASH NUMBER
CLIENT_INFO VARCHAR2(64)
FIXED_TABLE_SEQUENCE NUMBER
ROW_WAIT_OBJ# NUMBER
ROW_WAIT_FILE# NUMBER
ROW_WAIT_BLOCK# NUMBER
ROW_WAIT_ROW# NUMBER
LOGON_TIME DATE
LAST_CALL_ET NUMBER
PDML_ENABLED VARCHAR2(3)
FAILOVER_TYPE VARCHAR2(13)
FAILOVER_METHOD VARCHAR2(10)
FAILED_OVER VARCHAR2(3)
RESOURCE_CONSUMER_GROUP VARCHAR2(32)
PDML_STATUS VARCHAR2(8)
PDDL_STATUS VARCHAR2(8)
PQ_STATUS VARCHAR2(8)
CURRENT_QUEUE_DURATION NUMBER
CLIENT_IDENTIFIER VARCHAR2(64)
BLOCKING_SESSION_STATUS VARCHAR2(11)
BLOCKING_INSTANCE NUMBER
BLOCKING_SESSION NUMBER
SEQ# NUMBER
EVENT# NUMBER
EVENT VARCHAR2(64)
P1TEXT VARCHAR2(64)
P1 NUMBER
P1RAW RAW(4)
P2TEXT VARCHAR2(64)
P2 NUMBER
P2RAW RAW(4)
P3TEXT VARCHAR2(64)
P3 NUMBER
P3RAW RAW(4)
WAIT_CLASS_ID NUMBER
WAIT_CLASS# NUMBER
WAIT_CLASS VARCHAR2(64)
WAIT_TIME NUMBER
SECONDS_IN_WAIT NUMBER
STATE VARCHAR2(19)
SERVICE_NAME VARCHAR2(64)
SQL_TRACE VARCHAR2(8)
SQL_TRACE_WAITS VARCHAR2(5)
SQL_TRACE_BINDS VARCHAR2(5)
SQL> desc v$process;
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDR RAW(4)
PID NUMBER
SPID VARCHAR2(12)
USERNAME VARCHAR2(15)
SERIAL# NUMBER
TERMINAL VARCHAR2(30)
PROGRAM VARCHAR2(48)
TRACEID VARCHAR2(255)
BACKGROUND VARCHAR2(1)
LATCHWAIT VARCHAR2(8)
LATCHSPIN VARCHAR2(8)
PGA_USED_MEM NUMBER
PGA_ALLOC_MEM NUMBER
PGA_FREEABLE_MEM NUMBER
PGA_MAX_MEM NUMBER
SQL> desc v$sql;
Name Null? Type
----------------------------------------- -------- ----------------------------
SQL_TEXT VARCHAR2(1000)
SQL_FULLTEXT CLOB
SQL_ID VARCHAR2(13)
SHARABLE_MEM NUMBER
PERSISTENT_MEM NUMBER
RUNTIME_MEM NUMBER
SORTS NUMBER
LOADED_VERSIONS NUMBER
OPEN_VERSIONS NUMBER
USERS_OPENING NUMBER
FETCHES NUMBER
EXECUTIONS NUMBER
PX_SERVERS_EXECUTIONS NUMBER
END_OF_FETCH_COUNT NUMBER
USERS_EXECUTING NUMBER
LOADS NUMBER
FIRST_LOAD_TIME VARCHAR2(38)
INVALIDATIONS NUMBER
PARSE_CALLS NUMBER
DISK_READS NUMBER
DIRECT_WRITES NUMBER
BUFFER_GETS NUMBER
APPLICATION_WAIT_TIME NUMBER
CONCURRENCY_WAIT_TIME NUMBER
CLUSTER_WAIT_TIME NUMBER
USER_IO_WAIT_TIME NUMBER
PLSQL_EXEC_TIME NUMBER
JAVA_EXEC_TIME NUMBER
ROWS_PROCESSED NUMBER
COMMAND_TYPE NUMBER
OPTIMIZER_MODE VARCHAR2(10)
OPTIMIZER_COST NUMBER
OPTIMIZER_ENV RAW(797)
OPTIMIZER_ENV_HASH_VALUE NUMBER
PARSING_USER_ID NUMBER
PARSING_SCHEMA_ID NUMBER
PARSING_SCHEMA_NAME VARCHAR2(30)
KEPT_VERSIONS NUMBER
ADDRESS RAW(4)
TYPE_CHK_HEAP RAW(4)
HASH_VALUE NUMBER
OLD_HASH_VALUE NUMBER
PLAN_HASH_VALUE NUMBER
CHILD_NUMBER NUMBER
SERVICE VARCHAR2(64)
SERVICE_HASH NUMBER
MODULE VARCHAR2(64)
MODULE_HASH NUMBER
ACTION VARCHAR2(64)
ACTION_HASH NUMBER
SERIALIZABLE_ABORTS NUMBER
OUTLINE_CATEGORY VARCHAR2(64)
CPU_TIME NUMBER
ELAPSED_TIME NUMBER
OUTLINE_SID NUMBER
CHILD_ADDRESS RAW(4)
SQLTYPE NUMBER
REMOTE VARCHAR2(1)
OBJECT_STATUS VARCHAR2(19)
LITERAL_HASH_VALUE NUMBER
LAST_LOAD_TIME VARCHAR2(38)
IS_OBSOLETE VARCHAR2(1)
CHILD_LATCH NUMBER
SQL_PROFILE VARCHAR2(64)
PROGRAM_ID NUMBER
PROGRAM_LINE# NUMBER
EXACT_MATCHING_SIGNATURE NUMBER
FORCE_MATCHING_SIGNATURE NUMBER
LAST_ACTIVE_TIME DATE
BIND_DATA RAW(2000)
SQL> desc v$sqltext;
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDRESS RAW(4)
HASH_VALUE NUMBER
SQL_ID VARCHAR2(13)
COMMAND_TYPE NUMBER
PIECE NUMBER
SQL_TEXT VARCHAR2(64)
--sys用户登录,用session查看用户进程
SQL> SELECT username,sid FROM v$session;
USERNAME SID
------------------------------ ----------
149
150
152
155
SYS 159
160
161
162
163
164
165
USERNAME SID
------------------------------ ----------
166
167
168
169
170
16 rows selected.
--再以自建用户wgb登录,再次查看,会发现新增一条记录
SQL> SELECT username, sid from V$SESSION;
USERNAME SID
------------------------------ ----------
WGB 147
149
150
152
155
SYS 159
160
161
162
163
164
USERNAME SID
------------------------------ ----------
165
166
167
168
169
170
17 rows selected.
三 说明
SID:用户进程与服务器端进程进行连接产生的
没有用户名的记录:本身存在的一些连接,比如后台进程
PID:v$process视图进程查询
四 用户进程对DBA管理的作用
如何去控制用户与服务器进行连接的
要不要Oracle的网络客户端
用户进程与服务器进程产生后,是哪个用户产生的
用户进程产生后进行了一些什么工作
![]() | ![]() ![]() |
@Wentasy 博文仅供参考,欢迎大家来访。如有错误之处,希望批评指正。原创博文如需转载请注明出处,谢谢 :) [CSDN博客] |