Oracle体系结构及备份（三）——user-process

谁践踏了优雅 2022-02-03 15:04 243阅读 0赞

### 一 什么是用户进程 ###

用户进程是指用户到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的网络客户端

用户进程与服务器进程产生后，是哪个用户产生的

用户进程产生后进行了一些什么工作

<table style="border-bottom:#48c5db 5px double; border-left:#48c5db 5px double; padding-bottom:5px; background-color:black; padding-left:5px; border-spacing:0; width:100%; padding-right:30px; height:90px; border-top:#48c5db 5px double; border-right:#48c5db 5px double; padding-top:5px"> 
 <tbody> 
  <tr> 
   <td><img alt="katoon" src="https://img-my.csdn.net/uploads/201301/06/1357456262_5409.jpg" style="height:80px"></td> 
   <td style="text-align:right"><a title="新浪微博求粉 ^_^" href="http://weibo.com/wentasy" rel="nofollow"><img title="新浪微博求粉 ^_^" alt="Sina" src="https://img-my.csdn.net/uploads/201210/26/1351182273_5476.png.thumb.jpg" style="height:40px"></a>&nbsp;<a title="我的CSDN博客——HelloWorld" href="http://blog.csdn.net/Wentasy" rel="nofollow"><img title="我的CSDN博客——HelloWorld" alt="CSDN" src="https://img-my.csdn.net/uploads/201210/26/1351182268_8254.png.thumb.jpg" style="height:40px"></a></td> 
  </tr> 
  <tr> 
   <td style="text-align:right; font-family:microsoft yahei; color:white; font-size:12px"> <a title="我的CSDN博客——HelloWorld" href="http://blog.csdn.net/Wentasy" rel="nofollow">@Wentasy</a> 博文仅供参考，欢迎大家来访。如有错误之处，希望批评指正。原创博文如需转载请注明出处，谢谢 :) [CSDN博客]</td> 
  </tr> 
 </tbody> 
</table>