SESSIONS

• utilisateur courant

SELECT USER FROM dual

• sessions en cours

SET lines 500
col LOGON_TIME format a15
col MACHINE format a20
col PID format a8
col SID format a8
col ser# format a8
col username format a20
col os_user format a18
col BOX format a20
SELECT 
       b.LOGON_TIME,
       b.MACHINE,
       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,
       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 spid; 

• generate kill session

 SELECT 'alter system kill session '||''''||sid||','||serial#||','
            ||'@'||INST_ID||''''||' immediate;' 
     FROM gv$session 
    WHERE USERNAME='xxxxx' ORDER BY INST_ID;

• lock

SELECT 'Instance '||s1.INST_ID||' '|| s1.username || '@' || s1.machine
    || ' ( SID=' || s1.sid || ','|| s1.serial#||s1.status||  '  )  is blocking '
    || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' ||s2.sql_id
     FROM gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
    WHERE s1.sid=l1.sid AND
     s1.inst_id=l1.inst_id AND
     s2.sid=l2.sid AND
     s2.inst_id=l2.inst_id AND
     l1.BLOCK=1 AND
    l2.request > 0 AND
    l1.id1 = l2.id1 AND
    l2.id2 = l2.id2 ;

• Nombre de session en cours

SELECT COUNT(MACHINE) FROM v$session;

• liste des sessions en cours

SET lines 500
col LOGON_TIME format a15
col MACHINE format a20
col PID format a8
col SID format a8
col ser# format a8
col username format a20
col os_user format a18
col BOX format a20
SELECT 
       b.LOGON_TIME,
       b.MACHINE,
       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,
       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 spid;

• modifier le nombre sessions possible (processes)

SQL> show parameter processes;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      INTEGER     1
db_writer_processes                  INTEGER     2
gcs_server_processes                 INTEGER     2
global_txn_processes                 INTEGER     1
job_queue_processes                  INTEGER     80
log_archive_max_processes            INTEGER     4
processes                            INTEGER     400
 
SQL> CREATE pfile=/tmp/pfile_db1.ora FROM spfile;
SQL> ALTER system SET processes=1000 scope spfile;
SQL> shutdown IMMEDIATE
SQL> startup mount
SQL> ALTER database OPEN