admin SQLs - Check session or lock table-CSDN博客

select    nvl(ses.USERNAME,'ORACLE PROC') username,
    OSUSER os_user,
    PROCESS pid,
    ses.SID sid,
    SERIAL#,
    PHYSICAL_READS,
    BLOCK_GETS,
    CONSISTENT_GETS,
    BLOCK_CHANGES,
    CONSISTENT_CHANGES
from    v$session ses,
    v$sess_io sio
where     ses.SID = sio.SID
order     by PHYSICAL_READS, ses.USERNAME;


select  nvl(ss.USERNAME,'ORACLE PROC') username,
    se.SID,
    sn.NAME stastic,
    VALUE usage
from     v$session ss,
    v$sesstat se,
    v$statname sn
where      se.STATISTIC# = sn.STATISTIC#
and      se.SID = ss.SID
and    se.VALUE > 0
order      by sn.NAME, se.SID, se.VALUE desc;

select * from v$session;  --489 491 484


select    nvl(ses.USERNAME,'ORACLE PROC') username,
    OSUSER os_user,
    PROCESS pid,
    ses.SID sid,
    SERIAL#,
    PHYSICAL_READS,
    BLOCK_GETS,
    CONSISTENT_GETS,
    BLOCK_CHANGES,
    CONSISTENT_CHANGES
from    v$session ses,
    v$sess_io sio
where     ses.SID = sio.SID
order     by PHYSICAL_READS, ses.USERNAME;

select * from v$session where suser = 'rmai';

SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program
FROM   gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND'
and SID in (484,
491,
499,
531);

--Find out the running sql script
SELECT SE.sid, SE.serial#, PR.spid, SE.status, SUBSTR(SE.program, 1, 10) PROG,
  SUBSTR(SE.machine, 1, 10) MACH,
  SQ.sql_text
FROM v$session SE, v$sqlarea SQ, v$process PR
WHERE SE.paddr = PR.ADDR(+)
AND SE.sql_address = SQ.address(+)
AND schemaname <> 'SYS'
ORDER BY SE.sid;


--Find out the lock table
select c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine, b.logon_time, vp.spid
from v$locked_object a , v$session b,  dba_objects c, v$process  vp
where b.sid = a.session_id and vp.addr=b.paddr and a.object_id = c.object_id;


--Kill session
alter system kill session 'sid,serial#' ;


--Check Users
select
  username,
  osuser,
  terminal
--  utl_inaddr.get_host_address(terminal) IP_ADDRESS
from
  v$session
where
  username is not null
order by
  username,
  osuser;