Deploy updates to a number of Oracle Databases, concurrently

Oracle Database contains many free utilities and mechanisms to work together together with your database and knowledge. So whenever you get requested ‘tips on how to do one thing?’ it’s fairly frequent to listen to, “properly, it relies upon.”

At present I’m going to reveal tips on how to ‘kick off a script’ on 5 completely different databases, from a single occasion of our command line utility, SQLcl.

The script

That is the unit of ‘work’ you need to have executed in your group of databases. My instance is tremendous easy, substitute it together with your use case as wanted.

join -name "hr 🟢"
start
 dbms_session.sleep(60);
 dbms_output.put_line('finished');
finish;
/
disconnect

This script mainly connects to a database, takes a 1 minute nap, after which writes a message saying, it’s finished. And we shut our connection as a ultimate ‘goodbye.’

The SQLcl instructions we will probably be utilizing

• background (bg) – kicks off a background job
• jobs – reveals your duties, their standing, and lob location

You should utilize the ‘assist’ command to get the total syntax with examples.

Kicking off the roles

I’ll be doing this, interactively on the immediate, issuing the background command with the title of the script I need to run. For automation duties this is also finished as a script, which might require zero hands-on.

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

-tn is shorthand for -taskname – it makes it simpler to see what’s what whenever you use the Jobs command to work together with a particular background job.

So whereas that is operating – I’ll have lower than a minute to seize this – I can swap over to VS Code and use the SQL Developer Periods report to simply see, what’s what.

All however a type of classes belong to SQLcl, we are able to see all of them have the identical SQL_ID, which is smart, as they’re all operating the very same PL/SQL block. I can affirm this by observing the ‘Lively SQL’ panel within the report.

Which certainly mirrors the PL/SQL script we now have within the aforementioned code.

With refresh set on, I can observe the standing of the classes altering after which quietly going ‘goodbye’ as they disconnect from the database.

Checking to see what occurred

We are able to assume the whole lot labored properly, however I’d by no means assume that. Let’s verify one of many logs.

First, use the Jobs command to see what’s operating, and what’s out there for logs.

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

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

SQL> !cat /Customers/thatjeffsmith/.sqlcl/jobslogs/Stuff15.log
Related.
finished


PL/SQL process efficiently accomplished.

Disconnected from Oracle Database 23ai Free Launch 23.0.0.0.0 - Develop, Be taught, and Run for Free
Model 23.5.0.24.07

SQL> 

Word the ‘finished’ comes from the DBMS_OUTPUT little bit of our script, we’ll solely see this if SERVEROUTPUT is about to ‘on.’ Your script will need to log what it’s doing in a desk or file for ‘deeper’ logging, instrumentation, and debugging.

Concerning the database connection

My script calls this up high –

join -name "hr 🟢"

SQLcl helps named connections, and you’ve got these autoamtically should you’re already utilizing our VS Code extension.

You will get a listing of these utilizing the connmgr command.

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

Sure, SQLcl additionally helps connection folders.

Abstract

You should utilize SQLcl to run duties on a background thread, and proceed on doing ‘different issues.’ You’ll be able to have these duties be SQL scripts that execute, together with on databases aside from the present connection. Lastly, you’ll be able to monitor these duties utilizing the roles command, in addition to learn the robotically collected logs displaying their output/outcomes.

P.S. Sure this all works as properly in our VS Code Extension

Similar demo, however with operating it on certainly one of my OCI cases, on this case, my At all times Free Autonomous Database.

Right here’s it’s operating:

And after the actual fact:

VS Code’s terminal makes it straightforward to open recordsdata, like our job/job log!