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…

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 : Ansible Playbooks : Files and Templates

In todayโ€™s video we demonstrate how to use handlers in Ansible Playbooks.

The video is based on the following article.

You might find some useful stuff here.

The star of todayโ€™s video is Dan Norris, who is looking to the heavens for inspiration. ๐Ÿ™‚

Cheers

Timโ€ฆ

Death of the DBA… Again…

If you’ve followed the blog for a while you will know I’ve revisited this topic several times since I first wrote about it in 2007. Here are some links if you want to go down memory lane.

I think I’ve been pretty consistent with my opinion on this subject, but I still get people misunderstanding the argument, so I’m going to try again, without revisiting the contents of all those previous articles.

What is a DBA?

This means something different in each company and each person I speak to.

In some companies it can mean a basic operations job. Install, patch, check backups and run scripts that other people send to you. All of these tasks are easily automated on-prem, and essentially don’t exist on the cloud. If this is the role you do, and this is all you do, you are going to have a bad time unless you gain some new skills. In other companies it can mean something completely different. My official title is “Lead DBA”. What do I do? Just off the top of my head:

  • DBA work with Oracle, SQL Server, MySQL.
  • Administration of middle tier stuff like WebLogic, Tomcat, NGINX etc.
  • I look after the load balancer configuration for a big chunk of the back-end business systems, including writing iRules in TCL.
  • Support for a few proprietary 3rd party apps.
  • Docker/containers.
  • APEX and ORDS. I’m the worlds worst APEX developer (see here), but I have to look after it, and support the APEX developers.
  • I don’t do much traditional development in this company, but I provide support when people have SQL and PL/SQL questions, because I’ve done that for a minute. ๐Ÿ™‚
  • I’m increasingly being drawn into automation using shell scripts, Ansible, Terraform, Liquibase etc.

I’ve already got rid of some of the operational aspects of my job. Over time I’m hoping more will go, but that mostly depends on external constraints holding me back. Even if my involvement with databases stopped completely, I can still remain busy. Am I saying my role is what a DBA should be? No. I think my position is a little more extreme than some. I’m saying DBA is a title that various people and companies use, but it doesn’t really mean anything now. It can be anything from basic operations to “Do Bloody Anything”.

When is it going to happen?

For some companies it already has. Some companies will hang on to the old ways until the bitter end. It won’t happen overnight, but if it is not happening already in your company, what you are likely to see is a gradual drop in operational tasks as they get automated. This allows either the same number of people to cover more systems, or less people to do all the current work.

If you are seeing pieces of your role disappearing, you have to do something else to add value!

But person X disagrees with you!

That’s fine. This is only my opinion and you don’t have to agree, but please check the context of what people are saying. Often the responses to my comments include mentions of performance tuning and diagnosing architectural issues. I have consistently said the “operations DBA” role would go. The job that focuses on the basic grunt work. There will be a continued need for people who can diagnose and solve performance problems, both in the databases and in the application architecture. Moving to the cloud won’t magically cure all performance issues, and some would say they will increase the number of issues. You can still deliver architecturally crap solutions on the cloud. You can still do bad database design and write bad applications. Good people will always find a home, provided they don’t stick rigidly to the past.

You also have to look at the person making the comments. If someone is a performance consultant to the stars, but identifies as a DBA, they are probably going to take these comments personally and hear me saying they are redundant, which I am not. If someone runs a DBA as a service company, they won’t like the sound of this and will go into defensive mode.

I’ve been a regular DBA for most of my working life, and I’ve watched the job evolve. You may have a different experience, and that is fine, but I speak to a lot of people, and I think my opinion on this subject tracks pretty well with what is happening out there.

You are really talking about the cloud aren’t you?

Not. Automation is the thing that is removing the need for the operational DBA and basic system admin roles. Even if your company is in cloud denial, it doesn’t mean they won’t want to take advantage of automation. The cloud makes some things easier from an automation perspective, because the cloud providers have done some of the leg-work for you, but automation existed long before the cloud…

What should I do?

When you know, please let me know so I can do it too. ๐Ÿ™‚ Seriously though, keep your mind open to new opportunities, and if you get a chance to try something new, give it a go. Nothing is ever wasted. Some people will gravitate to the data and analytics side of things. Some to development. Some to the architectural end of things. In all cases, there is a lot to learn and the less you know when you start, the harder the journey will be, so get learning whatever you like the look of…

Cheers

Tim…

Update 1: In social media comments, some people have mentioned the term “Data Engineer”. To me a data engineer needs to understand data, and requires and understanding of the development process. I’ve met some operational DBAs that can barely string together a SQL statement. These types of operational DBAs have a lot to learn before they can consider themselves a data engineer. A DBA can become a data engineer, but being a DBA does not mean you are a data engineer.

Update 2: Don’t get distracted by the name DBA. I don’t care about the name of the role. I’m talking about the function the person is performing. If they are performing basic operational tasks, whatever they are called, they need to start getting some new skills.

Video : Gradual Database Password Rollover Time

In today’s video we demonstrate gradual database password rollover time, introduced in Oracle database 21c, and backported to 19c.

The video is based on this article.

This is a small, but really useful quality of life feature!

The stars of today’s video are the offspring of Jeff Smith. It was cold, dark and miserable when I recorded the video, and this is one of my favourite clips, so I included it to bring a touch a summer! ๐Ÿ™‚

Cheers

Tim…

Video : DBMS_CLOUD : Objects and Files

In today’s video we’ll demonstrate the functionality of the DBMS_CLOUD package, with specific reference to objects in a cloud object store and files on the database server file system.

The video is based on part of this article.

You might find these useful also.

The star of today’s video is Roel Hartman, who used to do APEX, but now just runs marathons I think… ๐Ÿ™‚

Cheers

Tim…

Video : Oracle Database 21c Express Edition (XE) Installation

In today’s video we’ll demonstrate how to install Oracle Express Edition 21c on Oracle Linux 8.

The video is based on this article.

Here are some other things you might find useful.

The star of today’s video is Martin Widlake, doing his best Monty Python impression.

Cheers

Tim…

Video : Multivalue Function-Based Indexes for JSON_EXISTS in Oracle Database 21c

In today’s video we demonstrate multivalue function-based indexes for JSON_EXISTS, introduced in Oracle 21c.

The video is based on this article.

You may also find these helpful.

The star of today’s video is my sister-in-law and the queen of the database Maria Colgan. It’s a pleasure to be one of your unworthy minions… ๐Ÿ™‚

Cheers

Tim…

Video : DML Error Logging

In today’s video we demonstrate DML Error Logging, introduced in Oracle 10.2.

The video is based on a section of this article.

The star of today’s video is Oren Nakdimon, who’s chilling in his garden along with the birds. ๐Ÿ™‚

Connor McDonald has done some videos on this feature, which are worth checking out.

Cheers

Tim…

Video : DDL_LOCK_TIMEOUT : DDL With the WAIT Option

In today’s video we demonstrate the DDL_LOCK_TIMEOUT feature, introduced in 11g.

The video is based on this article.

The star of today’s video is Bertrand Drouvot, who’s sporting a particularly impressive beard here. ๐Ÿ™‚

Cheers

Tim…