【예제】
SQL> connect system/password
Connected.
SQL> select sid, serial#, username, user# from v$session;
SID SERIAL# USERNAME USER#
---------- ---------- ------------------------------ ----------
1 1 0
2 1 0
3 1 0
4 1 0
5 1 0
6 1 0
7 1 0
8 1 0
9 97 SYSTEM 5
10 274 JIJOE 93
10 rows selected.
SQL>
사용자 jijoe의 세션을 종료하려며,
SQL> alter system kill session '10,274';
System altered.
SQL>
이 상태에서 jijoe 사용자가 SQL 문을 실행하면 다음과 같이 출력한다.
SQL> select sid, serial#, username from v_session;
ORA-00028: your session has been killed
SQL>
【예제】 disconnecting a session
【예제】terminating/Disconnecting a session
SQL> select sid, serial#, username from v$session;
SID SERIAL# USERNAME
---------- ---------- --------------------
128 17995
134 11945
135 7317 SYS
141 5
142 2
146 8
149 1
151 1
153 2
154 12
157 3
158 1
159 1
160 1
161 1
162 1
163 1
164 3
165 1
166 1
167 1
168 1
169 1
170 6374 JIJOE
24 rows selected.
SQL> alter system kill session '170,6374';
System altered.
SQL> select sid, serial#, username from v$session;
SID SERIAL# USERNAME
---------- ---------- --------------------
128 17995
135 7317 SYS
141 5
142 2
146 8
149 1
151 1
153 2
154 12
157 3
158 1
159 1
160 1
161 1
162 1
163 1
164 3
165 1
166 1
167 1
168 1
169 1
170 6374 JIJOE
23 rows selected.
SQL> alter system disconnect session '170,6374' post_transaction;
System altered.
SQL> select sid, serial#, username from v$session;
SID SERIAL# USERNAME
---------- ---------- --------------------
128 17995
134 11959
135 7317 SYS
141 5
142 2
146 8
149 1
151 1
153 2
154 12
157 3
158 1
159 1
160 1
161 1
162 1
163 1
164 3
165 1
166 1
167 1
168 1
169 1
170 6374 JIJOE
24 rows selected.
SQL>
【예제】
SQL> select username,sid,serial#,event,(seconds_in_wait/1000000)
2 "Session in wait"
3 from v$session where username='JIJOE';
USERNAME SID SERIAL# EVENT Session in wait
------------ ---------- ---------- --------------------------- ---------------
JIJOE 140 799 SQL*Net message to client 0
SQL>
참조 : v$session_wait
SADDR : Session address로 session의 주소값
SID : Session ID
SERIAL# : SESSION 번호
USERNAME: SESSION을 사용하는 user 이름
SQL> SELECT schemaname,process,program FROM V$SESSION;
SCHEMANAME PROCESS PROGRAM
--------------- ------------ ------------------------------------------------
SYS 29715 sqlplus@jinpo (TNS V1-V3)
SYS 29705 oracle@jinpo (q000)
SYS
SYS 512 oracle@jinpo (MMNL)
SYS 510 oracle@jinpo (MMON)
SYS 504 oracle@jinpo (QMNC)
SYS 490 oracle@jinpo (CJQ0)
SYS 488 oracle@jinpo (RECO)
SYS 486 oracle@jinpo (SMON)
SYS 484 oracle@jinpo (CKPT)
SYS 482 oracle@jinpo (LGWR)
SYS 480 oracle@jinpo (DBW0)
SYS 478 oracle@jinpo (MMAN)
SYS 476 oracle@jinpo (PMON)
14 개의 행이 선택되었습니다.
SQL>
【예제】
SQL> conn system/manager as sysdba
Connected.
SQL> desc v$session
Name Null? Type
----------------------------------------- -------- ----------------------------
SADDR RAW(8)
SID NUMBER
SERIAL# NUMBER
AUDSID NUMBER
PADDR RAW(8)
USER# NUMBER
USERNAME VARCHAR2(30)
COMMAND NUMBER
OWNERID NUMBER
TADDR VARCHAR2(16)
LOCKWAIT VARCHAR2(16)
STATUS VARCHAR2(8)
SERVER VARCHAR2(9)
SCHEMA# NUMBER
SCHEMANAME VARCHAR2(30)
OSUSER VARCHAR2(30)
PROCESS VARCHAR2(24)
MACHINE VARCHAR2(64)
TERMINAL VARCHAR2(30)
PROGRAM VARCHAR2(48)
TYPE VARCHAR2(10)
SQL_ADDRESS RAW(8)
SQL_HASH_VALUE NUMBER
SQL_ID VARCHAR2(13)
SQL_CHILD_NUMBER NUMBER
SQL_EXEC_START DATE
SQL_EXEC_ID NUMBER
PREV_SQL_ADDR RAW(8)
PREV_HASH_VALUE NUMBER
PREV_SQL_ID VARCHAR2(13)
PREV_CHILD_NUMBER NUMBER
PREV_EXEC_START DATE
PREV_EXEC_ID NUMBER
PLSQL_ENTRY_OBJECT_ID NUMBER
PLSQL_ENTRY_SUBPROGRAM_ID NUMBER
PLSQL_OBJECT_ID NUMBER
PLSQL_SUBPROGRAM_ID 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(8)
P2TEXT VARCHAR2(64)
P2 NUMBER
P2RAW RAW(8)
P3TEXT VARCHAR2(64)
P3 NUMBER
P3RAW RAW(8)
WAIT_CLASS_ID NUMBER
WAIT_CLASS# NUMBER
WAIT_CLASS VARCHAR2(64)
WAIT_TIME NUMBER
SECONDS_IN_WAIT NUMBER
STATE VARCHAR2(19)
WAIT_TIME_MICRO NUMBER
TIME_REMAINING_MICRO NUMBER
TIME_SINCE_LAST_WAIT_MICRO NUMBER
SERVICE_NAME VARCHAR2(64)
SQL_TRACE VARCHAR2(8)
SQL_TRACE_WAITS VARCHAR2(5)
SQL_TRACE_BINDS VARCHAR2(5)
SQL_TRACE_PLAN_STATS VARCHAR2(10)
SESSION_EDITION_ID NUMBER
CREATOR_ADDR RAW(8)
CREATOR_SERIAL# NUMBER
SQL> select module from v$session;
MODULE
--------------------------------------------------------------------------------
OEM.DefaultPool
emagent@unix (TNS V1-V3)
OEM.SystemPool
OEM.SystemPool
OEM.SystemPool
OEM.DefaultPool
OEM.CacheModeWaitPool
OEM.SystemPool
emagent@unix (TNS V1-V3)
OEM.CacheModeWaitPool
STREAMS
STREAMS
OEM.CacheModeWaitPool
KTSJ
KTSJ
STREAMS
sqlplus@unix (TNS V1-V3)
MODULE
--------------------------------------------------------------------------------
34 rows selected.
SQL>