Oracle Sessioni e processi

Processi e sessioni

Terminare sessioni

alter system disconnect session 'sid,serial#,@inst_id' immediate;

verifica valori limite

col resource_name for a40
select resource_name,current_utilization,max_utilization,limit_value from v$resource_limit;

Elenco processi di background

select s.sid,  s.process, s.program, p.name from v$session s join v$bgprocess p using (paddr) ;

sessioni

Sessioni Attive

set linesize 220
set pagesize 200
set autotrace off
col machine for a35
col username for a20
col program for a20
col sid_ser for a15
col spid for a10
alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
select s.sid ||','||s.serial# as sid_ser,p.spid,
 s.username,s.machine,s.sql_id,s.prev_sql_id,
 s.logon_time,(sysdate-s.sql_exec_start)*86400 sec_exec,substr(s.program,1,20) as program,s.seconds_in_wait as siw
 from v$session s
 left outer join v$process p on (p.addr=s.paddr)
 where s.type='USER' and s.status='ACTIVE'
 order by s.logon_time;

versione RAC

set linesize 220
set pagesize 200
set autotrace off
col machine for a35
col username for a20
col program for a20
col sid_ser for a15
col spid for a10
alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
select s.sid ||','||s.serial#||'@'||s.inst_id as sid_ser,p.spid,
 s.username,s.machine,s.sql_id,s.prev_sql_id,
 s.logon_time,(sysdate-s.sql_exec_start)*86400 sec_exec,substr(s.program,1,20) as program,s.seconds_in_wait as siw
 from gv$session s
 left outer join gv$process p on (p.addr=s.paddr and s.inst_id=p.inst_id)
 where s.type='USER' and s.status='ACTIVE'
 order by s.logon_time;

con eventi

set linesize 230
set pagesize 300
set autotrace off
col machine for a21
col username for a20
col program for a20
col sid_ser for a15
col spid for a10
col sql_id for a14
col prev_sql_id for a14
col siw for 9999999
col status for a15
col event for a32
alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
select s.sid ||','||s.serial# as sid_ser,p.spid,s.status,
 s.username,substr(s.machine,1,20) as machine,s.sql_id,s.prev_sql_id,
 s.logon_time,(sysdate-s.sql_exec_start)*86400 sec_exec,substr(s.program,1,20) as program,s.seconds_in_wait as siw,s.event
 from v$session s
 left outer join v$process p on (p.addr=s.paddr)
 where s.type='USER'
 --and s.status='ACTIVE'
 -- and s.username not in ('SYS','DBSNMP')
 order by s.status desc,s.logon_time;

Sessioni ordinate per tipo

set linesize 200
set pagesize 200
set autotrace off
col machine for a21
col username for a20
col program for a20
col sid_ser for a15
col spid for a10
alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
select s.sid ||','||s.serial# as sid_ser,p.spid,s.status,
 s.username,substr(s.machine,1,20) as machine,s.sql_id,s.prev_sql_id,
 s.logon_time,substr(s.program,1,20) as program,s.seconds_in_wait as siw
 from v$session s
 left outer join v$process p on (p.addr=s.paddr)
 where s.type='USER'
-- and s.username not in ('SYS','DBSNMP')
 order by s.status,s.logon_time;

--

alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
select s.sid ||','||s.serial# as sid_ser,p.spid,
s.username,substr(s.machine,1,20) as machine,s.sql_id,s.prev_sql_id,
s.logon_time,substr(s.program,1,20) as program,s.seconds_in_wait as siw
from v$session s
left outer join v$process p on (p.addr=s.paddr)
where s.type='USER'
and s.status='ACTIVE'
and s.username not in ('SYS','DBSNMP')
order by s.logon_time;

long operations

set linesize 200
set pagesize 200
set autotrace off
col sid for 9999
col serial# for 9999999
col sid_ser for a12
col username for A30
col machine for A50
col prog% for 999.00
col elapsed for A10
col remaining for A10
col message for a80
spool longops2.log
select s.sid || ',' || s.serial# sid_ser,
s.username,
-- 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) "prog%",
s.sql_id,
replace(sl.message,'Blocks done','') as message
from v$session s
inner join v$session_longops sl on ( s.sid = sl.sid AND s.serial# = sl.serial# AND sl.time_remaining > 0 )
order by s.sql_id;
spool off

Rollback

select state,UNDOBLOCKSDONE,UNDOBLOCKSTOTAL, UNDOBLOCKSDONE/UNDOBLOCKSTOTAL*100 from gv$fast_start_transactions;