How to query history in oracle

In Oracle database, query history refers to the historical record of query operations, including query statements, query time, query results and other related information. Query history is important for optimizing database performance, troubleshooting problems, and confirming data integrity.

In order to record query history, Oracle provides a variety of methods, which we will introduce one by one below.

1. Using Oracle Auditing

Oracle auditing is a mechanism for recording operations on the database. We can record query history by enabling the audit function and setting audit rules.

Enable the audit function:

ALTER SYSTEM SET audit_trail=DB SCOPE=SPFILE;

After turning on the audit function, you also need to set audit rules. You can set multiple rules as needed . For example, we can set the query for records with more than 1,000 rows in all tables to be audited.

CREATE AUDIT POLICY query_audit ACTIONS SELECT TABLE,SELECT ANY TABLE WHERE rowcount > 1000;

Enable the above rules:

AUDIT POLICY query_audit;

In this way, when an audit condition occurs, Oracle will record relevant information in the audit log file.

Query audit results:

SELECT username, TO_CHAR(timestamp, 'YYYY-MM-DD HH24:MI:SS'), statement_type, sql_text, extended_timestamp, return_code FROM dba_audit_trail WHERE statement_type = ' SELECT' AND return_code = 0 ORDER BY timestamp DESC;

It should be noted that when the audit function is enabled, the audit log needs to be cleaned regularly.

2. Query historical view

Oracle provides multiple system views to facilitate users to query historical records. Among them, DBA_HIST_SQLTEXT is used to record the history of SQL statements.

Query SQL statement history:

SELECT sql_text, force_matching_signature, plan_hash_value, last_active_time FROM DBA_HIST_SQLTEXT WHERE sql_text LIKE '%SELECT%' ORDER BY last_active_time DESC;

The above query statement All SQL statement history records starting with "SELECT" will be returned, sorted by the most recent activity time.

3. Use third-party tools

In addition to the methods provided by Oracle, we can also use GUI-based Oracle management tools (such as Toad) to easily query historical records.

Through the Toad visual interface, we can easily view SQL history, filter and sort it. In addition, Toad also provides a variety of tools for optimizing query performance, such as SQL tuning assistant, execution plan analysis, etc.

It can be said that using third-party tools is the most convenient and intuitive way to query historical records.

Summary:

Query history is very important for the management and maintenance of Oracle database. Oracle provides a variety of ways to record query history, including audit mechanisms, query history views, and third-party tools. Users can choose the most suitable method to query historical records based on actual needs. At the same time, you also need to pay attention to regularly cleaning and backing up query history to avoid problems such as taking up too much disk space or data loss.

The above is the detailed content of How to query history in oracle. For more information, please follow other related articles on the PHP Chinese website!