Hello, I think I found a bug in the V$TABLE_ACCESS_STATS view (also the dba_ view). It seems that the last_accessed_time value is returning results in a different timezone instead of my current timezone.
Example:
First, the current time is 4:04 PM.
SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') sysdt;
SYSDT
------------------
2024/06/21 16:04:06
2. Table creation:
SQL> create table timetest1 (col1 number);
Table created.
3. Check the V$TABLE_ACCESS_STATS view
SQL>
set lines 200 pages 1000
col owner for a15
col object_name for a20
select obj.owner, tas.object_id, obj.object_name, tas.read_count, to_char(tas.last_accessed_time, 'yyyy/mm/dd hh24:mi:ss') last_accessed_time, tas.con_id
from v$table_access_stats tas, dba_objects obj
where tas.object_id = obj.object_id
and obj.owner = 'SYSTEM'
and obj.object_name = 'TIMETEST1';
OWNER OBJECT_ID OBJECT_NAME READ_COUNT LAST_ACCESSED_TIME CON_ID
--------------- ---------- -------------------- ---------- ------------------- ----------
SYSTEM 89515 TIMETEST1 2 2024/06/21 07:04:56 0
The last accessed time is displayed as 7:04 AM.
(My actual access time is 2024/06/21 16:04:56.)
4. I checked the original table using the v$fixed_view_definition view:
SQL>
select view_definition
from v$fixed_view_definition
where view_name = 'GV$TABLE_ACCESS_STATS';
VIEW_DEFINITION
-------------------------------------------------------------------------------
select inst_id, objn, cnt
,cast(timestamp '1970-01-01 00:00:00'
+ NUMTODSINTERVAL(trunc(time / (24 * 60 * 60)), 'DAY')
+ NUMTODSINTERVAL(MOD(time, 24 * 60 * 60), 'SECOND') as timestamp(0)), con_id
from x$qesma_access_count;
It seems there is an issue with the calculation formula shown here.
5. Also When executing this query, the result also shows the time as 7:04 AM.
select inst_id, objn, cnt
,cast(timestamp '1970-01-01 00:00:00'
+ NUMTODSINTERVAL(trunc(time / (24 * 60 * 60)), 'DAY')
+ NUMTODSINTERVAL(MOD(time, 24 * 60 * 60), 'SECOND') as timestamp(0)), con_id
from x$qesma_access_count
WHERE objn = 89515;
INST_ID OBJN CNT CAST(TIMESTAMP'1970-01 CON_ID
---------- ---------- ---------- ---------------------- ----------
1 89515 2 21-JUN-24 07.04.56 AM 0
6. Adding FROM_TZ and AT TIME ZONE SESSIONTIMEZONE functions works correctly:
SQL>
SELECT inst_id, objn, cnt, time,
CAST(
FROM_TZ(
TIMESTAMP '1970-01-01 00:00:00'
+ NUMTODSINTERVAL(TRUNC(time / (24 * 60 * 60)), 'DAY')
+ NUMTODSINTERVAL(MOD(time, 24 * 60 * 60), 'SECOND'),
'UTC'
) AT TIME ZONE SESSIONTIMEZONE AS TIMESTAMP(0)
) AS last_accessed_time, con_id
FROM x$qesma_access_count
WHERE objn = 89515;
INST_ID OBJN CNT TIME LAST_ACCESSED_TIME SYSDT CON_ID
---------- ---------- ---------- ---------- ---------------------- ------------------- ----------
1 89515 2 1718953496 21-JUN-24 04.04.56 PM 2024/06/21 16:11:01 0
Can you improve this?
And The version of my database is as follows:
SQL> select * from v$version;
BANNER
---------------------------------------------------------------------------------------------------------------------------------
BANNER_FULL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BANNER_LEGACY CON_ID
--------------------------------------------------------------------------------------------------------------------------------- ----------
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free 0
Thank you.