How do I go about finding out the weekly, monthly, and yearly Oracle DB transactions? Is there a specific query for such analysis?
If your AWR retention is configured long enough (and you have the select any dictionary priv), you can query AWR data. As every transaction must end with either a commit or a rollback, the combination of commits and rollbacks should be the # of transactions. So a weekly report:
SELECT TRUNC(begin_interval_time,'IW'),
SUM(diff) transactions
FROM (SELECT sn.begin_interval_time,
ss.instance_number,
ss.stat_name,
ss.value - LAG(ss.value) OVER (PARTITION BY ss.stat_name,ss.instance_number ORDER BY sn.begin_interval_time) diff
FROM dba_hist_sysstat ss,
dba_hist_snapshot sn
WHERE stat_name IN ('user rollbacks','user commits')
AND ss.snap_id = sn.snap_id
AND ss.instance_number = sn.instance_number)
GROUP BY TRUNC(begin_interval_time,'IW')
ORDER BY 1 DESC
And other date format TRUNC parameters (like DD, MM, YYYY) can be used to report by other time periods. However, typically databases won't have more than a few weeks of this data, a month or two maybe. If you want longer, you'd have to extend the AWR retention and then wait for a longer time period to build up, or capture gv$sysstat data yourself on a regular basis and store in a repository. Neither of those methods are going to give you years of data today, however. That will almost certainly be unattainable. Oracle doesn't feel the need to store historical metrics for very long.