DDL ,DML on ables & Views

[san]

Hi All ,

Is there any way to find out the last DDL or DML statement on a view or table ? I need a date and time that the DML or DDL executed on a table or view.....

We have number of unnecessary tables created in a schema and want to drop them from the database.....

Thanks,
San

[davey23uk]

you can get last ddl from dba_objects

last dml you would need auditing I guess

[Srinivas_Sharma]

select * from user_objects where object_name='TABLENAME'

check out the last_ddl_time column.

not sure of the DML stuff..

Srini

[san]

Thanks you All .....

I need the DML info on the tables , Putting audit on all users in the database and searching for these tables in the audit files is a cumbersome process and also resouce intensive...

Can somebody suggest me a best mothod to do this ?

Thanks,
San

[slimdave]

What you're asking for is naturally going to be resource intensive.

there was a thread about this at http://asktom.oracle.com a little while ago. You might try searching there.

[tamilselvan]

Use logminer.

Tamil

[san]

Can I use logminer if the database is in Noarchivelog mode ? I don't think logminer can be used....

Can I audit each table for DML's ? or do I have to audit all the users ?

Thanks,
San

[slimdave]

http://download-west.oracle.com/docs...audit.htm#6098