Oracle 23ai: INTERVAL data type aggregations

 

- Overview:

• Oracle 23ai introduces the use of SUM and AVG functions with INTERVAL datatype. 
• This enhancement makes it easier to calculate totals and averages over INTERVAL values. 

In this blog, I'll demonstrate the use of SUM and AVG functions with INTERVAL datatype.

Prerequisites:

• Oracle Database 23ai Free Developer Release.

Step #1: Preparation

The example in this blog requires the following table.

drop table if exists trips purge;

create table trips (

  id          number,

  start_time  timestamp,

  end_time    timestamp,

  duration    interval day to second generated always as (end_time - start_time) virtual

);

begin

insert into trips (id, start_time, end_time) 

values (1, timestamp '2024-01-20 08:45:00.0', timestamp '2024-01-20 18:01:00.0');

insert into trips (id, start_time, end_time) 

values (2, timestamp '2024-01-22 09:00:00.0', timestamp '2024-01-22 17:00:00.0');

insert into trips (id, start_time, end_time) 

values (3, timestamp '2024-01-25 08:00:00.0', timestamp '2024-01-25 17:45:00.0');

insert into trips (id, start_time, end_time) 

values (4, timestamp '2024-01-27 07:00:00.0', timestamp '2024-01-27 16:00:00.0');

insert into trips (id, start_time, end_time) 

values (5, timestamp '2024-01-28 07:00:00.0', timestamp '2024-01-28 16:00:00.0');

insert into trips (id, start_time, end_time) 

values (6, timestamp '2024-01-29 07:00:00.0', timestamp '2024-01-29 16:00:00.0');

insert into trips (id, start_time, end_time) 

values (7, timestamp '2024-01-30 07:00:00.0', timestamp '2024-01-30 16:00:00.0');

insert into trips (id, start_time, end_time) 

values (8, timestamp '2024-01-31 07:00:00.0', timestamp '2024-01-31 16:00:00.0');

commit;

end;

/

Step #2: Testing 

1. If we use SUM or AVG functions on an INTERVAL datatype on pre-23ai Oracle database, we will get an error as shown below.

2. Oracle 23ai database allows the use of SUM and AVG functions with INTERVAL datatype.

select sum(duration),avg(duration) from trips;

  

- We can also use SUM and AVG as analytics functions with INTERVAL datatype. 

select id,start_time,end_time,duration,

sum(duration) over (order by id rows unbounded preceding) as DUR_RUN_TOTAL,

avg(duration) over (order by id rows unbounded preceding) as DUR_RUN_AVG

from trips;

Oracle 23ai: Direct Joins for UPDATE and DELETE Statements

 

- Overview:

• Oracle 23ai now allows you to use direct joins to other tables in UPDATE and DELETE statements in the FROM clause.
• These other tables can limit the rows changed or be the source of new values.
• Direct joins make it easier to write SQL to change and delete data.

In this blog, I'll test executing UPDATE and DELETE commands with direct joins using the HR schema.

Prerequisites:

• Oracle Database 23ai Free Developer Release.
• HR database schema.

Update with Direct Join

Use-Case: Increase the salaries of the Finance department by 5%. 

1. Query the tables EMPLOYEES and DEPARTMENTS and look at the rows to be updated.

SELECT e.employee_id, e.department_id, e.first_name, e.Last_name, e.salary

FROM employees e, departments d

WHERE e.department_id=d.department_id 

AND d.department_name='Finance';

 

2. Writing UPDATE in pre-23ai Oracle database using sub-query in the WHERE clause.

UPDATE employees e

SET e.salary=e.salary*1.05

WHERE e.department_id in (

SELECT department_id

FROM departments

WHERE department_name='Finance');

3. Writing UPDATE in Oracle 23ai database using direct join then query the tables.

When we run the SQL plan, we notice that the join is using the index EMP_DEPARTMENT_IX to access EMPLOYEES table. It is the same SQL plan and cost when using update with sub-query in the WHERE clause.

explain plan for 

UPDATE employees e

SET e.salary=e.salary*1.05

FROM departments d

WHERE e.department_id=d.department_id 

AND d.department_name='Finance';

UPDATE employees e

SET e.salary=e.salary*1.05

FROM departments d

WHERE e.department_id=d.department_id 

AND d.department_name='Finance';

Delete with Direct Join 

Use-Case: delete the employees of the Finance department.

 1. Query the tables EMPLOYEES and DEPARTMENTS and look at the rows to be updated.

SELECT e.employee_id, e.department_id, e.first_name, e.Last_name, e.salary

FROM employees e, departments d

WHERE e.department_id=d.department_id 

AND d.department_name='Finance';

2. Writing DELETE in pre-23ai Oracle database using sub-query in the WHERE clause.

DELETE employees e

WHERE e.department_id in (

SELECT department_id

FROM departments

WHERE department_name='Finance');

3. Writing DELETE in Oracle 23ai database using direct join then query the tables.

When we run the SQL plan, we notice that the join is using the index EMP_DEPARTMENT_IX to access EMPLOYEES table. It is the same SQL plan and cost when using update with sub-query in the WHERE clause.

DELETE employees e

FROM departments d

WHERE e.department_id=d.department_id 

AND d.department_name='Finance';

Deploy Oracle Autonomous Database Free Container Image on Linux VM

 

- Overview:

• Oracle announced the general availability of Autonomous Database free container image at Oracle 2023 Cloudworld.
• The ADB free container image comes pre-built with the following components exactly like OCI ADB serverless public cloud service: 
• Oracle Autonomous Databases (ADW or ATP).
• Oracle Application Express (Apex).
• Oracle Rest Data Service. (ORDS).
• Database Actions including SQL Developer, Performance Hub.
• MongoDB API.
• You can use the free container image to perform a local development and the ability to merge your work later in an OCI ADB service.
• Oracle Autonomous Database Free container minimum needs 4 CPUs and 8 GB memory.
• The free container image is now available on Oracle Container Github Registry.  

In this blog, I'll demonstrate the steps to deploy the ADB free container image on an Oracle Linux VM running from Oracle VM VirtualBox on my laptop. 

 

Prerequisites:

• Oracle VM VirtualBox installed.
• Oracle Linux 8 VM with internet access running from Oracle VM VirtualBox.

Step #1: Install and Start Docker on Linux VM

As root user 

1. Install yum-utils package using command:

    dnf install -y dnf-utils zip unzip  

2. Enable all required repositories using command:

    dnf config-manager --add-repo=https://download.docker.com/linux/centos/docker-ce.repo

3. Install Docker using commands:

    dnf remove -y runc

    dnf install -y docker-ce --nobest

4. Enable and start Docker service using commands:

     systemctl start docker.service

     systemctl status docker.service

5. Confirm Docker version using command:

     docker version

Step #2: Download and Run ADB Free Container Image

As non root user with sudo privilege.

1. Pull the image from the repository and start a container to run ADB free container image using command:

sudo docker run -d \

-p 1521:1522 \

-p 1522:1522 \

-p 8443:8443 \

-p 27017:27017 \

--hostname <Your_Host_name Or IP Address> \

--cap-add SYS_ADMIN \ 

--device /dev/fuse \

--name <Your Docket container name> \

container-registry.oracle.com/database/adb-free:latest

Where:

- Ports:

Port	Description
1521	TLS
1522	mTLS
8443	HTTPS port for ORDS / APEX and Database Actions
27017	Mongo API ( MY_ATP )

- hostname: is the Fully Qualified Domain Name (FQDN) of your host.

- For OFS mount, container should start with SYS_ADMIN capability. 

- Also, virtual device /dev/fuse should be accessible.

2. Check new Docker image using command:

     sudo docker image ls     

3. Check the status of the ADB container using command:

    sudo docker container ls

4. Change the preinstalled default expired password for the ADMIN user for MY_ADW and MY_ATP instances.

- For ATP instance

   sudo docker exec <Container_ID> /u01/scripts/change_expired_password.sh MY_ATP admin Welcome_MY_ATP_1234 <New Password>

- For ADW instance:

   sudo docker exec <Container_ID> /u01/scripts/change_expired_password.sh MY_ADW admin Welcome_MY_ADW_1234 <New Password>

Step #3: Sign-in to Web Database Actions

- APEX

https://<Host_Name>:8443/ords/my_atp/

https://<Host_Name>:8443/ords/my_adw/

- SQL Developer Web 

https://<Host_Name>:8443/ords/my_atp/sql-developer

https://<Host_Name>:8443/ords/my_adw/sql-developer

Sign-in with admin user and password previously reset in step #2.

Stop/Start Oracle Database Cloud Service using OCI CLI

 

- Overview:

• Oracle Database Cloud Service (DBCS) supports stop billing for virtual machine databases. To take the advantage of this capability, you need to stop DB system's node (DB system virtual machine). 
• Stopping a node stops billing for all OCPUs associated with that node. Billing resumes if you restart the node. However, the billing of other DBCS resources (like NVME disks) will continue.
•  DB system nodes are stopped individually. For multi-node RAC DB systems, you may need to act on only one node.
• You can stop/start DBCS node using
• Oracle OCI console.
• Oracle OCI CLI command line tool.
• REST APIs.

In this blog, I'll demonstrate the commands to stop/start DBCS node using OCI CLI command line. 

If you have a requirement to run non-production DBCS on working hours only, then you can schedule stop/start commands (for example from CRONTAB).

Prerequisites:

• An Oracle cloud fee trial or paid account.
• Installed and configured OCI CLI client. I have OCI client installed and configured on Oracle OCI compute instance (a Linux virtual machine) under OPC account.

Stop DBCS Node

- OCI CLI command: oci db node stop --db-node-id <DBCS-Node-OCID>

- The JSON-formatted command's response will show "lifecycle-state": "STOPPING".

- You can get DBCS Node OCID from OCI console.

   1. Open the navigation menu. Select Oracle Database, then select Oracle Base Database.

   2. Select your Compartment. A list of DB systems is displayed.

   3. In the list of DB systems, find the DB system you want to stop, and then click its name to display  

       details about it.

   4. In the list of nodes, click the Actions menu for a node and click "copy OCID" action.    

- Example

Get the Status of DBCS Node

- OCI CLI command: oci db node get --db-node-id <DBCS-Node-OCID>

- The JSON-formatted command's response will show "lifecycle-state".

   - STOPPING: DB node is stopping.

   - STOPPED: DB node is stopped.

   - STARTING: DB node is starting.

   - AVAILABLE: DB node is running.

- Example

Start DBCS Node

- OCI CLI command: oci db node start --db-node-id <DBCS-Node-OCID>

- The JSON-formatted command's response will show "lifecycle-state": "STARTING".

- Example:

ExaCC: Create a Custom Database Software Image

 

- Overview:

• ExaCC users patch and provision their Oracle Database Homes using standard Oracle published images. 
• Oracle makes the major Oracle database versions with the last 4 release updates available on control plane servers for download.
• Custom database software images provide
• Apply a standardized custom Database Software Image across multiple Database Homes for specific application needs.
• Move on-premise databases running custom one-off software updates to ExaCC.
• Build custom images within ExaCC service without special entitlements required to download patches from MOS.

In this blog, I'll demonstrate the steps to create a custom database software image using OCI console then use the new image to create a database home.

Step #1: Create A  Custom Database Software Image

1.  Sign in to your OCI tenancy where your Exadata Database Service on Cloud @ Customer system is deployed.

2. Navigate to "Oracle Database" > "Oracle Exadata Database Service on Cloud@Customer".

3. Under "Resources" section, select "Database Software Images" then click "Create Database Software Image" button.

4. In "Create Database Software Image" screen, provide the required information then click "Create Database Software Image" button.

     - Display Name: Image display name.

     - Choose the right compartment.

     - Choose major database version.

     - Choose a patch set update, proactive bundle patch, or release update.

     - Enter one-off patch numbers (optional).

Once create database software image successfully completes, image state will be "Available". 

Step #2: Create A Database Home Using New Image

1. Navigate to "Oracle Database" > "Oracle Exadata Database Service on Cloud@Customer".

2. Click on your VM cluster name.

3. Under "Resources" section, select "Database Homes" then click "Create Database Home" button.

4. In "Create Database Home" screen, enter database home, click "Change Database Image" button to select database home image type from either "Oracle Provided Database Software Images" or "Custom Database Software Images".

5. In "Select a Database Software Image" screen

    - Image Type: select "Custom Database Software Images".

    - Choose a compartment: the compartment where you created the customized image.

    - Select image row from the list of available customized images.

6. Click "Create Database Home" button.

Once create database home successfully completes, Oracle database home state will be "Available". 

7. Connect to any of the VM cluster nodes and run "opatch lspatches" to confirm installed patches.