Monotoring Session and SQL in Oracle10g

Some dynamic performance view to manage oracle user session and SQL.

·        v$sqlarea
·        v$session
·        v$sess_io
·        v$session
·        v$sqltext

Finding currently running SQL

 Select sesion.sid,
        sesion.username,
        optimizer_mode,
        hash_value,
        address,
        cpu_time,
        elapsed_time,
        sql_text
  from  v$sqlarea sqlarea, v$session sesion
  where sesion.sql_hash_value = sqlarea.hash_value
   and  sesion.sql_address    = sqlarea.address
   and  sesion.username is not null

See the I/O being done by an active SQL statement

select sess_io.sid,
       sess_io.block_gets,
       sess_io.consistent_gets,
       sess_io.physical_reads,
       sess_io.block_changes,
       sess_io.consistent_changes
from   v$sess_io sess_io, v$session sesion
where  sesion.sid = sess_io.sid
  and  sesion.username is not null

SQL to show the full SQL executing for active sessions.

select sesion.sid,
       sql_text
from   v$sqltext sqltext, v$session sesion
where  sesion.sql_hash_value = sqltext.hash_value
   and sesion.sql_address    = sqltext.address
   and sesion.username is not null
order by sqltext.piece

Which sessions are active this SQL will help

Select sid,
       to_char(logon_time,'MMDDYYYY:HH24:MI') logon_time,
       username,
       type,
       status,
       process,
       sql_address,
       sql_hash_value
from   v$session
where  username is not null

Killing Oracle Sessions

SQL Command to Kill Session

ALTER SYSTEM KILL SESSION 'sid,serial#';
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Killing your own session

alter session set events 'immediate crash';

Kill sessions from the UNIX level with this command

 
kill -9 spid

N.B: This is dangerous and should not normally be done. Pick the wrong OS process and you could crash your instance. There may also be time you need to bulk kill Oracle sessionsor kill all sessions in Oracle.

Simply selecting again v$session will get you the data needed

select 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||'''IMMEDIATE;'
from v$session
where username = '<SomeUserName in upper caps>'
 and  STATUS = 'ACTIVE';

Here is a Package to manage oracle session

create or replace package manage_users as
  procedure kill_user(the_sid in number);
  procedure kill_user(the_user in varchar2);
end manage_users;
/
create or replace package body manage_users as
  procedure kill_user(the_sid in number) as
    v_str varchar2(50) := 'alter system kill session '||chr(39);
    v_serial number;
  begin
    dbms_output.put_line(chr(10));
    dbms_output.put_line('Session to be killed:');
    dbms_output.put_line('---------------------');
    select serial# into v_serial
    from v$session
    where sid = the_sid;
    v_str := v_str||the_sid||','||v_serial||chr(39)||';';
    dbms_output.put_line(v_str);
    exception
    when no_data_found then
      dbms_output.put_line('No such session.');
  end;
  procedure kill_user(the_user in varchar2) as
    v_str varchar2(50) := 'alter system kill session '||chr(39);
    v_statement varchar2(50);
    cursor user_cur is
    select sid, serial#
    from v$session
    where username = upper(the_user);
  begin
    dbms_output.put_line(chr(10));
    dbms_output.put_line('Sessions to be killed:');
    dbms_output.put_line('----------------------');
    for user_rec in user_cur loop
     v_statement := v_str||user_rec.sid||','||user_rec.serial#||chr(39)||';';
     dbms_output.put_line(v_statement);
     v_statement := null;
    end loop;
  end;
end manage_users;
/

SQL> exec manage_users.kill_user('scott');
Sessions to be killed:
----------------------
alter system kill session '141,8';
alter system kill session '142,213';
alter system kill session '145,37';
PL/SQL procedure successfully completed.
SQL> exec manage_users.kill_user(141);
Session to be killed:
---------------------
alter system kill session '141,8';
PL/SQL procedure successfully completed.

Oracle Error : ORA-00018: maximum number of sessions exceeded

Cause: All session state objects are in use.

Action: Increase the value of the SESSIONS initialization parameter.

SELECT 'Currently, '
 || (SELECT COUNT(*) FROM V$SESSION) || ' out of '
 || DECODE(VL.SESSIONS_MAX,0,'unlimited',VL.SESSIONS_MAX)
 || ' connections are used.' AS USAGE_MESSAGE
FROM V$LICENSE VL

How to Solve the Problem

Alter system set sessions=200 scope=spfile;

shutdown immediate;

startup;