oracle中如何查找表最后一次被查询的时间
我想获取有关在 oracle 中查询表时的信息。 是否有任何登录 oracle 显示查询。我正在查看 v$sqlarea 和 v$sqltext 但是，系统管理员不允许我访问这些表。
I want to get the information about when a table is queried in oracle.
Is there any log in oracle which shows the queries. I was looking around v$sqlarea and v$sqltext but, the system admin does not allow me to reach those tables.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
在默认安装中，我不知道如何可靠地获取此信息。您也许能够捕获最近在 v$sql* 视图中运行的 SQL 语句，但 v$sql* 视图本质上是瞬态的，用于支持数据库的正常操作。报表可能会过时，因此这不是一种可靠的审计方法。
获取此信息的正确可靠方法是什么？甲骨文审计。它能够记录有关数据库对象如何被访问的细粒度信息。
在这种情况下，您将需要调查 AUDIT SELECT。完成审计的基本配置（通常由 DBA 完成）后，可以为特定表设置 SELECT 审计，如下所示：
当用户直接或通过视图从员工中选择时，一条记录将被写入审计跟踪（文本文件或 SYS.AUD$，具体取决于配置）。该线索将包含用户名、时间戳、表名和一些其他信息，以帮助您确定用户当时在做什么。
以下是用于审计的 9i 参考，其中概述了有关 AUDIT SELECT 的信息：http://download.oracle.com/docs/cd/B10500_01/server.920/a96524/c25audit.htm
请注意，细粒度的审核可能会减慢速度。无论您现在正在审核什么，都有一个必须完成的新活动层（写入审核跟踪）。如果您有业务需要知道谁看到哪些可以理解的数据，但请确保了解性能影响。
In a default installation I know of no way to reliably get this info. You may be able to catch SQL Statements that were recently run in v$sql* views, but v$sql* views are transient in nature and are used to support normal operations of the database. Statements can age out so it is not a reliable way to audit.
What is a proper reliable way to get this info? Oracle Auditing. It contains the ability to record fine grained information about how your database objects are touched.
In this case you will want to investigate the AUDIT SELECT. After doing the basic config for auditing (usually done by a DBA) then SELECT auditing can be set up for specific tables like this:
When a user SELECTS from employee, either directly or through a view, a record will be written to the audit trail (text file or SYS.AUD$ depending on configuration). The trail will have username, timestamp, table_name, and some other information to help you determine what the user was doing at the time.
Here is a 9i reference for auditing that gives an overview including info on AUDIT SELECT: http://download.oracle.com/docs/cd/B10500_01/server.920/a96524/c25audit.htm
Be aware that fine grained auditing can slow things down. Whatever you are auditing now has a new layer of activity that must be completed (writing to the audit trail). If you have a business need to know who sees what data that is understandable, but make sure to be aware of the performance implications.