A new, nice feature is introduced in Oracle 26ai that will enable developers  and system admins to retrieve their historically executed SQL queries.

The history recording of the executed queries will be persistent at the session level only and can only be viewed within the scope of the end-user (not visible to other users).

To simulate:

SQL> alter session set container=FREEPDB1;

// database parameter to enable SQL history is sql_history_enabled and its “FALSE” by default:

SQL> show parameter sql_history_enabled;

NAME                                 TYPE        VALUE

———————————— ———– ——————————

sql_history_enabled                  boolean     FALSE

// reset the parameter with ALTER SYSTEM command at the pluggable database level, it will require database restart

SQL> alter system set sql_history_enabled=true scope=spfile;

System altered.

SQL> startup force;

// the parameter is in effect now after restart

For testing, I will execute the following query:

SQL> select * from dual;

Checking the historical view v$sql_history to see if the executed query was recorded:

SQL> select SQL_ID,SQL_TEXT,SQL_EXEC_START,STATEMENT_TYPE from v$sql_history where SQL_TEXT like ‘%dual%’;

If you disconnect from your database session and connect again…no record for “select * from dual” query will be found.