====== 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>

  * récupérer la transaction avec un PID determine
<code PLSQL>
col sid format 9999
col pid format 9999
col serial# format 99999
col process format a8 heading "unixPID"
col spid format a8 heading "unixPID"
col username format a9
col addr format a11
col program format a20 trunc
col logon_time format a18
col osuser format a8 heading unixUsr
col p_user format a9 heading unixUsr
col terminal format a7 heading unixtrm
col command format 99 heading Cd
col machine format a7
col action format a7
col module format a10
set pagesize 24
prompt "Enter the Oracle Session ID (SID) user in question"
select p.PID,
p.SERIAL#,
p.USERNAME p_user,
p.SPID,
to_char(s.logon_time,'DD-MON-YY HH24:MI:SS') Logon_Time,
s.program,
s.command,
s.sid,
s.serial#,
s.username,
s.process,
s.machine,
s.action,
s.module,
s.osuser,
s.terminal
from v$process p,
v$session s
where s.sid = &sid
and p.addr = s.paddr
order by s.logon_time
/
</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>