====== SESSIONS ======
  * utilisateur courant
<code PLSQL>
select user from dual
</code>

  * sessions en cours Standalone (SQLPLUS)
<code PLSQL>
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,
       b.blocking_session
FROM v$session b, v$process a
WHERE
b.paddr = a.addr
AND TYPE='USER'
ORDER BY spid; 
</code>


  * sessions en cours RAC (SQLPLUS)
<code PLSQL>
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,
       b.blocking_session
FROM gv$session b, gv$process a
WHERE
b.paddr = a.addr
AND TYPE='USER'
ORDER BY spid; 
</code>

  * sessions en cours (SQL DEV)
<code PLSQL>
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;
</code>

  * sessions en cours RAC (SQL DEV)
<code PLSQL>
SELECT DISTINCT q.sql_id,
       q.plan_hash_value,
       p.spid,
       s.STATE,
       s.username,
       s.INST_ID,
       y.INSTANCE_NAME,
       y.HOST_NAME "DATABASE HOST",
       s.machine "CLIENT HOST",
       s.sid,
       s.program,
       s.serial#,
       s.blocking_session_status,
       s.blocking_session,
       w.event,
       SUBSTR(q.sql_text, 1, 100) sql_text
FROM gv$session s
JOIN gv$sql q ON q.address = s.sql_address AND q.hash_value = s.sql_hash_value
JOIN gv$process p ON p.addr = s.paddr
JOIN gv$session_wait w ON w.sid = s.sid
JOIN gv$instance y on y.INST_ID=s.INST_ID
WHERE s.status = 'ACTIVE'
  AND w.event NOT LIKE 'SQL%'
  AND w.event NOT LIKE 'rdbms%'
ORDER BY q.sql_id;
</code>

  * generate kill session 
<code PLSQL>
 select 'alter system kill session '||''''||sid||','||serial#||','
            ||'@'||INST_ID||''''||' immediate;' 
     from gv$session 
    where USERNAME='xxxxx' order by INST_ID;
</code>

  * lock
<code PLSQL>
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 ;
</code>

  * Nombre de session en cours
<code PLSQL>
select count(MACHINE) from v$session;
</code>



  * modifier le nombre sessions possible (processes)
<code PLSQL>
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
</code>