HIGH_VALUE (and other LONG columns) revisited

Just a quick post to ensure people don’t get caught out by a recent “trick” I saw on an AskTOM question for extracting the HIGH_VALUE out of it’s LONG storage in the dictionary to a more appropriate datatype. A reviewer (I’m sure in good faith) posted the following mechanism to extract the HIGH_VALUE


SQL> create table t1 ( x timestamp )
  2  partition by range ( x )
  3  ( partition p1 values less than  (more...)

Same dog, different leash – functions in SQL

Let’s start with this somewhat odd looking result. I have an inline function that returns a random number between 0 and 20, and I call that for each row in ALL_OBJECTS and then I extract just those rows for which the generated random number is 10. Seems simple enough….but why do I get results for which the value of the second column is most certainly not 10?


SQL> with
  2    function
  3      getrand(pval in  (more...)

MERGE and ORA-8006

I’m sure there will be a slew of post-Kscope wrap up posts coming out into the blogosphere, so in lieu of that, and the the fact that I’m just stuck in an airport waiting for a flight, I’ll offer something slightly more technical. I did a post a while back about a curious error “unable to get a stable set of rows” when using MERGE. Here is another variant which can occur when (more...)

SMON_SCN_TIME and ORA-8161? Digging deeper

In the recent versions of the Oracle database, we’ve had the ability to convert between a System Change Number (SCN) and the approximate time to which that SCN pertains. These functions are unsurprisingly called SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN. The only potential misnomer here is that even though the functions are called “timestamp” and return a datatype of timestamp, on most platforms you are going to notice that the granularity doesn’t run down into fractions of seconds

Kscope Sunday will be awesome

Yeah yeah I know. What kind of dufus cut-pastes a giant image into their blog post. That would be ….. me Smile

But the reality is, there is just soooooo much going on this year on the Sunday before the “official” start of the Kscope conference, it was too hard to condense it into a few sentences. So I just dumped the image from the website, but you can read about it here.

It’s my first (more...)

With and without WITH_PLSQL within a WITH SQL statement

OK, let’s be honest right up front. The motivation for this post is solely to be able to roll out a tongue twisting blog post title Smile. But hopefully there’s some value as well in here for you if you’re hitting the error:

ORA-32034: unsupported use of WITH clause

First some background. A cool little enhancement to the WITH clause came along in 12c that allowed PLSQL functions to be defined within the scope of the (more...)

Advice on fragmentation and shrinkage

If you have performed some sort of data cleanup or similar on a table, then the deleted space will be reused by future insertions. But if

  • that cleanup was the last task you were performing on that table, ie, you were not expecting a lot of new data to ever come in again, or
  • you are performing a lot of full scan queries on that table and you want to make sure they are as (more...)

Generic data models … generic applications … ugh

There’s a hesitation to publish this example, because publishing it may be interpreted as an endorsement of this approach and it certainly isn’t. Over the years there have been plenty of articles describing the long term pain that typically comes from generic data models. Here’s a few to whet your appetite.

https://rodgersnotes.wordpress.com/2010/09/21/muck-massively-unified-code-key-generic-three-table-data-model/

https://www.red-gate.com/simple-talk/opinion/opinion-pieces/bad-carma/

https://asktom.oracle.com/pls/asktom/asktom.search?tag=query-on-design

But I’m posting this example because it serves as a nice tutorial for (more...)

DBMS_JOB – the joy of transactions

This is a followup to yesterdays post on DBMS_JOB and is critical if you’re upgrading to 19c soon. Mike Dietrich wrote a nice piece last week on the “under the covers” migration of the now deprecated DBMS_JOB package to the new Scheduler architecture. You should check it out before reading on here.

Mike’s post concerned mainly what would happen during upgrade (spoiler: the DBMS_JOB jobs become scheduler jobs but you can maintain them using the (more...)

DBMS_JOB – watching for failures

I had a friend point this one out to me recently. They use DBMS_JOB to allow some “fire and forget” style functionality for user, and in their case, the jobs are “best efforts” in that if they fail, it is not a big deal.

So whilst this may sound counter-intuitive, but if you rely on jobs submitted via DBMS_JOB to fail, then please read on.

By default, if a job fails 16 times in (more...)