Video : DBMS_SESSION.SLEEP Replaces DBMS_LOCK.SLEEP in Oracle Database 18c

In today’s video we discuss a simple, but significant change introduced in Oracle database 18c.

The video is based on this post.

The star of today’s video is a rather giggly Joel PΓ©rez. We did more takes for this clip than you can imagine, and I still ended up using one of the first. πŸ™‚

Cheers

Tim…

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…

Learning new stuff is good, even if you don’t use it…

I mentioned on Twitter, I was on a training course last week for a non-Oracle cloud-based database engine.

I went into the week feeling quite nervous about things, because I like to learn things at my own pace. Having to work at the same pace as others doesn’t work well for me. I’m quick at some things, and slow at others. That said, it was a really fun week. It flew by, which is always a good sign.

The main point of this post is not about the product or the course itself. It’s a more general point. I think it’s important to expose yourself to new bits of tech all the time. I have no idea how involved I will be in the project using this product, but it’s still good to learn more about it. When you learn something new it makes you question how you approach your existing role, and how you use your existing tech.

Of course, the Oracle database is what I’ve invested most of my time into, and it’s the thing I know the most about, but it’s far from the only thing I do on a day to day basis. I often say people should try the latest version of the Oracle database, because it might make them alter their approach to the older versions. This is true of different technology stacks too. Over the last few years I’ve spent a lot of time using and writing about things that are not core Oracle database tech, but that has had a positive influence on my approach to the database.

So going back to the title of the post, it’s important you learn new things, even if you don’t use them often, as they will change you and give you a broader understanding of your goal.

Cheers

Tim…

PS. How you approach learning new stuff depends on you. I wrote a series of posts about Learning New Things.

PPS. It’s not all about tech. You should also expose yourself to different elements of projects, so you better understand where other people are coming from.

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 : Blockchain Tables in Oracle Database 21c

In today’s video we demonstrate blockchain tables, introduced in Oracle database 21c.

The video is based on this article.

As I mention in the video and the article, blockchain tables are a niche feature, but it’s good to know they exist in case you come across a requirement where they would be useful.

The star of today’s video is Martin Klier. πŸ™‚

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.