Last Accessed Date of Tables (including SELECT) for db clean-up 2

[rchandr3]

Hi all,

I am assigned the task of listing out tables in a large database which are not used anymore. Is there a way by which we can find the last accessed date of the tables (including SELECT access) so that it would ease my task of finding out used tables?

Many Thanks and Regards,

Cheers,

Ravi

 

[SantaMufasa]

Ravi,

Wouldn't that be a nice feature (to track SELECTs)? Too bad that Oracle has seen fit not to implement this capability...or perhaps they worried (depending upon their implementation) that such a feature could bring db performance to its knees...(Imagine how much writing would need to occur on simple SELECTs.) Well, such a debate is not for us to get into here.

Without pre-planning, the best that you can hope for, looking back on table activity, is querying "DBA_OBJECTS","ALL_OBJECTS", or "USER_OBJECTS" which all have columns "CREATED" and "LAST_DDL_TIME". As you can imagine, however, "LAST_DDL_TIME" is certainly not good enough to determine the "usage value" of a table.

Perhaps the best method currently available to determine "usage value" from a data-only perspective is to have implemented (at the birth of the table) a trigger to update a control table with the "LAST_INSERT", "LAST_UPDATE", and "LAST_DELETE" dates for a table. But, again, since you didn't implement such a feature "In The Beginning", you have no resource now to analysis "usage value".

Therefore, the only available resouce you have now is "Application Analysis"...specifically, you must analyse application code to determine code references.

Sorry for the bad news.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[rchandr3]

O... Thanks a lot, Dave of Sandy... From my knowledge and analysis of Oracle, I found it weird and now I know why thanks to your message...

Thanks again and my token of appreciation can be found in a star for you!


Cheers,

Ravi

 

[carp]

For DML tracking, you might be able to use log miner. Of course, this still won't let you track last SELECTs, and may be a rather limited source for INSERT, UPDATE, and DELETE.

 

[stefanhei]

Ravi,
you may use auditing to gather usage information - SELECTs included. You need to set the init-parameter AUDIT_TRAIL and then issue (as a privileged user):

AUDIT ALL BY yourApplicationUser;

Dave's comment about the overhead of course still is valid!
Performance on a heavily used system will decrease drastically. Also Diskspace might soon become a problem.
Oracle implemented this capability - just use it very, very carefully.

Stefan

 

[rchandr3]

Hi carp, That sounds interesting... But can I install logminer now (Mine is a Oracle 8 database and not 8i) and make it tell the history of last six months usage?

stefanhai: Thanks for your valuable post... I have already thought about Audit on Production DB and then said NOWAY!!

Many Thanks and Regards,



Cheers,

Ravi