Date Table was last Updated

[Welshbird]

Hi Guys,

I have a 10g database that has been built for me and at the moment not all tables have a 'date updated' field. This is being rectified table by table as and when time allows, but I just wondered if Oracle stores that date in any place (sys tables somewhere maybe? all_tables?).

I've had a quick look, but I don't really know where I would look or if this is even likely, so if someone could kindly point me in the right direction I'd really appreciate it.

Thanks in advance,

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[KenCunningham]

Hi Fee, perhaps dba_audit_object, but that requires auditing turned on and might have quite a significant performance/storage implications. See:

http://www.jlcomp.demon.co.uk/faq/table_update.html

I want to be good, is that not enough?

 

[Welshbird]

The run audit bit of that works, but none of the logins I know about allow me to see sys.dba_audit_object.

But thanks for the thought.



Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Thargy]

willif,

would it help if I provided a routine to add such fields to all the tables in the database in one go?

T

Grinding away at things Oracular

 

[Welshbird]

Thanks Tharg, but unfortunately not. The developers and DBA are adding them at the same time they alter each load script so they are getting there, I just wanted a way to work out what was going on right now.

I'm learning patience though!

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Welshbird]

Just for completeness, I used this in the end

SELECT 
object_name, created, last_ddl_time, timestamp
FROM all_objects WHERE owner = 'FEE' AND object_type = 'TABLE'

along with having a quick

select * from tablename where rownum < 21;

look in each table to check.


(I was really doing some housekeeping and wanted to delete a load of tables that were for ad-hoc work that is now complete - thanks for all advice as always guys.)

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[engineer2100]

Fee,

10G (infact from 9i onwards) provides you the facility of finding this information as a way of Flash Back Queries.

Note id on metalink :

317499.1
270270.1

HTH

Engi

 

[Welshbird]

Engineer2100: Does this work if all the tables are nologging? Excuse the ignorance, but hey - that's me!

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[engineer2100]

I may be wrong, but nologging doesnt effect the flashback.

 

[engineer2100]

Pleaes check this Note Id also 238674.1