DBA and PL/SQL Development Tools (Poll Results Discussed)

I’ve been thinking about my DBA and PL/SQL tool choices recently, so I thought I would go out to Twitter and ask the masses what they are using.

As always, the sample size is small and my followers have an Oracle bias, so you can decide how representative you think these number are…

Here was the first question.

What tool do you do *most* of your Oracle DBA work with?

I expected SQL*Plus and SQLcl to be the winner here, and I was right. A lot of DBAs are still “old school” where administration is concerned. It may be tough for a beginner to use these command line tools, but over time you build up a list of scripts that mean it is much quicker than using GUI tools for most jobs.

SQL Developer had a pretty good showing at nearly 28%. I’m glad people are finding value in the DBA side of SQL Developer. TOAD/other were not doing so well. I know there are a lot of companies out there trying to make money with DBA tools, but maybe this is a tough market for them. Of course there are cross platform tools that may do well with other engines, even though they don’t register so well with the Oracle crowd.

I guess the real surprise was less than 8% using EM Cloud Control. Having said that, I’m considering ditching it myself. I like the performance pages and we use it as a centralized scheduler for backups, but I’m not sure our usage justifies the crazy bloat that is Cloud Control. It would be nice to remove all those agents and clean up! This figure of less than 8% is all the more surprising when you consider it is free (no cost option). Of course total cost of ownership is not just about the price tag…

This was the next question.

What tool do you do most of your PL/SQL development with?

I was expecting SQL Developer to do well here, but I was surprised by how low TOAD was in the list. I’ve worked at a few companies over the years where TOAD was a staple. I guess the consistent improvements to SQL Developer and a price tag of “free” have broken the TOAD strangle hold.

There were a few comments about Allround Aautomations PL/SQL Developer, which I used in one company many years ago. If I could have added an extra line in the poll, I would have put that as an option, because I know it is still popular. There were also mentions of DataGrip and a number of people using VS code with assorted extensions, including Oracle Developer Tools for VS Code.

Sadly, but understandably, SQL*Plus and SQLcl were low on this list. I’m an old timer, so I’ve had jobs where this was the only option. At one job I wrote my own editor in Visual Basic, then rewrote it in Java. Once SQL Developer (known as Raptor at the time) was released I stopped working on my editor…

When you’re doing “proper” PL/SQL development, it’s hard not to use an IDE. They just come with so much cool stuff to make you more productive. These days I tend to mostly write little utilities, or support other coders, so I find myself writing scripts in UltraEdit and compiling them in SQLcl. If I went back to hard core PL/SQL development, I would use an IDE though…

For fun I ended with this question.

SQL Developer and TOAD have a fight to the death. Who wins?

SQL Developer won, but it came out with a detached retina and some broken ribs!

Remember, you are most productive using the tools that suit your working style, but you should always keep your eyes open for better ways of working. Choice is a wonderful thing!

Cheers

Tim…

Packer, Vagrant and Docker : Latest Changes

The quarterly Oracle security patches trigger a whole bunch of build changes for me. This post just gives you a run through of what happened over the weekend.

Packer

The release of VirtualBox 6.1.36 means all my Vagrant boxes on Vagrant Cloud have the wrong guest additions, so I rebuilt all of them using Packer. The Oracle Linux 7, 8 and 9 boxes are now up to date. You can see my Packer builds under my Vagrant repository on GitHub here.

https://github.com/oraclebase/vagrant/tree/master/packer

Note. During the packer builds I noticed the VirtualBox 6.1.36 guest additions require some extra packages during the installation (libX11, libXt, libXext, libXmu).

Vagrant

I had recently updated all relevant Vagrant builds with the latest versions of Tomcat, SQLcl and ORDS updates, but I was still waiting on the OpenJDK 11.0.16 release. On Friday morning I noticed Adoptium released it, so I made the necessary changes to the builds to include it. I usually don’t include Oracle patches in my database builds, but some installations, like Oracle 19c on OL8, require them. I’ve updated them to include the 19.16 patches where necessary. You can find my Vagrant builds on GitHub here.

https://github.com/oraclebase/vagrant

Docker

Similar to the Vagrant section above, the relevant Docker/Podman builds were updated to use OpenJDK 11.0.16, and the Oracle 19c on OL8 build had it’s patch script modified for the 19.16 patches. You can find my container stuff on GitHub here.

https://github.com/oraclebase/dockerfiles

Happy building!

Cheers

Tim…

VirtualBox 6.1.36

VirtualBox 6.1.36 has been released.

The downloads and changelog are in the usual places.

I’ve installed it on Windows 11 and macOS Big Sur hosts with no dramas. Some time in the next 24 hours I’ll upload updated versions of my Oracle Linux 7, 8 and 9 Vagrant boxes.

Once that’s done I will be running through all my Vagrant builds using the latest Oracle patches.

Cheers

Tim…

Oracle LiveLabs : Experience Oracle’s best technology, live!

Just a quick post to mention Oracle LiveLabs.

Workshops

The LiveLabs workshops allow you to step through examples live on your Oracle Cloud tenancy. The workshops can be found here.

Contributions

If you are interested in helping to create some workshops, I’m sure Michelle Malcher would love to hear from you. You might also want to send her some feedback about what you would like to see in this space.

Cheers

Tim…

PS. A little bird told me there are ACE points for people from the Oracle ACE program that get involved. 🙂

PPS. You can also follow @Oracle_LiveLabs on Twitter. It’s new, so there’s not a lot of tweets at the moment…

Video : Data Pump Checksums in Oracle Database 21c

In today’s video we demonstrate the use of Data Pump checksums in Oracle Database 21c.

The video is based on part of this article.

The star of today’s video is Franck Pachot, who is checking out his escape routes. 🙂

Cheers

Tim…

Video : RANK and DENSE_RANK Aggregate Functions : Making Ranking Predictions

In today’s video we demonstrate the RANK and DENSE_RANK aggregate functions, using them to make ranking predictions.

The video is based on part of this analytic functions article.

RANK and DENSE_RANK Analytic Functions

You might find these useful.

The star of today’s video is Kim Berg Hansen, who is an all round SQL and PL/SQL good guy, as well as a tireless contributor to the Dev Gym.

Cheers

Tim…

What architecture are you using for your production Oracle databases? (Poll Results Discussed)

I was having an email discussion with the folks from DBmarlin, and the question came up about the adoption of the multitenant architecture. Were people using the multitenant architecture, or sticking with the non-CDB architecture for now?

Followers of the blog know I threw my hat in with the multitenant architecture from Oracle 12.1.0.2 onward. We use Oracle 19c for everything of value now (there are a couple of dead projects still on 11.2) and all but one of those projects use PDBs. Suspecting I was not the norm here, I asked some questions on Twitter.

As usual I’m going to start with some caveats. The sample size is small. People who interact about tech on social media my not be a diverse sample. I’m going to act like these results are representative of the wider scene, but they may not be.

Here is the first question.

What architecture are you using for your production Oracle databases?

The fact that only 33% were on non-CDB only was a positive sign in my view. The introduction of the multitenant architecture made a lot of people nervous, and it was not without its problems in the early days. Even so, the combined total of only non-CDB and mostly non-CDB is still 50% of respondents. When you consider Oracle 21c makes the multitenant architecture mandatory, and the next long term release is 23c, there is a lot of work for people to do when they make the switch to 23c. The conversion is simple enough. It’s the testing resource that could hurt people.

Having 50% of people using mostly PDBs or all PDBs is a really good sign, and will make life much easier for them when they come to upgrade to the next long term release.

To dig a little deeper I asked this question.

Are you provisioning new production Oracle databases as PDBs?

So just over 65% of people said they are provisioning new Oracle databases as PDBs. That’s very positive, and makes a lot of sense going forward. Why would 35% of people stick with the non-CDB architecture for new databases? Some things I can think of include.

  • They are using an older versions of the database, and don’t have the option of using the multitenant architecture.
  • They want a one-size-fits-all approach to the database, and will convert everything when they are forced to.
  • Vendors don’t support the multitenant architecture. I have one project where I suspect the vendor doesn’t even know the multitenant architecture exists, let alone supports it.
  • Internal development teams haven’t caught up with the database version. From my experience, the only thing that was really affected by our move to PBDs was CRON jobs using OS authentication. We switched to using secure external password stores and everything was fine. I wrote an article on possible solutions to the OS authentication issue here.

If I was not working at my company, I don’t believe they would have been running on 19c with PDBs. I’ve been pushing for many years to improve the attitudes to upgrades and patching. The easy path is to do nothing…

Finally I wondered how many people were purchasing the multitenant option. Remember, from 19c onward you can run up to 3 user-defined PDBs without having to buy the multitenant option.

For those people using PDBs for production Oracle databases, have you bought the Multitenant Option?

At 35%, I’m actually surprised how many people have purchased the multitenant option. I expected it to be a lot lower. Don’t get me wrong, I think the multitenant architecture is fine. I’ve been advocating for people to switch to it and use lone-pdb since it was introduced on Oracle 12.1. I would like to use more than 3 PDBs per 19c instance, but I can’t justify the cost for a feature that I could argue should be free in all editions.

So there you have it. A quick snapshot of what my followers are saying.

Remember, the multitenant architecture is mandatory from Oracle 21c onward, with Oracle 23c being the next long term support release, so you are going to have to get comfortable with this stuff if you want to remain in support long term.

If you want any help getting to grips with the multitenant architecture I have a load of articles and videos.

Cheers

Tim…

Video : SQLcl : Data Pump (Part 2)

In today’s video we demonstrate table-level export and imports using the integration between SQLcl and Data Pump.

This video builds on the first video, so it makes sense to watch that first.

Both these videos are based on this article.

I have a few articles on other SQLcl features, which you can find here.

The star of today’s video is Craig Shallahamer. Craig is the only person I know who defies the first law of thermodynamics, as he’s able to produce more energy than he consumes. If you’ve seen him teach or present, you’ll know what I mean. If you haven’t, you really should! 🙂

Cheers

Tim…

Video : Ansible Playbooks : Vault

In today’s video we demonstrate how to use Ansible Vault with Ansible Playbooks.

The video is based on this article.

You might find some useful stuff here.

The star of today’s video is Ilmar Kerm, who is very vocal about automation in the Oracle space.

Cheers

Tim…