如何检测 Oracle 10g 来获取表访问指标？
我们有一个遗留/生产数据库。它一直在不断开发和使用，为已经发展了 10 多年的网站提供支持。该数据库可通过多种技术访问 - perl cgi、php 和 java webapps。
积累了很多废品。我希望进行一次重大重构。我确信有些部分完全是遗留的（明显的是零行的表）。为了确定哪些部分最常用，我的首选策略是检测数据库，而不是检查大量潜在的访问组件。
oracle (10g) 是否有能力在每个表上放置一个触发器来报告访问的时间和频率？有人可以指出我如何做到这一点或其他一些机制来实现相同的目标吗？
（请评论并提供其他策略的建议，以帮助在这种情况下进行数据库重构）。
We have a legacy/production database. It has been in continuous development and use backing a website that has evolved over 10 years. The database is accessed by multiple technologies - perl cgi, php and java webapps.
Lots of cruft has accumulated. I wish to undertake a major refactoring. I am sure some parts are completely legacy (the obvious ones being tables with zero rows). To determine which parts are most used my preferred strategy is to instrument the database rather than go through a very large number of potential accessing components.
Does oracle (10g) have the capability to put a trigger on each table to report when and how often it is accessed? Can someone point me to how to do this or some other mechanism to achieve the same goal?
(Please comment with suggestions for other strategies to aid a database refactoring in this senario too).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果您只想知道访问了哪些表（或者即使您想要更多详细信息），那么最简单的方法是使用 Oracle 的内置 AUDIT 功能。 了解一下更多。
请记住，某些数据库对象可能仅每季度甚至每年使用一次。因此，您确实需要一套像样的文档或一套覆盖率 100% 的回归测试。当然，如果您有其中任何一个，您可能不会在这里问您的问题:)
If you just want to know which tables are accessed (or even if you want more details) then the easiest way would be to use Oracle's built-in AUDIT functionality. Find out more.
Bear in mind that some database objects may only be used quarterly or even annually. So you really need a decent set of documentation or a suite of regression tests with 100% coverage. Of course if you had either of those you probably wouldn't be asking your question here :)
如果您使用的是 10g 并且为表打开了 TABLE MONITORING，您可以轻松访问 DBA_ALL_TAB_MODIFICATIONS。
它保存有关表修改的信息，包括自上次分析以来对表的插入、更新和删除的大致数量。
有关打开表监控以及计算百分比变化的查询的一些信息如下：链接
If you are on 10g and have TABLE MONITORING turned on for tables you can easily access DBA_ALL_TAB_MODIFICATIONS.
It holds information about modifications to tables including the approximate number of inserts, updates, and deletes to a table since last analyze.
Some info on turning Table Monitoring on and a query to calculate the percentage change is here: link