1. find a long session in the database.

set linesize 200
set pagesize 999
col OPNAME for a10
col TARGET for a40
alter session set nls_date_format = 'dd-mm-yyyy hh24:mi:ss';

select a.inst_id,a.SID,a.OPNAME,a.TARGET,a.SOFAR,a.TOTALWORK,a.ELAPSED_SECONDS,a.start_time,a.SQL_ID
FROM GV$SESSION_LONGOPS a
where start_time >sysdate-10/1440
ORDER BY a.inst_id,a.START_TIME asc, a.ELAPSED_SECONDS desc;

2. find which session is running currently.

select a.sid,a.serial#,a.username,c.executions,status, b.sql_text,OSUSER
from v$session a,v$sqltext b,v$sqlarea c
where a.username is not null
--and status='ACTIVE'
--and username in ('PAMPERS')
--and sid in (11, 20, 46, 110, 127, 134, 136, 170, 243)
--and sid in (146)
--and SUSER = 'dsadm'
and a.sql_address = b.address(+)
and a.sql_address = c.address(+)
order by status,1,2,b.piece;

3, -找到鎖住表的sid和serial#

select sid,serial#
from v$session
where sid=(select sid
              from v$lock
             where id1= (select object_id
                           from user_objects
                          where object_name='TMP_REP_5'));

--刪除這個session
alter system kill session 'sid,serial#';
commit;

4. Find a blocking session in the database.

select l1.inst_id, l1.sid, ' IS BLOCKING ',l2.inst_id, l2.sid from gv$lock l1,gv$lock l2
where l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2;

SID 'ISBLOCKING' SID
----- ------------- ----------
1410 IS BLOCKING 461
451 IS BLOCKING 1078
1335 IS BLOCKING 1582

6. ====> show the following information to the application team and he will know which user/program using it.
set linesize 200
set pagesize 999
col usename for a10
col state for a10
col osuser for a10
col program for a20
col machine for a10
alter session set nls_date_format = 'dd-mm-yyyy hh24:mi:ss';

SELECT sid,serial#, inst_id, username, state, status, osuser, machine, program, sql_id, logon_time FROM gv$session
WHERE suser= 'webspher' AND status = 'ACTIVE' ;

SID INST_ID USERNAME STATE STATUS OSUSER USER# MACHINE SCHEMA# SQL_ID LOGON_TIME
---------- ---------- ---------- ------------------- -------- ---------- ---------- ---------- ---------- ------------- -------------------
2161 2 PLMAPP WAITED SHORT TIME ACTIVE webspher 294 svdg1212 294 fuf6k5qf6zxkh 01-11-2011 11:41:47

7. ====> find the session and check it can be killed or not ? confirm with Applications.

set linesize 150
set pagesize 999

SELECT sql_text FROM
v$session, v$sqltext_with_newlines
WHERE decode(v$session.sql_hash_value, 0, prev_hash_value, sql_hash_value) = v$sqltext_with_newlines.hash_value
AND v$session.sid = &sid
ORDER BY piece;

Enter value for sid: 892
old 5: AND v$session.sid = &sid
new 5: AND v$session.sid = 892

SQL_TEXT
----------------------------------------------------------------
SELECT ERR_OID, MQ_MSGE FROM ESM_PRCS_ERR WHERE ERR_OID = :1 FOR UPDATE NOWAIT

Oracle wait interface -- session

相關文章