Query Oracle sessions to date and time
In the article “Query Oracle sessions to date and time”, we can control Oracle sessions before a certain date or time using a specific query.
Sometimes, it can be difficult to locate the oldest sessions in the database due to the high number of sessions.
The article provides a solution to locating the oldest sessions in an Oracle database, which can be challenging due to the high number of sessions.
By using a specific query and setting a date in the WHERE condition, we can easily find sessions that precede that date.
However, with the following query, we can easily find sessions that precede a certain date, just by setting the date we want in the WHERE condition.
The selected fields include:
LOGON_TIME,
STATUS,
INST_ID,
SQL_ID,
PROCESS,
PROGRAM,
SCHEMANAME,
SID,
SERIAL#,
SPID,
OSUSER,
MACHINE,
TERMINAL,
LAST_CALL_ET,
USERNAME,
and COMMAND.
To extract this information from the Oracle database, we relate three tables:
GV$SESSION,
GV$PROCESS,
and DBA_USERS.
Finally, we pass the condition “to_char(S.logon_time,’DD-MM-YY HH24:MI’) < ’15-01-17 12:30′” to check the selected information before a specific date.
Query Oracle sessions to date and time
Set lines 300
Set pages 60
Col LAST_CALL for a18
Col LOGON_TIME for a18
Col schemaname for a16
Col command for a24
Col machine for a16
Col action for a12
Col username for a16
Col osuser for a16
Col terminal for a16
select to_char(S.logon_time,'DD-MON-YY hh24:mi:ss') LOGON_TIME, s.status, s.inst_id,
s.sql_id, s.process,s.program, s.schemaname,
s.sid, s.serial#, p.spid, p.pid, s.osuser, S.machine, S.terminal,to_char(sysdate -(LAST_CALL_ET/86400),'DD-MON-YY hh24:mi:ss') LAST_CALL ,
U.username, S.ACTION, decode(S.command,0, 'No command in progress.', 1, 'CREATE TABLE',2, 'INSERT', 3, 'SELECT', 4, 'CREATE CLUSTER',
5, 'ALTER CLUSTER', 6, 'UPDATE', 7, 'DELETE') COMMAND
from gv$session S,
dba_users U,
gv$process P
where P.ADDR = S.PADDR
and S.user# = U.user_id
and to_char(s.logon_time,'DD-MM-YY HH24:MI') < '&data';To view my guides on CRS commands click here!
At this LINK, however, you will find SQL queries
Stay updated on our activities by joining our community:
Facebook
Youtube
Linkedin
For any doubt, do not hesitate to comment on the article.
0 Comments