Create and terminate DB from Visual Code

We saw earlier how easy it is to start and stop autonomous databases from Visual Code. Now we’ll use ODT (Oracle Developer Tools for Visual Studio Code) to create a new, access it and then toss it.

First if you do not have a cloud environment or set up ODT for it, take a look at Oracle Tools Is Great, Start ATP With A Click to get the basic configuration up. Here we’re starting from the point where you already have at least one OCI-environment configured in ODT.

That means that in ODT you have an entry like this, if it is empty then look at the mentioned blogpost for how to configure it,

Open up your OCI connection and right click on any of the three database types – ATP, AWH, JSON – and select “Create New…”. The dialog looks like this.

Workload type lets you chose another database type than the one you clicked create new on. Then you can name it something meaningful.

Note that it defaults to “Always Free”, at least for a tenancy created as such. Nice to not end up creating one you pay for by mistake.

Then you can chose version of the database. Right now it is 19c and 21c.

CPU, Storage, auto scaling, and license type cannot be entered for always free databases.

Lastly you enter the password you want for the admin account in the database.

With those things entered you can just click Create.

For the process look in the output for Oracle Developer Tools, you’ll see something like this.

Information:4/17/2022, 8:53:22 PM: Database: blogdb - PROVISIONING
 Profile: DEFAULT
 Compartment: evilape
 Workload Type: Autonomous Transaction Processing
 Database Name: blogdb
 Display Name: blogdb
 Always Free: true
 Deployment Type: Shared Infrastructure
 Database Version: 19c
 OCPU Count: 1
 Storage(TB): 1
 Auto Scaling: false
 License Type: License Included
Information:4/17/2022, 8:54:27 PM:Database: blogdb - AVAILABLE

As you can see it took all of 63 seconds to bring up a brand new PDB. Fast enough to create it, run some tests and just toss it. Let’s set up a connection to it.

Right click on the new database on the OCI section on the right side in Oracle Developer Tools, and select Create Connection in Database Explorer.

This brings up the create connection with some things set up already, it also allows you to download the needed wallet so you don’t have to worry about that.

The first dialog only let’s you chose where the wallet will be saved and if you want it downloaded (you probably do unless you already have it). In this case, chose a path unless you want the default and allow it to be downloaded and click OK. The wallet is downloaded and unzipped into the folder you selected. Next is a dialog for the rest of the settings.

Here you’ll not want to change much. In this case you just have an admin account, so set user name to be admin and password to the one you entered when you created the database. Optionally elect to save the password and then hit create connection.

It is now up in your connections and you can klick on it top open it. Right click on it and select “Open New SQL File”, enter a SQL like “select * from all_objects;” and execute it by right clicking on it and select “Execute SQL”.

It shows that you have access and everything works. You can of course create tables and do whatever your test case requires. Now the only thing left to complete the process is to terminate the database, which means to get rid of it.

You get a question to make sure you really do want to delete the database. Select yes. It then took almost two minutes for my open PDB to become terminated.

The database is still shown under your databases. Probably as a way to remind you it was terminated. It will dispapear from there in about 12 hours.

To finish cleaning up you may want to remove the connection you created as well as the wallet.

With that you’ve spun through the whole cycle of creating a database doing some testing and getting rid of the database. All from within Visual Code. Pretty neat if you ask me.