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.

Session Terminated Problem when using the load_method hint.

nmmmgJul 2 2024 — edited Jul 2 2024

Hello,

I think I have found a bug that occurs when using the load_method hint. While testing direct load (append hint), I discovered a hidden hint called load_method. During testing, I found that when using the load_method high_water_mark option, the query unexpectedly terminates (this does not happen with the conventional option).

Database version: 23.4.0.24.05

====================

--example
--Create table

SQL> create table emp2 as select * from emp;

Table created.

--Insert data with load_method hint

SQL> insert /*+ load_method(emp2 high_water_mark) */ into emp2 select * from emp2;
insert /*+ load_method(emp2 high_water_mark) */ into emp2 select * from emp2
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 455996
Session ID: 41 Serial number: 40699
Help: https://docs.oracle.com/error-help/db/ora-03113/
--The session closed when an error occurs.

--If I check the alert log, I can see that an error has occurred.

2024-07-02T22:50:52.195289+09:00
FREEPDB1(3):Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x34] [PC:0x40A34E9, klcliti()+777] [flags: 0x0, count: 1]
Errors in file /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_455996.trc (incident=94625) (PDBNAME=FREEPDB1):
ORA-07445: exception encountered: core dump [klcliti()+777] [SIGSEGV] [ADDR:0x34] [PC:0x40A34E9] [Address not mapped to object] []
FREEPDB1(3):Incident details in: /opt/oracle/diag/rdbms/free/FREE/incident/incdir_94625/FREE_ora_455996_i94625.trc
FREEPDB1(3):Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2024-07-02T22:50:53.342891+09:00
FREEPDB1(3):********************************************************************
A process (pid: 455996) has internally requested a dump of selected
redo. This usually happens following a specific internal error, when
analysis of the redo logs will help Oracle Support with the
diagnosis.
It is recommended that you retain all the redo logs generated (by
all the instances) during the past 12 hours, in case additional redo
dumps are required to help with the diagnosis.
********************************************************************

--However, if I run a regular insert once before using the load_method(emp2 high_water_mark) hint, no error occurs.

--After reconnecting the session, perform a regular insert.

SQL> insert into emp2 select * from emp2;

14 rows created.

--Insert data with load_method hint

SQL> insert /*+ load_method(emp2 high_water_mark) */ into emp2 select * from emp2;

28 rows created.
(then no error occurs.)

Could you please check this issue for me?

Thank you. Have a nice Day!

This post has been answered by MartinBach-Oracle on Jul 4 2024
Jump to Answer

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 Jul 2 2024
5 comments
320 views