You want to use the contents of the data dictionary to drive a query. This can only be done with dynamic SQL, in a procedure.
Some points to bear in mind:
- Oracle maintains the SCN Timestamp mapping to support Flashback Query. It just keeps the mapping for the supported UNDO_RETENTION period. So we can use SCN_TO_TIMESTAMP() only for tables which have recent activity. Staler tables will hurl ORA-08181.
- Tables with no rows won't have an associated SCN. SCN_TO_TIMESTAMP() hurls if we pass it null for the SCN.
So a robust solution is quite complex. This one uses DBMS_OUTPUT to display the results; other mechanisms are available:
declare
n pls_integer;
max_scn number;
x_scn_too_old exception;
pragma exception_init(x_scn_too_old ,-08181);
txt varchar2(30);
begin
for lrec in ( select table_name from user_tables )
loop
execute immediate
'select count(*), max(ora_rowscn) from '
|| lrec.table_name
into n, max_scn;
dbms_output.put(lrec.table_name
||' count='||to_char(n));
begin
if n > 0 then
select to_char(scn_to_timestamp(max_scn), 'yyyy-mm-dd hh24:mi:ss.ff3')
into txt
from dual;
else
txt := null;
end if;
exception
when x_scn_too_old then
txt := ('earlier');
end;
dbms_output.put_line(' ts='||txt );
end loop;
end;
/
There is a pure SQL alternative, using NUM_ROWS from USER_TABLES and the USER_TAB_MODIFICATIONS view. This view is maintained by Oracle to monitor the staleness of statistics on tables. As you're on 10g this will be happening automatically (in 9i we had to switch on monitoring for specific tables).
USER_TAB_MODIFICATIONS gives us numbers for the DML activity on each table, which is neat because we can add those numbers to NUM_ROWS to get an accurate total, which is much more efficient than issuing a COUNT().
Again a couple of points.
- Any table which lacks statistics will have NUM_ROWS=0. For this reason I use NVL() in the arithmetic column
- USER_TAB_MODIFICATIONS only contains data for tables which have changed since the last time statistics were gathered on them. Once we gather statistics on a table it disappears from that view until more DML is issued. So, use an outer join.
- Note that we will only have a timestamp for tables with stale statistics. This is less predictable than the SCN_TO_TIMESTAMP used above, as it depends on your stats gathering strategy.
So here it is:
select t.table_name
, m.timestamp
, t.num_rows
, ((nvl(t.num_rows,0) + m.inserts) - m.deletes) as tot_rows
from user_tables t
left outer join USER_TAB_MODIFICATIONS m
on t.table_name = m.table_name
order by t.table_name
/
Perhaps the best solution is a combination, using NUM_ROWS and USER_TAB_MODIFICATIONS to avoid the count, and only checking ORA_ROWSCN for tables with fresh statistics.
Note that this is only a concern because you don't have your own journalling or table audit. Many places add metadata columns on their tables to track change data (e.g. CREATED_ON, CREATED_BY, UPDATED_ON, UPDATED_BY).
