Script pro výpis sessions dle využití RAM.

select
e.sid,
e.username,
e.osuser,
e.terminal,
e.MACHINE,
e.status,
e.state,
e.SQL_ID,
e.sql_exec_start,
a.uga_memory as UGA_MEMORY_KB,
b.pga_memory as PGA_MEMORY_KB,
c.sql_text,
substr(e.program,1,20) program,
decode(e.command,
1,'Create table' , 2,'Insert',
3,'Select' , 6,'Update',
7,'Delete' , 9,'Create index',
10,'Drop index' ,11,'Alter index',
12,'Drop table' ,13,'Create seq',
14,'Alter sequence' ,15,'Alter table',
16,'Drop sequ.' ,17,'Grant',
19,'Create syn.' ,20,'Drop synonym',
21,'Create view' ,22,'Drop view',
23,'Validate index' ,24,'Create procedure',
25,'Alter procedure' ,26,'Lock table',
42,'Alter session' ,44,'Commit',
45,'Rollback' ,46,'Savepoint',
47,'PL/SQL Exec' ,48,'Set Transaction',
60,'Alter trigger' ,62,'Analyze Table',
63,'Analyze index' ,71,'Create Snapshot Log',
72,'Alter Snapshot Log' ,73,'Drop Snapshot Log',
74,'Create Snapshot' ,75,'Alter Snapshot',
76,'drop Snapshot' ,85,'Truncate table',35,'Alter database',
170,'Call method',182,'Update indexes',114,'Grant role',
0,'No command', '? : '||e.command) nocommand,
e.event
from v$session e
left join
(select y.SID,
TO_CHAR(ROUND(y.value/1024),99999999) UGA_MEMORY
from
v$sesstat y,
v$statname z
where
y.STATISTIC# = z.STATISTIC#
and
NAME = 'session uga memory') a
ON a.sid=e.sid
left join
(select
y.SID,
TO_CHAR(ROUND(y.value/1024),99999999) PGA_MEMORY
from
v$sesstat y, v$statname z
where
y.STATISTIC# = z.STATISTIC#
and
NAME = 'session pga memory') b
on e.sid=b.sid
left join DBA_HIST_SQLTEXT c
on c.sql_id = e.sql_id
--where e.STATUS = 'ACTIVE'
order by
e.status,
a.uga_memory desc;
Příspěvek byl publikován v rubrice Admin scripts, Oracle SQL. Můžete si uložit jeho odkaz mezi své oblíbené záložky.