JAVA J2EE 4 U

 Great question, Daidipya! Identifying least-used tables in Oracle can be tricky because Oracle does not maintain per-table access counts by default. However, we can approximate "least used" tables using the following approaches:

---

🔍 Option 1: Use v$segment_statistics for runtime stats

This dynamic view tracks segment-level stats, including logical reads.

✅ Query: Least accessed tables by logical reads

sql
SELECT owner, object_name, value AS logical_reads
FROM v$segment_statistics
WHERE statistic_name = 'logical reads'
AND object_type = 'TABLE'
ORDER BY value ASC
FETCH FIRST 20 ROWS ONLY;

🔹 Shows tables with the fewest logical reads since instance startup.

---

🧊 Option 2: Use DBA_HIST_SEG_STAT (if AWR licensed)

This gives historical usage, which is more reliable over time.

sql
SELECT
    s.owner,
    s.object_name,
    SUM(st.logical_reads_total) AS total_logical_reads
FROM
    dba_hist_seg_stat st
JOIN
    dba_objects s ON st.obj# = s.object_id
WHERE
    s.object_type = 'TABLE'
GROUP BY
    s.owner, s.object_name
ORDER BY
    total_logical_reads ASC
FETCH FIRST 20 ROWS ONLY;

✅ Best choice if AWR is enabled and licensed. Reflects multi-day usage.

---

🕒 Option 3: Look at last analyzed time (Stale stats = Possibly unused)

sql
SELECT owner, table_name, last_analyzed
FROM dba_tables
WHERE last_analyzed IS NULL
   OR last_analyzed < SYSDATE - 90
ORDER BY last_analyzed NULLS FIRST
FETCH FIRST 20 ROWS ONLY;

🔸 Helpful if you want to clean up old tables. Not precise for access frequency.

---

🪪 Bonus Tip: Use AUDIT or Fine-Grained Auditing (FGA)

For precise table access tracking, consider enabling:

• AUDIT SELECT ON <table>;

• Or FGA for selective columns/rows access logging.

But be careful—it adds overhead and should be used selectively.

---

🚀 Recommendation

If AWR is licensed:
➡️ Use Option 2 (dba_hist_seg_stat) — most accurate.

If not licensed:
➡️ Use Option 1 (v$segment_statistics) — gives real-time insight.

Would you like a shell script or SQL report that runs this for all schemas and filters only user tables (excluding Oracle-maintained ones)?