Hi Tom, one of my customer have asked to prove that data in one of the tables have not been updated in last few months. The table have no Last_update_date / creation_date field. is there any way to prove this with some data. please advise.
Here's an example from my laptop database
SQL> select created from user_objects
2 where object_name = 'T1'
3 and object_type = 'TABLE';
CREATED
---------
02-SEP-15
SQL> select count(*) from t1;
COUNT(*)
----------
180321
I create that table nearly two weeks ago, and havent touched it since. The question is now - can I show that without using any columns on the table.
We know each block has the SCN on it
SQL> select max(ora_rowscn) from t1;
MAX(ORA_ROWSCN)
---------------
6925456
So I can convert that to a timestamp
SQL> select scn_to_timestamp(6925456) from dual;
select scn_to_timestamp(6925456) from dual
*
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1
That didnt work, because we dont keep the mapping from SCN to timestamp forever. I can see that with this query
SQL> col m for 99999999999999999
SQL> select min(SCN) m from sys.smon_scn_time;
M
------------------
7275763
SQL> select scn_to_timestamp(7275763) from dual;
SCN_TO_TIMESTAMP(7275763)
-----------------------------------------------------------------
07-SEP-15 10.42.09.000000000 PM
So know its *at least* before September 7th. To try get a better estimate, we can take a look at our redo log activity
SQL> select *
2 from (
3 select SEQUENCE#, FIRST_CHANGE#, FIRST_TIME, lead(FIRST_CHANGE#) over ( order by FIRST_CHANGE#) as next_FIRST_CHANGE#
4 from v$log_history
5 order by FIRST_CHANGE#
6 )
7 where 6925456 between FIRST_CHANGE# and next_FIRST_CHANGE#;
SEQUENCE# FIRST_CHANGE# FIRST_TIM NEXT_FIRST_CHANGE#
---------- ------------- --------- ------------------
204 6923744 02-SEP-15 6948678
So we see that the SCN falls into a log switch period around Sep 2.
Hope this helps.