Search

Top 60 Oracle Blogs

Recent comments

19c

JSON_TABLE() and date/time columns in Oracle 19c

While researching the use of JSON in Oracle 19c I came some interesting behaviour that wasn’t immediately obvious (to me). With this post I am hoping to save you a couple of minutes scratching your head when working with JSON_TABLE(). This is Oracle 19.3.0 on Linux and I’m connecting to it using SQLcl 19.1.

Some background

As part of my JSON-support-in-Oracle research I had a good look at JSON_TABLE. Although complex at first sight, it is a lot less intimidating if you know how to use XMLTABLE :) My goal for this post is to convert a JSON document to a relational structure.

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 old API without drama), but immediately Twitter was a buzz with a couple of concerns that I wanted to address:

1) What about new jobs submitted via the old API after the upgrade?

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 a row, it will marked as broken by the the database. Here’s a simple of example that, where the anonymous block will obviously fail because we are dividing by zero each time. I’ll set the job to run every 5 seconds, so that within a couple of minutes we’ll have 16 failures. First I’ll run this on 11.2.0.4

Avoid compound hints for better Hint Reporting in 19c

Even if the syntax accepts it, it is not a good idea to write a hint like:

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Comments.html#GUID-56DAA0EC-54BB-4E9D-9049-BCEA934F7A89

/*+ USE_NL(A B) */ with multiple aliases (‘tablespec’) even if it is documented.

One reason is that it is misleading. How many people think that this tells the optimizer to use a Nested Loop between A and B? That’s wrong. This hint just declares that Nested Loop should be used if possible when joining from any table to A, and for joining from any table to B.

Actually, this is a syntax shortcut for: /*+ USE_NL(A) USE_NL(B) */

19c EM Express (aka Oracle Cloud Database Express)

Oracle has a long history of interactive tools for DBA and, as usual, the name has changed at each evolution for marketing reasons.

OEM in Oracle7 #nostalgia

SQL*DBA had a Menu mode for text terminals. You may also remember DBA Studio. Then called Oracle Enterprise Manager with its SYSMAN repository and also referred to as OEM or EM. The per-database version has been called OEM “Database Control” and then “EM Express” in 12c. The multi-database version has been called according to the marketing tag “Grid Control” in 11g, and “Cloud Control” in 12c.

Intro: Initial Thoughts On Oracle Autonomous Database Cloud Services (Automatic For The People)

I’m currently writing up a few blog pieces on indexing in the Oracle Autonomous Database environments, but I thought I’ll begin by introducing what exactly are Oracle Autonomous Database Cloud Services and some of my initial thoughts, as there’s still some confusion on all this. Introduced by Uncle Larry at Oracle OpenWorld 2017, Oracle Autonomous […]

Use the features available!

Advance warning: This post is a just another normal Friday morning rant. If you’re not interested in my pontificating, move along…nothing else to see here Smile

Sometimes you can’t make use of a facility that you normally would, and you have to substitute in something else. For example, if I would normally take the train to the basketball game, but today it’s not running due to track maintenance, then I’ll take the bus. I have no problem with that, because there’s a reason that I can’t take the train that day.

What does get my goat is on a day when the train is running, you come to me and say:

LISTAGG hits prime time

It’s a simple requirement. We want to transform this:


SQL> select deptno, ename
  2  from   emp
  3  order by 1,2;

    DEPTNO ENAME
---------- ----------
        10 CLARK
        10 KING
        10 MILLER
        20 ADAMS
        20 FORD
        20 JONES
        20 SCOTT
        20 SMITH
        30 ALLEN
        30 BLAKE
        30 JAMES
        30 MARTIN
        30 TURNER
        30 WARD

into this: