Optimizer Tricks 1

I’ve got a number of examples of clever little tricks the optimizer can do to transform your SQL before starting in on the arithmetic of optimisation. I was prompted to publish this one by a recent thread on ODC. It’s worth taking note of these tricks when you spot one as a background knowledge of what’s possible makes it much easier to interpret and trouble-shoot from execution plans. I’ve labelled this one “#1” since I (more...)

sql_patch

This note is a short follow-up to a note I wrote some time ago about validating foreign key constraints where I examined the type of SQL Oracle generates internally to do the validation between parent and child tables.  In that article I suggested (before testing) that you could create an SQL patch for the generated SQL to over-ride the plan taken by Oracle – a plan dictated to some extent by hints (including a (more...)

Join View

It’s strange how one thing leads to another when you’re trying to check some silly little detail. This morning I wanted to find a note I’d written about the merge command and “stable sets”, and got to a draft about updatable join views that I’d started in 2016 in response to a question on OTN (as it was at the time) and finally led to a model that I’d written in 2008 showing that (more...)

Video : Install Docker on Oracle Linux 7 (OL7)

Today’s video is a run through installing the Docker engine on Oracle Linux 7 (OL7).

You can get the commands mentioned in this video from the following article.

You can see my other Docker posts and builds here.

The star of today’s video is Robyn Sands, formerly of the Oracle Real World Performance Group, and now something to (more...)

Why do I have an Ace logo on my website?

For the observant, you’ll have noticed that I have a logo on left side of my site saying Oracle Ace. Periodically I get asked what is it, what does it mean, and for those who are less involved in the Oracle community probably don’t know what it means.

What

Most developers will probably have encountered the idea of Java Champions or perhaps Microsoft MVPs (Most Valued Professional). All of these badges, and other large (more...)

How to install Oracle Database 18c XE on Windows

Get up and running with the free Oracle Database edition on Windows

Misleading ORA- Message

Oracle error messages are more or less the best in the industry but time-to-time they saddle you with a bad or misleading message. For example, I was running one of the code modules from my Oracle Database 12c PL/SQL Programming book for a class exercise and got this error message:

BEGIN
*
ERROR at line 1:
ORA-22288: file or LOB operation  failed
ORA-06512: at "STUDENT.LOAD_CLOB_FROM_FILE", line 71
ORA-06512: at line 11

Oddly enough, it (more...)

ORA-14300 partitioning key maps to a partition outside maximum permitted number of partitions and NULLs

While researching interval partitioning in Oracle 19c I came across a phenomenon I had already solved a long time ago but then forgot about. This time I decided to write about it so I don’t have to spend 5 minutes rediscovering the wheel. As always, if you are following along, make sure you are appropriately licensed for this feature

The Situation

Consider the following example:

SQL*Plus: Release 19.0.0.0.0 - Production on  (more...)

Recovering ODA VMs From Lost ACFS Snapshots

This is a continuation of my previous post regarding dropped ACFS snapshots.  In this scenario, a user logged in to a virtualized ODA system and deleted the underlying ACFS snapshots for multiple virtual machines on the host.  Oracle advises how to back up and restore guest VMs on an ODA in MOS note #1633166.1.

The basic process to get a clean backup of the VM is to shut it down, take a (more...)

gather_system_stats

What happens when you execute dbms_stats.gather_system_stats() with the ‘Exadata’ option ?

Here’s what my system stats look like (12.2.0.1 test results) after doing so. (The code to generate the two different versions is at the end of the note).


System Stats
============
Status: COMPLETED
Timed: 13-Aug-2019 15:00:00 - 13-Aug-2019 15:00:00
--------------------------------------------------
CPUSPEED        :
CPUSPEEDNW      :          918
IOSEEKTIM       :           10
IOTFRSPEED      :      204,800
MAXTHR          :
MBRC            :          128
MREADTIM        :
SLAVETHR        :
SREADTIM         (more...)

Oracle Error Bash f(x)

My students always struggle initially with basic Linux skills. I wrote little function for their .bashrc file to help them avoid the frustration. It finds and displays all errors by file name, line number and error message for a collection of log files in a single directory (or folder).

errors()
{
  # Determine if any log files exist and check for errors.
  label="File Name:Line Number:Error Code"
  list=`ls ./*.$1 | wc -l`
  if [[ $list}  (more...)

Azure VMs with Oracle- Next Steps

Microsoft has done a great job of documenting how to create a VM with the appropriate VM image and Oracle version, then how to log in, startup the listener and create the database.  I just have some enhancements I’d like to make to it, hoping to help it move up one level.

I Bequeath to You

All instructions provided by the Microsoft documentation show how to connect to the database using a bequeath, (BEQ) (more...)

Create Oracle User

After you create and provision the Oracle Database 11g XE, you create an instance with the following two step process.

  1. Create a student Oracle user account with the following command:

    CREATE USER student IDENTIFIED BY student
    DEFAULT TABLESPACE users QUOTA 200M ON users
    TEMPORARY TABLESPACE temp;
    

  2. Grant necessary privileges to the newly created student user:

    GRANT CREATE cluster, CREATE indextype, CREATE operator
    ,     CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION
    ,     CREATE TABLE, CREATE TRIGGER, CREATE  (more...)

Multi-table

Here’s a problem (and I think it should be called a bug) that I first came across about 6 years ago, then forgot for a few years until it reappeared some time last year and then again a few days ago. The problem has been around for years (getting on for decades), and the first mention of it that I’ve found is MoS Bug 2891576, created in 2003, referring back to Oracle 9.2.0. (more...)

ADW – Loading data using Object Storage

There are a number of different ways to load data into your Autonomous Data Warehouse (ADW) environment. I’ll have posts about these alternatives.

In this blog post I’ll go through the steps needed to load data using Object Storage. This might appear to have a large-ish number of steps, but once you have gone through it and have some of the parts already setup and configuration from your first time, then the second and subsequent (more...)

Vagrant : Oracle Database Build (19c on OL8)

Today’s video is an example of using Vagrant to perform an Oracle database build.

In this example I was using Oracle 19c on Oracle Linux 8. It also installs APEX 19.1, ORDS 19.2, SQLcl 19.2, with ORDS running on Tomcat 9 and OpenJDK 12.

If you’re new to Vagrant, there is an introduction video here. There’s also an article if you prefer to read that.

If you (more...)

Fedora 30 Missing Library

Having run into an obsolete library issue installing Oracle Database 18c XE on Fedora, Version 30, I opted to revert my student image to Oracle Database 11g XE. The installation went without issue but when I tried to log into SQL*Plus as the oracle user, I got the following error message:

sqlplus: error while loading shared libraries: libnsl.so.1: cannot open shared object file: No such file or directory

The libnsl.so.1 library (more...)

Troubleshooting

Here’s a question to provoke a little thought if you’ve got nothing more entertaining to do on a Sunday evening.  What threats do you think of when you see a statement like the following in (say) an AWR report, or in a query against v$sql ?

update tableX set
        col001 = :1, col002 = :2, col003 = :3, ...
        -- etc. 
        -- the names are supposed to indicate that the statement updates 302 columns
        -- etc.
         (more...)

Split Partition

This is a little case study on “pre-emptive trouble-shooting”, based on a recent question on the ODC database forum asking about splitting a range-based partition into two at a value above the current highest value recorded in a max_value partition.

The general principle for splitting (range-based) partitions is that if the split point is above the current high value Oracle will recognise that it can simply rename the existing partition and create a new, (more...)

Monitoring Oracle Database using Prometheus

Prometheus is a very popular framework for gathering metrics from a plethora of runtime components, recording, analyzing, visualizing them and making them available for companion technologies such as Grafana. Prometheus harvests information from ‘exporters’ – processes that expose an HTTP endpoint where Prometheus can scrape metrics in a format that it understands.

Recently, I received an email with the following question:

I need help on monitoring Oracle Database using Prometheus. I Googled but not find (more...)