Deploy updates to multiple Oracle Databases, concurrently

Oracle Database includes many free utilities and mechanisms to interact with your database and data. So when you get asked ‘how to do something?’ it’s quite common to hear, “well, it depends.”

Today I’m going to demonstrate how to ‘kick off a script’ on 5 different databases, from a single instance of our command line utility, SQLcl.

The script

This is the unit of ‘work’ you want to have executed on your group of databases. My example is super simple, substitute it with your use case as needed.

PLSQL

connect -name "hr 🟢"
begin
 dbms_session.sleep(60);
 dbms_output.put_line('done');
end;
/
disconnect

This script basically connects to a database, takes a 1 minute nap, and then writes a message saying, it’s done. And we close our connection as a final ‘goodbye.’

The SQLcl commands we will be using

background (bg) – kicks off a background task
jobs – shows your tasks, their status, and lob location

You can use the ‘help’ command to get the full syntax with examples.

Kicking off the jobs

I’ll be doing this, interactively at the prompt, issuing the background command with the name of the script I want to run. For automation tasks this could also be done as a script, which would require zero hands-on.

Bash

SQL> bg -tn Stuff10 @script.sql
Started task with id: 7
SQL> bg -tn Stuff11 @script.sql
Started task with id: 8
SQL> bg -tn Stuff13 @script.sql
Started task with id: 9
SQL> bg -tn Stuff12 @script.sql
Started task with id: 10
SQL> bg -tn Stuff14 @script.sql
Started task with id: 11
SQL> bg -tn Stuff15 @script.sql
Started task with id: 12

-tn is shorthand for -taskname – it makes it easier to see what’s what when you use the Jobs command to interact with a specific background task.

So while this is running – I’ll have less than a minute to capture this – I can switch over to VS Code and use the SQL Developer Sessions report to easily see, what’s what.

All but one of those sessions belong to SQLcl, we can see they all have the same SQL_ID, which makes sense, as they’re all running the exact same PL/SQL block. I can confirm this by observing the ‘Active SQL’ panel in the report.

Which indeed mirrors the PL/SQL script we have in the aforementioned code.

With refresh set on, I can observe the status of the sessions changing and then quietly going ‘goodbye’ as they disconnect from the database.

Checking to see what happened

We can assume everything worked well, but I would never assume that. Let’s check one of the logs.

First, use the Jobs command to see what’s running, and what’s available for logs.

Bash

SQL> jobs
 ...
 7: [ Finished ] Stuff10 (/Users/thatjeffsmith/.sqlcl/jobslogs/Stuff10.log)
 8: [ Finished ] Stuff11 (/Users/thatjeffsmith/.sqlcl/jobslogs/Stuff11.log)
 9: [ Finished ] Stuff13 (/Users/thatjeffsmith/.sqlcl/jobslogs/Stuff13.log)
10: [ Finished ] Stuff12 (/Users/thatjeffsmith/.sqlcl/jobslogs/Stuff12.log)
11: [ Finished ] Stuff14 (/Users/thatjeffsmith/.sqlcl/jobslogs/Stuff14.log)
12: [ Finished ] Stuff15 (/Users/thatjeffsmith/.sqlcl/jobslogs/Stuff15.log)

Now let’s peek into task 12, or the one named ‘Stuff15.’

Bash

SQL> !cat /Users/thatjeffsmith/.sqlcl/jobslogs/Stuff15.log
Connected.
done


PL/SQL procedure successfully completed.

Disconnected from Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.5.0.24.07

SQL>

Note the ‘done’ comes from the DBMS_OUTPUT bit of our script, we’ll only see this if SERVEROUTPUT is set to ‘on.’ Your script will want to log what it’s doing in a table or file for ‘deeper’ logging, instrumentation, and debugging.

About the database connection

My script calls this up top –

Bash

connect -name "hr 🟢"

SQLcl supports named connections, and you have these autoamtically if you’re already using our VS Code extension.

You can get a list of those using the connmgr command.

Bash

SQL> connmgr list
.
├── ADMIN
│   ├── Containers
│   ├── SYSTEM
│   │   ├── SYSTEM
│   │   └── SYSTEM @ MARIN
│   ├── SYS
│   ├── SYS Container 🫙
│   └── SYS PDB
├── Cloud
│   ├── ATP-ADMIN-🛠️ 🔴
│   └── AutonomousFunDemo🍻
├── JEFF
│   ├── JEFF @ PDB2
│   ├── JEFF HR
│   └── my name is jeff
├── 12 thousand tables ⚀⚁
├── 23cVirtualBoxHR
├── CMA
├── F1 Duality View Demo 🏎️
├── HRREST
├── OE 🏬
├── TNS example (HR)
├── access
├── expires
├── hr 🟢
└── noname

Yes, SQLcl also supports connection folders.

Summary

You can use SQLcl to run tasks on a background thread, and continue on doing ‘other things.’ You can have those tasks be SQL scripts that execute, including on databases other than the current connection. Finally, you can monitor those tasks using the jobs command, as well as read the automatically collected logs showing their output/results.