備忘録：Oracle schema毎の利用セッション数

SELECT 
        A.USERNAME,
        count(*)
FROM    v$session a, 
        v$sesstat b, 
        v$sess_io c, 
        v$statname d 
WHERE   a.sid        = b.sid 
    AND b.sid        = c.sid 
    AND b.statistic# = d.statistic# 
    AND d.name like '%CPU%session' 
group by A.USERNAME
ORDER BY a.username
;

 

SET LINESIZE 500; 
SET PAGESIZE 80; 
SET COLSEP |; 
SET FEEDBACK OFF; 
COLUMN "SID/SERIAL" FORMAT A15; 
COLUMN USERNAME FORMAT A10; 
COLUMN "SID/SERIAL" FORMAT A15; 
COLUMN OSUSER FORMAT A10; 
COLUMN MACHINE FORMAT A15; 
COLUMN TERMINAL FORMAT A10; 
COLUMN PROGRAM FORMAT A35; 
COLUMN TYPE FORMAT A10; 
COLUMN COMMAND FORMAT A20; 
COLUMN COMMANDid ; 
COLUMN "経過時間(sec)　" FORMAT 9,999,999,990; 
COLUMN "CPU使用時間(sec)" FORMAT 9,999,990.000; 
COLUMN "CPU占有率(%)" FORMAT 990.000; 
COLUMN "ブロック取得回数" FORMAT 9,999,999,990; 
COLUMN "物理読み込み回数" FORMAT 9,999,999,990; 
TTITLE LEFT '【 セッションの確認 】' SKIP 1 LINE ; 
SELECT  A.SID || ',' || A.SERIAL# "SID/SERIAL", 
        A.OSUSER, 
        A.MACHINE, 
        A.TERMINAL, 
        A.PROGRAM, 
        A.TYPE, 
        A.USERNAME, 
        A.STATUS, 
        DECODE(A.COMMAND ,1,'CREATE TABLE' ,2,'INSERT' ,3,'SELECT' ,
        4,'CREATE CLUSTER' ,5,'ALTER CLUSTER' ,6,'UPDATE' ,7,'DELETE' ,
        8,'DROP CLUSTER' ,9,'CREATE INDEX' ,10,'DROP INDEX' ,
        11,'ALTER INDEX' ,12,'DROP TABLE' ,13,'CREATE SEQUENCE' ,
        14,'ALTER SEQUENCE' ,15,'ALTER TABLE' ,16,'DROP SEQUENCE' ,
        17,'GRANT' ,18,'REVOKE' ,19,'CREATE SYNONYM' ,20,'DROP SYNONYM' ,
        21,'CREATE VIEW' ,22,'DROP VIEW' ,23,'VALIDATE INDEX' ,
        24,'CREATE PROCEDURE' ,25,'ALTER PROCEDURE' ,26,'LOCK TABLE' ,
        27,'NO OPERATION' ,28,'RENAME' ,29,'COMMENT' ,30,'AUDIT' ,
        31,'NOAUDIT' ,32,'CREATE DATABASE LINK' ,33,'DROP DATABASE LINK' ,
        34,'CREATE DATABASE' ,35,'ALTER DATABASE' ,36,'CREATE ROLLBACK SEG' ,
        37,'ALTER ROLLBACK SEGM' ,38,'DROP ROLLBACK SEGME' ,
        39,'CREATE TABLESPACE' ,40,'ALTER TABLESPACE' ,41,'DROP TABLESPACE' ,
        42,'ALTER SESSION' ,43,'ALTER USE' ,44,'COMMIT' ,45,'ROLLBACK' ,
        46,'SAVEPOINT' ,47,'PL/SQL EXECUTE' ,48,'SET TRANSACTION' ,
        49,'ALTER SYSTEM SWITCH' ,50,'EXPLAIN' ,51,'CREATE USER' ,
        52,'CREATE ROLE' ,53,
        'DROP USER' ,54,'DROP ROLE' ,55,'SET ROLE' ,56,'CREATE SCHEMA' ,
        57,'CREATE CONTROL FILE' ,58,'ALTER TRACING' ,59,'CREATE TRIGGER' ,
        60,'ALTER TRIGGER' ,61,'DROP TRIGGER' ,62,'ANALYZE TABLE' ,
        63,'ANALYZE INDEX' ,64,'ANALYZE CLUSTER' ,65,'CREATE PROFILE' ,
        66,'DROP PROFILE' ,67,'ALTER PROFILE' ,68,'DROP PROCEDURE' ,
        69,'DROP PROCEDURE' ,70,'ALTER RESOURCE COST' ,
        71,'CREATE SNAPSHOT LOG' ,72,'ALTER SNAPSHOT LOG' ,
        73,'DROP SNAPSHOT LOG' ,74,'CREATE SNAPSHOT' ,
        75,'ALTER SNAPSHOT' ,76,'DROP SNAPSHOT' ,
        79,'ALTER SESSION' ,85,'TRUNCATE TABLE' ,86,'TRUNCATE COUSTER' ,
        88,'ALTER VIEW' ,91,'CREATE FUNCTION' ,92,'ALTER FUNCTION' ,
        93,'DROP FUNCTION' ,94,'CREATE PACKAGE' ,95,'ALTER PACKAGE' ,
        96,'DROP PACKAGE' ,97,'CREATE PACKAGE BODY' ,
        98,'ALTER PACKAGE BODY' ,99,'DROP PACKAGE BODY' ,'待機中') COMMAND , 
        A.COMMAND commandid,
        (sysdate - a.logon_time)*86400 "経過時間(sec)", 
        b.value  /100 "CPU使用時間(sec)", 
        b.value  /((sysdate - a.logon_time)*86400+1) "CPU占有率(%)", 
        c.block_gets "ブロック取得回数", 
        c.physical_reads "物理読み込み回数" 
FROM    v$session a, 
        v$sesstat b, 
        v$sess_io c, 
        v$statname d 
WHERE   a.sid        = b.sid 
    AND b.sid        = c.sid 
    AND b.statistic# = d.statistic# 
    AND d.name like '%CPU%session' 
ORDER BY a.username, 
        a.sid;