查找可查看用户各表最后查询时间的数据字典

sys.all_objects的LAST_DDL_TIME仅记录表的DDL操作（如创建、修改）时间，确实无法追踪查询操作。以下是几种可行的解决方案：

1. 利用实时SQL视图（V$SQL/V$SQLAREA） #

通过查询实时执行的SQL语句视图，筛选出针对目标表的SELECT操作，取最近的执行时间。需要注意的是，这些视图中的数据会被数据库的老化机制清理，仅保留近期的SQL记录。

示例查询：

SELECT 
    o.owner,
    o.object_name AS table_name,
    MAX(s.last_active_time) AS last_query_time
FROM 
    sys.all_objects o
LEFT JOIN 
    v$sql s ON s.sql_text LIKE '%' || o.object_name || '%'
           AND s.command_type = 3  -- 3代表SELECT命令
WHERE 
    o.object_type = 'TABLE'
    AND o.owner = 'YOUR_SCHEMA_NAME'  -- 替换为你的用户名
GROUP BY 
    o.owner, o.object_name
ORDER BY 
    last_query_time DESC;

注意：需要当前用户拥有SELECT_CATALOG_ROLE权限才能访问V$系列视图；如果SQL语句中表名有别名或被包裹在子查询中，这种模糊匹配可能会有误差。

2. 通过AWR（自动工作负载仓库）查询历史记录 #

如果数据库开启了AWR（默认开启），可以通过AWR的历史视图查询较久之前的表查询时间，适合需要追溯历史数据的场景。

示例查询：

SELECT 
    o.owner,
    o.object_name AS table_name,
    MAX(dhs.end_interval_time) AS last_query_time
FROM 
    dba_hist_sqltext dhst
JOIN 
    dba_hist_sqlstat dhss ON dhst.sql_id = dhss.sql_id
JOIN 
    dba_hist_snapshot dhs ON dhss.snap_id = dhs.snap_id
JOIN 
    sys.all_objects o ON dhst.sql_text LIKE '%' || o.object_name || '%'
WHERE 
    o.object_type = 'TABLE'
    AND o.owner = 'YOUR_SCHEMA_NAME'
    AND dhst.command_type = 3
GROUP BY 
    o.owner, o.object_name
ORDER BY 
    last_query_time DESC;

AWR的快照默认每小时生成一次，保留时间通常为8天（可配置），能覆盖更长周期的查询记录。

3. 启用表访问审计 #

如果需要长期稳定追踪表的查询时间，可以开启Oracle的审计功能，针对表的SELECT操作进行审计。

步骤： #

1. 开启审计（需DBA权限）：

ALTER SYSTEM SET audit_trail=DB,EXTENDED SCOPE=SPFILE;
-- 重启数据库生效

2. 对目标表开启SELECT审计：

AUDIT SELECT ON YOUR_SCHEMA_NAME.YOUR_TABLE_NAME BY ACCESS;

3. 查询审计记录获取最后查询时间：

SELECT 
    obj_name AS table_name,
    MAX(timestamp) AS last_query_time,
    username AS queried_by
FROM 
    dba_audit_trail
WHERE 
    action_name = 'SELECT'
    AND obj_name = 'YOUR_TABLE_NAME'
    AND owner = 'YOUR_SCHEMA_NAME'
GROUP BY 
    obj_name, username;

注意：审计会增加数据库的性能开销和存储占用，建议仅对需要追踪的表开启，或定期清理审计日志。

内容的提问来源于stack exchange，提问作者Şükran Mamedov