고정 헤더 영역
상세 컨텐츠
본문
SET DOCUMENT OFF
/*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ +
+ FileName : session.sql +
+ Purpose : Show session info +
+ Genarated : 2006/07/03 by Corner +
+ Modified : 2006/08/03 Requested by dhkim +
+ show default active session +
+ 2006/08/07 Requested by skcho +
+ add idle time(last_call_et) +
+ +
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
SET RECSEP OFF
SET LINESIZE 165
SET PAGESIZE 200
SET VERIFY OFF
CLEAR COLUMN
COLUMN sid FORMAT A25 HEADING 'SID|SERIAL#'
COLUMN osuser FORMAT A8
COLUMN machine FORMAT A12
COLUMN command FORMAT A8
COLUMN os_pid FORMAT A8 HEADING 'ORACLE|OS PID'
COLUMN program FORMAT A15 TRUNC
COLUMN client FORMAT A8 HEADING 'CLIENT|OS PID'
COLUMN ulock FORMAT A2 HEADING 'LK'
COLUMN rollname FORMAT A4 HEADING 'ROLL|BACK|NAME'
COLUMN status FORMAT A3 HEADING 'STA|TUS'
COLUMN logon_time FORMAT A8 HEADING 'LOGON|TIME|DD HH:MM'
COLUMN idle_time FORMAT A8 HEADING 'IDLE|TIME|HH:MM:SS'
ACCEPT username PROMPT 'Enter username [Enter for all user]; ' DEFAULT '%'
ACCEPT status PROMPT 'Enter status [Enter for active session]; ' DEFAULT 'ACTIVE'
SELECT s.audsid,
s.username||'('||s.sid||','||s.serial#||')' sid,
p.spid os_pid,
DECODE(VSIZE(s.process), 9, 'Window', s.process) client,
s.machine,
s.osuser,
s.sql_address,
s.sql_hash_value,
SUBSTR(s.status, 1, 3) status,
DECODE(s.command, 1, 'CreTab', -- CREATE TABLE
2, 'INSERT', -- INSERT
3, 'SELECT', -- SELECT
4, 'CreClu', -- CREATE CLUSTER
5, 'AltClu', -- ALTER CLUSTER
6, 'UPDATE', -- UPDATE
7, 'DELETE', -- DELETE
8, 'DrpClu', -- DROP CLUSTER
9, 'CreIdx', -- CREATE INDEX
10, 'DrpIdx', -- DROP INDEX
11, 'AltIdx', -- ALTER INDEX
12, 'DrpTab', -- DROP TABLE
13, 'CreSeq', -- CREATE SEQUENCE
14, 'AltSeq', -- ALTER SEQUENCE
15, 'AltTab', -- ALTER TABLE
16, 'DrpSeq', -- DROP SEQUENCE
17, 'GrtObj', -- GRANT OBJECT
18, 'RvkObj', -- REVOKE OBJECT
19, 'CreSyn', -- CREATE SYNONYM
20, 'DrpSyn', -- DROP SYNONYM
21, 'CreView', -- CREATE VIEW
22, 'DrpView', -- DROP VIEW
23, 'ValIdx', -- VALIDATE INDEX
24, 'CreProc', -- CREATE PROCEDURE
25, 'AltProc', -- ALTER PROCEDURE
26, 'LOCK', -- LOCK
27, 'NO-OP', -- NO-OP
28, 'RENAME', -- RENAME
29, 'Commnt', -- COMMENT
30, 'AdtObj', -- AUDIT OBJECT
31, 'NoAdtObj', -- NOAUDIT OBJECT
32, 'CreLink', -- CREATE DATABASE LINK
33, 'DrpLink', -- DROP DATABASE LINK
34, 'CreDB', -- CREATE DATABASE
35, 'AltDB', -- ALTER DATABASE
36, 'CreRS', -- CREATE ROLLBACK SEG
37, 'AltRS', -- ALTER ROLLBACK SEG
38, 'DrpRS', -- DROP ROLLBACK SEG
39, 'CreTbs', -- CREATE TABLESPACE
40, 'AltTbs', -- ALTER TABLESPACE
41, 'DrpTbs', -- DROP TABLESPACE
42, 'AltSess', -- ALTER SESSION
43, 'AltUser', -- ALTER USER
44, 'COMMIT', -- COMMIT
45, 'RollBk', -- ROLLBACK
46, 'SavPoin', -- SAVEPOINT
47, 'PL/SQL', -- PL/SQL EXECUTE
48, 'SetTran', -- SET TRANSACTION
49, 'AltSys', -- ALTER SYSTEM
50, 'EXPLAIN', -- EXPLAIN
51, 'CreUser', -- CREATE USER
52, 'CreRole', -- CREATE ROLE
53, 'DrpUser', -- DROP USER
54, 'DrpRole', -- DROP ROLE
55, 'SetRole', -- SET ROLE
56, 'CREATE SCHEMA', -- CREATE SCHEMA
57, 'CreCtrl', -- CREATE CONTROL FILE
59, 'CreTrig', -- CREATE TRIGGER
60, 'AltTrig', -- ALTER TRIGGER
61, 'DrpTrig', -- DROP TRIGGER
62, 'AnalTab', -- ANALYZE TABLE
63, 'AnalIdx', -- ANALYZE INDEX
64, 'AnalClu', -- ANALYZE CLUSTER
65, 'CreProf', -- CREATE PROFILE
66, 'DrpProf', -- DROP PROFILE
67, 'AltProf', -- ALTER PROFILE
68, 'DrpProc', -- DROP PROCEDURE
70, 'ALTER RESOURCE COST', -- ALTER RESOURCE COST
71, 'CreMVLog', -- CREATE MATERIALIZED VIEW LOG
72, 'AltMVLog', -- ALTER MATERIALIZED VIEW LOG
73, 'DrpMVLog', -- DROP MATERIALIZED VIEW LOG
74, 'CreMV', -- CREATE MATERIALIZED VIEW
75, 'AltMV', -- ALTER MATERIALIZED VIEW
76, 'DrpMV', -- DROP MATERIALIZED VIEW
77, 'CreType', -- CREATE TYPE
78, 'DrpType', -- DROP TYPE
79, 'AltRole', -- ALTER ROLE
80, 'AltType', -- ALTER TYPE
81, 'CreTypeBd', -- CREATE TYPE BODY
82, 'AltTypeBd', -- ALTER TYPE BODY
83, 'DrpTypeBd', -- DROP TYPE BODY
84, 'DrpLib', -- DROP LIBRARY
85, 'TrcTab', -- TRUNCATE TABLE
86, 'TrcClu', -- TRUNCATE CLUSTER
91, 'CreFunc', -- CREATE FUNCTION
92, 'AltFunc', -- ALTER FUNCTION
93, 'DrpFunc', -- DROP FUNCTION
94, 'CrePkg', -- CREATE PACKAGE
95, 'AltPkg', -- ALTER PACKAGE
96, 'DrpPkg', -- DROP PACKAGE
97, 'CrePkgBd', -- CREATE PACKAGE BODY
98, 'AltPkgBd', -- ALTER PACKAGE BODY
99, 'DrpPkgBd', -- DROP PACKAGE BODY
100, 'LOGON', -- LOGON
101, 'LOGOFF', -- LOGOFF
102, 'LOGOFF BY CLEANUP', -- LOGOFF BY CLEANUP
103, 'SESSION REC', -- SESSION REC
104, 'SysAdt', -- SYSTEM AUDIT
105, 'SysNoAdt', -- SYSTEM NOAUDIT
106, 'AdtDef', -- AUDIT DEFAULT
107, 'NoAdtDef', -- NOAUDIT DEFAULT
108, 'SysGrt', -- SYSTEM GRANT
109, 'SysRvk', -- SYSTEM REVOKE
110, 'CrePubSyn', -- CREATE PUBLIC SYNONYM
111, 'DrpPubSyn', -- DROP PUBLIC SYNONYM
112, 'CrePubLink', -- CREATE PUBLIC DATABASE LINK
113, 'DrpPubLink', -- DROP PUBLIC DATABASE LINK
114, 'GrnRole', -- GRANT ROLE
115, 'RvkRole', -- REVOKE ROLE
116, 'ExeProc', -- EXECUTE PROCEDURE
117, 'UserComm', -- USER COMMENT
118, 'EnTrig', -- ENABLE TRIGGER
119, 'DisTrig', -- DISABLE TRIGGER
120, 'EnAllTrig', -- ENABLE ALL TRIGGERS
121, 'DisAllTrig', -- DISABLE ALL TRIGGERS
122, 'NetErr', -- NETWORK ERROR
123, 'ExeType', -- EXECUTE TYPE
157, 'CreDir', -- CREATE DIRECTORY
158, 'DrpDir', -- DROP DIRECTORY
159, 'CreLib', -- CREATE LIBRARY
160, 'CreJava', -- CREATE JAVA
161, 'AltJava', -- ALTER JAVA
162, 'DrpJava', -- DROP JAVA
163, 'CreOper', -- CREATE OPERATOR
164, 'CreIdxType', -- CREATE INDEXTYPE
165, 'DrpIdxType', -- DROP INDEXTYPE
-- 167, 'DrpOper', -- DROP OPERATOR
-- 168, 'ASSOCIATE STATISTICS', -- ASSOCIATE STATISTICS
-- 169, 'DISASSOCIATE STATISTICS', -- DISASSOCIATE STATISTICS
-- 170, 'CALL METHOD', -- CALL METHOD
-- 171, 'CreSumm', -- CREATE SUMMARY
-- 172, 'AltSumm', -- ALTER SUMMARY
-- 173, 'DrpSumm', -- DROP SUMMARY
-- 174, 'CreDimen', -- CREATE DIMENSION
-- 175, 'AltDimen', -- ALTER DIMENSION
-- 176, 'DrpDimen', -- DROP DIMENSION
-- 177, 'CreContext', -- CREATE CONTEXT
-- 178, 'DrpContext', -- DROP CONTEXT
-- 179, 'AltOutLn', -- ALTER OUTLINE
-- 180, 'CreOutLn', -- CREATE OUTLINE
-- 181, 'DrpOutLn', -- DROP OUTLINE
-- 182, 'UpdIdx', -- UPDATE INDEXES
-- 183, 'AltOper', -- ALTER OPERATOR
command) command,
DECODE(s.program, null, p.program, s.program) program,
DECODE(s.lockwait, null, null, 'X') ulock,
r.name rollname,
TO_CHAR(s.logon_time, 'DD HH24:MI') logon_time,
LPAD(FLOOR(last_call_et/3600), 2, 0)||':'||LPAD(FLOOR(MOD(last_call_et,3600)/60), 2, 0)||':'||LPAD(MOD(MOD(last_call_et,3600),60), 2, 0) idle_time
FROM v$session s,
v$process p,
v$transaction t,
v$rollname r
WHERE s.paddr = p.addr(+)
AND s.taddr = t.addr(+)
AND t.xidusn = r.usn(+)
AND s.username LIKE UPPER('&username')
AND s.status LIKE UPPER('&status')
order by idle_time
/
CLEAR BREAK
SET VERIFY ON
댓글 영역