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
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
/
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;
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);
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
/
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;
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;
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;