My favorite top 10 new features in Oracle database 19c

Justification

This database version is out now for 2 years. Why talk about new features now?

Many of my customers recently made the upgrade to the 19c database. Usually from 12.1 or 12.2, at least one from an even older version. So I compiled a list of things that I enjoy using and now having available in a 19c environment.

Be aware, 19c is not a major new database release it is just a rebranded 12.2.0.4. However it is the final (=long term support) release of the Oracle 12.2 database family. As such it already received a stack of backported features of 21c. And some of them look very useful to me.

This is a highly subjective compilation. My time spent is 75% development, 50% DBA stuff and 25% management (yes those areas overlap) – which might explain some of the preferences.

10 Gradual Database Password Rollover for Applications (19.12)

A new profile parameter PASSWORD_ROLLOVER_TIME allows to change a database account password, without a downtime for the application that needs to use this password.

See also: https://docs.oracle.com/en/database/oracle/oracle-database/21/nfcon/gradual-database-password-rollover-for-applications-222774864.html

This parameter was originally developed for 21c and was backported in version 19.12. It can be set for a profile, but also the alter user syntax was enhanced.

Essentially it means for a certain time a user can login with either the old or with the new password. The maximum allowed time is 7 days.

Some accounts (administrative) can not use this, probably for security reasons.
ORA-28227: Gradual password rollover is not supported for administrative users.

Julian Dontcheff explains the parameter in more detail:

For security consideration check this post by Rodrigo Jorge: https://www.dbarj.com.br/en/2020/12/21c-gradual-database-password-rollover-brings-new-backdoor-opportunities/

9 Hybrid Partitioned Tables

Partitioned external tables were introduced in 12.2. Such tables use the external table driver (ORACLE_LOADER or ORACLE_DATAPUMP) to get the data from a file or even a cloud source (see also DBMS_CLOUD). And each partition can have a different file or even a different oracle directory as the source.

Now we can have tables that have external partitions and normal table partitions. They are called hybrid partitioned tables and are a special case of the external partitioned tables.

The base idea is that actively used partitions would stay in the database, but rarely used partitions can be externalised into cheaper storage systems and out of the DB.

The interesting part is that all external partitioned tables when used in queries can profit from certain partitioning operations, like partition pruning and partition wise joins.

Of cause the partitioning license is needed for this (or use 21cXE)!

Further readings:

8 MAX_IDLE_BLOCKER_TIME

Additionally to MAX_IDLE_TIME there is a new parameter that can restrict the duration of a database session: MAX_IDLE_BLOCKER_TIME. Both are initialization parameters, but also ressource plan directives.

Setting such a parameter to 0 means the session is unrestricted. Other values (for the initialization parameters) are in minutes.

MAX_IDLE_BLOCKER_TIME will limit sessions that consume ressources. This is the much better option, because connection pools from application servers usually are idle when the are not in use. Those sessions should not be touched if we set MAX_IDLE_TIME to 0 and MAX_IDLE_BLOCKER_TIME to 30 for example.

https://docs.oracle.com/en/database/oracle/oracle-database/21/nfcon/details-max_idle_blocker_time-parameter-282450835.html

7 Bitmap Based Count Distinct SQL Function

Essentially this allows to create materialized views (MV) using COUNT (DISTINCT …) over multiple dimensions. Large aggregations in DWH environments is where this technique shines.

Use case

Imagine a statement as this

select count(distinct product) unique_#_of_products,
          count(distinct customer) unique_#_of_customers
from orders
where order_month = 202108;

If this statement is slow, we can improve it using a materialized view. However such a view is very difficult to write, if we allow different levels of hierachies and filters. Like for a time dimension it could be day, month, year.

The problem is that we can not aggregate the distinct count from a lower level to a higher level. The distinct customer count for single month could be 3. If each month in this year has the same distinct customer count of 3, we still don’t know if it was the same customer each month or totally different customers. All we can deduct is that the distinct customer count for this year is at least 3 and at most 36 (=3*12).

With 19c we got several new BITMAP_* functions that combined help us to create a materialized view for such a scenario. It is a complex task.

Connor McDonald explains how the logic works: https://connor-mcdonald.com/2020/08/17/faster-distinct-operations-in-19c/

Here are direct links to the 5 new functions that are needed to implement this. Three scalar functions and two aggregate functions (those that end with _AGG)

Also see Dani Schniders take on the subject: https://danischnider.wordpress.com/2019/04/20/bitmap-based-countdistinct-functions-in-oracle-19c/

6 SQL Macros (19.7)

SQL Macros were announced for 20c.

There are two kinds of SQL macros – scalar and table macros. In 19c we only have TABLE macros (so far).

Scalar macros can be used in most clauses of a SQL statement (typically a select). The select, the where, the order by clause, table macros can only be used in the from clause.

I was quite sceptical about SQL macros, but I’m starting to see how useful they can be. Personally I think the scalar macros are more usable, but we don’t have them in 19c yet. The table macros are the fancier things of cause.

Macros are implemented useing a plsql function. This function is resolved at compile time (not at runtime!) and provides additional code, that replaces the function. We can see it as some kind of advanced text substitution.

Links

SQL Macros – Creating parameterised views

5 JSON_MERGEPATCH

JSON_MERGEPATCH is a new sql function that allows partial updates to JSON clobs.

https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/updating-json-document-json-merge-patch.html#GUID-31F88F28-3D92-489B-9CCD-BD1931B91F1F

It allows easy patching of json objects. However we can not as easily add entries to a json array for example. To do so, the whole array needs to be replaced.

The clob is copied during the mergepatch process. If performance or memory is relevant, keep that in mind.

Examples from the docs

UPDATE j_purchaseorder 
SET po_document =
  json_mergepatch(po_document, '{"Special Instructions":null}');

SELECT json_mergepatch(treat('{"Order":"0815","Special Instructions":"Handle with Care"}' as json), '{"Special Instructions":null}'
                       RETURNING CLOB PRETTY)
  FROM dual;
{
  "Order" : "0815"
}

4 Easy Connect Plus

Easy connect plus allows everything that was previousy added in a tnsnames.ora or sqlnet.ora. So no tnsnames.ora is needed anymore, even when setting some special options for the connection.

Syntax

Easy connect:


database_host[:port][/[service_name]


Easy Connect Plus:

[[protocol:]//]host1{,host2}[:port1]{,host2:port2}[/[service_name][:server_type][/instance_name]][?parameter_name=value{&parameter_name=value}]

Important to note here is a way to specify the protocol, which can be TCPS or TCP for example. Multiple servers are supported, which is very useful in case of a data guard environment. And there is a way to add parameters.

Some common parameters could be:

  • failover
  • wallet_location
  • sdu
  • retry_count

What those parameters do can be seen in the white paper about Easy Connect Plus.

Example

tcps://salesserver1:1521/sales.us.example.com?wallet_location=”/tmp/
oracle”

Further readings

White Paper from oracle

Easy connect plus in scripts against environments protected by data guard (German article) by Marco Mischke:

https://www.robotron.de/unternehmen/aktuelles/blog/easy-connect-plus-in-version-19c

3 SQL/JSON Simplifications

Dot notation for JSON inside SQL.

Example

The following example is taken direcly from LiveSQL (link below).

drop table t;
create table t (id number, col clob check (col IS JSON));
insert into t values(1, '{a:[1, "1", "cat", true, null, false, {}, ["dog","mouse"]], b:"json is fun"}');

-- The so-called JSON simplified syntax or 'dot notation' allows to use dots and square brackets to navigate into a SQL expression if it is known to be JSON.

select t.col.a from t t;

A
[1,"1","cat",true,null,false,{},["dog","mouse"]]

select t.col.b from t t;

B
json is fun

Also see treat( ... as json)

More on LiveSQL:

JSON Path expressions and simplified syntax

2 3 PDBs for Standard Edition 2 (19.5)

Multitenant for Standard Edition!

Starting from 19c all databases including Standard Edition 2 can use up to 3 PDBs without any additional license.

That means for example we can now clone a PDB from Database TEST to Database INTEGRATION without unplugging the existing PDBs (as long as the total number excluding the PDB$SEED is equal or less than three).

The parameter MAX_PDBS influences how many pdbs you are allowed to create. It should be set to 3 if you are on a standard edition and have the recent patches.

See also: https://mikedietrichde.com/2019/09/17/3-pdbs-included-with-oracle-19c-and-desupport-of-non-cdbs-with-20c/

Also I remember that the limit was later further increased to 5. But I didn’t find any documentation for that, so I can only recommend using 3 PDBs if you are on SE2.

1 LISTAGG distinct

LISTAGG is a very usefull aggregate function (also with an analytic version) mostly used in reporting environments. For example we can create a list of all employees that belong to a department.

All following code examples have been run on LiveSQL.

listagg(e.last_name,',') within group (order by e.last_name)

Since each employee is always only in one department, that is a fairly easy list to do.

How about if we want to see the list of jobs that are in each department?

listagg(j.job_title,',') within group (order by j.job_title)

Unfortunatly this does repeat all the jobs.

Listagg distinct avoids those duplications.

listagg(distinct j.job_title,',') within group (order by j.job_title)

Thanks to @oraesque who suggested this feature in the Database Ideas Space.

Try it on LiveSQL: https://livesql.oracle.com/apex/livesql/file/content_HT1O85E4BHSBWN93G1B3M8SI2.html

Honorable mentions:

Some features did not make it into the list. I still like to make a quick mental note about them, but without further explanation.

  • Polymorphic table functions (18c)
  • dynamic sequence cache (19.10 – seems a bit shaky at the moment)
  • blockchain tables
  • dbms_hprof trace output in the database (18c)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.