如何判断 Oracle 中是否正在使用物化视图？
我们的 Oracle 9i 数据库中有一些物化视图，这些视图是很久以前由不再在这里工作的人创建的。是否有一种简单（或任何）方法可以确定 Oracle 是否使用这些视图来提供查询服务？如果不再使用它们，我们想摆脱它们。但我们不想在事后发现这些视图允许一些随机报告在不到几个小时的时间内运行。我梦想的答案会是这样的
SELECT last_used_date FROM dba_magic
WHERE materialized_view_name = 'peters_mview'
更棒的是它可以告诉我哪些实际的 SQL 查询正在使用物化视图。我意识到我可能不得不接受更少的事情。
如果有需要 10g 的解决方案，我们很快就会升级，所以这些答案也很有用。
We have some Materialized views in our Oracle 9i database that were created a long time ago, by a guy no longer working here. Is there an easy (or any) method to determine whether Oracle is using these views to serve queries? If they aren't being used any more, we'd like to get rid of them. But we don't want to discover after the fact that those views are the things that allow some random report to run in less than a few hours. The answer I'm dreaming of would be something like
SELECT last_used_date FROM dba_magic
WHERE materialized_view_name = 'peters_mview'
Even more awesome would be something that could tell me what actual SQL queries were using the materialized view. I realize I may have to settle for less.
If there is a solution that requires 10g, we are upgrading soon, so those answers would be useful also.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
一旦按照文档配置，Oracle 审计就可以告诉您这一点。配置完成后，通过“AUDIT SELECT ON {name of Materialized View}”启用它。审计跟踪将位于 SYS 模式的 AUD$ 表中。
Oracle auditing can tell you this once configured as per the docs. Once configured, enable it by "AUDIT SELECT ON {name of materialized view}". The audit trail will be in the AUD$ table in the SYS schema.
除审核之外的一种方法是在一次刷新之后和下一次刷新之前读取 v$segment_statistics 视图，以查看是否有任何读取。您还必须考虑所有自动统计数据收集作业。
One method other than auditing would be to read the v$segment_statistics view after one refresh and before the next refresh to see if there have been any reads. You'd have to account for any automatic statistics collection jobs also.
V$SQLAREA 表有两列，有助于识别数据库执行的查询。
SQL_TEXT - VARCHAR2(1000) - 当前游标的 SQL 文本的前 1000 个字符
SQL_FULLTEXT - CLOB - 当前光标的 SQL 文本的所有字符
我们可以使用此列来查找使用所述物化视图的查询
V$SQLAREA table has two columns which help identify the queries executed by the database.
SQL_TEXT - VARCHAR2(1000) - First thousand characters of the SQL text for the current cursor
SQL_FULLTEXT - CLOB - All characters of the SQL text for the current cursor
We can use this columns to find the queries using the said materialized views