SELECT USER FROM dual
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;
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;
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;
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;
SELECT 'alter system kill session '||''''||sid||','||serial#||',' ||'@'||INST_ID||''''||' immediate;' FROM gv$session WHERE USERNAME='xxxxx' ORDER BY INST_ID;
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 ;
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 /
SELECT COUNT(MACHINE) FROM v$session;
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