How to find unused objects?

Experts -

Would it be possible to get a report of what tables are used (have read/writes operations over them) in the selected oracle schema.

We would like to remove the unused objects to make up some space.



Thanks,
sve
LVL 18
sventhanAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
I don't know of a way to track DML on tables that will be 100% accurate.

Even if you turn on auditing or run log miner through all available logs, it won't catch everything.  For example, you might have a table that is only used for year-end closing and only accessed once a year.
0
 
sventhanAuthor Commented:
Agree. I would say table is not being used for more than 60 days. Is there any way to find that out?
0
 
slightwv (䄆 Netminder) Commented:
>> Is there any way to find that out?

Already mentioned the ways I know of:  turn on auditing and/or use log miner to go back through your old logs.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
MikeOM_DBAConnect With a Mentor Commented:
You could set the tables to "MONITORING" and then query {ALL|DBA}_TAB_MODIFICATIONS view:
ALTER TABLE MyTable MONITORING;"

Open in new window

:p
0
 
slightwv (䄆 Netminder) Commented:
Setting monitoring might be a decent approximation but from what I read in the docs, I don't think it would be accurate here.  From the docs: "since the last time statistics were gathered".

If you have tables that are used infrequently wouldn't the *_tab_modifications view likely show no activity on a regular basis?
0
 
MikeOM_DBACommented:
You could copy the data to another table before computing statistics?
But you are right, we gather statistics daily -- bummer.
:)
0
 
slightwv (䄆 Netminder) Commented:
>>You could copy the data to another table before computing statistics?

That might be a decent work-around over time.  It would almost definitely generate less data than auditing.  Likely be less of a headache than constantly worrying about purging audit logs.

Just need to make sure the capture runs before automatic statistics collection.

I've not messed with adding my own tasks to the maintenance window...
0
 
sventhanAuthor Commented:
Thanks.
0
All Courses

From novice to tech pro — start learning today.