Differences

This shows you the differences between two versions of the page.

--- database_sessions [2018/12/06 21:05] – created 91.177.234.129
+++ database_sessions [2021/03/18 21:11] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
-====== Database_sessions ======
+  * [[https://techgoeasy.com/enqueue-and-locks/|Oracle Locks & table locks: How it works - techgoeasy.com]]
-=====Sessions sorted by login time=====
+==== List Users Created on the Database (including dba role grantee) ====
-<code>0@@</code>
+<code>
-=====Free space in TEMP tablespaces=====
+CLEAR BREAKS
-<code>1@@</code>
+CLEAR COLUMNS
+CLEAR COMPUTES
+COL username HEA "      User Name      " FOR a21
+COL default_tablespace HEA " Default Tablespace " FOR a20;
+COL temporary_tablespace HEA "Temporary Tablespace" FOR a20;
+COL grole HEA " DBA " FOR a5;
+SELECT
+   LPAD( DECODE( p.granted_role, 'DBA' , '*' ), 3 ) grole,
+   u.username, u.default_tablespace, u.temporary_tablespace
+FROM
+   dba_users u,
+   (
+   SELECT
+      grantee, granted_role
+   FROM
+      dba_role_privs
+   WHERE
+      granted_role = 'DBA'
+   ) p
+WHERE
+   u.username = p.grantee (+)
+ORDER BY
+   u.username
+/
+</code>
+==== List logged on users with O/S info ====
+<code>
+COL orauser HEA "   Oracle User   " FOR a17 TRUNC
+COL osuser HEA " O/S User " FOR a10 TRUNC
+COL ssid HEA "Sid" FOR a4
+COL sserial HEA "Serial#" FOR a7
+COL ospid HEA "O/S Pid" FOR a7
+COL slogon HEA "  Logon Time  " FOR a14
+COL sstat HEA "Status" FOR a6
+COL auth HEA "Auth" FOR a4
+COL conn HEA "Con" FOR a3
+SELECT
+   ' '||NVL( s.username, '    ????    ' ) orauser,
+   ' '||s.osuser osuser,
+   LPAD( s.sid, 4 ) ssid, LPAD( s.serial#, 6 ) sserial,
+   LPAD( p.spid, 6 ) ospid,
+   INITCAP( LOWER( TO_CHAR( logon_time, 'MONDD HH24:MI:SS' ) ) ) slogon,
+   DECODE( s.status, 'ACTIVE', ' Busy ', 'INACTIVE', ' Idle ', 'KILLED', ' Kill ', '  ??  ' ) sstat,
+   DECODE( sc.authentication_type, 'DATABASE', ' DB ', 'OS', ' OS ', ' ?? ' ) auth,
+   DECODE( s.server, 'DEDICATED', 'Dir', 'NONE', 'Mts', 'SHARED', 'Mts', '???' ) conn
+FROM
+   v$session s, v$process p,
+   (
+   SELECT
+      DISTINCT sid, authentication_type
+   FROM
+      v$session_connect_info
+   ) sc
+WHERE
+   s.paddr = p.addr AND s.sid = sc.sid
+ORDER BY
+   s.status,s.sid
+/
+</code>
+==== Check if Session Tracing is On ====
+<code>
+set lines 180
+col module for a45
+col sql_trace_waits for a20
+col sql_trace_binds for a20
+col sql_trace for a20
-=====Live TEMP free space monitoring=====
+select username,module,sid,sql_trace,sql_trace_waits,sql_trace_binds from v$session where sql_trace='ENABLED'
-<code>2@@</code>
+/
-=====Sessions currently holding segments in TEMP tablespace=====
+</code>
-<code>3@@</code>
+==== Enable Session Tracing ====
-=====More detailed sessions currently holding segments in TEMP tablespace=====
+<code>
-<code>4@@</code>
+EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(&sid,&serial);
+</code>
+==== Disable Session Tracing ====
+</code>
+EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(&sid,&serial);
+</code>
+==== Lists all Locked Objects ====
+<code>
+SET PAGESIZE 60
+SET LINESIZE 300
+COLUMN owner FORMAT A20
+COLUMN username FORMAT A20
+COLUMN object_owner FORMAT A20
+COLUMN object_name FORMAT A30
+COLUMN locked_mode FORMAT A15
+SELECT b.session_id AS sid,
+       NVL(b.oracle_username, '(oracle)') AS username,
+       a.owner AS object_owner,
+       a.object_name,
+       Decode(b.locked_mode, 0, 'None',
+, 'Null (NULL)',
+, 'Row-S (SS)',
+, 'Row-X (SX)',
+, 'Share (S)',
+, 'S/Row-X (SSX)',
+, 'Exclusive (X)',
+                             b.locked_mode) locked_mode,
+       b.os_user_name
+FROM   dba_objects a,
+       gv$locked_object b
+WHERE  a.object_id = b.object_id
+ORDER BY 1, 2, 3, 4
+/
+</code>
+==== Setting sessions and transactions parameters using processes ====
+If building a new database,..
+<code>
+"SESSIONS parameter is the maximum number of sessions that can be created in the system. Every login requires a session.
+It is calculated from PROCESSES parameter:
+g -> ((1.1 * PROCESSES) + 5)
+g -> ((1.5 * PROCESSES) + 22)
+.2-> ((1.5 * PROCESSES) + 22)
+You may leave SESSIONS and let Database choose its value automatically."
+TRANSACTIONS = (1.1 * SESSIONS)
+</code>
+or if updating an existing database...
+<code>
+select (&&processes*1.5)+22      sessions_11g_upwards
+,      ((&processes*1.5)+22)*1.1 transactions_11g_upwards
+from   dual;
+undef processes
+</code>
+Then again, you could just set processes and let the database work out what to set sessions and transactions to...
+<code>
+alter system reset sessions scope=spfile sid='*';
+alter system reset transactions scope=spfile sid='*';
+</code>
+==== Sessions sorted by login time ====
+<code>
+ set lines 200
+ set pages 999
+ col killer      format a15
+ col osuser      format a15
+ col login_time  format a15
+ select s.username
+ ,      s.osuser
+ ,      s.sid || ',' || s.serial# "Killer"
+ ,      p.spid                    "OS PID"
+ ,      s.status
+ ,      to_char(s.logon_time, 'dd-mon-yy hh24:mi') login_time
+ ,      s.last_call_et
+ from   v$session s
+ ,      v$process p
+ where  1=1
+ and    s.paddr    = p.addr
+ and    s.username is not null
+ order  by login_time;
+</code>
+==== Free space in TEMP tablespaces ====
+<code>
+select tablespace_name temp_name
+,      tablespace_size/1024/1024 temp_size
+,      allocated_space/1024/1024 temp_alloc
+,      free_space/1024/1024 temp_free
+from   dba_temp_free_space
+/
+</code>
+==== Live TEMP free space monitoring ====
+<code>
+select a.tablespace_name                                             tablespace
+,      d.mb_total                                                    mb_total
+,      sum (a.used_blocks * d.block_size) / 1024 / 1024              mb_used
+,      d.mb_total - sum (a.used_blocks * d.block_size) / 1024 / 1024 mb_free
+from   v$sort_segment a
+,      (
+       select b.name
+       ,      c.block_size
+       ,      sum (c.bytes) / 1024 / 1024 mb_total
+       from   v$tablespace b
+       ,      v$tempfile   c
+       where  b.ts# = c.ts#
+       group  by b.name
+       ,      c.block_size
+       ) d
+where  a.tablespace_name = d.name
+group  by a.tablespace_name
+,      d.mb_total
+/
+</code>
+==== Sessions currently holding segments in TEMP tablespace ====
+<code>
+select b.tablespace
+,      b.segfile#
+,      b.segblk#
+,      b.blocks
+,      a.sid
+,      a.serial#
+,      a.username
+,      a.osuser, a.status
+from  v$session a
+,     v$sort_usage b
+where  a.saddr = b.session_addr
+order  by b.tablespace
+,      b.segfile#
+,      b.segblk#
+,      b.blocks
+/
+</code>
+==== More detailed sessions currently holding segments in TEMP tablespace ====
+<code>
+set lines 1000 pages 1000
+col sid_serial for a10
+col username   for a15
+col osuser     for a10
+col spid       for a10
+col module     for a35
+col program    for a25
+col tablespace for a10
+select s.sid || ',' || s.serial# sid_serial
+,      s.username
+,      s.osuser
+,      p.spid
+,      s.module
+,      s.program
+,      sum (t.blocks) * tbs.block_size / 1024 / 1024 mb_used
+,      t.tablespace
+,      count(*) sort_ops
+from   v$sort_usage t
+,      v$session s
+,      dba_tablespaces tbs
+,      v$process p
+where  t.session_addr = s.saddr
+and    s.paddr = p.addr and t.tablespace = tbs.tablespace_name
+group  by s.sid
+,      s.serial#
+,      s.username
+,      s.osuser
+,      p.spid
+,      s.module
+,      s.program
+,      tbs.block_size
+,      t.tablespace
+order  by sid_serial
+/
+</code>
 or
-<code>5@@</code>
+<code>
+SET PAGESIZE 50
+SET LINESIZE 300
+COLUMN tablespace FORMAT A20
+COLUMN temp_size FORMAT A20
+COLUMN sid_serial FORMAT A20
+COLUMN username FORMAT A20
+COLUMN program FORMAT A50
+SELECT b.tablespace,
+       ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size,
+       a.inst_id as Instance,
+       a.sid||','||a.serial# AS sid_serial,
+       NVL(a.username, '(oracle)') AS username,
+       a.program,
+       a.status,
+       a.sql_id
+FROM   gv$session a,
+       gv$sort_usage b,
+       gv$parameter p
+WHERE  p.name  = 'db_block_size'
+AND    a.saddr = b.session_addr
+AND    a.inst_id=b.inst_id
+AND    a.inst_id=p.inst_id
+ORDER BY b.tablespace, b.blocks
+/
+</code>
+==== SQL statements used by TEMP sessions ====
+<code>
+select s.sid || ',' || s.serial# sid_serial
+,      s.username
+,      t.blocks * tbs.block_size / 1024 / 1024 mb_used
+,      t.tablespace
+,      t.sqladdr address
+,      q.hash_value
+,      q.sql_text
+from   v$sort_usage t
+,      v$session s
+,      v$sqlarea q
+,      dba_tablespaces tbs
+where  t.session_addr = s.saddr
+and    t.sqladdr      = q.address (+)
+and    t.tablespace   = tbs.tablespace_name
+order  by s.sid
+/
+</code>
+==== Drop a schema but drop its sessions first ====
+Sometimes dropping a user proves difficult because database processes owned by the user are still running. This script will kill all sessions owned by the schema before dropping it.
+<code>
+set serveroutput on
+variable schema2drop varchar2(50);
+execute :schema2drop := '&1';
+declare
+    open_count integer;
+begin
+    dbms_output.enable(null);
+    -- prevent any further connections
+    execute immediate 'alter user '||:schema2drop||' account lock';
+    --kill all sessions
+    for session in (
+        select sid, serial#
+        from  v$session
+        where username = ':schema2drop'
+    )
+    loop
+        dbms_output.put_line ('alter system disconnect session ''' || session.sid || ',' || session.serial# || ''' immediate;');
+        execute immediate 'alter system disconnect session ''' || session.sid || ',' || session.serial# || ''' immediate';
+    end loop;
+    -- killing is done in the background, so we need to wait a bit
+    loop
+        select count(*)
+        into open_count
+        from  v$session where username = '''||:schema2drop||''';
+        exit when open_count = 0;
+        dbms_lock.sleep(0.5);
+    end loop;
-=====SQL statements used by TEMP sessions=====
+    -- finally, it is safe to issue the drop statement
-<code>6@@</code>
+    dbms_output.put_line ('drop user '||:schema2drop||' cascade;');
+    execute immediate 'drop user '||:schema2drop||' cascade';
+end;
+</code>
+or same thing in SQL*Plus
+<code>
+</code>
-=====Show a users current sql=====
+==== Show a users current sql ====
+<code>
  select sql_text
  from   v$sqlarea
@@ Line 25: / Line 355: @@
         (
         select sql_address
         ,      sql_hash_value
         from   v$session
         where  1=1
         and    username like '&username'
         );
-=====Top 10 SQL statements=====
+</code>
+==== Top 10 SQL statements ====
 Lists all SQL statements in the library cache with CPU time > &1 &1 % of the total of all statements
-<code>7@@</code>
+<code>
+define CPUTimePct = '&1'
+@sqlplus_settings
+@nls_settings
+@header_title 155 DC 'All statements in the library cache with a total CPU time >= &CPUTimePct % of all statements together'
-=====Show what SQL statements are being run by all user sessions=====
+COLUMN cpu_time_total       FORMAT      9G999G990 HEADING "CPU time|total"
-<code>8@@</code>
+COLUMN sql_text             FORMAT            A60 HEADING "Sql text"
+COLUMN pct                  FORMAT          990D9 HEADING "%"
+COLUMN buffer_gets_per_exec FORMAT 99G999G999G999 HEADING "Buffer gets|per exec"
+COLUMN wait_per_exec        FORMAT      999G990D9 HEADING "Wait time|per exec"
+COLUMN cpu_per_exec         FORMAT      999G990D9 HEADING "CPU time|per exec"
+COLUMN disk_reads_per_exec  FORMAT    999G999G999 HEADING "Disk reads|per exec"
+COLUMN executions           FORMAT     99G999G999 HEADING "Executions"
+COLUMN sql_id               FORMAT            A13 HEADING "Sql id"
-=====Show blocking sessions using Oracle standard script=====
+SET FEEDBACK  ON
+SET ARRAYSIZE 1
+SET LONG      2000
+SET RECSEP    WRAPPED
+SET PAGESIZE  100
+WITH total_cpu_time
+  AS ( SELECT sum(cpu_time) total_cpu FROM v$sqlarea )
+SELECT cpu_time/1000000                                                    cpu_time_total,
+       (cpu_time*100)/total_cpu                                            pct,
+       sql_text,
+       executions,
+       cpu_time/(decode(executions,0,1,executions)*1000000)                cpu_per_exec,
+       (elapsed_time-cpu_time)/(decode(executions,0,1,executions)*1000000) wait_per_exec,
+       buffer_gets/decode(executions,0,1,executions)                       buffer_gets_per_exec,
+       disk_reads/decode(executions,0,1,executions)                        disk_reads_per_exec,
+       sql_id
+  FROM v$sqlarea,
+       total_cpu_time
+ WHERE cpu_time >= &CPUTimePct*total_cpu/100
+ ORDER BY cpu_time desc;
+undefine CPUTimePct
+SET ARRAYSIZE 20
+SET LONG      200
+</code>
+==== Show what SQL statements are being run by all user sessions ====
+<code>
+col username  for a12
+col sid       for 99999
+col sql_text  for a200
+set lines 1000 pages 1000 long 100000
+select a.username
+,      to_char(a.logon_time,'DD-MON-YY HH24:MI:SS') dt
+,      a.sid
+,      a.serial#
+,      b.sql_text
+from   v$session a
+,      v$sqlarea b
+where  1=1
+and    a.sql_address = b.address;
+</code>
+==== Incative sessions still holding lots of memory can probably be killed (after consulting the relevant user) ====
+<code>
+SELECT
+s.sid,s.serial#,s.username
+,s.status
+,substr(s.machine,1,10)
+,s.osuser,s.module
+,to_char(logon_time, 'mm/dd/yy hh24:mi:ss') logon_time
+-- idle time
+-- days separately
+,substr('0'||trunc(last_call_et/86400),-2,2) || ':' ||
+-- hours
+substr('0'||trunc(mod(last_call_et,86400)/3600),-2,2) || ':' ||
+-- minutes
+substr('0'||trunc(mod(mod(last_call_et,86400),3600)/60),-2,2) || ':' ||
+--seconds
+substr('0'||mod(mod(mod(last_call_et,86400),3600),60),-2,2) idle_time
+FROM v$session s, v$process p
+WHERE s.username IS NOT NULL
+-- use outer join to show sniped sessions in
+-- v$session that don't have an OS process
+AND p.addr(+) = s.paddr
+and s.sid=nvl(&SID,s.sid)
+ORDER BY
+idle_time desc;
+</code>
+==== Show SQL transaction usage ====
+For example after killing a session with alter system disconnect session immediate, it takes a long time to die, use this script to see how long it will take to disappear by checking how many undo blocks still remain occupied.
+<code>
+select vs.sid
+,      vs.username
+,      vt.xidusn
+,      vt.used_urec
+,      vt.used_ublk
+from   v$session     vs
+,      v$transaction vt
+where  vs.saddr = vt.ses_addr
+/
+</code>
+or a little more detailed...
+<code>
+select s.username username,
+   s.program program,
+   decode(s.command,
+,'No Command',
+,'Create Table',
+,'Insert',
+,'Select',
+,'Update',
+,'Delete',
+,'Create Index',
+,'Alter Table',
+,'Create View',
+,'Validate Index',
+,'Alter Database',
+,'Create Tablespace',
+,'Drop Tablespace',
+,'Alter Tablespace',
+,'Drop User',
+,'Analyze Table',
+,'Analyze Index',
+     s.command||': Other') command
+,      t.used_ublk
+from
+   v$session     s,
+   v$process     p,
+   v$transaction t,
+   v$rollstat    r,
+   v$rollname    n
+where s.paddr = p.addr
+and s.taddr = t.addr (+)
+and t.xidusn = r.usn (+)
+and r.usn = n.usn (+)
+and s.username='SYS'
+order by 1
+/
+</code>
+produces
+<code>
+USERNAME        PROGRAM                                            COMMAND                 USED_UBLK
+--------------- -------------------------------------------------- ---------------------- ----------
+SYS             [email protected] (TNS V1-V3)                  Update                       3751
+SYS             [email protected] (TNS V1-V3)                  Select
+</code>
+==== Other useful tables to look at for blocking objects ====
+<code>
+V$ACCESS
+DBA_DDL_LOCKS
+V$LOCKED_OBJECT
+</code>
+==== List Database Sessions including any blocking ====
+<code>
+SET HEADING ON
+SET LINESIZE 300
+SET PAGESIZE 60
+COLUMN username FORMAT A21
+COLUMN osuser FORMAT A8
+COLUMN sid FORMAT 9,999,999
+COLUMN serial# FORMAT 9,999,999
+COLUMN lockwait FORMAT A6
+COLUMN status FORMAT A8
+COLUMN module FORMAT A20
+COLUMN machine FORMAT A24
+COLUMN program FORMAT A20
+COLUMN logon_time FORMAT A20
+SELECT LPAD(' ', (level-1)*2, ' ') || NVL(s.username, '(oracle)') AS username,
+s.osuser,
+s.sid,
+s.serial#,
+s.lockwait,
+s.status,
+s.module,
+s.machine,
+s.program,
+TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
+FROM   v$session s
+CONNECT BY PRIOR s.sid = s.blocking_session
+START WITH s.blocking_session IS NULL
+/
+</code>
+==== Blocking Locks ====
+  * DBA_LOCKS        Show all the locks like v$lock
+  * DBA_DML_LOCKS    Shows all DML™ locks held or being requested
+  * DBA_DDL_LOCKS    Shows all DDL locks held or being requested
+  * DBA_WAITERS      Shows all sessions waiting on, but not holding waited for locks
+  * DBA_BLOCKERS     Shows non-waiting sessions holding locks being waited-on
+Sessions blocking others
+<code>
+SET LINESIZE 300
+SET PAGESIZE 60
+COLUMN username FORMAT A15
+COLUMN osuser FORMAT A8
+COLUMN sid FORMAT 99999
+COLUMN serial# FORMAT 99999
+COLUMN process_id FORMAT A5
+COLUMN wait_class FORMAT A12
+COLUMN seconds_in_wait FORMAT 9999
+COLUMN state FORMAT A17
+COLUMN blocking_session 9999
+COLUMN blocking_session_state a10
+COLUMN module FORMAT a10
+COLUMN logon_time FORMAT A20
+SELECT
+    NVL(a.username, '(oracle)') AS username,
+    a.osuser,
+    a.inst_id,
+    a.sid,
+    a.serial#,
+    a.sql_id,
+    d.spid AS process_id,
+    a.wait_class,
+    a.seconds_in_wait,
+    a.state,
+    a.blocking_instance,
+    a.blocking_session,
+    a.blocking_session_status,
+    a.module,
+    TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
+FROM
+    gv$session a,
+    gv$process d
+WHERE
+    a.paddr  = d.addr
+AND
+   a.inst_id = d.inst_id
+AND
+    a.status = 'ACTIVE'
+AND
+    a.blocking_session IS NOT NULL
+ORDER BY 1,2
+/
+</code>
+==== Blocking locks (verbose) ====
+<code>
+-- Shows blocked session, blocking session and details of the programs and machines they are running from.
+-- In addition, shows the CTIME value which details how long the blocking session has had hold of the lock
+SELECT
+    sysdate,
+    c.blocker_inst,
+    c.blocker_sid,
+    c.blocker_username,
+    c.blocker_sql_id,
+    c.blocker_program,
+    c.blocker_machine,
+    c.blocker_info,
+    c.blocker_action,
+    c.blocker_client,
+    c.blockee_inst,
+    c.blockee_sid,
+    c.blockee_username,
+    c.blockee_sql_id,
+    c.blockee_program,
+    c.blockee_machine,
+    c.blockee_action,
+    c.blockee_info,
+    c.blockee_client,
+    d.os_user_name locked_os_user_name,
+    d.oracle_username locked_username,
+    b.owner object_owner,
+    b.object_name,
+    b.object_type,
+    c.ctime "Time Locked (s)"
+FROM
+    gv$locked_object d,
+    dba_objects b,
+    (
+    SELECT
+        ( SELECT username FROM gv$session WHERE sid=y.sid AND inst_id=y.inst_id) blocker_username,
+        ( SELECT sql_id FROM gv$session WHERE sid=y.sid AND inst_id=y.inst_id) blocker_sql_id,
+        ( SELECT program FROM gv$session WHERE sid=y.sid AND inst_id=y.inst_id) blocker_program,
+        ( SELECT machine FROM gv$session WHERE sid=y.sid AND inst_id=y.inst_id) blocker_machine,
+        ( SELECT action FROM gv$session WHERE sid=y.sid AND inst_id=y.inst_id) blocker_action,
+        ( SELECT client_info FROM gv$session WHERE sid=y.sid AND inst_id=y.inst_id) blocker_info,
+        ( SELECT client_identifier FROM gv$session WHERE sid=y.sid AND inst_id=y.inst_id) blocker_client,
+        y.sid blocker_sid,
+        y.inst_id blocker_inst,
+        ( SELECT username FROM gv$session WHERE sid=z.sid AND inst_id=z.inst_id) blockee_username,
+        ( SELECT sql_id FROM gv$session WHERE sid=z.sid AND inst_id=z.inst_id) blockee_sql_id,
+        ( SELECT program FROM gv$session WHERE sid=z.sid AND inst_id=z.inst_id) blockee_program,
+        ( SELECT machine FROM gv$session WHERE sid=z.sid AND inst_id=z.inst_id) blockee_machine,
+        ( SELECT action FROM gv$session WHERE sid=z.sid AND inst_id=z.inst_id) blockee_action,
+        ( SELECT client_info FROM gv$session WHERE sid=z.sid AND inst_id=z.inst_id) blockee_info,
+        ( SELECT client_identifier FROM gv$session WHERE sid=z.sid AND inst_id=z.inst_id) blockee_client,
+        z.sid blockee_sid,
+        z.inst_id blockee_inst,
+        z.ctime
+    FROM
+        gv$lock y,
+        gv$lock z
+    WHERE
+        y.block = 1 AND
+        z.request > 0 AND
+        y.id1 = z.id1 AND
+        y.id2 = z.id2
+    ) c
+WHERE
+     d.OBJECT_ID = b.OBJECT_ID AND
+     d.inst_id = c.blockee_inst AND
+     d.session_id = c.blockee_sid
+/
+</code>
+==== Show blocking sessions using Oracle standard script ====
 Run this if never run before (to create needed tables)
-<code>9@@</code>
+<code>
+@?/rdbms/admin/catblock
+</code>
 then to show locking processes in a tree structure (non indented process is blocking indented sessions)
-<code>10@@</code>
+<code>
+@?/rdbms/admin/utllockt
+</code>
-=====Show blocking sessions=====
+==== Show blocking sessions ====
- select s1.username || '@' || s1.machine||
+<code>
-        ' ( SID=' || s1.sid || ' )  is blocking '||
+select s1.username||'@'||s1.machine||'('||s1.sid||','||s1.serial#||') is blocking '||
-        s2.username || '@' || s2.machine ||
+       s2.username||'@'||s2.machine||'('||s2.sid||','||s2.serial#||')'                 blocking_status
-        ' ( SID=' || s2.sid || ' ) '  blocking_status
+from   v$lock l1
- from   v$lock l1
+,      v$session s1
- ,      v$session s1
+,      v$lock l2
- ,      v$lock l2
+,      v$session s2
- ,      v$session s2
+where  1=1
- where  1=1
+and    s1.sid     = l1.sid
- and    s1.sid     = l1.sid
+and    s2.sid     = l2.sid
- and    s2.sid     = l2.sid
+and    l1.block   = 1
- and    l1.block   = 1
+and    l2.request > 0
- and    l2.request > 0
+and    l1.id1     = l2.id1
- and    l1.id1     = l2.id1
+and    l2.id2     = l2.id2
- and    l2.id2     = l2.id2;
+/
+</code>
-=====Show blocking sessions with lock type=====
+==== Show blocking sessions with lock type ====
-<code>11@@</code>
+<code>
+select nvl(s.username,'Internal')         username
+,      nvl(s.terminal,'None')             terminal
+,      l.sid||','||s.serial#              killer
+,      u1.name||'.'||substr(t1.name,1,20) tab
+,      decode ( l.lmode
+              ,1, 'No Lock'
+              ,2, 'Row Share'
+              ,3, 'Row Exclusive'
+              ,4, 'Share'
+              ,5, 'Share Row Exclusive'
+              ,6, 'Exclusive'
+                , null)                   lmode
+,      decode ( l.request
+              ,1, 'No Lock'
+              ,2, 'Row Share'
+              ,3, 'Row Exclusive'
+              ,4, 'Share'
+              ,5, 'Share Row Exclusive'
+              ,6, 'Exclusive'
+                , null)                   request
+from  v$lock    l
+,     v$session s
+,     sys.user$ u1
+,     sys.obj$  t1
+where l.sid    = s.sid
+and   t1.obj#  = decode(l.id2,0,l.id1,l.id2)
+and   u1.user# = t1.owner#
+and   s.type  != 'BACKGROUND'
+and   S.sid   in ( select blocking_session from v$session where blocking_session is not null )
+order by 1,2,5
+/
+</code>
-=====Blocking sessions in tree layout=====
+==== Blocking sessions in tree layout ====
-from www.guyharrison.net
+from www.guyharrison.net. this one seems to be the best!
-<code>12@@</code>
+<code>
+set wrap off
+column sid format a8
+column object_name format a20
+column sql_text format a150
+set echo on
+WITH sessions AS
+   (SELECT /*+materialize*/
+           sid, blocking_session, row_wait_obj#, sql_id
+      FROM v$session)
+SELECT LPAD('  ', LEVEL ) || sid sid, object_name,
+       substr(sql_text,1,240) sql_text
+  FROM sessions s
+  LEFT OUTER JOIN dba_objects
+       ON (object_id = row_wait_obj#)
+  LEFT OUTER JOIN v$sql
+       USING (sql_id)
+ WHERE sid IN (SELECT blocking_session FROM sessions)
+    OR blocking_session IS NOT NULL
+ CONNECT BY PRIOR sid = blocking_session
+ START WITH blocking_session IS NULL
+/
+</code>
 ... and prepare the killer statement
-<code>13@@</code>
+<code>
+set wrap off lines 2000 echo on
+column sid for     a8
+column username    for a12
+column terminal    for a12
+column object_name for a20
+column sql_text    for a150
+column killer      for a60
+with sessions as
+    (
+    select /*+materialize*/ sid         sid
+    ,      nvl(username,'Internal')     username
+    ,      serial#                      serial#
+    ,      nvl(terminal,'None')         terminal
+    ,      blocking_session             blocking_session
+    ,      row_wait_obj#                row_wait_obj#
+    ,      sql_id                       sql_id
+    ,      'alter system disconnect session '''|| sid || ',' || serial# || ''' immediate;' killer
+    from   v$session
+    )
+select lpad('   ', level)||sid          sid
+,      object_name                      object_name
+,      substr(sql_text,1,240)           sql_text
+,      decode(level,1,s.killer,null)    killer
+from   sessions s
+left   outer join dba_objects on    (object_id = row_wait_obj#)
+left   outer join v$sql       using (sql_id)
+where  sid in (select blocking_session from sessions)
+or     blocking_session       is not null
+connect by prior sid          = blocking_session
+start  with blocking_session  is null
+/
+</code>
-=====Show all ddl locks in the system=====
+==== Kill a session with a force timeout! ====
-<code>14@@</code>
+Saw this flashing by in a database upgrade log. Maybe a useful hidden trick.
-=====Show all locks, internal also=====
+<code>
-<code>15@@</code>
+ALTER SYSTEM KILL SESSION '626,37999' force timeout 0;
+</code>
-=====Show object locks=====
+==== Show all ddl locks in the system ====
-<code>16@@</code>
+<code>
+set lines 1000
+col  object for a40
+select ses.username
+,      ddl.session_id
+,      ses.serial#
+,      owner || '.' || ddl.name object
+,      ddl.type
+,      ddl.mode_held
+from   dba_ddl_locks ddl
+,      v$session     ses
+where  owner          like '%&userid%'
+and    ddl.session_id = ses.sid;
+</code>
+==== Show all locks, internal also ====
+<code>
+select nvl(b.username,'SYS') username
+,      session_id
+,      lock_type
+,      mode_held
+,      mode_requested
+,      lock_id1
+,      lock_id2
+from   sys.dba_lock_internal a
+,      sys.v_$session        b
+where  1=1
+and    a.session_id = b.sid
+and    b.username   like '%&username%'
+and    b.sid        = &session_id
+/
+</code>
-=====Generate kill statement for ddl locking sessions=====
+==== Show object locks ====
-<code>17@@</code>
+<code>
+set linesize 150;
+set head on;
+col sid_serial form a13
+col ora_user for a15;
+col object_name for a35;
+col object_type for a10;
+col lock_mode for a15;
+col last_ddl for a8;
+col status for a10;
+break on sid_serial;
+SELECT l.session_id||','||v.serial# sid_serial,
+       l.ORACLE_USERNAME ora_user,
+       o.object_name,
+       o.object_type,
+       DECODE(l.locked_mode,
+, 'None',
+, 'Null',
+, 'Row-S (SS)',
+, 'Row-X (SX)',
+, 'Share',
+, 'S/Row-X (SSX)',
+, 'Exclusive',
+          TO_CHAR(l.locked_mode)
+       ) lock_mode,
+       o.status,
+       to_char(o.last_ddl_time,'dd.mm.yy') last_ddl
+FROM dba_objects o, gv$locked_object l, v$session v
+WHERE o.object_id = l.object_id
+      and l.SESSION_ID=v.sid
+order by 2,3;
+</code>
-=====Display any long operations=====
+==== Generate kill statement for ddl locking sessions ====
-<code>18@@</code>
+<code>
-=====Show waits for (blocking) datapump sessions=====
+col killer for a65
-<code>19@@</code>
+ select 'alter system disconnect session ''' || ddl.session_id || ',' || ses.serial# || ''' immediate;' killer
-=====Show current sessions that perform a lot of hard parses=====
+ from   dba_ddl_locks ddl
-<code>20@@</code>
+ ,      v$session ses
+ where  1=1
+ and    ddl.session_id = ses.sid;
+ and    owner          like upper('%&userid%')
+</code>
+==== Generate killer statements for a particular schema / user ====
+<code>
+set lines 1000 pages 100
+col username for a15
+col status   for a10
+select ses.username
+,      ses.status
+,      'alter system disconnect session ''' || ses.sid || ',' || ses.serial# || ''' immediate;' killer
+from   v$session ses
+where  1=1
+and    lower (ses.username) like lower('%&username%')
+order  by ses.status
+/
+</code>
+==== Generate killer statements from PL/SQL for INACTIVE sessions ====
+<code>
+begin
+    for i in (select * from v$session where status='INACTIVE')
+    LOOP
+        EXECUTE IMMEDIATE(q'{ALTER SYSTEM KILL SESSION '}'||i.sid||q'[,]'  ||i.serial#||q'[']'||' IMMEDIATE');
+    END LOOP;
+end;
+</code>
+==== Display any long operations ====
+<code>
+set lines 100
+set pages 100
+col username  format a15
+col message   format a40
+col remaining format 9999
+select username
+,      to_char(start_time, 'dd-mon-yy hh24:mi') started
+,      time_remaining remaining
+,      message
+from   v$session_longops
+where  1=1
+and    time_remaining != 0
+order  by time_remaining desc
+</code>
+==== Show waits for (blocking) datapump sessions ====
+<code>
+set pages 100 lines 1000
+col p1 for 999999999999
+col p2 for 999999999999
+col p3 for 999999999999
+select sid
+,      event
+,      p1
+,      p2
+,      p3
+,      seconds_in_wait
+from   v$session_wait
+where  sid in
+       (
+       select sid
+       from   v$session vs
+       ,      v$process vp
+       ,      dba_datapump_sessions dp
+       where  vp.addr  = vs.paddr(+)
+       and    vs.saddr = dp.saddr
+       )
+</code>
+==== Show current sessions that perform a lot of hard parses ====
+<code>
+set pages 1000 lines 1000
+col username for a15
+select vss.sid
+,      vs.username
+,      vsn.name
+,      vss.value
+,      round((sysdate-vs.logon_time)*24) hours_connected
+from   v$sesstat  vss
+,      v$statname vsn
+,      v$session  vs
+where  1=1
+and    vss.sid        = vs.sid
+and    vss.statistic# = vsn.statistic#
+and    vss.value      > 0
+and    vsn.name       like '%parse count%'
+</code>
-=====All active sql=====
+==== All active sql ====
-<code>21@@</code>
+<code>
+set serveroutput on
+set feedback off
+column username format a20
+column sql_text format a55 word_wrapped
+begin
+    dbms_output.enable(null);
-=====Identify the number of SQL statements in the library cache that are not using bind variables=====
+    for x in
+    (
+    select username||'('||sid||','||serial#||') ospid = '|| process ||' program = ' || program username
+    ,      to_char(LOGON_TIME,' Day HH24:MI') logon_time
+    ,      to_char(sysdate,' Day HH24:MI')    current_time
+    ,      sql_address
+    ,      sql_hash_value
+    from   v$session
+    where  1=1
+    and    status                 = 'ACTIVE'
+    and    rawtohex(sql_address) != '00'
+    and    username is not null
+    ) loop
+        for y in
+        (
+        select sql_text
+        from   v$sqlarea
+        where  1=1
+        and    address = x.sql_address
+        ) loop
+            if ( y.sql_text not like '%listener.get_cmd%' and y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%' ) then
+                dbms_output.put_line( '--------------------' );
+                dbms_output.put_line( x.username );
+                dbms_output.put_line( x.logon_time || ' ' || x.current_time || ' SQL#=' || x.sql_hash_value);
+                dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
+            end if;
+        end loop;
+    end loop;
+end;
+/
+</code>
+==== Identify the number of SQL statements in the library cache that are not using bind variables ====
 These SQL statements cause expensive hard parse events in the shared pool
-<code>22@@</code>
+<code>
+select count('x')                       num_sql
+,      sum(decode(executions, 1, 1, 0)) one_use_sql
+,      sum(sharable_mem)/1024/1024      meg_used
+,      sum(decode ( executions
+                  , 1, sharable_mem
+                     , 0
+                  )
+          )/1024/1024                   mb_per
+from   v$sqlarea
+where  sharable_mem > 0;
+</code>
-=====List number of open cursors by user=====
+==== List number of open cursors by user ====
-<code>23@@</code>
+<code>
+set pages 999
+select sess.username
+,      sess.sid
+,      sess.serial#
+,      stat.value cursors
+from   v$sesstat stat
+,      v$statname sn
+,      v$session sess
+where  1=1
+and    sess.username   is not null
+and    sess.sid        = stat.sid
+and    stat.statistic# = sn.statistic#
+and    sn.name         = 'opened cursors current'
+order  by value;
+</code>
-=====Show SQL statements generating the high number of cursors=====
+==== Show SQL statements generating the high number of cursors ====
 Use SID from query above.
-<code>24@@</code>
+<code>
+select sid
+,      sql_text
+,      count('e') open_crsrs
+,      user_name
+from   v$open_cursor
+where  1=1
+and    sid = &SID
+group  by sid
+,      sql_text
+,      user_name
+order  by 3;
+</code>
+==== Show current setting for max open cursors and highest number used since startup ====
+<code>
+select max(a.value) highest_open_crsrs
+,      p.value      max_open_crsrs
+from   v$sesstat   a
+,      v$statname  b
+,      v$parameter p
+where  1=1
+and    a.statistic# = b.statistic#
+and    b.name       = 'opened cursors current'
+and    p.name       = 'open_cursors'
+group  by p.value;
+</code>
-=====Show current setting for max open cursors and highest number used since startup=====
-<code>25@@</code>