LLI Wiki — My Digital Knowledge Base

Oracle instance startup time, uptime and activity history

Queries to check the startup time and uptime of an Oracle instance, and to analyse past active sessions.

The queries below show when the Oracle instance started, how long it has been up and which sessions were active over a given period. The active session history views (DBA_HIST_*) store data from the Automatic Workload Repository.

Date format

Set the date format in the shell environment and in the session:

export NLS_DATE_FORMAT="DD-MM-YYYY HH24:MI:SS"
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

Check the instance startup time

SET LINES 200
SET PAGES 999
COLUMN INSTANCE_NAME FOR A20
SELECT INSTANCE_NAME,TO_CHAR(STARTUP_TIME, 'HH24:MI DD-MON-YY') "STARTUP TIME"
FROM V$INSTANCE;

Check the startup history

COL INSTANCE_NAME FOR A10
SELECT INSTANCE_NAME,TO_CHAR(STARTUP_TIME, 'HH24:MI DD-MON-YY') FROM DBA_HIST_DATABASE_INSTANCE ORDER BY STARTUP_TIME DESC;

Check the uptime

SET LINE 60
COLUMN HOSTNAME FOR A60
COLUMN INSTANCE_NAME FOR A60
COLUMN STIME FOR A60
COLUMN UPTIME FOR A60
SELECT
'HOSTNAME : ' || HOST_NAME
,'INSTANCE NAME : ' || INSTANCE_NAME
,'STARTED AT : ' || TO_CHAR(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') STIME
,'UPTIME : ' || FLOOR(SYSDATE - STARTUP_TIME) || ' DAYS(S) ' ||
TRUNC( 24*((SYSDATE-STARTUP_TIME) -
TRUNC(SYSDATE-STARTUP_TIME))) || ' HOUR(S) ' ||
MOD(TRUNC(1440*((SYSDATE-STARTUP_TIME) -
TRUNC(SYSDATE-STARTUP_TIME))), 60) ||' MINUTE(S) ' ||
MOD(TRUNC(86400*((SYSDATE-STARTUP_TIME) -
TRUNC(SYSDATE-STARTUP_TIME))), 60) ||' SECONDS' UPTIME
FROM
SYS.V_$INSTANCE;

Check the session activity

Active sessions over the last day:

select * from DBA_HIST_ACTIVE_SESS_HISTORY
WHERE sample_time >= SYSDATE - 1
order by  sample_time desc

Active sessions with user, program, module and SQL text over the last day:

SELECT
h.sample_time,
u.username,
h.program,
h.module,
s.sql_text
FROM
DBA_HIST_ACTIVE_SESS_HISTORY h,
DBA_USERS u,
DBA_HIST_SQLTEXT s
WHERE sample_time >= SYSDATE - 1
AND h.user_id=u.user_id
AND h.sql_id = s.sql_iD
ORDER BY h.sample_time desc

Active sessions between two dates:

SELECT
   h.sample_time,
   u.username,
   h.program,
   h.module,
   s.sql_text
FROM
   DBA_HIST_ACTIVE_SESS_HISTORY h,
   DBA_USERS u,
   DBA_HIST_SQLTEXT s
WHERE  sample_time between to_date('01/10/2018 00:00:00','DD/MM/YYYY HH24:MI:SS')
and to_date('15/10/2018 00:00:00','DD/MM/YYYY HH24:MI:SS')
   AND h.user_id=u.user_id
   AND h.sql_id = s.sql_iD
ORDER BY h.sample_time

Active sessions over the last 30 days:

SELECT
h.sample_time,
u.username,
h.program,
h.module,
s.sql_text
FROM
DBA_HIST_ACTIVE_SESS_HISTORY h,
DBA_USERS u,
DBA_HIST_SQLTEXT s
WHERE sample_time >= SYSDATE - 30
AND h.user_id=u.user_id
AND h.sql_id = s.sql_iD
ORDER BY h.sample_time desc
Mastodon LLIMastodon SocialFosstodon