-
Tip: Expanding a SQL statement
In all recent versions of the database you can call DBMS_UTILITY.EXPAND_SQL_TEXT to get the “true” version of a SQL that the database will run. It takes your SQL as input and returns a CLOB, which means its all a bit fiddly if you have a SQL statement already sitting in your SQLcl/SQLPlus buffer. Using a… Read more
-
WITH clause gets a 26ai boost!
By way of background, a while back I did video called “My New Favourite Join” on the LATERAL clause. The reason I like it is that it lets you solve a problem by composing little pieces together rather than writing one enormous query. Now this post is not about LATERAL, but I’ll show a quick… Read more
-
The Goldilocks problem – Materialized Views
I’m sure you’ve all heard the tale of Goldilocks and the Three Bears, but I’d like to apply the Goldilocks principle to a database object-namely, a materialized view. You might be thinking, how does that apply to database objects when it comes to things like not too cold, not too hot, or not too heavy,… Read more
-
Blue Sky Programming – The Optimism Trap
Many years ago, before I joined Oracle, I was working on a major modernisation project. We were replacing an existing non-Oracle system with an entirely new Oracle database application written from scratch. Not long after deploying a new version into our test environment, the results came back and a large number of tests had failed.… Read more
-
Keeping your tech ego in check
In my usual twitter doom scrolling, I came across this post the other day Now it’s pretty obvious to me that this is one of the standard “engagement bait” posts that are commonplace nowadays. It’s designed to polarize opinions and thus the resultant “discussion” becomes a lot more inflammatory and thus succeeds the goal of… Read more
-
First dabblings with APEXlang
Obviously there are plenty of folks in the Oracle APEX team with waaaayy more experience in APEXlang than I have, so this isn’t an in-depth expose into all of the nuts and bolts of APEXlang. For that, I recommend you check out the AskTOM Live sessions where the APEX team are holding regular deep-dives on… Read more
-
Vanishing objects; hello ORA-08103
In the previous post we saw that even though we had done an exchange partition mid-flights through a query execution, the query kept on running to successful completion. The database managed to find the data from the exchanged partition and keep on executing. The logical next question is – is that always the case? What… Read more
-
A very robust query
Some times I’m blown away by how the database will try very hard to save you from yourself :-). Consider the following example: I’ve got a table called T. It’s got two columns, X and Y, and it’s partitioned into four partitions, the values being based on X, 1, 2, 3 and 4 for the… Read more
-
The secret APEX performance hack
Here’s a little secret APEX performance hack if you are running on the Autonomous database. As background, I’m running an Autonomous database with six CPUs and a terabyte of storage. Let me grab a good sized chunk of that terabyte with a single table. I just kept duplicating copies of DBA_OBJECTS until my table got… Read more
-
Signal to noise ratio – the AWR trap
Its never been easier to analyze an AWR report. With our new oracle skills repository, your favourite agent can do a respectable job giving some insights into the AWR report you created for your database. In my case, once codex has churned through the report using its various skills, the driving recommendation was as below… Read more
-
The new release update experience
For about the last … hmmm…maybe decade or more, when you downloaded a release update from MOS for your database, the patch would consist of a single folder which was the patch number. You would unzip that to temporary location, download a fresh copy of OPatch and then patch your database. OPatch would take the… Read more
-
My APEX installer / patcher
Hopefully you have just seen that APEX patchset bundle 16 has just been released for 24.2. Of particular note is that a well-publicised Chrome change to deprecate the unload event created some issues in APEX. I regularly create fresh databases to handle questions on AskTOM or for demos in my videos or at conferences, and… Read more