Oracle 10046 trace files contain data like
*** SESSION ID:(501.50681) 2022-08-08T13:27:08.567565+02:00
*** CLIENT ID:() 2022-08-08T13:27:08.567584+02:00
...
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=10287043490743
...
PARSE #140428862194944:c=204,e=203,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=422997890,tim=10287043504707
EXEC #140428862194944:c=103,e=103,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=422997890,tim=10287043504953
WAIT #140428862194944: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=10287043505039
...
I know my question is almost solved in Convert/replace time from epoch milliseconds... but in my case I don't want to start from "epoch" but from the date displayed just above, say at line with "*** CLIENT ID:... 2022-08-08T13:27:08...".
So - knowing that "tim=10287043490743" approximately equals to Aug 8th at 13:27:08, how could I convert all further occurrences of this microseconds string "tim=....." to a legible date and time format ?
More info about Oracle 10046 trace files structure, let's take INST2_ora_7504.trc file:
[oracle@machine sql]$ ls -l $tf
-rw-r----- 1 oracle asmadmin 2996974 Aug 8 13:39 /opt/.../rdbms/dbuniq_nam/INST2/trace/INST2_ora_7504.trc
As you can see it was finished being written on Aug 8th at 13:39.
Retrieving timestamp of when it began being written:
[oracle@machine sql]$ grep "CLIENT ID" $tf
*** CLIENT ID:() 2022-08-08T13:27:08.567584+02:00
First occurrence of timing info:
[oracle@machine sql]$ grep "tim=" $tf|head -1
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=10287043490743
Last one:
[oracle@machine sql]$ grep "tim=" $tf|tail -1
CLOSE #140428862208536:c=8,e=9,dep=0,type=0,tim=10287767781596
This file contains 28756 lines with "tim=". Under Oracle it's very easy to compute what I want in SQL:
[oracle@machine sql]$ sqlplus / as sysdba
SQL> select to_date('2022-08-08 13:27:08','YYYY-MM-DD HH24:MI:SS')+((10287767781596/24/60/60/1000000) -
2 (10287043490743/24/60/60/1000000)) from dual;
TO_DATE('2022-08-08
08/08/2022_13:39:12
See ? Taking last "tim=" value, adding it to timestamp contained in file header, and substracting its equivalent value in microseconds allows you to verify that the filed ended up being written indeed at 13:39 (like 'ls -l' showed above).
But I'd like to do this outside of SQL/Oracle tools...
Thanks.
Regards,
Seb
tim=10287043490743
becomes+327953-06-07T04:45:43.000000
if it is assumed to be seconds since epoch or conversely,Aug 8th at 13:27:08
becomes1659979628
. That is a huge difference with what you claim it to be. – QuartzCristal Aug 10 '22 at 17:08/1000000
suggests they are microseconds rather than milliseconds. – Stéphane Chazelas Aug 11 '22 at 09:08