SESSIONS

• utilisateur courant

SELECT USER FROM dual

• sessions en cours Standalone (SQLPLUS)

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; 

• sessions en cours RAC (SQLPLUS)

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; 

• sessions en cours (SQL DEV)

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;

• sessions en cours RAC (SQL DEV)

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;

• 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;

• 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