dbawiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
  * [[https://techgoeasy.com/enqueue-and-locks/|Oracle Locks & table locks: How it works - techgoeasy.com]]

==== List Users Created on the Database (including dba role grantee) ====
<code>
CLEAR BREAKS
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

select username,module,sid,sql_trace,sql_trace_waits,sql_trace_binds from v$session where sql_trace='ENABLED'
/
</code>
==== Enable Session Tracing ====
<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',
                             1, 'Null (NULL)',
                             2, 'Row-S (SS)',
                             3, 'Row-X (SX)',
                             4, 'Share (S)',
                             5, 'S/Row-X (SSX)',
                             6, '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:

10g -> ((1.1 * PROCESSES) + 5)
11g -> ((1.5 * PROCESSES) + 22)
12.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>
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;

    -- finally, it is safe to issue the drop statement
    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 ====
<code>
 select sql_text
 from   v$sqlarea
 where  1=1
 and    (address, hash_value) in
        (
        select sql_address
        ,      sql_hash_value
        from   v$session
        where  1=1
        and    username like '&username'
        );
</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>
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'

COLUMN cpu_time_total       FORMAT      9G999G990 HEADING "CPU time|total"
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"

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,
     0,'No Command',
     1,'Create Table',
     2,'Insert',
     3,'Select',
     6,'Update',
     7,'Delete',
     9,'Create Index',
     15,'Alter Table',
     21,'Create View',
     23,'Validate Index',
     35,'Alter Database',
     39,'Create Tablespace',
     41,'Drop Tablespace',
     40,'Alter Tablespace',
     53,'Drop User',
     62,'Analyze Table',
     63,'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             sqlplus@hn5205.cln.be (TNS V1-V3)                  Update                       3751
SYS             sqlplus@hn5205.cln.be (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>
@?/rdbms/admin/catblock
</code>
then to show locking processes in a tree structure (non indented process is blocking indented sessions)
<code>
@?/rdbms/admin/utllockt
</code>

==== Show blocking sessions ====
<code>
select s1.username||'@'||s1.machine||'('||s1.sid||','||s1.serial#||') is blocking '||
       s2.username||'@'||s2.machine||'('||s2.sid||','||s2.serial#||')'                 blocking_status
from   v$lock l1
,      v$session s1
,      v$lock l2
,      v$session s2
where  1=1
and    s1.sid     = l1.sid
and    s2.sid     = l2.sid
and    l1.block   = 1
and    l2.request > 0
and    l1.id1     = l2.id1
and    l2.id2     = l2.id2
/
</code>

==== Show blocking sessions with lock type ====
<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 ====
from www.guyharrison.net. this one seems to be the best!
<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>
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>

==== Kill a session with a force timeout! ====
Saw this flashing by in a database upgrade log. Maybe a useful hidden trick.
<code>
ALTER SYSTEM KILL SESSION '626,37999' force timeout 0;
</code>

==== Show all ddl locks in the system ====
<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>

==== Show object locks ====
<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,
          0, 'None',
          1, 'Null',
          2, 'Row-S (SS)',
          3, 'Row-X (SX)',
          4, 'Share',
          5, 'S/Row-X (SSX)',
          6, '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>

==== Generate kill statement for ddl locking sessions ====
<code>
col killer for a65
 select 'alter system disconnect session ''' || ddl.session_id || ',' || ses.serial# || ''' immediate;' killer
 from   dba_ddl_locks ddl
 ,      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 ====
<code>
set serveroutput on
set feedback off
column username format a20
column sql_text format a55 word_wrapped
begin
    dbms_output.enable(null);

    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>
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 ====
<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 ====
Use SID from query above.
<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>