Skip to Main Content

Oracle Database Free

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

V$SQL_HISTORY

Solomon YakobsonDec 16 2024 — edited Dec 16 2024

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>

Comments

MartinBach-Oracle Jan 8 2025

Hi Salomon,

please have a look at this blog post written by @ulrike-schwinn-oracle :

https://blogs.oracle.com/coretec/post/easy-sql-statement-tracking-in23c

I hope this answers your question, if not, please shout!

- Martin

Solomon Yakobson Jan 8 2025

@martinbach-oracle - No, it doesn't answer my question. Article you pointed to shows uses:

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

And in my post I said “Works fine when enabled on system level”. My question was about

SQL> ALTER SESSION SET SQL_HISTORY_ENABLED = TRUE;

where I showed SQL history was NOT captured even though it should be based on SQL_HISTORY_ENABLED:

Modifiable **ALTER SESSION**, ALTER SYSTEM

SY.

MartinBach-Oracle Jan 8 2025

As per the article I shared the situation is as follows at the moment

  • You must enable SQL history PDB-wide (only a DBA can do that) so there's a certain level of control over the feature
  • Your session has access to the SQL history
  • If you don't want to record anything, set sql_history_enabled to false.

I'm currently assessing if that's intended behaviour (in which case the documentation should be amended) or a feature not working as it should (in which case it needs fixing). The parameter is indeed session-modifyable, but not in the sense you expected.

We'll keep you posted.

- Martin

Solomon Yakobson Jan 8 2025

Do you mean it must be enabled on system level and not on session level and all session can do is disabe it for the session?

SY.

MartinBach-Oracle Jan 14 2025

Yes,

that's correct as of Oracle Database Free 23.6.

- Martin

Solomon Yakobson Jan 14 2025

Thanks Martin, I hope this will be added to 23AI docs soon.

SY.

1 - 6

Post Details

Added on Dec 16 2024
6 comments
162 views