• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1443
  • Last Modified:

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
0
sventhan
Asked:
sventhan
  • 4
  • 2
  • 2
2 Solutions
 
slightwv (䄆 Netminder) 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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
MikeOM_DBACommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now