Archive for the ‘New features’ Category

SQL Domains in Oracle Database 23c: an example

In DBA, New features, SQL on April 11, 2023 at 13:00

In Finland, vehicle registration plates consist of three letters and three numbers separated with a dash (e.g. ORA-600), there is actually a real car (a Saab) with this license plate. For 1000€ one can buy a vanity plate with 2-3 letters and 1-3 numbers.

Often, in application forms, etc., you are asked to type your 6 character license plate and most of us insert it without the dash. How about if you want the application to be automatically able to show it (when needed) with the dash (even if the field is varchar2(6))? Here is an example showing a new feature of Oracle Database 23c called SQL Domains.

A SQL domain is a dictionary object that belongs to a schema and encapsulates a set of optional properties and constraints for common values, such as credit card numbers, registration plates or email addresses. After you define a SQL domain, you can define table columns to be associated with that domain, thereby explicitly applying the domain’s optional properties and constraints to those columns.

With SQL domains, you can define how you intend to use data centrally. They make it easier to ensure you handle values consistently across applications and improve data quality.

SQL Domains allow users to declare the intended usage for columns. They are data dictionary objects so that abstract domain specific knowledge can be easily reused.

We will use the CREATE DOMAIN command to create a SQL domain where the domain must specify a built-in Oracle datatype (think of annotation as an extended comment):

 SQL> create domain reg_plate as varchar2(6)
   constraint check (length(reg_plate) >=3)
   display (substr(reg_plate,1,3)||'-'||substr(reg_plate,4,3))
  annotations (title 'regplateformat');

 Domain created.

After we defined our SQL domain reg_plate we will define table column(s) to be associated with that domain. In our case this is the registration_plate column. Thus, we declare via the SQL Domain the intended usage for the column(s).

 SQL> create table car_owners
  ( id number(5),
    name varchar2(50),
    car_brand varchar2(20),
    registration_plate varchar2(6) DOMAIN reg_plate
 annotations (display 'car_owners_table');  

Now, have a look the the type of the column registration_plate:

 SQL> desc car_owners

  Name                   Null?    Type
 ---------------------- -------- ----------------------------
  ID                             NUMBER(5)
  NAME                           VARCHAR2(50)
  CAR_BRAND                      VARCHAR2(20)

Let us insert a row into the table so that the license plate will be without the dash:

 SQL> insert into car_owners values (1,'JULIAN','LEXUS','ORA600');

 1 row created.

 SQL> commit work;

 Commit complete.

With the new function DOMAIN_DISPLAY we can display the property meaning we can force the dash to be used in the output:

 SQL> select DOMAIN_DISPLAY(registration_plate) from car_owners;


Dropping SQL domains is almost like dropping tablespaces, you need first to drop the objects associated with the SQL domain:

 SQL> drop domain reg_plate;
 drop domain reg_plate
 ERROR at line 1:
 ORA-11502: The domain to be dropped has dependent objects.

You can drop though the domain using the FORCE mode, similar to drop tablespace including contents and datafiles.

 SQL> drop table car_owners purge;

 Table dropped.

 SQL> drop domain reg_plate;

 Domain dropped.

The new dictionary views associated with SQL domains are USER_DOMAIN_COLS and USER_DOMAINS. Of course you have the corresponding DBA_ and ALL_ views. USER_ANNOTATIONS_USAGE provides usage information about the annotations owned by the current user. Tim Hall has just published a blog post on annotations, have a look. He said it very well: we can think of annotations as an extension of database comments. I would recommend reading (at least) the end of his blog post on SQL Domains.

List of the Oracle Database 23c New Features

In Databases, DBA, New features, Oracle database on November 1, 2022 at 10:28

On October 18th 2022, Oracle announced the new long term release of 23c Beta.

Here is a compiled list of the new features I am aware of:

OLTP and Core DB:

Accelerate SecureFiles LOB Write Performance
Automatic SecureFiles Shrink
Automatic Transaction Abort
Escrow Column Concurrency Control
Fast Ingest (Memoptimize for Write) Enhancements
Increased Column Limit to 4k
Managing Flashback Database Logs Outside the Fast Recovery Area
Remove One-Touch Restrictions after Parallel DML
Annotations – Define Metadata for Database Objects
SELECT Without the FROM Clause
Usage of Column Alias in GROUP BY and HAVING
Table Value Constructor – Group Multiple Rows of Data in a Single DML or SELECT statement
Better Error Messages to Explain why a Statement Failed to Execute
New Developer Role: dbms_developer_admin.grant_privs(‘JULIAN’);
Schema Level Privileges
RUR’s are transitioning to MRPs (available on Linux x86-64)

Application Development:

Aggregation over INTERVAL Data Types
Asynchronous Programming
Blockchain Table Enhancements
Direct Joins for UPDATE and DELETE Statements
GROUP BY Column Alias or Position
Introduction to Javascript Modules and MLE Environments MLE – Module Calls
New Database Role for Application Developers
OJVM Web Services Callout Enhancement
OJVM Allow HTTP and TCP Access While Disabling Other OS Calls
Oracle Text Indexes with Automatic Maintenance
Sagas for Microservices
SQL Domains
SQL Support for Boolean Datatype
SQL UPDATE RETURN Clause Enhancements
Table Value Constructor
Transparent Application Continuity
Transportable Binary XML
Ubiquitous Search With DBMS_SEARCH Packages
Unicode IVS (Ideographic Variation Sequence) Support


Improve Performance and Disk Utilization for Hybrid Columnar Compression
Index-Organized Tables (IOTs) Advanced Low Compression

Data Guard:

Per-PDB Data Guard Integration Enhancements

Event Processing:

Advanced Queuing and Transactional Event Queues Enhancements
OKafka (Oracle’s Kafka implementation)
Prometheus/Grafana Observability for Oracle Database


Automatic In-Memory enhancements for improving column store performance


JDBC Enhancements to Transparent Application Continuity
JDBC Support for Native BOOLEAN Datatype
JDBC Support for OAuth2.0 for DB Authentication and Azure AD Integration
JDBC Support for Radius Enhancements (Challenge Response Mode a.k.a. Two Factor Authentication)
JDBC Support for Self-Driven Diagnosability
JDBC-Thin support for longer passwords
UCP Asynchronous Extension


JSON-Relational Duality View


Local Rolling Patching
Oracle RAC on Kubernetes
Sequence Optimizations in Oracle RAC
Simplified Database Deployment
Single-Server Rolling Patching
Smart Connection Rebalance


Ability to Audit Object Actions at the Column Level for Tables and Views
Enhancements to RADIUS Configuration
Increased Oracle Database Password Length: 1024 Byte Password
Schema Privileges to Simplify Access Control
TLS 1.3


JDBC Support for Split Partition Set and Directory based Sharding
New Directory-Based Sharding Method
RAFT Replication
UCP Support for XA Transactions with Oracle Database Sharding

Spatial and Graph:

Native Representation of Graphs in Oracle Database
Spatial: 3D Models and Analytics
Spatial: Spatial Studio UI Support for Point Cloud Features
Support for the ISO/IEC SQL Property Graph Queries (SQL/PGQ) Standard
Use JSON Collections as a Graph Data Source

And here are few links that give you additional information on what will be coming with the new release:

Oracle Database 23c: New feature highlights by Lucas Jellema

Oracle 23c: New features by René Nyffenegger

23c Twitter Summary by Philipp Salvisberg

P.S. Just found out that road cyclists are moving away from 23C and that there are products called Merkur 23C Safety Razor and Glock Model 23C.

2022: What’s new in the database world?

In Databases, DB2 database, DBA, New features, Oracle database, PostgreSQL, SQL Server on May 23, 2022 at 07:55

The big picture in the global database market is changing rapidly. It is an $80B market right now – the largest software market and growing in double digits year over year.

Gartner has recently published the software market revenue numbers for 2021.

In 2021, the revenue for managed cloud services is more than $39B – which means about half of al global DBMS revenue. Have a look at the drastic change since 2017:

Microsoft and AWS have right now about half of the global database market while Oracle have slipped down to having only about one fifth of the overall share. Google have entered for the first time the top 5 while IBM have just lost there 4th position. SAP are out of the top 5.

In terms of database popularity, Oracle are still #1 based on the DB engines rankings:

So, what is new and causing these turbulances?

Google have just announced AlloyDB for PostgreSQL. Compared with standard PostgreSQL, according to Google’s own performance tests, AlloyDB was more than four times faster for transactional workloads, and up to 100 times faster for analytical queries. AlloyDB was also two times faster for transactional workloads than Amazon’s comparable service. This makes AlloyDB a powerful new modernization option for transitioning off of legacy databases. Here are the other offerings from Google Cloud:

The latest from AWS is predominantly around RDS and Redshift (a database based on PostgreSQL). Amazon Redshift RA3 instances are now available in the Asia Pacific (Osaka), Europe (Milan), Middle East (Bahrain) and Africa (Cape Town) regions. Amazon Redshift now offers new enhancements for Audit Logging, which enables faster delivery of logs for analysis by minimizing latency while also adding Amazon CloudWatch as a new log destination. With the latest release, customers can choose to stream audit logs directly to Amazon CloudWatch, which enables customers to perform real-time monitoring. Amazon Redshift now also provides native integration with Microsoft Azure Active Directory (AD), which customers can use for authentication and authorization with tools like Microsoft Power BI. 

Amazon Relational Database Service (Amazon RDS) Performance Insights now makes it easier for you to see the database performance metrics for the exact timeframe you want to analyze, by choosing a custom time window within your retention period. Previously, you could only see metrics in Performance Insights by choosing relative time intervals such as the past 1 hour, the past 24 hours, etc. Amazon RDS for PostgreSQL, MariaDB and MySQL now supports M6i and R6i instances with new instance sizes up to 128 vCPUs and 1,024 GiB RAM. Also, Amazon Aurora Serverless v2 is now generally available.

The latest from Azure is around Change data capture: CDC lets you track all the changes that occur on a database. Though this feature has been available for SQL Server for quite some time, using it with Azure SQL Database is now generally available. When creating a Hyperscale database, you can choose your preferred storage type: read-access geo-redundant storage (RA-GRS), zone-redundant storage (ZRS), or locally redundant storage (LRS) Azure standard storage. The selected storage redundancy option will be used for the lifetime of the database for both data storage redundancy and backup storage redundancy. It is now possible to configure your Azure SQL Database to allow authentication only from Azure Active Directory.

IBM i 7.5, previously know as AS/400, is coming with some “interesting” features. There is a new BOOLEAN data type, the maximum size of a binary radix index is extended, up to 16 TB. Used t be 1.7TB. I do not know many DBAs who enjoy dealing with double digit TB indexes. The Db2 for i SQL Query Engine (SQE) provides a Query Supervisor which enables real-time monitoring of resource consumption by SQL and native queries. There is now a RESTRICT ON DROP attribute that can be added or removed using the ALTER TABLE (SQL) statement (ALTER TABLE TABLE_NAME ADD RESTRICT ON DROP;). When RESTRICT ON DROP is added, nobody will be allowed to delete or drop that file. Even users with *ALLOBJ user special authority will not be allowed to delete the file.

The only change I am aware of in Oracle Database Release 21c, Version 21.5 are the new AE Analytic Views. AE stands for “All Edition”. Each new AE analytic view corresponds to an existing non-AE analytic view. AE views have the same columns as their non-AE counterparts, plus a column that displays the name of the application edition where the editioned object is defined. For the complete list of changes in Oracle 21, check this link.

Oracle Database 21c Upgrade: good to know

In Databases, DBA, New features on April 25, 2022 at 13:57

The latest release of the Oracle database 21.3 can be downloaded from here. 21c is available for Linux, HP-UX and Windows. By using Oracle Fleet Patching & Provisioning, you can perform a direct upgrade to Oracle database release 21c from the following releases:

Oracle Fleet Patching and Provisioning is a software lifecycle management method for provisioning and maintaining Oracle homes. Configuring Oracle Fleet Patching and Provisioning (Oracle FPP) involves creating an Oracle Fleet Patching and Provisioning Server, adding gold images to the server, and creating working copies of gold images to provision software.

The Oracle Grid Infrastructure on which the database to be upgrade is running must be at least of the same release or later than the database release to which you will be upgrading.

The source Oracle home to be upgraded can be either a managed working copy, i.e., an Oracle home provisioned using Fleet Patching and Provisioning, or an unmanaged home, i.e., an Oracle home not provisioned using Fleet Patching and Provisioning. If you are upgrading an unmanaged Oracle home, you have to provide the complete path of the database for upgrade.

Otherwise, without Oracle FPP, you can upgrade directly from, any 18c or any 19c.

Here is what you need to know about compatibility:

  • Before upgrading to Oracle Database 21c, you must set the COMPATIBLE initialization parameter to at least 12.2.0 (I am unsure if 11.2.0 is ok as well)
  • In Oracle Database 21c, when the COMPATIBLE initialization parameter is not set in your parameter file, the COMPATIBLE parameter value defaults to 21.0.0
  • Installing earlier releases of Oracle Database on the same computer that is running Oracle Database 21c can cause issues with client connections

Starting with Oracle Database 21c, non-CDB Oracle Database upgrades to non-CDB architecture are de-supported:

Option 1: Convert the non-CDB to a PDB before upgrade: with this option, you plug in the non-CDB Oracle Database release to the same release CDB. (For example, plug in a non-CDB Oracle Database Release 19c into an Oracle Database 19c release CDB). Finish converting the non-CDB Oracle Database to a PDB. Then, upgrade the entire CDB, with its PDBs, to Oracle Database 21c

Option 2: Plug in the non-CDB, upgrade, and finish converting the non-CDB to a PDB after upgrade: with this option, you plug in a non-CDB Oracle Database release to an OracleDatabase 21c CDB. Upgrade the plugged-in non-CDB Oracle Database to Oracle Database 21c. Then, finish converting the non-CDB Oracle Database to a PDB.

Here is what you need to know about the DBUA and the Oracle home:

  • Starting with Oracle Database 21c, Database Upgrade Assistant (DBUA) is replaced by the AutoUpgrade utility
  • Starting with Oracle Database 21c, the default network administration directory changes from the previous default in the local Oracle home, Oracle_home/network (for example, /u01/app/oracle/product/19.1.0/dbhome_1/network), to a new location
  • The new default location is the shared Oracle Base Home, in the path ORACLE_BASE/ homes/HOME_NAME/network/admin
  • Starting with Oracle Database 21c, an Oracle Database installation configures all Oracle Database homes in read-only mode by default

Note last what is being changed about security and parameters:

  • Starting with Oracle Database 21c, the data types DBMS_CRYPTO_TOOLKIT_TYPES and package DBMS_CRYPTO_TOOLKIT are desupported
  • Desupport of IGNORECASE parameter for passwords
    Starting in Oracle Database 21c, the IGNORECASE parameter for the orapwd file is desupported and all newly created password files are case-sensitive
    The DISABLE_DIRECTORY_LINK_CHECK parameter is desupported, with no replacement

Running JavaScript from within the Oracle Database using DBMS_MLE

In DBA, New features, PL/SQL on March 8, 2022 at 08:39

JavaScript is by far the most used language according to Github’s Octoverse Report.

As of 2021 JavaScript is the most commonly used programming language among software developers around the world, with nearly 65 percent of respondents stating that they used JavaScript. HTML/CSS, Python, SQL, and Java rounded out the top five most widely used programming languages around the world.

Did you know that you can run JavaScript code from right inside the Oracle Database?

By using the new 21c PL/SQL multilingual engine package called DBMS_MLE, we can now run JavaScrips from within the Oracle database.

The new package has 18 procedures and 1 function. So, it might take some time to understand what they do and how they should be used.

As of today, it is not possible to create stored JavaScript procedures in the database. But what we can do for now, is store them in a CLOB column in a table and create an executable PL/SQL procedure which will select the CLOB, i.e., the JavaScript procedure, and execute it via DBMS_MLE.EVAL

We cannot only run JavaScript code inside the Oracle Database but also exchange data seamlessly between PL/SQL and JavaScript. The JavaScript code itself can execute PL/SQL and SQL through built-in JavaScript modules. JavaScript data types are automatically mapped to Oracle Database data types and vice versa.

Well, here is a simple example of how to run JavaScript from the database:

The user of DBMS_MLE must have EXECUTE DYNAMIC MLE and EXECUTE ON JAVASCRIPT otherwise you will get again ORA-01031.

For further and more detailed examples, check Lucas Jellema‘s and Stefan Dobre‘s articles.

One might wonder, why run JavaScript code from within Oracle?

Actually, there are several reason why we would want to run JavaScript from within the Oracle database.

Sean Stacey outlined 3 reasons in his blog post How to Run JavaScript In Oracle Database 21c:

  1. Use JavaScript with your APEX applications: Stefan Dobre wrote an excellent blog post entitled JavaScript as a Server-Side Language in Oracle APEX 20.2
  2. Use your existing JavaScript programs and run them directly against your Oracle database without having to worry about re-writing the logic in PL/SQL. There are about 100K reusable JS libraries that can be leveraged.
  3. Not to use the Oracle database simply as a bag of tables with data. There is no need to move the data out of the database to run your JavaScript code against it.

There are several other reasons as well. There are more software developers who are and interested in writing JS code compared to PL/SQL. R and Java are already part of the Oracle database, so it is natural that JavaScript is being embedded as well. Naturally, most likely Python, Ruby, etc. are also in the pipeline.

As a side note (on the humorous side), here is a Twitter screenshot for the ones watching on Nextflix “Emily in Paris” 🙂

Two important additional resources:

Oracle Database Multilingual Engine (MLE): GraalVM in the Database

Executing JavaScript from PL/SQL in Oracle Database 21c with Multi Language

Oracle JavaScript Extension Toolkit (Oracle JET)


Licensing Types of the Oracle Database

In Cloud, Database options, Databases, DBA, New features, Oracle database on May 16, 2021 at 13:18

After being asked on daily basis all kinds of questions on Oracle Database Licensing, as time goes by, you sort of understand it. Sort of, because the Oracle Database Licensing Guide is 602 pages long and gets often updated. The latest one is from April 2021 – now it is mid-May.

Moreover, you have perhaps seen all Oracle certifications but if you search for one on licensing you will find what I did – there isn’t one.

What I am trying to do now, is to summarize Database Licensing in a short blog post – this might be helpful for many to at least understand the concept.

There are 3 types of licenses for the Oracle Database: Packs, Options and Features and 9 Oracle Database Offerings: Standard Edition 2, Enterprise Edition, Oracle Database Appliance, Exadata, Exadata Cloud Service and Cloud@Customer, Database Cloud Service Standard Edition, Database Cloud Service Enterprise Edition, Database Cloud Service Enterprise Edition – High Performance and Database Cloud Service Enterprise Edition – Extreme Performance (you can see their abbreviations in the table below).

  1. Packs: there are 5 different packs for the Oracle Database:

2. Options: there are 15 database options for the Oracle Database:

  • Oracle Active Data Guard
  • Oracle Advanced Compression
  • Oracle Advanced Security
  • Oracle Database In-Memory
  • Oracle Database Vault
  • Oracle Label Security
  • Oracle Machine Learning
  • Oracle Multitenant
  • Oracle On-Line Analytical Processing (OLAP)
  • Oracle Partitioning
  • Oracle RAC One Node
  • Oracle Real Application Clusters (Oracle RAC)
  • Oracle Real Application Testing
  • Oracle Spatial and Graph
  • Oracle TimesTen Application-Tier Database Cache

Here are the ones related to Consolidation, HA, Managability and Performance:

3. Features: there are 131 features that can be licensed with the Oracle Database out of which 105 are for EE and 123 are for Exadata. As you can see, there are 3 features available for Exadata, ExaCS and ExaC@C falling under the functional category of Autonomous:

If you would like to drill down in detail, use the Database Feature and Licensing tool which is available online without the need to register or have an Oracle account.

Moreover, the Oracle Enterprise Manager Licensing Manual is 366 pages, so there is more to read if you are done with the Database Licensing Manual.

You might think that is way too much for me, and perhaps it is, but the situation is very similar with other database vendors. Let us look at AWS and GCP for instance:

AWS have more than 10 database offering:

Amazon Aurora
Amazon RDS
Amazon Redshift
Amazon DynamoDB
Amazon ElastiCache
Amazon DocumentDB (with MongoDB compatibility)
Amazon Keyspaces (for Apache Cassandra)
Amazon Neptune
Amazon Timestream
Amazon Quantum Ledger Database (QLDB)
AWS Database Migration Service (DMS)

GCP have also more than 10 database offerings:

Relational: Bare Metal Solution for Oracle workloads
Cloud SQL: Managed MySQL, PostgreSQL and SQL Server
Cloud Spanner and BigQuery
Key value: Cloud Bigtable
Document: Firestore and Firebase Realtime Database
In-memory: Memorystore
NoSQL: MongoDB Atlas and managed offerings from open source partner network including MongoDB, Datastax, Redis Labs, and Neo4j

And, after all, Azure are not much behind:

Azure SQL Database
Azure SQL Managed Instance
SQL Server on Virtual Machines
Azure Database for PostgreSQL
Azure Database for MySQL
Azure Database for MariaDB
Azure Cosmos DB
Azure Cache for Redis
Azure Database Migration Service
Azure Managed Instance for Apache Cassandra

After all, being expert in database licensing in a skill of its own!

Automatic Materialized Views in Oracle Database 21c

In Databases, DBA, New features, Oracle database on February 5, 2021 at 15:51

“It’s supposed to be automatic, but actually you have to push this button.” ― John Brunner

With Oracle Database 21c, there is no button for Automatic Materialized Views – it is fully automatic.

DBA_MVIEWS has a new column call AUTO – that is how a DBA can distinguish the auto MVs from the manual ones. The auto naming convention is something like AUTO_MV$$_H3KBHG7DAH6T5. That is all start with AUTO_MV$$.

The Oracle database automatically collects workload statistics, SQL statements and query execution statistics. Oracle also maintains and purges the history of the workload. Automatic materialized views use workload information provided by the Object Activity Tracking System (OATS) as part of the automated decision-making processes.

All preconfigured / additionally configured parameters can be viewed from DBA_AUTO_MV_CONFIG:

Here is how automatic materialized views work:

– The database automatically detects and collects workload query execution statistics including buffer-gets, database time, estimated cost, and other statistics: DBA_AUTO_MV_ANALYSIS_EXECUTIONS displays information about analysis and tuning executions, including concurrency, degree of parallelism (DOP) requested by the user and actual DOP upon execution finish, status, associated advisor, and informational or error message.

– Oracle creates candidate materialized views hidden from the database workload and verifies that they will deliver the projected performance benefit by test executing a sample of workload queries in the background: DBA_AUTO_MV_ANALYSIS_RECOMMENDATIONS
Displays recommendations associated with automatic materialized views

– There are provided reports with detailed performance test results and which materialized views have been implemented: DBA_AUTO_MV_ANALYSIS_REPORT reports on analyses and recommendations, including task and execution names, sequence number of the journal entry, and message entry in the journal

– Automatic materialized view refresh is also automatic: DBA_AUTO_MV_REFRESH_HISTORY displays the owner name, view name, date, start and end time, elapsed time, status, and error number (if an error occurred) for each automatic materialized view refresh

Automatic MVs are off by default. As DBAs, we can use the CONFIGURE procedure of the DBMS_AUTO_MV package to configure automatic materialized views creation in the database. The AUTO_MV_MODE parameter enables (IMPLEMENT) or disables (OFF) automatic materialized views, or engages report-only mode (REPORT ONLY). The AUTO_MV_MAINT_TASK parameter activates or deactivates the task performing the maintenance (refreshes, validations, and clean up).

Let us enable it all:

Oracle Database 21c includes data dictionary views that display information about automatic materialized views as well as OATS (Object Activity Tracking System). DBAs can use the DBMS_ACTIVITY.CONFIGURE procedure to control the three OATS parameters within a specific database.

ACTIVITY_INTERVAL defines the interval between snapshots:

exec dbms_activity.configure('ACTIVITY_INTERVAL_MINUTES','30');

ACTIVITY_RETENTION_DAYS defines how long snapshots are saved:

exec dbms_activity.configure('ACTIVITY_RETENTION_DAYS','60');

ACTIVITY_SPACE_PERCENT sets how much of available space is reserved for snapshots:

exec dbms_activity.configure('ACTIVITY_SPACE_PERCENT','10');

You mostly likely will get though the following error, even alter system set “_exadata_feature_on”=true scope=spfile; and restart:

ERROR at line 1:
ORA-40216: feature not supported
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_ACTIVITY", line 278
ORA-06512: at "SYS.DBMS_ACTIVITY", line 314
ORA-06512: at line 1

The feature is only available on Exadata and ExaCs.

Here are some additional details:

– Automatic materialized views support partitioned and non-partitioned base tables
– Incremental materialized view refresh is supported
– If partitioned base tables use either range, list, or composite partitioning, then they are eligible for Partition Change Tracking (PCT) view refresh
– If there is performance advantage, the automatic materialized view recommendations will include a partitioned automatic materialized view based on the partitioning of the base table of the materialized view and the partitioning type supported is auto-list partitioning, which will mirror the partitioning of the fact table
– The automatic materialized view maintenance module decides the type of refresh that is the most beneficial at the time of refresh, and will decide during run time whether to switch from incremental refresh to full refresh
– DBAs can drop automatic materialized views using the dbms_auto_mv.drop_auto_mv procedure

5 new core DBA features in Oracle Database 21c

In DBA, New features, Oracle database, Oracle internals, PL/SQL on January 8, 2021 at 08:14

If wondering what new can come after so many releases of the Oracle Database – well, here is something for system DBAs.

Here are 5 new features related to Database Vault, Syslog, the new SYS_AUTO_STS_MODULE, and the 2 new DBA packages SYS.DBMS_FLASHBACK_ARCHIVE_MIGRATE & SYS.DBMS_ACTIVITY.

1. No need any longer to disable Oracle Database Vault in every container before the upgrade! We only need to grant the DV_PATCH_ADMIN role to SYS commonly before performing the upgrade. After the upgrade is complete the Database Vault controls work as before. Then we can revoke the DV_PATCH_ADMIN role from SYS. Alternatively, we can explicitly disable Oracle Database Vault in all containers before the upgrade and then after the upgrade explicitly enable Oracle Database Vault in all the containers.

2. A new concept in the Oracle Database is the Object Activity Tracking System.

Object Activity Tracking System (OATS) tracks the usage of various types of database objects. Usage includes operations such as access data manipulation or refresh.

The DBMS_ACTIVITY PL/SQL package contains functions and procedures for configuring Object Activity Tracking System (OATS) information collection and management. Data collected by OATS is used in analyses performed by automatic materialized views.

DBAs can use the DBMS_ACTIVITY.CONFIGURE procedure to control three OATS parameters within a specific database.

ACTIVITY_INTERVAL defines the interval between snapshots:

exec dbms_activity.configure('ACTIVITY_INTERVAL_MINUTES',30);

ACTIVITY_RETENTION_DAYS defines how long snapshots are saved:

exec dbms_activity.configure('ACTIVITY_RETENTION_DAYS',60);

ACTIVITY_SPACE_PERCENT sets how much of available space is reserved for snapshots:

exec dbms_activity.configure('ACTIVITY_SPACE_PERCENT',10);

And use these tables to monitor the activity:


3. SYSLOG destination for common Unified Audit Policies:

Certain predefined columns of unified audit records from common unified audit policies can be written to the UNIX SYSLOG destination.

To enable this new feature we should set UNIFIED_AUDIT_COMMON_SYSTEMLOG which is a new CDB level init.ora parameter. This enhancement enables all audit records from common unified audit policies to be consolidated into a single destination. This feature is available only on UNIX platforms not Windows (of course).

The new parameter has no default, we should set both the facility_clause and the priority_clause values.

Only a subset of unified audit record fields are written to ensure that the audit record entries do not exceed the maximum allowed size for a SYSLOG entry (typically 1024 bytes).

4. SYS_AUTO_STS_MODULE is the new module for the Auto SQL Tuning Sets. The feature exists since Oracle 19.7 so technically not really a new 21c feature although listed as such. Check Automatic SQL Tuning Sets (ASTS) 19c RU 19.7 Onwards (Doc ID 2686869.1)

If you try searching Google or MOS for SYS_AUTO_STS_MODULE, then most likely you will get similar to what I got from my search:

Clearly, Franck Pachot has already met with the auto SQL tuning set module but that is expected behavior – he is one of the pioneers in new features and database research.

First, what is the Automatic SQL Tuning Set? In short, auto STS is an automatic repository for historic SQL performance metrics and execution plans. Oracle is now having the module handling all that automatically.

Recently, Doc ID 2733254.1 shows after a search for the SYS_AUTO_STS_MODULE but the note is about a hang when creating text indexes although SYS_AUTO_STS_MODULE pops up 3 times under SQL ordered by CPU Time.

Out of curiosity, you may run this SQL also against your 21c database and observe the actions the module has been taking:

select ACTION, count(*) 
where module = 'SYS_AUTO_STS_MODULE' 
group by ACTION;

In a real production database (ADW), I noticed that after the module stopped appearing in the AWR report, the performance went back to normal:


A new PL/SQL package called DBMS_FLASHBACK_ARCHIVE_MIGRATE enables the migration of Flashback Data Archive enabled tables from a database on any release (in which the package exists) to any database on any release (that supports Flashback Data Archive).

The package has 3 procedures:

– EXPORT exports the given Flashback Archive enabled base tables, their history and related tablespaces
– EXPORT_ANALYZE analyzes the given Flashback Archive enabled base tables, their history, and related tablespaces for self containment using Transportable tablesapce checks
– IMPORT imports the Flashback Archive enabled base tables that were exported, their history, and related tablespaces

Here are the prerequisites:

Database version >= 11.2
If database version is 11.2, following conditions should be met:
shared_pool_size >= 500M
streams_pool_size >= 40M or
sga_target >= 2G

The DBMS_FLASHBACK_ARCHIVE_MIGRATE package must be compiled on both the source and target databases as SYS. The source file is located at ?/rdbms/admin/crefbamig.sql, using which the package can be created or compiled.

The export and import procedures must be executed as SYS. Since the package uses DBMS_DATAPUMP, DBMS_LOCK, DBMS_SYSTEM, DBMS_SQL and DBMS_SCHEDULER PL/SQL packages, their security models are also applicable.

More on 21c? Check:

1. Introducing Oracle Database 21c
2. A glimpse of what is new in Oracle Database 21c

A glimpse of what is new in Oracle Database 21c

In Autonomous, New features, Oracle database on December 4, 2020 at 16:12

Oracle Database 21c will be soon available first on Oracle Cloud: from the Database Cloud Service and the Autonomous Database Free Tier.

Here is a preview of what we can expect as new features:

1. You can enable automatic indexing at the table level:

The AUTO_INDEX_TABLE configuration setting specifies tables that can use auto indexes. When you enable automatic indexing for a schema, all the tables in that schema can use auto indexes. However, if there is a conflict between the schema level and table level setting, the table level setting takes precedence.

Here is an example of how to instruct Oracle to create auto indexes on the NDA_DOCS table:


If I would like to add the NDA_DOCS table to the auto index exclusion list, I simply run:


If later, I decide to remove it from the exclusion list, I will run:


And if at one point, I decide to remove all the tables from the exclusion list, so that all the tables in the database can use auto indexes, I will execute:


In Oracle 20c and below, you will get the following error message: ORA-38133: invalid parameter name AUTO_INDEX_TABLE specified.

2. Attention Log

The attention log which is unique for each database instance is according to the documentation “structured, externally modifiable file that contains information about critical and highly visible database events”.

We can use the attention log to quickly access information about critical events that need action.

The attention log has the following attributes:

Attention ID: A unique identifier for the message.
Attention type: The type of attention message. Possible values are Error, Warning, Notification, or Additional information. The attention type can be modified dynamically.
Message text
Urgency: Possible values are Immediate, Soon, Deferrable, or Information.
Scope: Possible values are Session, Process, PDB Instance, CDB Instance, CDB Cluster, PDB (for issues in persistent storage that a database restart will not fix), or CDB (for issues in persistent storage that a database restart will not fix).
Target user: The user who must act on this attention log message. Possible values are Clusterware Admin, CDB admin, or PDB admin.

Here is an example from Oracle:

3. For Oracle Autonomous Database, the size of the sequence cache is dynamically computed based on the rate of usage of sequence numbers:

“The automatic sequence cache size on each instance is dynamically computed based on the rate of usage of sequence numbers. Each instance caches the maximum of the manually configured sequence cache size and the projected cache size requirement for the next 10 seconds. Based on the sequence usage, the sequence cache size can shrink or grow. The minimum size to which the cache can shrink is the manually configured cache size. To prevent the sequence cache size from growing indefinitely, the cache size and each increment in the cache size is capped.”

4. Traditional auditing is now deprecated and Oracle recommends that we use unified auditing instead.

5. REMOTE_OS_AUTHENT is now desupported – in fact it was deprecated in Oracle 11.1 and afterwards retained only for backward compatibility.

The New Features 21c Guide lists also the following features which you can use also in Oracle 20c:

Blockchain Tables
– Database Resident Connection Pooling (DRCP) can be configured at the PDB level:

Connected to:
Oracle Database 20c EE High Perf Release - Production

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

– Transportable tablespace jobs can be restarted:

expdp system/password attach=jmd_exp_20c
Export> continue_client

– In Oracle Cloud environments, a PDB can be downsized by reducing the value of the CPU_MIN_COUNT parameter. The default value for JOB_QUEUE_PROCESSES across all containers is automatically derived from the number of sessions and CPUs configured in the system. It is the lesser value of:


– The IGNORECASE parameter for the orapwd file is desupported and all newly created password files are case-sensitive.

SecureFiles segments can be shrunk for improving performance.

Detecting Data Tampering and Measuring Asymmetry and Tailedness of Data in Oracle Database 20c

In DBA, New features, SQL on September 11, 2020 at 11:27

There are seven types of SQL functions in the Oracle database:

1. Single-Row Functions
2. Aggregate Functions
3. Analytic Functions
4. Object Reference Functions
5. Model Functions
6. OLAP Functions
7. Data Cartridge Functions

The single-row functions are the most popular and used ones but the aggregate and analytical function are also extremely popular among Oracle developers (and by DBAs too).

Three new analytical and statistical aggregate functions are now available in Oracle Database 20c. Let us use the RDBMS_BRANDS table for the 3 examples below. For the sake of clarity the last column shows if the database is only available from the cloud.

1. CHECKSUM computes the checksum of the input values or expression and can be applied on a column, a constant, a bind variable, or an expression involving them. All datatypes except ADT and JSON are supported.

In earlier releases you can still use DBMS_SQLHASH.GET_HASH in order to check the integrity of result sets or some other other PL/SQL packages: STANDARD_HASH or DBMS_CRYPTO.

Here is an example of how to use CHECKSUM:

where DB_ENGINES_RANK < 50; 

--------------- ----------------
              1          1345.44
              2          1282.64
              3          1078.31
              4           514.81
              5           162.64
              8            90.09
             10            73.89
             13            50.54
             15            27.59
             18            20.27
             42             3.73
             48             2.36

12 rows selected.


------------------------- --------------------------
                   288250                     209742

Let us now update the rankings of Oracle which in September went up to 1369.36 points and observe how the checksum value for DB_ENGINES_SCORE changed from 209742 to 180002!


1 row updated.

SQL> commit;

Commit complete.

SQL> select 

------------------------- --------------------------
                   288250                     180002

Note: NULL values in CHECKSUM column are ignored. Also, if you rollback the transaction, then the checksum value does not change.

There are 2 other analytical functions which are new to Oracle 20c. Skewness and Kurtosis describes the shape of a probability distribution and there are different ways of quantifying it for a theoretical distribution and corresponding ways of estimating it from a sample from a population. Oracle is now offering a very easy way of calculating their values by providing the in-built analytical functions in Oracle 20c.

2. SKEWNESS functions SKEWNESS_POP and SKEWNESS_SAMP are measures of asymmetry in data. A positive skewness is means the data skews to the right of the center point. A negative skewness means the data skews to the left.

Here is an example of how to use SKEWNESS:

SQL> select CLOUD_ONLY, count(*) from RDBMS_BRANDS group by CLOUD_ONLY;

C   COUNT(*)
- ----------
N          9
Y          3

group by CLOUD_ONLY; 

- ----------------------------- ------------------------------
N                     2.0503487                     .584150452
Y                    .685667537                      -.4626607

Skewness makes sense in the situation where DB_ENGINES_RANK and DB_ENGINES_SCORE represent the database brand rank and score in the list and you want to determine whether the outliers in data are biased towards the top end or the bottom end of the distribution, that is, if there are more values to the top of the mean when compared to the number of values to the bottom of the mean.

3. KURTOSIS functions KURTOSIS_POP and KURTOSIS_SAMP measure the tailedness of a data set where a higher value means more of the variance within the data set is the result of infrequent extreme deviations as opposed to frequent modestly sized deviations.

Here is an example of how to use KURTOSIS:

SQL> select CLOUD_ONLY, count(*) from RDBMS_BRANDS group by CLOUD_ONLY;

C   COUNT(*)
- ----------
N          9
Y          3

group by CLOUD_ONLY;  

- ----------------------------- ------------------------------
N                    2.88880521                     -1.4382391
Y                             0                              0

Note that a normal distribution has a kurtosis of zero. Have a look at the KURTOSIS_POP for the cloud-only databases Google BigQuery, Amazon Redshift and Snowflake.