Search

Top 60 Oracle Blogs

Recent comments

February 2020

Oracle 20c SQL Macros: a scalar example to join agility and performance

By Franck Pachot

.
Let’s say you have a PEOPLE table with FIRST_NAME and LAST_NAME and you want, in many places of your application, to display the full name. Usually my name will be displayed as ‘Franck Pachot’ and I can simply add a virtual column to my table, or view, as: initcap(FIRST_NAME)||’ ‘||initcap(LAST_NAME). Those are simple SQL functions. No need for procedural code there, right? But, one day, the business will come with new requirements. In some countries (I’ve heard about Hungary but there are others), my name may be displayed with last name… first, like: ‘Pachot Franck’. And in some context, it may have a comma like: ‘Pachot, Franck’.

There comes a religious debate between Dev and Ops:

Fake Baselines – 2

Many years ago (2011) I wrote a note describing how you could attach the Outline Information from one query to the SQL_ID of another query using the official Oracle mechanism of calling dbms_spm.load_plans_from_cursor_cache(). Shortly after publishing that note I drafted a follow-up note with an example demonstrating that even when the alternative outline was technically relevant the optimizer might still fail to use the SQL Plan Baseline. Unfortunately I didn’t quite finish the draft – until today.

The example I started with nearly 10 years ago behaved correctly against 11.1.0.7, but failed to reproduce the plan when I tested it against 11.2.0.3, and it still fails against 19.3.0.0. Here’s the test data and the query we’re going to attempt to manipulate:

Video : Secure External Password Store

Today’s video demonstrates how to use a Secure External Password Store to hold database credentials in a client wallet.

The video is based on this old article.

VirtualBox 6.1.4

VirtualBox 6.1.4 has been released.

The downloads and changelog are in the usual places.

I’ve done the installation on my Windows 10 PC at work and all is good. I’ll probably do the installations on my Windows 10, macOS and Oracle Linux 7 hosts at home tonight and update this post.

Happy upgrading!

Cheers

Tim…

Update: I did the upgrades on my Windows 10, macOS and Oracle Linux 7 hosts at home. Everything went fine, and it all looks good for now.

Data Pump Between Database Versions : It’s not just about the VERSION parameter! (Time Zone Files)

I was doing a small “quick” data transfer between two servers. The source was 19c and the destination was 18c, so I used the VERSION parameter during the export.

expdp … version=18 directory=…

The export went fine, but when I started the import I immediately got this error.

ORA-39002: invalid operation

A little Googling and I came across MOS Doc ID 2482971.1. In short, the time zone file was different between the two databases.

Announcing My Retirement (in 2030)!

I hereby announce that I will retire in 2030 and replace myself with an AI bot! I am serious. This is probably the clearest mission statement I’ve ever come up with in my career! This is essentially my statement of direction for the next 10 years. There will be a series of posts about what I am up to and where I’m going.
My plan for the next 10 years When the year 2020 arrived, I spent a month thinking about the future and what I want to be working on for the next decade.

Announcing My Retirement (in 2030)!

I hereby announce that I will retire in 2030 and replace myself with an AI bot! I am serious. This is probably the clearest mission statement I’ve ever come up with in my career! This is essentially my statement of direction for the next 10 years. There will be a series of posts about what I am up to and where I’m going.
My plan for the next 10 years When the year 2020 arrived, I spent a month thinking about the future and what I want to be working on for the next decade.

Exadata Workloads to Azure, Part II

In my last post, I discussed some of the unique challenges migrating Oracle workloads from Exadata to Azure posed.  Engineered systems are not your everyday lift and shift and are rarely simple.

Although I covered some focus areas for success, I’d like to get into the migration philosophical questions around cell offloading and IO.  cell information is referred to in the average Oracle 12c AWR report almost 350 times.  That’s a LOT of data to consider when migrating a workload to a server that won’t have cell nodes to OFFLOAD TO.

If cell nodes are creating a ton of different IO in Exadata and don’t exist in Azure, will it require IO in Azure?

Interval Partition(s)

A quirky little feature of interval partitioning showed up on Twitter today – a parallel insert that would only use a single PX slave to do the inserting. With 1.1 billion rows and the option for running parallel 32 this made the loading process rather slower than it ought to have been.

Fortunately it’s quite easy to model (and work around) the oddity. So here’s a small data set and an empty partitioned table to work with:

Cloud Control 13.4 : Silent Installation and Silent Upgrade

A little over a week ago Enterprise Manager Cloud Control 13.4 was released. The following weekend I spent 3 days running builds constantly trying to get a clean install to work. Eventually I tweeted out in frustration and a friendly face at Oracle, who I’ve stalked on numerous occasions, put me in touch with the EM dev team.

Having had a quick look at my Vagrant build, they suggested I unset the CLASSPATH environment variable, and a working build was born. Thanks very much to the EM dev team! Without them I would have spent days looking at it and would probably still have failed.

Installation

The resulting Vagrant build and an article about the silent installation of Cloud Control 13.4 can be found here.