The Oracle ACE Program : My 15 Year Anniversary

Just noticed itโ€™s April 1st, which means itโ€™s my 15th year anniversary of being an Oracle ACE.

As usual Iโ€™ll mention some of the other anniversaries that will happen throughout this year.

  • 26 years working with Oracle technology in August. (August 1995)
  • 21 years doing my website in July. (Original name: 03 July 2000 or current name: 31 August 2001)
  • 16 years blogging in June. (15 June 2005)
  • 15 years on the Oracle ACE Program. (01 April 2006)
  • A combined 4 years as an Oracle Developer Champion, now renamed to Oracle Groundbreaker Ambassador. (21 June 2017)

Keep safe. Have a good one!

Cheers

Timโ€ฆ

Video : Attribute Clustering in Oracle Database 12c Onward

In today’s video we demonstrate attribute clustering, introduced on Oracle database 12.1.

The video is based on this article.

The star of today’s video is Francesco Tisiot, who seems to have some aversion to putting pineapple on pizza. Why anyone would ask an Italian about pizza is beyond me… ๐Ÿ™‚

Cheers

Tim…

Video : Create Table for Exchange With a Partitioned Table

In today’s video we demonstrate the CREATE TABLE โ€ฆ FOR EXCHANGE WITH TABLE feature, introduced in Oracle database 12.2.

The video is based on this article.

You can check out my other partitioning articles here.

The star of today’s video is Frits Hoogland and his son. This clip is from six years ago, and I don’t think Frits look any different today, but I’m guessing his son has changed a lot in that time. ๐Ÿ™‚ Frits recently set sail for the land of Yugabyte. I hope he will come back to visit from time to time… ๐Ÿ™‚

Cheers

Tim…

Video : Qualified Expressions Enhancements in Oracle Database 21c (part 2)

In today’s video we demonstrate some more of the enhancements to qualified expressions in Oracle database 21c.

You can see the first part here. This is the second video based on this article.

If you are new to qualified expressions, and don’t already know about the FOR LOOP enhancements in 21c, these may be helpful.

The star of today’s video is Kim Berg Hansen, who is an Oracle ACE Director and long time SQL and PL/SQL developer and advocate.

Cheers

Tim…

Oracle Database Upgrades : One Size Does Not Fit All

If you follow me on Twitter, you’ll know I’ve been doing a lot of upgrades recently. Whenever I mention upgrades, someone comes back with a comment asking me to describe in detail what I’ve done. This always makes me nervous, as every upgrade is potentially unique. What you have to do depends on a number of factors.

  • The database version you are upgrading from, and the version you are upgrading to.
  • The options you have installed, especially if some are deprecated or desupported in the new release.
  • The topology of your system. If you are running single instance on a regular file system, you’ve got a lot less to do compared to someone working with RAC on ASM with a Data Guard standby.
  • Are you taking the opportunity to move to new kit and/or base operating system? From OL6 to OL7/8 for example.
  • Are you changing OS completely? Maybe you’ve finally made the decision to upgrade from AIX/HP-UX/Solaris to Oracle Linux. ๐Ÿ™‚
  • Are you planning to convert from non-CDB to the multitenant architecture? You should be!
  • How big is the database you are upgrading? I’ve done some small ones with data pump rather than doing a regular upgrade.
  • What is your tolerance for downtime? If you have a reasonable downtime window, you can turn everything off and upgrade it, which is a lot simpler than trying to keep the lights on.
  • Are there any vendor restrictions that alter how you approach the upgrade?

All of these things, and more I can’t think of off the top of my head, have to be factored in when planning an upgrade, and this is why I say every upgrade is potentially unique.

The types of upgrades I’ve done recently fall into the following general groups, with varying numbers in each group. A number of them have included a move to new kit, because they were running on Oracle Linux 6.

  • 11.2.0.4 to 19c non-CDB (Vendor support issues)
  • 11.2.0.4 to 19c PDB
  • 12.1.0.2 non-CDB to 19c PDB
  • 18c PDB to 19c PDB
  • 18c PDB to 19c PDB using Data Pump
  • 11.2.0.4 to 12.1.0.2 as stepping stone to 19c

The last one may seem odd to people, but this is due to an application dependency issue. The full process for this is as follows.

  • Upgrade database from 11.2.0.4 to 12.1.0.2.
  • Upgrade application to a version that supports both 12.1.0.2 and 19c.
  • Migrate to new kit.
  • Upgrade database from 12.1.0.2 to 19c.
  • Upgrade application to latest version.

What we are trying to do is make everything as “one size fits all” as possible, to make future upgrades, or moves to the cloud, easier, but that’s not always possible due to other constraints.

I do have a couple of upgrade articles on the site, but they are intentionally basic and I never intend to write anything more detailed about upgrades, because it’s impossible to write something that will satisfy every possibility.

So in summary, there is no one size fits all solution to upgrades unless you have already commoditized all your systems, like the cloud providers do. If you are working with a load of on-prem systems, some of which you have inherited from others, each upgrade will be a voyage of discovery, so don’t ask me for a detailed breakdown of what I did, because I’m just going to say no. There is a reason why there is a great big upgrade manual released with every version of the database!

Cheers

Tim…

Video : Qualified Expressions Enhancements in Oracle Database 21c (part 1)

In today’s video we demonstrate some of the enhancements to qualified expressions in Oracle database 21c.

The video is based on this article.

If you are new to qualified expressions, and don’t already know about the FOR LOOP enhancements in 21c, these may be helpful.

The star of today’s video is Mike Hichwa. Everything you know and love about APEX started with this guy!

Cheers

Tim…

Oracle Cloud Infrastructure (OCI) and Terraform : First Steps

We’ve got some stuff going on at work using Terraform, or Terrahawks as I like to call it, so I figured it was about time I had a play with it. I probably won’t be doing much of the project work myself, but I like to understand a bit about all the things we do.

The biggest problem with going on one of these “learning missions” is finding something to do that makes it feel real to me. I have some test environments across two Oracle Cloud accounts. One is my free tier account and the other is a trial account I get through the Oracle ACE Program, that has quite a lot of credit. ๐Ÿ™‚ I figured I would automate the build of my test environments, so I can trash and rebuild them at will. So with that as my mission, I’ve taken my first steps into Terraform.

I’m not finished yet, and I’m not saying this is production ready “best practice” stuff. It’s just something I’ve been playing around with and it works great. Fortunately the Terraform OCI Provider and resources do all the heavy lifting, and if you are used to using Oracle Cloud, it’s pretty easy to navigate around the documentation, as a lot of it is organised similar to the menu structure. You can find the top-level of the docs here.

As I always say in these situations, it’s early days for me. I’ve got a number of things I want to build, and I’m sure that process will teach me more, and make me look back at these article and cringe. That’s more rewriting on the way. ๐Ÿ™‚

I’m putting this stuff into a GitHub repo, but I’ve not published that yet. I’m still trying to figure out what I should and shouldn’t include.

Cheers

Tim…

PS. If you don’t remember Terrahawks, this might remind you.

Video : FOR LOOP Iteration Enhancements in Oracle Database 21c

In today’s video we demonstrate the FOR LOOP iteration control enhancements in Oracle database 21c.

The video is based on this article.

If you’re not already got up to speed with qualified expressions in 18c, you might want to check this out also.

The star of today’s video is Alex Nuijten, of PL/SQL and in recent years APEX fame.

Cheers

Tim…

Video : Using Expressions in Initialization Parameters in Oracle Database 21c

In today’s video we demonstrate using expressions in initialization parameters, introduced in Oracle database 21c.

The video is based on this article.

The star of today’s video is Deiby Gรณmez, who is a fellow Oracle ACE Director, and was kind enough to take me sightseeing when I visited Guatemala for a conference.

Cheers

Tim…

Video : Partial Indexes for Partitioned Tables

In today’s video we demonstrate partial indexes for partitioned tables, introduced in Oracle database 12.1.

The video is based on this article.

The star of today’s video is Carry Millsap, who is being lead astray by a very naughty James Morle.

Cheers

Tim…