ACTIVE SESSION & Inactive session AND BLOCKING SESSION IN ORACLE DB

Active :- waiters

Inactive:- holders 

dba_blockers:- non-waiting sessions

dba_ddl_locks:- ddl locks

dba_dml_locks:- dml locks

dba_lock_interval:- 1 row for every lock username

dba_locks:- show all locks/latches

dba_waiters:- waiting session

dba_objects, all_objects, user_objects, dba_segments, gv$loced_objects, gv$session_longops

Real-time monitoring of sql query:- gv$sql_plan_monitor

 col inst_id for a13
col sid for a12
col serial# for a12
col username for a14
col status for a12
col server for a14
col schemaname for a12
col osuser for a14
col machine for a13
col program for a13
select inst_id,sid,serial#,username,status,server,schemaname,osuser,machine,program,sql_id from gv$session
where status = 'ACTIVE';
#################### Working status and seconds v$session ###############################
col "Description" format a50
select sid,
        decode(state, 'WAITING','Waiting',
                'Working') state,
        decode(state,
                'WAITING',
                'So far '||seconds_in_wait,
                'Last waited '||
                wait_time/100)||
        ' secs for '||event
        "Description"
from gv$session
where username = 'schema';
###################################################################################
select SID, osuser, machine, terminal, service_name,
       logon_time, last_call_et
from gv$session
where username = 'schema';
###################################################################################
Session wait for each machine 
-------------------------------------------
col username format a5
col program format a10
col state format a10
col last_call_et head 'Called|secs ago' format 999999
col seconds_in_wait head 'Waiting|for secs' format 999999
col event format a50
select sid, username, program,
        decode(state, 'WAITING', 'Waiting',
                'Working') state,
last_call_et, seconds_in_wait, event
from gv$session
where machine = 'host_name';

########################################################################

Blocking sessions occur when a session issues an "insert, update or delete" command that changes a row. 

When the change occurs,"the row is locked until the session either commits the change, rolls the change 

back or the user logs off the system." 

    select

blocking_session,

sid,

serial#,

wait_class,

seconds_in_wait

    from

        v$session

    where

blocking_session is not NULL

    order by

blocking_session;

=========================================================================

How to check Locking session/Blocking session

=========================================================================

 col username for a15

 col machine for a20

 col event for a20

 col program for a20

 col osuser for a20

 set line 3000

 set pagesize 2000

 SELECT

 Inst_id,

    s.username,

    s.blocking_session blk_sess,

    s.blocking_instance blk_inst,

    s.sid, 

    s.serial#, 

    s.sql_id,

    s.status,

    s.seconds_in_wait waittime,

    s.event,

    s.machine 

 FROM

    gv$session s

 WHERE

    blocking_session IS NOT NULL order by SECONDS_IN_WAIT;

=========================================================================

###################################  How to search users on database##################

SET LINESIZE 100

COLUMN spid FORMAT A10

COLUMN username FORMAT A10

COLUMN program FORMAT A45

SELECT s.inst_id,

       s.sid,

       s.serial#,

       --s.sql_id,

       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';

   INST_ID        SID    SERIAL# SPID       USERNAME   PROGRAM

---------- ---------- ---------- ---------- ---------- ---------------------------------------------

         1         30         15 3859       TEST       sqlplus@oel5-11gr2.localdomain (TNS V1-V3)

         1         23        287 3834       SYS        sqlplus@oel5-11gr2.localdomain (TNS V1-V3)

         1         40        387 4663                  oracle@oel5-11gr2.localdomain (J000)

         1         38        125 4665                  oracle@oel5-11gr2.localdomain (J001)

############################ To get spid and then kill them#########################

$ ps -ef | grep ora 

$ kill -9 spid 

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';   -------------------------> single instance db

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';  ------------------> RAC instance db

###############################################################