According to SQL_HISTORY_ENABLED:
Modifiable ALTER SESSION, ALTER SYSTEM
However it doesn't seem it works on session level:
SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Mon Dec 16 10:21:03 2024
Version 23.5.0.24.07
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Enter password:
Last Successful login time: Mon Dec 16 2024 10:20:31 -05:00
Connected to:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.5.0.24.07
SQL> SHOW PARAMETER SQL_HISTORY_ENABLED
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sql_history_enabled boolean FALSE
SQL> ALTER SESSION SET SQL_HISTORY_ENABLED = TRUE;
Session altered.
SQL> SHOW PARAMETER SQL_HISTORY_ENABLED
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sql_history_enabled boolean TRUE
SQL> SELECT COUNT(*) FROM V$SQL_HISTORY;
COUNT(*)
----------
0
SQL> SELECT 1 FROM TEST_FI_LOB;
no rows selected
SQL> SELECT 2 FROM TEST_FI_LOB;
no rows selected
SQL> SELECT 3 FROM TEST_FI_LOB;
no rows selected
SQL> SELECT COUNT(*) FROM V$SQL_HISTORY;
COUNT(*)
----------
0
SQL>
Works fine when enabled on system level. Also, dosc say “This feature monitors user-issued SQL statements in each user session on a best effort, depending on memory capacity”. No clues on what memory, how does it allocate it and is there a way to manually set it. I enabled it on system level and tried 100 selects form SELECT 1 FROM TEST_FI_LOB; to SELECT 100 FROM TEST_FI_LOB; and V$SQL_HISTORY saved just 60 last statements
SY.
P.S. I forgot to mention 23AI for Exadata:
SQL> select version_full from v$instance;
VERSION_FULL
-----------------
23.5.0.24.07
SQL>