Veri tabanındaki tüm sessionlarla ilgili bilgileri getiren script.
SET PAGESIZE 1000
SET FEEDBACK OFF
SELECT Substr(a.username,1,15) “Username”,
a.osuser “OS User”,
a.sid “Session ID”,
a.serial# “Serial No”,
d.spid “Process ID”,
a.lockwait “LockWait”,
a.status “Status”,
Trunc(b.value/1024) “PGA (Kb)”,
Trunc(e.value/1024) “UGA (Kb)”,
a.module “Module”,
Substr(a.machine,1,15) “Machine”,
a.program “Program”,
Substr(To_Char(a.logon_Time,’DD-Mon-YYYY HH24:MI:SS’),1,20) “Time”
FROM v$session a,
v$sesstat b,
v$statname c,
v$process d,
v$sesstat e,
v$statname f
WHERE a.paddr = d.addr
AND a.sid = b.sid
AND b.statistic# = c.statistic#
AND c.name = ‘session pga memory’
AND a.sid = e.sid
AND e.statistic# = f.statistic#
AND f.name = ‘session uga memory’
ORDER BY 1,2;
SET PAGESIZE 14
SET FEEDBACK ON
V$statname ve saat bilgilerini de içeren sorgu
v$session.process,v$session.sid,
v$session.serial#,
SUBSTR(v$session.program,1,23),v$session.status,
v$session.terminal,
SUBSTR(v$statname.name,1,30),
ROUND(((v$timer.hsecs – v$sesstat.VALUE)/100/60/60),2) HOURS
FROM v$timer,v$statname,v$sesstat,v$session
WHERE v$statname.statistic# = v$sesstat.statistic# AND
v$sesstat.statistic# IN (13,14) AND
v$sesstat.sid = v$session.sid
ORDER BY v$session.username,v$session.sid,v$sesstat.statistic#;
Belirtilen şemanın objlerine kimlerin eriştiğini gösteren script
SET PAGESIZE 1000
SET LINESIZE 255
SET VERIFY OFFSELECT Substr(a.object,1,30) object,
a.type,
a.sid,
b.username,
b.osuser,
b.program
FROM v$access a,
v$session b
WHERE a.sid = b.sid
AND a.owner = Upper(‘&1’);
PROMPT
SET PAGESIZE 18
Bağlı olan tüm kullanıcıları gösteren script
set lines 100 pages 999
col ID format a15
select username
, sid || ‘,’ || serial# “ID”
, status
, last_call_et “Last Activity”
from v$session
where username is not null
order by status desc
, last_call_et desc ;
Kullanıcıların aktif oldukları süreleri gösteren script
set lines 100 pages 999
select username
, floor(last_call_et / 60) “Minutes”
, status
from v$session
where username is not null
order by last_call_et ;
Kullanıcıları giriş yaptıkları zamana göre sıralayan script
select username
, osuser
, sid || ‘,’ || serial# “ID”
, status
, to_char(logon_time, ‘hh24:mi dd/mm/yy’) login_time
, last_call_et
from v$session
where username is not null
order by login_time
SID,Serial ve OS proses ID sini getiren script.
col username format a15
col osuser format a15
col program format a40
select s.sid || ‘,’ || s.serial# “SID/SERIAL”
, s.username
, s.osuser
, p.spid “OS PID”
, s.program
from v$session s
, v$process p
Where s.paddr = p.addr
order by to_number(p.spid)
İstenilen kullanıcının o an hangi sql çalıştırdığını getiren script
from v$sqlarea
where (address, hash_value) in
(select sql_address, sql_hash_value
from v$session
where username like ‘&username’)
Girilen OS proses ID nin Oracle tarafındaki bilgilerini getirir.
, s.sid
, s.serial#
, p.spid
, last_call_et
, status
from V$SESSION s
, V$PROCESS p
where s.PADDR = p.ADDR
and p.spid=’&pid’
Tüm aktif SQL leri getiren script.
set serveroutput on size 9999
column username format a20
column sql_text format a55 word_wrapped
begin
for x in
(select username||'(‘||sid||’,’||serial#||’) ospid = ‘|| process ||
‘ program = ‘ || program username,
to_char(LOGON_TIME,’ Day HH24:MI’) logon_time,
to_char(sysdate,’ Day HH24:MI’) current_time,
sql_address,
sql_hash_value
from v$session
where status = ‘ACTIVE’
and rawtohex(sql_address) ’00’
and username is not null ) loop
for y in (select sql_text
from v$sqlarea
where address = x.sql_address ) loop
if ( y.sql_text not like ‘%listener.get_cmd%’ and
y.sql_text not like ‘%RAWTOHEX(SQL_ADDRESS)%’ ) then
dbms_output.put_line( ‘——————–‘ );
dbms_output.put_line( x.username );
dbms_output.put_line( x.logon_time || ‘ ‘ || x.current_time || ‘ SQL#=’ || x.sql_hash_value);
dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
end;
Uzun süren işlemleri getiren script.
col username format a15
col message format a40
col remaining format 9999
select username
, to_char(start_time, ‘hh24:mi:ss dd/mm/yy’) started
, time_remaining remaining
, message
from v$session_longops
where time_remaining = 0
order by time_remaining desc;
Kullanıcıların açık cursor sayısını getiren script.
select sess.username
, sess.sid
, sess.serial#
, stat.value cursors
from v$sesstat stat
, v$statname sn
, v$session sess
where sess.username is not null
and sess.sid = stat.sid
and stat.statistic# = sn.statistic#
and sn.name = ‘opened cursors current’
order by value
Üsttekinin alternatifi
select count(hash_value) cursors
, sid
, user_name
from v$open_cursor
group by
sid
, user_name
order by
cursors
En çok kaynak Kullanan SQL leri getirir
SET PAGESIZE 1000
SET FEEDBACK OFF
SET VERIFY OFF
SET SERVEROUTPUT ON
PROMPT
DECLARE
CURSOR c_sql IS
SELECT Substr(a.sql_text,1,50) sql_text,
Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution,
a.buffer_gets,
a.disk_reads,
a.executions,
a.sorts,
a.address
FROM v$sqlarea a
ORDER BY 2 DESC;
BEGIN
Dbms_Output.Enable(1000000);
Dbms_Output.Put_Line(Rpad(‘SQL Text’,50,’ ‘) ||
Lpad(‘Reads/Execution’,16,’ ‘) ||
Lpad(‘Buffer Gets’,12,’ ‘) ||
Lpad(‘Disk Reads’,12,’ ‘) ||
Lpad(‘Executions’,12,’ ‘) ||
Lpad(‘Sorts’,12,’ ‘) ||
Lpad(‘Address’,10,’ ‘));
Dbms_Output.Put_Line(Rpad(‘-‘,50,’-‘) || ‘ ‘ ||
Lpad(‘-‘,15,’-‘) || ‘ ‘ ||
Lpad(‘-‘,11,’-‘) || ‘ ‘ ||
Lpad(‘-‘,11,’-‘) || ‘ ‘ ||
Lpad(‘-‘,11,’-‘) || ‘ ‘ ||
Lpad(‘-‘,11,’-‘) || ‘ ‘ ||
Lpad(‘-‘,9,’-‘));
FOR cur_rec IN c_sql LOOP
Dbms_Output.Put_Line(Rpad(cur_rec.sql_text,50,’ ‘) ||
Lpad(cur_rec.reads_per_execution,16,’ ‘) ||
Lpad(cur_rec.buffer_gets,12,’ ‘) ||
Lpad(cur_rec.disk_reads,12,’ ‘) ||
Lpad(cur_rec.executions,12,’ ‘) ||
Lpad(cur_rec.sorts,12,’ ‘) ||
Lpad(cur_rec.address,10,’ ‘));
IF c_sql%ROWCOUNT = &&1 THEN
EXIT top_sql;
END IF;
END LOOP;
END;
/
PROMPT
SET PAGESIZE 14
SET FEEDBACK ON
Girilen sql_address in sql’ini getirir
SET PAGESIZE 1000
SET FEEDBACK OFF
SET VERIFY OFF
SELECT a.sql_text “SQL Text”
FROM v$sqltext a
WHERE a.address = Upper(‘&&1’)
ORDER BY a.piece;
PROMPT
SET PAGESIZE 14
SET FEEDBACK ON
–v$session tablosunun SQL_ADDRESS kolonundaki değer girilecek
Çalışan job ları getirir
FROM dba_jobs_running r, dba_jobs j
WHERE r.job = j.job
Uzun süren tüm operasyonları getirir
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
SELECT s.sid,
s.serial#,
s.machine,
ROUND(sl.elapsed_seconds/60) || ‘:’ || MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ‘:’ || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM v$session s,
v$session_longops sl
WHERE s.sid = sl.sid
AND s.serial# = sl.serial#;
SQL cümlelerini EP ve disk bilgilerini getirir
, executions
, to_char((((disk_reads+buffer_gets)/executions) * 8192)/1048576, ‘9,999,999,990.00’)
as total_gets_per_exec_mb
, to_char((( disk_reads /executions) * 8192)/1048576, ‘9,999,999,990.00’)
as disk_reads_per_exec_mb
, to_char((( buffer_gets /executions) * 8192)/1048576, ‘9,999,999,990.00’)
as buffer_gets_per_exec_mb
, parsing_user_id
from v$sqlarea
where executions > 10
order by 6 desc
/
Açık cursorları getirir
a.sid,
a.sql_text
FROM v$open_cursor a
ORDER BY 1,2
/
OS proses id ve çalıştırdığı sql cümlesini getirir
SET PAGESIZE 1000
SET FEEDBACK OFF
SELECT s.sid,
s.status “Status”,
p.spid “Process”,
s.schemaname “Schema Name”,
s.osuser “OS User”,
Substr(a.sql_text,1,120) “SQL Text”,
s.program “Program”
FROM v$session s,
v$sqlarea a,
v$process p
WHERE s.sql_hash_value = a.hash_value (+)
AND s.sql_address = a.address (+)
AND s.paddr = p.addr;
SET PAGESIZE 14
SET FEEDBACK ON
/
Tree-Style Execution planı gösterir
COLUMN OBJECT_NAME FORMAT A30 HEADING “Object”
COLUMN OBJECT_TYPE FORMAT A10 HEADING “Type”
COLUMN BYTES FORMAT 9999999999 HEADING “Bytes”
COLUMN COST FORMAT 9999999 HEADING “Cost”
COLUMN PARTITION_START FORMAT 9999999 HEADING “PStart”
COLUMN PARTITION_STOP FORMAT 9999999 HEADING “PEnd”
SET PAGESIZE 1000
SET LINESIZE 1000
SET VERIFY OFF
SELECT LPAD(‘ ‘, 2 * (level – 1)) ||
Decode (level,1,NULL,level-1 || ‘.’ || pt.position || ‘ ‘) ||
Initcap(pt.operation) ||
Decode(pt.options,NULL,”,’ (‘ || Initcap(pt.options) || ‘)’) plan,
pt.object_name,
pt.object_type,
pt.bytes,
pt.cost,
pt.partition_start,
pt.partition_stop
FROM plan_table pt
START WITH pt.id = 0
AND pt.statement_id = ‘&1’
CONNECT BY PRIOR pt.id = pt.parent_id
AND pt.statement_id = ‘&1’;
SET VERIFY ON
SQL syntaxları,SID,Status,OS user Prcess id leri getiren başka bir script
SET LINESIZE 255
COL SID FORMAT 999
COL STATUS FORMAT A8
COL PROCESS FORMAT A10
COL SCHEMANAME FORMAT A16
COL OSUSER FORMAT A16
COL SQL_TEXT FORMAT A120 HEADING ‘SQL QUERY’
COL PROGRAM FORMAT A30
SELECT s.sid,
s.status,
s.process,
s.schemaname,
s.osuser,
a.sql_text,
p.program
FROM v$session s,
v$sqlarea a,
v$process p
WHERE s.SQL_HASH_VALUE = a.HASH_VALUE
AND s.SQL_ADDRESS = a.ADDRESS
AND s.PADDR = p.ADDR
/
SET VERIFY ON
SET LINESIZE 255
Çalışan Jobları getiren başka bir script
SET PAGESIZE 1000
SET VERIFY OFF
SELECT a.job “Job”,
a.sid,
a.failures “Failures”,
Substr(To_Char(a.last_date,’DD-Mon-YYYY HH24:MI:SS’),1,20) “Last Date”,
Substr(To_Char(a.this_date,’DD-Mon-YYYY HH24:MI:SS’),1,20) “This Date”
FROM dba_jobs_running a;
SET PAGESIZE 14
SET VERIFY ON
Belirli bir zaman aralığında çalıştırılan SQL’ler
FIRST_LOAD_TIME
BETWEEN
‘2011-01-10/13:00:08’
AND
‘2011-01-10/14:00:22’
