Useful Queries

When using SQL*Plus, usually from a linux or unix session, I have the following queries in files which I can run from within SQL*Plus. So for example, to run sessions.sql I would issue @sessions Everyone has their own preference for their login.sql, mine (with or without the serveroutput line, as it can adversely affect autotrace) is
set serveroutput on size unlimited
ALTER SESSION SET nls_date_format = 'DD/MM/YYYY HH24:MI:SS';

SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER _DATE> "

set lines 132
set pages 40
    

ls.sql - Query User Tables

The COLUMN line is only needed for Oracle 12, as table_name is a VARCHAR2(128) in Oracle 12 which really messes up the SQL*Plus output
COLUMN table_name FORMAT a30;
SELECT table_name, num_rows, blocks, compression, last_analyzed, iot_type
FROM    tabs
/
    

sessions.sql - Sessions

set lines 132
set pages 50000
ALTER SESSION SET nls_date_format='DD/MM/YYYY HH24:MI';

COLUMN program FORMAT a48

SELECT osuser, program, logon_time, status, sid, serial#
FROM   v$session
WHERE  osuser != 'oracle' -- Oracle 10
AND    osuser != 'SYSTEM' -- Oracle 11
ORDER BY logon_time;
    

doing.sql - What is a session doing

set long 50000
set pages 50000
set lines 132
COLUMN sql_fulltext FORMAT a130

SELECT sql_fulltext
FROM   v$session s, v$sql q
WHERE  s.sid = &1
AND    q.sql_id = NVL(s.sql_id, s.prev_sql_id);
    

ash.sql - Recent Session Activity

Having an interest in session with sid 123, this script would be run with @ash 123 or just @ash after 123 has already been used to run this (or another) script.

Oracle 10 Version

ALTER SESSION SET nls_timestamp_format = 'HH24:MI:SS';

COLUMN "Start"     FORMAT a8
COLUMN "Samples"   FORMAT 999990
COLUMN wait_class  FORMAT a10
COLUMN event       FORMAT a30
COLUMN object      FORMAT a30

WITH ash AS (
  SELECT sample_time, session_state, a.wait_class, a.event,
         NVL(o.object_name, TO_CHAR(current_obj#)) object, a.blocking_session_status
  FROM   v$session s, v$active_session_history a, dba_objects o
  WHERE  s.sid = &1
  AND    a.sql_id = NVL(s.sql_id, s.prev_sql_id)
  AND    o.object_id (+) = a.current_obj#
), current_time AS (
  SELECT MAX(sample_time) max_sample_time
  FROM   ash
), prev_line_det AS (
  SELECT sample_time, session_state, wait_class, event, object, blocking_session_status,
         LAG(session_state)           OVER (ORDER BY sample_time) AS prev_session_state,
         LAG(wait_class)              OVER (ORDER BY sample_time) AS prev_wait_class,
         LAG(event)                   OVER (ORDER BY sample_time) AS prev_event,
         LAG(object)                  OVER (ORDER BY sample_time) AS prev_object,
         LAG(blocking_session_status) OVER (ORDER BY sample_time) AS prev_blocking_session_status,
         ROW_NUMBER()                 OVER (ORDER BY sample_time) AS sample_number
  FROM   ash
), lines_match AS (
  SELECT sample_time, session_state, wait_class, event, object, blocking_session_status, sample_number,
         CASE WHEN session_state = prev_session_state
              AND  wait_class    = prev_wait_class
              AND  event         = prev_event
              AND  object        = prev_object
              AND  blocking_session_status = prev_blocking_session_status
              THEN 'Y'
              ELSE 'N'
         END as match_prev
  FROM   prev_line_det
), distinct_only AS (
  SELECT sample_time, session_state, wait_class, event, object, blocking_session_status, sample_number,
         LEAD(sample_number) OVER (ORDER BY sample_number) next_sample_number
  FROM   lines_match, current_time
  WHERE  match_prev = 'N'
  OR     sample_time = max_sample_time
  ORDER BY sample_time DESC
)
SELECT sample_time "Start", (next_sample_number - sample_number) "Samples", session_state, wait_class,
       event, object, blocking_session_status
FROM   distinct_only
WHERE  rownum <= 20
/
    

Oracle 11 Version

ALTER SESSION SET nls_timestamp_format = 'HH24:MI:SS';

COLUMN "Start"     FORMAT a8
COLUMN "Samples"   FORMAT 999990
COLUMN wait_class  FORMAT a10
COLUMN event       FORMAT a30
COLUMN object      FORMAT a30

WITH ash AS (
  SELECT sample_time, session_state, a.wait_class, a.event,
         NVL(o.object_name, TO_CHAR(current_obj#)) object, a.sql_plan_line_id
  FROM   v$session s, v$active_session_history a, dba_objects o
  WHERE  s.sid = &1
  AND    a.sql_id = NVL(s.sql_id, s.prev_sql_id)
  AND    o.object_id (+) = a.current_obj#
), current_time AS (
  SELECT MAX(sample_time) max_sample_time
  FROM   ash
), prev_line_det AS (
  SELECT sample_time, session_state, wait_class, event, object, sql_plan_line_id,
         LAG(session_state)    OVER (ORDER BY sample_time) AS prev_session_state,
         LAG(wait_class)       OVER (ORDER BY sample_time) AS prev_wait_class,
         LAG(event)            OVER (ORDER BY sample_time) AS prev_event,
         LAG(object)           OVER (ORDER BY sample_time) AS prev_object,
         LAG(sql_plan_line_id) OVER (ORDER BY sample_time) AS prev_sql_plan_line_id,
         ROW_NUMBER()          OVER (ORDER BY sample_time) AS sample_number
  FROM   ash
), lines_match AS (
  SELECT sample_time, session_state, wait_class, event, object, sql_plan_line_id, sample_number,
         CASE WHEN session_state = prev_session_state
              AND  NVL(wait_class, 'x')    = NVL(prev_wait_class, 'x')
              AND  NVL(event, 'x')         = NVL(prev_event, 'x')
              AND  object        = prev_object
              AND  sql_plan_line_id = prev_sql_plan_line_id
              THEN 'Y'
              ELSE 'N'
         END as match_prev
  FROM   prev_line_det
), distinct_only AS (
  SELECT sample_time, session_state, wait_class, event, object, sql_plan_line_id, sample_number,
         LEAD(sample_number) OVER (ORDER BY sample_number) next_sample_number
  FROM   lines_match, current_time
  WHERE  match_prev = 'N'
  OR     sample_time = max_sample_time
  ORDER BY sample_time DESC
)
SELECT sample_time "Start", (next_sample_number - sample_number) "Samples", session_state, wait_class,
       event, object, sql_plan_line_id
FROM   distinct_only
WHERE  rownum <= 25
/
    

ash_totals.sql - Current Query Activity Totals

Having an interest in session with sid 123, this script would be run with @ash_totals 123 or just @ash_totals after 123 has already been used to run this (or another) script.

Oracle 10 Version

ALTER SESSION SET nls_timestamp_format = 'HH24:MI:SS';

COLUMN samples     FORMAT 999990
COLUMN wait_class  FORMAT a11
COLUMN event       FORMAT a30
COLUMN object      FORMAT a30

SELECT count(*), session_state, a.wait_class, a.event, NVL(o.object_name, TO_CHAR(current_obj#)) object, a.blocking_session_status
FROM   v$session s, v$active_session_history a, dba_objects o
WHERE  s.sid = &1
AND    a.sql_id = NVL(s.sql_id, s.prev_sql_id)
AND    o.object_id (+) = a.current_obj#
GROUP BY session_state, a.wait_class, a.event, NVL(o.object_name, TO_CHAR(current_obj#)), a.blocking_session_status
ORDER BY count(*) DESC;
    

Oracle 11 And Later Version

ALTER SESSION SET nls_timestamp_format = 'HH24:MI:SS';

COLUMN samples     FORMAT 999990
COLUMN wait_class  FORMAT a11
COLUMN event       FORMAT a30
COLUMN object      FORMAT a30

SELECT count(*), session_state, a.wait_class, a.event, NVL(o.object_name, TO_CHAR(current_obj#)) object, a.sql_plan_line_id
FROM   v$session s, v$active_session_history a, dba_objects o
WHERE  s.sid = &1
AND    a.sql_id = NVL(s.sql_id, s.prev_sql_id)
AND    o.object_id (+) = a.current_obj#
GROUP BY session_state, a.wait_class, a.event, NVL(o.object_name, TO_CHAR(current_obj#)), a.sql_plan_line_id
ORDER BY count(*) DESC;
    

xplan.sql - Show plan for current query

Having an interest in session with sid 123, this script would be run with @xplan 123 or just @xplan after 123 has already been used to run this (or another) script.
set lines 132
set pages 50000
set long 50000
ALTER SESSION SET nls_date_format='HH24:MI:SS';
COLUMN opname FORMAT a11
COLUMN target FORMAT a25
--COLUMN message FORMAT a65
COLUMN message FORMAT a90
COLUMN "% Comp" FORMAT a6

SELECT x.*
FROM   v$session s, table(DBMS_XPLAN.display_cursor(s.sql_id)) x
WHERE  s.sid = &1;
    

longops.sql - Current Session Long Ops

Having an interest in session with sid 123, this script would be run with @longops 123 or just @longops after 123 has already been used to run this (or another) script.
set lines 132
set pages 50000
set long 50000
ALTER SESSION SET nls_date_format='HH24:MI:SS';
COLUMN opname FORMAT a11
COLUMN target FORMAT a25
--COLUMN message FORMAT a65
COLUMN message FORMAT a90
COLUMN "% Comp" FORMAT a6

--SELECT opname, target, start_time "Started", time_remaining "Remaining", message,
SELECT opname, start_time "Started", time_remaining "Remaining", message,
       LPAD(TRIM(TO_CHAR(sofar/totalwork*100, '990.00')), 6) "% Comp"
FROM   v$session_longops
WHERE  sid = &1
ORDER BY start_time;