Oracle 历史SQL记录查询|极客笔记

Oracle 历史SQL记录查询

介绍

在Oracle数据库中，每次执行的SQL语句都会被记录在历史SQL中。这些历史SQL记录可以帮助开发人员和DBA分析和调优SQL语句的性能，以及追踪数据库的变化。

本文将详细介绍如何查询Oracle数据库中的历史SQL记录。包括如何启用历史SQL记录功能、如何查询历史SQL记录，并给出一些实际的示例。

启用历史SQL记录功能

要查询历史SQL记录，首先需要确保已启用该功能。在Oracle数据库中，可以通过以下步骤来启用历史SQL记录功能：

使用DBA账户登录到Oracle数据库。
执行以下命令以启用历史SQL记录功能：
```
ALTER SYSTEM SET audit_trail='DB,EXTENDED' SCOPE=SPFILE;
```
这将启用数据库级别和扩展的SQL审计，并将设置保存到数据库的参数文件（SPFILE）中。
重新启动数据库以使参数设置生效：
```
SHUTDOWN IMMEDIATE;
STARTUP;
```
现在，历史SQL记录功能已经启用。

查询历史SQL记录

一旦启用了历史SQL记录功能，就可以使用以下方法来查询历史SQL记录。

方法1：使用DBA_AUDIT_TRAIL视图

Oracle数据库提供了一个名为DBA_AUDIT_TRAIL的视图，可以用于查询历史SQL记录。该视图包含了所有已审计的SQL操作信息。

以下是一个示例查询，展示如何使用DBA_AUDIT_TRAIL视图来查询历史SQL记录：

SELECT
    os_username,
    username,
    extended_timestamp,
    sql_text
FROM
    dba_audit_trail
WHERE
    extended_timestamp > SYSDATE - 7
    AND action_name = 'SELECT'
ORDER BY
    extended_timestamp DESC;

上述查询将返回过去7天内执行的所有SELECT语句的详细信息，包括操作系统用户名、数据库用户名、时间戳和SQL文本。

方法2：使用DBA_HIST_SQLTEXT视图

除了DBA_AUDIT_TRAIL视图外，还可以使用DBA_HIST_SQLTEXT视图来查询历史SQL记录。该视图包含了已保存的SQL语句的文本。

以下是一个示例查询，展示如何使用DBA_HIST_SQLTEXT视图来查询历史SQL记录：

SELECT
    sql_id,
    sql_text,
    parsing_schema_name
FROM
    dba_hist_sqltext
WHERE
    parsing_schema_name = 'HR'
    AND sql_id = '47zvjshqtpvsd'
ORDER BY
    first_load_time DESC;

上述查询将返回用户HR在过去的历史中执行的特定SQL语句的详细信息，包括SQL ID、SQL文本和解析的模式名称。

示例

以下是一个实际示例，展示如何查询历史SQL记录并进行分析。

假设有一个名为employees的表，其中存储着公司的员工信息。现在要查询上个月调薪的所有员工的姓名、调薪前的薪水和调薪后的薪水。

首先，我们需要在数据库中执行以下SQL语句以启用历史SQL记录功能：

ALTER SYSTEM SET audit_trail='DB,EXTENDED' SCOPE=SPFILE;

然后，我们可以使用以下查询来检查上个月的所有历史SQL记录：

SELECT
    os_username,
    username,
    extended_timestamp,
    sql_text
FROM
    dba_audit_trail
WHERE
    extended_timestamp > TRUNC(SYSDATE, 'MONTH') - INTERVAL '1' MONTH
ORDER BY
    extended_timestamp DESC;

通过分析历史SQL记录，我们可以找到一条符合要求的SQL语句：

SELECT
    e.employee_name,
    e.salary AS old_salary,
    e.salary * 1.1 AS new_salary
FROM
    employees e
WHERE
    e.adjustment_date >= TO_DATE('2021-06-01', 'YYYY-MM-DD')
    AND e.adjustment_date < TO_DATE('2021-07-01', 'YYYY-MM-DD');

根据上述查询，我们可以获取上个月调薪的员工信息。