During an ODA X7-2S upgrade from 19.9 to 19.13 we encountered the following issue. The prepatch report mentioned that the check “Validate kernel log level” failed with the message OS kernel log level is set to debug, this may result in a failure when patching Clusterware If kernel OS log level is more than KERN_ERR(3) then GI patching may fail This problem also seems to exist in versions 19.10+ . It is a problem that can not be ignored. Trying to update the server anyways will lead to an error. Here is an example how such a prepatch report might look like Patch pre-check report ..read more

Problem / Justification The installation and configuration of ORDS has been changed quite a bit in version 22. Your hitherto existing installation and upgrade process needs to be carefully examined and probably reworked. Here is an issue I encountered. Imagine a database with two PDBs. One of them is the normal APEX database (test environment), the other is a regular pdb clone from a different environment, for example from development. The goal is to run and access a different version of ORDS (and potentially APEX) in each PDB. This post considers only ORDS, not APEX. For simplicity we want t ..read more

Motivation In recent times I speculate where and when to use scalable sequences. Especially if it makes sense to use them in general for sequence driven ID columns of larger tables. I know this sounds a bit like an early sign of CTD (compulsive tuning disorder), but at least I’m aware of it. Scalable sequences offer a performance benefit in certain special situations (hot block, index contention). This post is not about those benefits. I assume the reader has a basic understanding what a scalable sequence is. If not, check out this older post of mine: All about sequences . A scalable sequence ..read more

To show (not to edit) pretty code inside an APEX application in the past I had used the libraries that were deployed along with APEX, like CodeMirror (see https://svenweller.wordpress.com/2015/12/07/apex-5-syntax-highlighting/) and CkEditor. In APEX 21 CkEditor got a new version and CodeMirror is not supplied anymore since several APEX versions now. But there is a new very lightweight alternative, which is prism. In my use case I need this to quickly present currently running edition based plsql code. Implementation Step 1) Load code into a hidden page item I use a before region process to loa ..read more

Vector image by VectorStock / Anastasia8 This is something I read about and forgot until Chris Saxon mentioned and showcased it during todays AskTOM Office Hour session. In Oracle 12.2 the create table command was enhanced to avoid the error ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION during an exchange partition operation. We can now do create table ... for exchange. The basic idea is that the for exchange syntax enhancement considers things like invisible columns that are usually not created and by that it avoids complications during an exchange partition at a ..read more

tl;dr -- enable sql*plus to track the name of any currently running script as module name in application_info set appinfo on -- define sql*plus substitution variables SCRIPTNAME and LOGFILENAME column script_name new_value scriptname column logfile_name new_value logfilename -- fetch name of the currently running SQL*plus script select regexp_replace(sys_context('userenv','module'),'^\d*@ *') as script_name from dual; -- change suffix from .sql to .log and use as name for the log select replace('&scriptname.','.sql$','.log') as logfile_name from dual; -- start the log spool &log ..read more

Here is a quick overview about commands in SQL*plus that help to track and measure time. set time on/off This displays a prompt in front of each statement with the current time. Be aware that it is not the time, when the statement was executed, but the time when the line in sql*plus was created. This difference is usually not relevant when running scripts, just something to be aware of when manually typing and executing statements in sql*plus. SQL> set time on; 10:56:02 SQL> 10:56:10 SQL> execute dbms_session.sleep(3); PL/SQL procedure successfully completed. 10:56:23 SQL> The ..read more

A colleque made me aware of the following misbehaviour of Oracles optimizer. Shoutout to Christine S. who discovered that problem. demo create table a as select 1 x from dual; create table b as select 1 x, 1 y from dual union all select 1 x ,2 y from dual; select a.x, c.y from a, lateral (select b.x, max (b.y) y from b where b.x = a.x) c where a.x = c.x; result X Y ---------- ---------- 1 2 At first glance this looks like what we intended to see. However a closer inspection of the code reveals that select b.x, max (b.y) y from b is not val ..read more

Introduction To gather statistics for a schema or a table there is the dbms_stats package. Either we call it manually or the automatic statistic gathering (scheduled) job is used. We can provide many settings for the statistic gathering job as a parameter during the gather call. For parameters that we do not explicitly set, preferences are used. Either on a global or on individual table level. Since there are many preferences this article has some SQL statements that help to check how the current dbms_stats preferences are. Table preferences overrule the global preferences. And preferences set ..read more

The problem A little while ago during an ODA X7-2S upgrade from 19.6 to 19.9 the following error was encountered. SUCCESS: 2021-06-04 10:02:05: ...EFI device backup saved as '/opt/odabr/out/hbi/efi.img' INFO: 2021-06-04 10:02:05: ...step3 - checking EFI device backup ERROR: 2021-06-04 10:02:05: Error running fsck over /opt/odabr/out/hbi/efi.img ERROR: 2021-06-04 10:02:05: Command: 'fsck -a /opt/odabr/out/hbi/efi.img' failed as fsck from util-linux 2.23.2 fsck.fat 3.0.20 (12 Jun 2013) 0x25: Dirty bit is set. Fs was not properly unmounted and some data may be corrupt. Automatically removing di ..read more