当oracle有多个实例时,用pl/sql查看session
来源:互联网 发布:java分割字符串 编辑:程序博客网 时间:2024/06/06 18:27
当oracle中一个数据库有两个实例时,使用pl/sql工具中的session工具,有时看不到自己正在执行的语句。因为,pl/sql工具中的session工具无法判断,需要查询的是哪个实例下的session
打开session工具,点击设置
我们可以看到,pl/sql的session工具其实是执行sql语句来查询,从这个查询语句中我们可以看出问题,这个查询语句查询的是v$session表,从这里oracle无法判断我们要查的是哪个实例。我们把它改成gv$session就可以查询所有实例中的session了。记着改的时候几个过滤器中的sql语句都要改
当然,从这里我们也可以看到使用sql来查看session的方法
关于v$session中的各个字段,以下为摘录oracleAPI中的说明
V$SESSION
This view lists session information for each current session.
SADDR
RAW(4 | 8)
Session addressSID
NUMBER
Session identifierSERIAL#
NUMBER
Session serial number. Used to uniquely identify a session's objects. Guarantees that session-level commands are applied to the correct session objects if the session ends and another session begins with the same session ID.AUDSID
NUMBER
Auditing session IDPADDR
RAW(4 | 8)
Address of the process that owns the sessionUSER#
NUMBER
Oracle user identifierUSERNAME
VARCHAR2(30)
Oracle usernameCOMMAND
NUMBER
Command in progress (last statement parsed); for a list of values, see Table 7-5. These values also appear in the AUDIT_ACTIONS
table.OWNERID
NUMBER
The column contents are invalid if the value is 2147483644
. Otherwise, this column contains the identifier of the user who owns the migratable session.For operations using Parallel Slaves, interpret this value as a 4-byte value. The low-order 2 bytes of which represent the session number, and the high-order bytes the instance ID of the query coordinator.
TADDR
VARCHAR2(8)
Address of transaction state objectLOCKWAIT
VARCHAR2(8)
Address of lock waiting for; null if noneSTATUS
VARCHAR2(8)
Status of the session:ACTIVE
- Session currently executing SQLINACTIVE
KILLED
- Session marked to be killedCACHED
- Session temporarily cached for use by Oracle*XASNIPED
- Session inactive, waiting on the client
SERVER
VARCHAR2(9)
Server type (DEDICATED
| SHARED
| PSEUDO
| NONE
)SCHEMA#
NUMBER
Schema user identifierSCHEMANAME
VARCHAR2(30)
Schema user nameOSUSER
VARCHAR2(30)
Operating system client user namePROCESS
VARCHAR2(12)
Operating system client process IDMACHINE
VARCHAR2(64)
Operating system machine nameTERMINAL
VARCHAR2(30)
Operating system terminal namePROGRAM
VARCHAR2(48)
Operating system program nameTYPE
VARCHAR2(10)
Session typeSQL_ADDRESS
RAW(4 | 8)
Used with SQL_HASH_VALUE
to identify the SQL statement that is currently being executedSQL_HASH_VALUE
NUMBER
Used with SQL_ADDRESS
to identify the SQL statement that is currently being executedSQL_ID
VARCHAR2(13)
SQL identifier of the SQL statement that is currently being executedSQL_CHILD_NUMBER
NUMBER
Child number of the SQL statement that is currently being executedPREV_SQL_ADDR
RAW(4 | 8)
Used with PREV_HASH_VALUE
to identify the last SQL statement executedPREV_HASH_VALUE
NUMBER
Used with SQL_HASH_VALUE
to identify the last SQL statement executedPREV_SQL_ID
VARCHAR2(13)
SQL identifier of the last SQL statement executedPREV_CHILD_NUMBER
NUMBER
Child number of the last SQL statement executedMODULE
VARCHAR2(48)
Name of the currently executing module as set by calling theDBMS_APPLICATION_INFO.SET_MODULE
procedureMODULE_HASH
NUMBER
Hash value of the above MODULE
ACTION
VARCHAR2(32)
Name of the currently executing action as set by calling theDBMS_APPLICATION_INFO.SET_ACTION
procedureACTION_HASH
NUMBER
Hash value of the above action nameCLIENT_INFO
VARCHAR2(64)
Information set by the DBMS_APPLICATION_INFO.SET_CLIENT_INFO
procedureFIXED_TABLE_SEQUENCE
NUMBER
This contains a number that increases every time the session completes a call to the database and there has been an intervening select from a dynamic performance table. This column can be used by performance monitors to monitor statistics in the database. Each time the performance monitor looks at the database, it only needs to look at sessions that are currently active or have a higher value in this column than the highest value that the performance monitor saw the last time. All the other sessions have been idle since the last time the performance monitor looked at the database.ROW_WAIT_OBJ#
NUMBER
Object ID for the table containing the row specified in ROW_WAIT_ROW#
ROW_WAIT_FILE#
NUMBER
Identifier for the datafile containing the row specified in ROW_WAIT_ROW#
. This column is valid only if the session is currently waiting for another transaction to commit and the value ofROW_WAIT_OBJ#
is not -1
.ROW_WAIT_BLOCK#
NUMBER
Identifier for the block containing the row specified in ROW_WAIT_ROW#
. This column is valid only if the session is currently waiting for another transaction to commit and the value ofROW_WAIT_OBJ#
is not -1
.ROW_WAIT_ROW#
NUMBER
Current row being locked. This column is valid only if the session is currently waiting for another transaction to commit and the value of ROW_WAIT_OBJ#
is not -1
.LOGON_TIME
DATE
Time of logonLAST_CALL_ET
NUMBER
If the session STATUS
is currently ACTIVE
, then the value represents the elapsed time in seconds since the session has become active.If the session STATUS
is currently INACTIVE
, then the value represents the elapsed time in seconds since the session has become inactive.
PDML_ENABLED
VARCHAR2(3)
This column has been replaced by column PDML_STATUS
FAILOVER_TYPE
VARCHAR2(13)
Indicates whether and to what extent transparent application failover (TAF) is enabled for the session:NONE
- Failover is disabled for this sessionSESSION
- Client is able to fail over its session following a disconnectSELECT
- Client is able to fail over queries in progress as well
See Also:
Oracle Database Concepts for more information on TAF
Oracle Database Net Services Administrator's Guide for information on configuring TAF
FAILOVER_METHOD
VARCHAR2(10)
Indicates the transparent application failover method for the session:NONE
- Failover is disabled for this sessionBASIC
- Client itself reconnects following a disconnectPRECONNECT
- Backup instance can support all connections from every instance for which it is backed up
FAILED_OVER
VARCHAR2(3)
Indicates whether the session is running in failover mode and failover has occurred (YES
) or not (NO
)RESOURCE_CONSUMER_GROUP
VARCHAR2(32)
Name of the session's current resource consumer groupPDML_STATUS
VARCHAR2(8)
If ENABLED
, the session is in a PARALLEL DML enabled mode. If DISABLED
, PARALLEL DML enabled mode is not supported for the session. If FORCED
, the session has been altered to force PARALLEL DML.PDDL_STATUS
VARCHAR2(8)
If ENABLED
, the session is in a PARALLEL DDL enabled mode. If DISABLED
, PARALLEL DDL enabled mode is not supported for the session. If FORCED
, the session has been altered to force PARALLEL DDL.PQ_STATUS
VARCHAR2(8)
If ENABLED
, the session is in a PARALLEL QUERY enabled mode. If DISABLED
, PARALLEL QUERY enabled mode is not supported for the session. If FORCED
, the session has been altered to force PARALLEL QUERY.CURRENT_QUEUE_DURATION
NUMBER
If queued (1
), the current amount of time the session has been queued. If not currently queued, the value is 0
.CLIENT_IDENTIFIER
VARCHAR2(64)
Client identifier of the sessionBLOCKING_SESSION_STATUS
VARCHAR2(11)
Blocking session status:VALID
NO HOLDER
GLOBAL
NOT IN WAIT
UNKNOWN
BLOCKING_INSTANCE
NUMBER
Instance identifier of blocking sessionBLOCKING_SESSION
NUMBER
Session identifier of blocking sessionSEQ#
NUMBER
Sequence number that uniquely identifies the wait. Incremented for each wait.EVENT#
NUMBER
Event numberEVENT
VARCHAR2(64)
Resource or event for which the session is waitingSee Also: Appendix C, "Oracle Wait Events"
P1TEXT
VARCHAR2(64)
Description of the first additional parameterP1
NUMBER
First additional parameterP1RAW
RAW(4)
First additional parameterP2TEXT
VARCHAR2(64)
Description of the second additional parameterP2
NUMBER
Second additional parameterP2RAW
RAW(4)
Second additional parameterP3TEXT
VARCHAR2(64)
Description of the third additional parameterP3
NUMBER
Third additional parameterP3RAW
RAW(4)
Third additional parameterWAIT_CLASS_ID
NUMBER
Identifier of the wait classWAIT_CLASS#
NUMBER
Number of the wait classWAIT_CLASS
VARCHAR2(64)
Name of the wait classWAIT_TIME
NUMBER
A nonzero value is the session's last wait time. A zero value means the session is currently waiting.SECONDS_IN_WAIT
NUMBER
If WAIT_TIME
= 0
, then SECONDS_IN_WAIT
is the seconds spent in the current wait condition. IfWAIT_TIME
> 0
, then SECONDS_IN_WAIT
is the seconds since the start of the last wait, andSECONDS_IN_WAIT
- WAIT_TIME
/ 100
is the active seconds since the last wait ended.STATE
VARCHAR2(19)
Wait state:0 - WAITING
(the session is currently waiting)-2 - WAITED UNKNOWN TIME
(duration of last wait is unknown)-1 - WAITED SHORT TIME
(last wait <1/100th of a second)>0 - WAITED KNOWN TIME
(WAIT_TIME
= duration of last wait)
SERVICE_NAME
VARCHAR2(64)
Service name of the sessionSQL_TRACE
VARCHAR2(8)
Indicates whether SQL tracing is enabled (ENABLED
) or disabled (DISABLED
)SQL_TRACE_WAITS
VARCHAR2(5)
Indicates whether wait tracing is enabled (TRUE
) or not (FALSE
)SQL_TRACE_BINDS
VARCHAR2(5)
Indicates whether bind tracing is enabled (TRUE
) or not (FALSE
- 当oracle有多个实例时,用pl/sql查看session
- oracle pl/sql实例
- oracle pl/sql实例练习
- oracle pl/sql 完整实例
- oracle pl/sql实例练习
- oracle pl/sql实例练习
- Oracle Pl/Sql 查看建表语句
- ORACLE PL/SQL实例精解笔记
- ORACLE PL/SQL实例精解笔记
- Oracle PL/SQL 编程基础 实例
- Oracle PL/SQL 编程基础 实例 2
- oracle 怎么查看表空间,求高手指点,可以用PL/SQL查看
- 用PL/SQL连接Oracle
- oracle pl/sql kill指定用户的session
- 查看PL/SQL编译时的错误信息
- 查看PL/SQL编译时的错误信息
- 4、Oracle PL/SQL编译错误查看与处理
- 查看Oracle PL 锁一例
- 从B树、B+树、B*树谈到R 树
- 从文本文件中读取随机数,并打印出数字所出现的次数,按次数降序排列
- 我是新人,好紧张啊
- poj 1159 DP
- 关于dll的一些事
- 当oracle有多个实例时,用pl/sql查看session
- 渣硕笔记——偶读《TCP/IP详解:卷1 协议》 关于IP路由选项和时间戳选项
- poj2481 树状数组
- CentOS-min 操作记录(3)
- STL map常用操作简介
- 进制转换算法
- pjsip 搭建 sip服务器
- Android开源项目SlidingMenu深入剖析
- CListCtrl控件失去焦点时仍保持高亮