This guide is a collection of issues known to affect multiple/all versions of Intershop 7 that are using Oracle 11g or 12c. Solutions or (temporary) workarounds are provided, where applicable, to the best of our current knowledge. The following should be considered reading this document
|CSI||Customer Support Identifier||The identifier of the support contract purchased from Oracle; needed to download patches and access certain areas in the support portal (MOS)|
|CTE||Common Table Expression||Named temporary result set, defined within the scope of SQL statement|
|MOS||My Oracle Support||Oracle's support portal and primary source of information about bugs and patches; requires an active OTN account (https://support.oracle.com)|
|OTN||Oracle Technology Network||Oracle's community network, providing access to documentation, knowledge and support|
|PSU||Patch Set Update||Set of patches to be installed on top of previously installed patch sets; e.g., updating from 18.104.22.168 to 22.214.171.124.1|
This section replaces the outdated article with the ID 28E122 and the title SQLLDR not working on Windows with Oracle Client 126.96.36.199.
Oracle 188.8.131.52 on Windows platforms (64-bit)
An attempt to execute
sqlldr.exe (SQLLoader) fails due to a missing library (
This issue can be resolved by installing patchset 20315685 or later. Alternatively, either copy the missing DLL from a working 184.108.40.206 server installation on the same Windows platform. See MOS 1936068.1 for more details (requires an active Oracle CSI).
It is also possible to use an older version of the Oracle client (11g), in order to work around this problem. This, however, may induce other issues and is not recommended as a permanent solution.
Oracle XE (11g), 220.127.116.11 and 18.104.22.168 on all platforms
Symptoms are (very) long execution times of usually fast queries, with sessions executing them waiting on event asynch descriptor resize. Single wait times are typically very short, but lots of waits occur. This typically causes very high CPU usage and the sessions appears to hang (see MOS 9829397.8). Currently, only
dbdelta (used to create a database difference report) seems to be affected by this bug.
Patches are available and the issue is fixed in Oracle 22.214.171.124 and higher. If the problem occurs on Oracle XE, the only workaround is to disable asynchronous disk I/O (Oracle does not provide any patches for XE).
In some cases this wait event might also indicate that affected SQL should be reviewed and tuned (see MOS 1273748.1 for details).
This section replaces the outdated article with the ID 23939F and the title Oracle server 126.96.36.199 can raise ORA-1652 when "WITH alias AS" queries are used.
Oracle 188.8.131.52 and higher
If the optimizer chooses to materialize the result of a WITH clause (also known as subquery factoring or common table expression / CTE), Oracle implicitly creates a temporary table for this purpose. The execution plan would then contain an operation called
TEMP TABLE TRANSFORMATION, as shown in the following example:
------------------------------------------------------------------ | Id | Operation | Name | ------------------------------------------------------------------ ... | 2 | TEMP TABLE TRANSFORMATION | | | 3 | LOAD AS SELECT | SYS_TEMP_0FD9D6601_F201F06C | ...
Under certain circumstances, these temporary tables, such as
SYS.SYS_TEMP_0FD9D6601_F201F06C in the example above, will only be removed if the session that created them is closed. This, however, might never happen because application servers keep some connections in an open state, unless they are being shut down. Therefore, these temporary tables may accumulate and occupy (lots) of space in the temporary tablespace that is assigned to the user creating executing the queries with CTEs.
This is not a bug and the only way to prevent implicit creation of such temporary tables is to force the optimizer to avoid temp table transformations. There are multiple ways to achieve this:
Disable the subquery materialization globally (affects all statements running in the instance).
ALTER SYSTEM SET "_with_subquery" = inline;
It is recommended to disable subquery materialization on statement-level, as shown below, because doing so on a global level may cause unwanted side effects.
Disable subquery materialization for specific queries by adding a hint, for example:
WITH cte AS ( SELECT /*+ INLINE */ foo FROM bar ) SELECT ...
Oracle XE (11g), 184.108.40.206 or higher on all platforms (but mostly on Unixoid ones)
The authentication method used when establishing a connection to a database has changed in Oracle's JDBC drivers version 220.127.116.11. It now requires the operating system to provide enough entropy (i.e., random numbers) in order to work properly. If, for whatever reason, there is a lack of entropy the authentication will block until enough is available. If blocking takes too long then network/socket timeouts may occur and/or you may notice errors like
java.sql.SQLRecoverableException: IO Error: Connection reset at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:467) Caused by: java.net.SocketException: Connection reset at oracle.jdbc.driver.T4CTTIoauthenticate.doOSESSKEY(T4CTTIoauthenticate.java:404) at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:385)
Errors like the one above are not necessarily caused by a lack of entropy. They might also be related to problems with the network, a firewall or configuration of the server.
In general, this problem can be solved by implementing a method to create or provide more entropy by the operating system. Usually this is done by running a background process (daemon). Most Linux distributions, for example, provide one of the following packages
rng-tools(enable use of hardware RNG)
haveged(feed randomness based on the HAVEGE algorithm into
Alternatively, though not recommended, you can use the non-blocking
/dev/urandom instead of
/dev/random. While the former provides less random numbers, it should be good enough for most practical purposes. You can use one of the following methods to achieve this:
This approach will affect all Java processes on the system and may cause unwanted side-effects. Intershop therefore recommends to configure this in
$IS_HOME/bin/tomcat.sh, as shown above.
This section replaces the outdated article with the ID 2G3590.
Oracle 18.104.22.168.3 (i.e., 22.214.171.124 with PSU 3) or lower on all platforms, two or more database schemas with identical or similar sets of database objects (e.g., staging/replication setups)
If a system is affected by the bug and
SQL statements may be executed in the wrong database schema. This may cause one or more of the following results:
The effects of this bug may be very subtle and hard to diagnose, because performing some action in the wrong schema does not necessarily cause an error. Intershop therefore strongly recommends to patch or to upgrade your database in case it may be affected by this bug.
More information and fixes related to the original bug can be found in MOS 13080778.8. The fix for this bug, however, introduced another issue that was fixed in another patch (see MOS 16344871.8). It is generally advised to install the latest patchset and/or PSU available for your platform, regardless of whether you are already affected by these bugs or not.
Oracle 126.96.36.199 on all platforms. Other releases (188.8.131.52 and later) may also be affected.
An attempt to import a schema from a dump fails with
IMP-00003: ORACLE error 600 encountered ORA-00600: internal error code, arguments: [kqrhsh], , , , , , , , , , , 
A dump import is most likely not the only affected operation, but it is the most reliable way to reproduce the issue. You will also find messages like this in the alert log of the affected instance:
DDE: Problem Key 'ORA 600 [kqrhsh]' was completely flood controlled (0x4) Further messages for this problem key will be suppressed for up to 10 minutes
Patch 17183227 seems to fix this issue and is available for various platforms and Oracle releases (see MOS 1596302.1 for more details). There is no known workaround for this issue.
Oracle 184.108.40.206 and above on all platforms.
All database authenticated users are getting ORA-01017 (invalid username/password; logon denied).
This is caused by initialization parameter
sec_case_sensitive_logon being set to false by default in (all) ICM setups, which is incompatible with the new default settings of SQL*Net, starting with Oracle 12.2. There are two possible solutions (see MOS 2040705.1 for more details)
Enable case-sensitive logons (recommended)
ALTER SYSTEM SET sec_case_sensitive_logon = true;
Modify SQLNET.ALLOWED_LOGON_VERSION_(SERVER|CLIENT) by adding the following line to $ORACLE_HOME/network/admin/sqlnet.ora on the database server:
The information provided in the Knowledge Base may not be applicable to all systems and situations. Intershop Communications will not be liable to any party for any direct or indirect damages resulting from the use of the Customer Support section of the Intershop Corporate Web site, including, without limitation, any lost profits, business interruption, loss of programs or other data on your information handling system.