Setting Oracle Parameters: processes, sessions, open_cursors

SQL> SHOW parameter sessions;
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
sessions                             INTEGER     1105
 
-- Count all sessions
SQL> SELECT COUNT(*) FROM v$session;
  COUNT(*)
----------
       530
 
-- Count user sessions
SQL> SELECT COUNT(*) FROM V$SESSION
WHERE USERNAME = 'USER1';
 
  COUNT(*)
----------
        27
 
-- View session details
SQL> SELECT SID, SERIAL#, STATUS, SERVER FROM V$SESSION WHERE USERNAME='USER1';
 
       SID    SERIAL# STATUS   SERVER
---------- ---------- -------- ---------
        11        354 INACTIVE NONE
        22        272 INACTIVE NONE
        96        468 INACTIVE SHARED
        ...
      1064         77 INACTIVE NONE
 
26 ROWS selected.
 
-- Kill a session
ALTER SYSTEM KILL SESSION 'sid, serial#'
 
-- For example
ALTER SYSTEM KILL SESSION '23, 4241';
 
-- Session summary
SQL> SELECT
 rpad(c.name||':',11)||rpad(' current logons='||
 (to_number(b.sessions_current)),20)||'cumulative logons='||
 rpad(substr(a.value,1,10),10)||'highwater mark='||
 b.sessions_highwater Information
FROM
 v$sysstat a,
 v$license b,
 v$database c
WHERE
 a.name = 'logons cumulative';
 
INFORMATION
--------------------------------------------------------------------------------
 
XE:         CURRENT logons=59  cumulative logons=3068      highwater mark=1084
 
-- Session detail
SQL> SELECT
 substr(a.spid,1,9) pid,
 substr(b.sid,1,5) sid,
 substr(b.serial#,1,5) ser#,
 substr(b.machine,1,6) box,
 substr(b.username,1,10) username,
--       b.server,
 substr(b.osuser,1,8) os_user,
 substr(b.program,1,30) program
FROM v$session b, v$process a
WHERE
 b.paddr = a.addr
 AND TYPE='USER'
 ORDER BY username;
 
PID       SID   SER#  BOX    USERNAME   OS_USER  PROGRAM
--------- ----- ----- ------ ---------- -------- ------------------
5984      803   7     CENTER DBSNMP     NT AUTHO emagent.exe
5152      810   5     CENTER DBSNMP     NT AUTHO emagent.exe
5152      225   4     BOX1 USER1       SYSTEM   JDBC Thin Client
5152      223   18    BOX1 USER1       SYSTEM   JDBC Thin Client
...
5152      586   434   BOX1 USER2    SYSTEM   JDBC Thin Client
5152      166   50    BOX1 USER2    SYSTEM   JDBC Thin Client
...
5152      228   3377  BOX1 USER3
5152      231   213   BOX1 USER3
...
5152      847   14    BOX1 SYSMAN              OMS
5152      845   2     BOX1 SYSMAN              OMS
6928      844   3     BOX1 SYSMAN              OMS
 
PID       SID   SER#  BOX    USERNAME   OS_USER  PROGRAM
--------- ----- ----- ------ ---------- -------- ------------------
5152      839   340   BOX1 SYSMAN              OMS
7960      115   2646  DO\USR SYSTEM     DO\User- sqlplus.exe
2320      1086  635   BOX1            SYSTEM   ORACLE.EXE (J000)
 
58 ROWS selected.