Archive for 2020|Yearly archive page

On Kafka, JSON, PL/SQL and Advanced Queuing in Oracle Database 20c

In Autonomous, Cloud, Data, DBA, New features, PL/SQL on June 1, 2020 at 05:50

Oracle Corp. starts today (June 1st, 2020) a new fiscal year as the Oracle Database is slowly moving towards version 20c (still in preview mode only) which comes with almost 500 init.ora parameters and 5326 hidden/underscore parameters. There is a new one for 20c called kafka_config_file. But let us first take a step back and see its connection with Advanced Queuing.

Advanced Queuing is available in all editions of Oracle database, including XE. Since Oracle Advanced Queuing is implemented in database tables, all the operational benefits of high availability, scalability, and reliability are applicable to queue data.

Advanced Queuing can be accessed through the several interfaces: PL/SQL, Visual Basic, Java, Java Message Service, JDBC, ODP.NET, OCI (do not read Oracle Cloud Infrastructure – think of C language), etc.

Using PL/SQL to access Oracle Advanced Queuing is probably the most common method: using the PL/SQL packages DBMS_AQADM and DBMS_AQ.

Reading all that, one might think of Kafka. Kafka is a distributed, partitioned, replicated commit log service providing the functionality of a messaging system, but with a unique design. The aim here is not position Kafka and Oracle AQ against each other but show what is new in Oracle Database 20c that brings Kafka and Oracle together and what is important from DBA point of view.

Todd Sharp explained recently how to use Kafka Connect wth Oracle Streaming Service and Autonomous Database. Let us now see what is new in Oracle Database 20c:

Oracle Database 20c introduces Transactional Event Queues (TEQ), which are partitioned message queues that combine the best of messaging, streaming, direct messages, and publish/subscribe. TEQ operates at scale on the Oracle database. TEQ provides transactional event streaming, and runs in the database in a scale of 10s to 100s of billions of messages per day on 2-node to 8-node Oracle RAC databases, both on-premise and on the cloud. TEQ has Kafka client compatibility, which means, Kafka producer and consumer can use TEQ in the Oracle database instead of a Kafka broker. Check Advanced Queuing in 20c for more details.

1. Advanced Queuing: Kafka Java Client for Transactional Event Queues

Kafka Java Client for Transactional Event Queues (TEQ) enables Kafka application compatibility with Oracle database. This provides easy migration of Kafka applications to TEQ.

You do not have to manage a separate Kafka infrastructure and this new feature simplifies the event-driven application architectures with an Oracle converged database that now includes events data. Starting from Oracle Database 20c, Kafka Java APIs can connect to Oracle database server and use Transactional Event Queues (TEQ) as a messaging platform. Developers can migrate an existing Java application that uses Kafka to the Oracle database. A client side library allows Kafka applications to connect to Oracle database instead of Kafka cluster and use TEQ messaging platform transparently.

Two levels of configuration are required to migrate Kafka application to TEQ messaging platform:

– Database level configuration
– Application level configuration

Kafka application needs to set certain properties which will allow OKafka library to locate the Oracle Database. This is analogous to how Kafka application provides zoo keeper information. These connection properties can be set in the following two ways:

– using database user and password provided in plain text
– using JDBC wallet

The following are the prerequisites for configuring and running Kafka Java client for TEQ in an Oracle Database. Create a database user. Grant the following user privileges:

grant connect, resource to user;
grant execute on dbms_aq to user;
grant execute on dbms_aqadm to user;
grant execute on dbms_aqin to user;
grant execute on dbms_aqjms to user;
grant select_catalog_role to user;

Next, set the correct database init.ora parameter to use TEQ:


Set the local listener too:


2. Advanced Queuing Support for JSON Data Type and PL/SQL

Oracle Database Advanced Queuing now supports the JSON data type.

Many client application and micro-services which use Advanced Queuing for messaging have better performance if they use JSON data type to handle JavaScript Object Notation (JSON) messages.

In this aspect, PUBLIC is granted EXECUTE privilege on all these types:


Regarding the new features of PL/SQL in 20c, check first the interesting example of Steven Feuerstein on extension of loop iterators.

Check the New Features in Release 20c for Oracle Database PL/SQL for more details on PL/SQL extended iterators, PL/SQL qualified expressions enhancements, SQL macros, the new JSON data type and the new pragma SUPPRESSES_WARNING_6009.

3. Advanced Queuing: PL/SQL Enqueue and Dequeue Support

The following features are new in this release:

– Kafka Java Client for Transactional Event Queues (TEQ) which enables Kafka application compatibility with Oracle Database and thus providing easy migration of Kafka applications to TEQ
– PL/SQL Enqueue and Dequeue Support for JMS Payload and non-JMS Payload in Transactional Event Queues
– Transactional Event Queues for Performance and Scalability
– Simplified Metadata and Schema in Transactional Event Queues
– Support for Message Retention and Seekable Subscribers
– Advanced Queuing Support for JSON Data Type

For all the details, check the Changes in Oracle Database Advanced Queuing Release 20c.

In terms of performance and scalability, Oracle Transactional Event Queues have their Queue tables partitioned in 20c into multiple Event Streams which are distributed across multiple RAC nodes for high throughput messaging and streaming of events.

Remember that in 10.1, AQ was integrated into Oracle Streams and thus Oracle AQ was called “Oracle Streams AQ”. But in 12.1, Oracle Streams got deprecated and AQ was again named just “Oracle AQ”.

And finally: here is the 546 page long Transactional Event Queues and Advanced Queuing User’s Guide along with few good additional articles:

Oracle + Kafka = Better Architecture by Jonathan Wallace
Streaming data from Oracle into Kafka by Robin Moffatt
Extending Oracle Streaming with Kafka Compatibility by Somnath Lahiri

Defragmentation of Large Objects / SecureFiles LOBs in Oracle Database 20c

In Database tuning, DBA, New features, Oracle database on May 25, 2020 at 06:09

In Oracle 20c, the init.ora parameter DB_SECUREFILE defaults to PREFERRED. This means that all large objects / LOBs are created as SecureFiles unless BASICFILE is explicitly specified in the LOB storage clause or the tablespace is an MSSM (= Manual Segment Space Management) tablespace.

Until 19c, only defragmentation of BasicFile LOBs was possible. Tim Hall showed, that in order to shrink a SecureFile LOB you need to move it.

SecureFiles defragmentation in 20c provides online defragmentation of allocated and freed space in SecureFiles segments for all types of SecureFiles LOBs – compressed, deduplicated and encrypted.

In an Oracle 20.2.0 database, I have a table called BLOGS. Let us turn on compression, deduplication and encryption:

SQL> ALTER TABLE blogs MODIFY LOB (blog_text) 

Table altered.

Defragmentation can be done automatically by a background process and the segment advisor can estimate the fragmentation levels and how much space can be saved. Note that some temp segment space needed to hold intermediate results.

Let us try to defragment the SecureFiles LOB column BLOG_TEXT and use the segment space advisor to see what is forecast vs. reallity.

In order to defragment the SecureFiles LOBs, we need to use the shrink_clause. The shrink_clause lets us (in general) manually shrink space in a table, index-organized table or its overflow segment, index, partition, subpartition, LOB segment, materialized view, or materialized view log. This clause is valid only for segments in tablespaces with automatic segment management.

By default, Oracle Database compacts the segment, adjusts the high water mark, and releases the recuperated space immediately. Compacting the segment requires row movement. Therefore, you must enable row movement for the object you want to shrink before specifying this clause. Further, if your application has any rowid-based triggers, you should disable them before issuing this clause.


Table altered.

With release 20c, you can use the shrink_clause on SecureFile LOB segments by using these two ways in order to invoke it:

1. Target a specific LOB column and all its partitions:


2. Cascade the shrink operation for all the LOB columns of the table and its partitions:


Do not attempt to enable row movement for an index-organized table before specifying the shrink_clause. The ROWID of an index-organized table is its primary key, which never changes. Therefore, row movement is neither relevant nor valid for IOTs.

There are 2 important options/keywords with the shrink space syntax:

COMPACT: If you specify COMPACT, then Oracle only defragments the segment space and compacts the table rows for subsequent release. Meaning Oracle will recover space but will not amend the high water mark (HWM). So, Oracle does not release the space immediately.

CASCADE: If you specify CASCADE, then Oracle performs the same operations on all dependent objects of table, including secondary indexes on index-organized tables. Meaning Oracle will recover space for the object and all dependent objects.

Lat us follow the steps for the BLOGS table:

1. Run the Segment Space Advisor:

seg_task_id   number;
seg_task_name varchar2(100);
seg_task_desc varchar2(500);
seg_task_name := 'SecureFileDefragmentation1';
seg_task_desc := 'Manual Segment Advisor Run for table BLOGS';
dbms_advisor.create_task (
advisor_name := 'Segment Advisor',
task_id      := seg_task_id,
task_name    := seg_task_name,
task_desc    := seg_task_desc);

obj_id        number;
dbms_advisor.create_object (
task_name   := 'SecureFileDefragmentation1',
object_type := 'TABLE',
attr1       := 'JULIAN',
attr2       := 'BLOGS', 
attr3       := NULL,
attr4       := NULL,
attr5       := NULL,
object_id   := obj_id);

task_name := 'SecureFileDefragmentation1',
parameter := 'recommend_all',
value     := 'TRUE');

exec dbms_advisor.execute_task('SecureFileDefragmentation1');

2. Let us check the findings from DBA_ADVISOR_FINDINGS:

SQL> select message,more_info from dba_advisor_findings where task_name='SecureFileDefragmentation1';

The free space in the object is less than 10MB.
Allocated Space:15728640: Used Space:4013928: Reclaimable Space :180376:

3. Now let us defragment the SecureFile LOB:

SQL> select bytes from dba_segments where segment_name='BLOGS';



Table altered.

SQL> select bytes from dba_segments where segment_name='BLOGS';



Table altered.

SQL> select bytes from dba_segments where segment_name='BLOGS';


As you can see, with the simple operations above, we managed to decrease the size of the BLOGs table 15 times: from 15728640 to 1048576 bytes.

The shrink_clause is subject to the following restrictions:
– You cannot combine this clause with any other clauses in the same ALTER TABLE statement.
– You cannot specify this clause for a cluster, a clustered table, or any object with a LONG column
– Segment shrink is not supported for tables with function-based indexes, domain indexes, or bitmap join indexes
– With this clause, Oracle does not shrink mapping tables of index-organized tables, even if you specify CASCADE
– You can specify the shrink_clause for a table with advanced row compression enabled (ROW STORE COMPRESS ADVANCED) but you cannot specify this clause for a table with any other type of table compression enabled
– You cannot shrink a table that is the master table of an ON COMMIT materialized view
– Rowid materialized views must be rebuilt after the shrink operation.

Automatic Zone Maps in the Oracle Database

In Data, Database tuning, Databases, DBA, New features, Oracle database on May 18, 2020 at 06:18

A zone is a set of a contiguous data blocks on disk.

A zone map is an index-like structure built on a table and stores information about the zones of that table.

There are 2 major differences between indexes and zone maps:

– A zone map stores information per zone instead of per row which makes it much more compact than an index
– A zone map is not actively managed the way an index is kept in sync with the DML on the table

Zone maps are closer as a concept to Exadata’s storage indexes than to B-tree indexes.

Before going into how Automatic Zone Maps work in Oracle 20c, let me explain the concept with an example. Consider a small table containing basic information about some relational databases from (rank, score, initial and last release, cloud based):

The RDBMS_BRANDS segment has 6 data blocks with 2 rows per block:

Let us now create the zonemap on the RDBMS_BRANDS table (on 3 columns only):

rdbms_brands (db_engines_rank, db_engines_score, initial_release); 

Materialized zonemap RDBMS_ZMAP created.

We have now 3 zones and each zone contains two blocks and stores the minimum and maximum of db_engines_rank, db_engines_score and initial_release:

Next, let us run a query returning all RDBMS brands with ranking score more than 1000:

Looking at the execution plan below we see that Oracle is scanning only Zone 1 as the maximum score in all other zone is smaller than 1000:

That is how zone maps work … but what is new in Oracle 20c?

We can now enable automatic creation and maintenance of basic zone maps for both partitioned and non-partitioned tables. But for now, the creation is not available for join zone maps, IOTs, external tables or temporary tables!

In 20c, you can use the new package DBMS_AUTO_ZONEMAP to enable Automatic Zone Maps in the database. Automatic zone map creation is turned off by default.

These four values are allowed for the parameter AUTO_ZONEMAP_MODE:

ON: Turns on auto zone map feature completely. Both for foreground and background zone map creation and maintenance
OFF: Turns off auto zone map feature completely. Both for foreground and background zone map creation and maintenance
FOREGROUND: Turns on only for foreground zone map creation and maintenance
BACKGROUND: Turns on only for background zone map creation and maintenance

You may use the ACTIVITY_REPORT function to view auto zone map activity for a given time window. Note that the background job that performs automatic zone map processing starts once per hour and each run may last up to three hours.

SET LONG 100000
SELECT dbms_auto_zonemap.activity_report() report FROM dual;

These 2 zonemaps related views show the most important information DBAs need:

DBA_ZONEMAPS displays all zone maps in the database
DBA_ZONEMAP_MEASURES displays the measures for all zone maps in the database

On a final note: Automatic Zone Maps are available for now only on Exadata and requires the Oracle Partitioning option.

Automatic Index Optimization in Oracle Database 20c

In Database tuning, Databases, DBA, New features, Oracle database on May 11, 2020 at 05:47

Oracle database 20c came with 138 new features and one of them is directly related to the indexes in the database. It is called Automatic Index Optimization.

For some DBAs indexes in the database do not need extra care and they don’t bother much about rebuilding, compressing, coalescing or shrinking them. I have administered in the past a 24×7 mission critical database with size of 5TB where indexes were occupying 4.5TB of all that. Correct, real data was less than 500GB granted that you have SYSTEM, SYSAUX, etc.

Automatic Index Optimization does not mean optimization of the Automatic Indexes in the database but rather making now the Index Optimization an automatic process. Here is how it works and what you have to do in order to enable it and make it work.

First, in order to implement an ILM strategy, you have to enable Heat Maps in the database to track data access and modification. You can enable and disable heat map tracking at the system or session level with the ALTER SYSTEM or ALTER SESSION statement using the HEAT_MAP init.ora parameter, for example:

SQL> alter system set HEAT_MAP = ON;

Like ADO for data segments, Automatic Index Optimization works via ILM on indexes by enabling policies that automatically optimize indexes by compressing, shrinking and rebuilding them. Oracle is using the existing Heat Maps and collects activity statistics on the indexes.

So next, add ADO policies for indexes in order to enable their compression and optimization using the existing Automatic Data Optimization (ADO) framework. You can do it for newly created indexes as well as for already existing indexes.

There are 2 options:

– ADD POLICY TIER in order to perform the operation on a say low cost/ tier 2 tablespace when tier 1 storage is under space pressure
– ADD POLICY OPTIMIZE in order to kick off the process after a certain number of days passes without accessing the index

Here are few examples:

SQL> create index julian.price_idx ON julian.sales(price)

SQL> alter index julian.price_idx ILM ADD POLICY TIER TO BC_DATA;

SQL> alter index julian.price_idx


---------------------   -------------  ---
P1                      DATA MOVEMENT  YES

Note that the Oracle documentation has the tier syntax wrong: instead of “ILM ADD POLICY SEGMENT TIER” use “ILM ADD POLICY TIER”.

The optimization process includes actions such as compressing, shrinking or rebuilding the indexes:

Compress: Compresses portions of the key values in an index segment (~3 times)
Shrink: Merges the contents of index blocks where possible to free blocks for reuse
Rebuild: Rebuilds an index to improve space usage and access speed

Notice that you cannot decide which of the 3 above to use. Oracle automatically determines which action is optimal for the index and implements that action as part of the optimization process.

But can we have Automatic Index optimization for Automatic Indexes and not have to rebuild them manually any longer? I did try indeed and here is the result:

SQL> alter index julian."SYS_AI_abrca2u9qmxt7"

Error starting at line : 17 in command -
alter index julian."SYS_AI_abrca2u9qmxt7"
Error report -
ORA-65532: cannot alter or drop automatically created indexes

Clearly not possible but the error message is sort of misleading because actually you can alter and even drop automatic indexes:

SQL> alter index julian."SYS_AI_abrca2u9qmxt7" rebuild online;
Index altered.
SQL> alter index julian."SYS_AI_abrca2u9qmxt7" coalesce;
Index altered.
SQL> alter index julian."SYS_AI_abrca2u9qmxt7" shrink space;
Index altered.

How to drop the Auto Index? Just rebuild it a new tablespace and run “drop tablespace … including contents and datafiles” – yes it works.

Also, while administering ADO policies for indexes, you cannot manually disable these policies but you can delete an index policy. An ADO policy for indexes executes only one time. After the policy executes successfully, the policy is disabled and is not evaluated again.

SQL> alter index julian.price_idx ILM DELETE POLICY p1;

Moreover, such policies for indexes on partition level are not yet supported. The ADO policy is cascaded to all partitions. So, if we have hybrid tables in the Cloud, we cannot move local indexes automatically to object storage but it should work for global indexes. Note that we can use Automatic Data Optimization (ADO) policies with hybrid partitioned tables under some conditions.

Here are some other limitations:

– ADO does not perform checks for storage space in a target tablespace when using storage tiering
– ADO is not supported on materialized views
– ADO is not supported with index-organized tables or clusters
– ADO concurrency depends on the concurrency of the Oracle scheduler meaning if a policy job for ADO fails more than two times, then the job is marked disabled and the job must be manually enabled later

The feature is available in almost all flavors of the database, i.e., EE, Database Cloud Service, Exadata and ODA: but it requires the Oracle Advanced Compression option.

SYSDATE and Time Zones in the Autonomous Database

In Autonomous, Cloud, DBA, Oracle database on May 4, 2020 at 06:16

In the Oracle database, SYSDATE is one of the most popular functions, arguably the most popular.

PL/SQL based applications use quite extensively the SYSDATE function. What will happen when you migrate their data to Autonomous Cloud (or build a new application on Autonomous) as by default the Time Zone is set to Coordinated Universal Time (= UTC) in an OCI environment?

You are allowed to change the database and session timezones in ADB, but this doesn’t change the SYSDATE and SYSTIMESTAMP in the timezones. So, the PL/SQL packages, procedure and functions and in particular all SQL using SYSDATE and SYSTIMESTAMP might not return what you expect. You need to check for instance what happened during the last one hour (sysdate-1/24) – and you might be surprised – you will get the result for the past one hour but it might not be your last hour.

Currently, it is not possible to change the timezone of SYSDATE and SYSTIMESTAMP since they are coming from the operating system. The SYSDATE and SYSTIMESTAMP functions simply performs a system-call to the server Operating System to get the time – the so called “gettimeofday” call. The server OS (Unix) timezone settings influences the time that the OS will pass on to Oracle and returned by SYSDATE and SYSTIMESTAMP.

Moreover, there is no Oracle database parameter/init.ora setting that will impact the SYSDATE and SYSTIMESTAMP timezone (= give a result that is in an other timezone than the OS timezone setting). Also, SYSDATE and SYSTIMESTAMP do not use the Oracle timezone information (= Oracle RDBMS dst patches) in the database and are NOT affected by the used DBTIMEZONE / SESSIONTIMEZONE settings.

On a side note, in the Oracle database there is a dynamic init.ora parameter called FIXED_DATE which enables you to set a constant date that SYSDATE will always return instead of the current date.

As far as I know, there is a project going on inside Oracle to provide a parameter to make SYSDATE return date in a database timezone, but there is no ETA for it.

So, how can we bypass this issue?

The general recommendation is to use CURRENT_DATE and CURRENT_TIMESTAMP which will return the date in session timezone. If you cannot modify the applications, then there is a workaround to use the SQL Translation Framework functionality (Hello Kerry!) to change SYSDATE to CURRENT_DATE in the queries on the fly. This would require recreating the PL/SQL procedures, so that they can be replaced as well. It also requires creating a schema level logon rigger to enable the Translation Framework. This needs to be carefully tested, but in general it works.

UTC is the recommended time zone to use, and of course, in theory at least, this makes a lot of sense in a global distributed database environment. In reality, everyone like his/her own time zone!

These 4 links below are worth reading if you are interested in timestamp and time zone issues and functionalities:

How to change the Time Zone in Oracle Database hosted in OCI with an Example: Doc ID 2459830.1
Timestamps and time zones – Frequently Asked Questions: Doc ID 340512.1
How to Change the “Database Time” (SYSDATE and SYSTIMESTAMP) to another Time/Timezone: Doc ID 1988586.1
Datetime Data Types and Time Zone Support

The following are some points to note related to time zone upgrade in a multitenant environment:

– Updating the time zone version of a CDB does not change the time zone version of the PDBs
– Updating the time zone version of a PDB does not change the time zone version of the other PDBs
– A new PDB is always assigned the time zone version of PDB$SEED
– PDB$SEED is always assigned the time zone version at the time of CDB creation
– The time zone version of PDB$SEED cannot be changed

And if you plan on running ALTER DATABASE SET TIME_ZONE, have in mind that:

– The ALTER DATABASE SET TIME_ZONE statement returns an error if the database contains a table with a TIMESTAMP WITH LOCAL TIME ZONE column and the column contains data
– The change does not take effect until the database has been shut down and restarted
– You can find out the database time zone by entering the following query: SELECT dbtimezone FROM DUAL;

SET TIME_ZONE can be done also on session level if needed:

alter session set time_zone='-03:00';

And please note once more that SYSDATE and SYSTIMESTAMP have nothing to with the DATABASE TIMEZONE (DBTIMEZONE).

On a final note: the information above is valid for all flavors of Autonomous: ADW, ATP, ADW-D and ATP-D.


Years ago, I saw this quiz on – we have the following two PL/SQL blocks:


  WHILE sysdate = sysdate LOOP
  x DATE;
      SELECT null INTO x FROM dual WHERE sysdate = sysdate;

Question is: what happens after you run them? Are the loops above both finite, both infinite or is it so that one of them is finite and the other one infinite?

If you cannot answer the question just run them in SQL*Plus, etc. Then, try to explain why – the reason for being finite or infinite.

The 20c Database Upgrade Mystery

In Cloud, Databases, DBA, Oracle database on April 27, 2020 at 14:31

Oracle Database 20c is available only for preview. It is not available for production use. Upgrades to or from Oracle Database 20c are not supported.

So, upgrades are not supported but there exists an Oracle Database 20c Upgrade Guide. The 20c upgrade pdf file is 461 pages long!

Let me shed some light on this “mystery”.

As of today, April 27th 2020 (Oracle Database 19c is today 1 year old on Linux), Oracle 20c is only available from the Oracle Public Cloud in preview mode. To try out this 20c preview, you will need an Oracle Public Cloud tenancy with cloud credits (paid or promotional), as the DBCS instance will require an OCI Compute VM. And, by the way, 20c does *not* run on Exadata.

Once 20c becomes generally available also in non-preview only mode, upgrades will be also available.

You can perform a direct upgrade to the new release from the following releases:

• 19c
• 18c
• 12.2.0

Here is what we need to know about compatibility:

– Before upgrading to Oracle Database 20c, you must set the COMPATIBLE initialization parameter to at least 12.2.0
– In Oracle Database 20c, when the COMPATIBLE initialization parameter is not set in your parameter file, the COMPATIBLE parameter value defaults to 20.0.0
– Installing earlier releases of Oracle Database on the same computer that is running Oracle Database 20c can cause issues with client connections

The most important point about the 20c architecture is that starting with the first release of Oracle Database 20c, non-CDB Oracle Database upgrades to non-CDB architecture are desupported. Meaning you need a container and your data will be in a pluggable database.

Starting with Oracle 19c you can have up to 3 pluggable databases (PDBs) per container database (CDB) without requiring additional multitenant license. See page 11 of Oracle Database Licensing Information User Manual for all the details (it is a 361 page pdf file).

So, when upgrading to 20c, we have 2 options:

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 20c.

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 20c CDB. Upgrade the plugged-in non-CDB Oracle Database to Oracle Database 20c. 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 20c, Database Upgrade Assistant (DBUA) is replaced by the AutoUpgrade utility
– Starting with Oracle Database 20c, 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 20c, an Oracle Database installation configures all Oracle Database homes in read-only mode by default

And here is what you need to know before the upgrade about security and the init.ora parameters:

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

Further info and links were listed in detail by the King of Database Upgrades Mike Dietrich – his blog is dedicated to Oracle Database upgrades!

Now, how about long term support? Do you care only about LTS database releases when planning upgrades? As Tim Hall says: “It’s important to check out what is happening in the 20c release, because it may alter how you use the earlier releases now. There is no point launching into a new development using a feature that is about to disappear. Remember Oracle Streams anyone?” Yes, indeed Tim: Streams, Advanced Replication… I rather be on the latest release and use the new features – live and learn…

Tablespaces in Oracle Database 20c

In Databases, DBA, New features, Oracle database on April 8, 2020 at 08:52

Oracle 20c, released recently with 138 new features, is still only available from the cloud but it brings one major change for DBAs which they can no longer avoid: use containers and pluggable databases. And this changes slightly the way how DBAs look also at tablespaces.

But what is new in 20c about tablespaces? Now, DBAs can set the default tablespace encryption algorithm:

The new TABLESPACE_ENCRYPTION_DEFAULT_ALGORITHM dynamic init.ora parameter defines the default encryption algorithm for tablespace creation operations. The default value in 20.2.0 is AES128.

If you set TABLESPACE_ENCRYPTION_DEFAULT_ALGORITHM to say 3DES168, then all future tablespace creation operations will use 3DES168 as the default encryption algorithm. And this is both for offline and online tablespace encryption operations.

The supported values for the encryption algorithms are AES128, AES192, AES256, ARIA128, ARIA192, ARIA256, GOST256, SEED128 and 3DES168.

The CREATE TABLESPACE command in 20c is not more complex than before. Tablespaces can be permanent, undo or temporary; bigfile (one data file or temp file up to approx. 4 billion blocks, max. size is 128 terabytes (TB) for a tablespace with 32K blocks and 32TB for a tablespace with 8K blocks) or smallfile (max. 1022 data files or temp files each with max. 4 million blocks).

The concept of tablespace sets is not new but not highly adopted.

But going back to tablespaces and PDBs. Here is the output from DBA_TABLESPACES run from a PDB as SYS:

And here is the the output from DBA_TABLESPACES run from the root container as SYS:

Of course, DBA_TABLESPACES is no longer the place from which the DBA will find all tablespaces from the database. The correct one is CDB_TABLEPSPACES but if you run the “select * from cdb_tablepsaces;” as SYS from within the PDB, you will still get the same output.

However, run is as SYS from the root container, you get what you are looking for:

From 20c onwards, DBA should get used to the new CDB_ views. And then, less and less people will ask the question “Why do we have 2 tablespaces with the same name?”….

Here is something else which is new in 20c: Oracle automatically shrinks an Oracle ACFS file system based on policy providing there is enough free storage in the volume. ACFS stands for Automatic Storage Management Cluster File System.

In 20c, you can use now File Group templates by customizing and setting default file group properties for automatically created file groups. Without file group templates if you wanted to change properties for an automatically created file group you would have to manually change the properties after the associated files are created which triggers an unnecessary rebalance. The file group templates feature provides a much better option.

New is also the SecureFiles defragmentation, it provides online defragmentation of allocated and freed space in SecureFiles segments for all types of SecureFiles LOBs – compressed deduplicated encrypted. Defragmentation can be done automatically by a background process and the segment advisor can estimate the fragmentation levels and how much space can be saved. Defragmentation can be done mostly in-place with some temp segment space needed to hold intermediate results.

Next, there are 2 new features of Data Pump related to tablespaces:

– Starting with Oracle Database 20c, Data Pump improves transportable tablespace metadata operations with parallelism. Oracle Data Pump now supports parallel export and import operations for Transportable Tablespace (TTS) metadata. This is the information that associates the tablespace data files with the target database in a TTS migration. Parallelism improves TTS export and import performance especially when there are millions of database objects in the data files including tables indexes partitions and subpartitions.

– Starting with Oracle Database 20c, Data Pump resumes transportable tablespace export and import jobs that are stopped. Oracle Data Pump has the capacity to resume transportable tablespace export and import jobs. Due to errors or other problems you can find that transportable tablespace export or import jobs are stopped. Oracle Data Pump’s capacity to resume these stopped jobs helps to save you time and makes the system more available.

Another new 20c feature is Automatic Zone Maps which are created and maintained for any user table without any customer intervention. Zone maps allow the pruning of blocks and partitions based on the predicates in the queries without any user intervention. Automatic zone maps are maintained for direct loads and are maintained and refreshed for any other DML operation incrementally and periodically in the background. Automatic zone maps are improving the performance of any query transparently and automatically without the need of any user action. However, this feature is available only on Exadata.

And finally, something which I find interesting in the good way: starting with Oracle Database 20c, you can specify an expression as the value for some initialization parameters, which enables the database to automatically adjust the parameter value in response to environmental changes. This is especially useful in Oracle Autonomous Database environments.


Blockchain tables in Oracle Database 20c

In Databases, DBA, Oracle database on March 16, 2020 at 10:58

Blockchain tables are insert-only tables that organize rows into a number of chains and is a new concept starting with Oracle 20c. Each row in a chain, except the first row, is chained to the previous row in the chain by using a cryptographic hash. For each Oracle RAC instance a blockchain table contains thirty two chains, ranging from 0 through 31.

This is an example of how a blockchain table is created in 20c:

Let me first point out the main restrictions:

– Blockchain tables cannot be created in the root container and in an application root container: ORA-05729: blockchain table cannot be created in root container
– You cannot update the rows: ORA-05715: operation not allowed on the blockchain table
– In general, you cannot delete rows, truncate the table or drop the blockchain table: ORA-05723: drop blockchain table NDA_RECORDS not allowed
– Don’t even try to drop the tablespace containing blockchain tables, here is what happens:

ORA-00604: error occurred at recursive SQL level 1
ORA-05723: drop blockchain table NDA_RECORDS not allowed

The most important new view in 20c related to blockchain tables is DBA_BLOCKCHAIN_TABLES:

The 4 (non-trivial) columns of DBA_BLOCKCHAIN_TABLES contain the following information:

1. ROW_RETENTION: The minimum number of days a row must be retained after it is inserted into the table – if the value of this column is NULL, then rows can never be deleted from the table. In the example above, the row can be deleted after 16 days. Otherwise, you will get: ORA-05715: operation not allowed on the blockchain table

2. ROW_RETENTION_LOCKED: 2 possible values (YES and NO) showing if the row retention period for the blockchain table is locked.

YES: The row retention period is locked. You cannot change the row retention period.
NO: The row retention period is not locked. You can change the row retention period to a value higher than the current value with the SQL statement ALTER TABLE … NO DELETE UNTIL n DAYS AFTER INSERT.

3. TABLE_INACTIVITY_RETENTION: Number of days for which the blockchain table must be inactive before it can be dropped, that is, the number of days that must pass after the most recent row insertion before the table can be dropped. A table with no rows can be dropped at any time, regardless of this column value. In the example above, a year of inactivity must pass before the table can be dropped.

4. HASH_ALGORITHM: The algorithm used for computing the hash value for each table row.

To each row you add/insert to the blockchain table, Oracle adds values to the hidden columns of the blockchain table. Hidden columns are populated after you commit. They are used to implement sequencing of rows and verify that data is tamper-resistant. You can create indexes on hidden columns. In order to view the values of the hidden columns, you should explicitly include their names in the SQL, just like this:

Hidden Columns in Blockchain Tables will give you more details about the subject.

The following additional operations are not allowed with blockchain tables:

– Adding, dropping, and renaming columns
– Dropping partitions
– Defining BEFORE ROW triggers that fire for update operations (other triggers are allowed)
– Direct-path loading
– Inserting data using parallel DML
– Converting a regular table to a blockchain table (or vice versa)

There is a new PL/SQL procedure DBMS_BLOCKCHAIN_TABLE which contains 5 procedures, one of which VERIFY_ROWS is used to validate he data in the blockchain table.

Use DBMS_BLOCKCHAIN_TABLE.DELETE_EXPIRED_ROWS to remove rows that are beyond the retention period of the blockchain table.

For DBAs:

– For each chain in a database instance, periodically save the current hash and the corresponding sequence number outside the database.
– In an Oracle Data Guard environment, consider using the maximum protection mode or maximum availability mode to avoid loss of data.

You can use certificates to verify the signature of a blockchain table row. Check here on how to add and delete certificates to blockchain table rows.

Final note: you really have a good eye if you noticed the new 20c datatype I used in the table creation at the top of this blog post.

Oracle 20.2.0 new features for DBAs

In Autonomous, Cloud, Databases, New features, Oracle database on February 25, 2020 at 09:38

Oracle 20c is now available in preview mode from the Oracle Public Cloud. Preview version databases are not intended for production use and have limited functionality.

After testing some of the new features of 20c, here is what might be of interest for most DBAs:

1. Provisioning the database is relatively simple. You need an SSH Public key, create a VCN (Virtual Cloud Network) and a client subnet in your compartment. A hostname prefix is also mandatory. Note that the administrator password must be 9 to 30 characters and contain at least 2 uppercase, 2 lowercase, 2 special, and 2 numeric characters. The special characters must be _, #, or -. You cannot bypass that. The shape type must be “Virtal Machine” and the SMS (Storage Management Software) must be “Logical Volume Manager”.

Once provisioned you need the IP address which is under “Nodes” (bottom left, just under “Resources”). As you can see, it is no longer under “General Information”:

And … you can stop the database only manually (as of today, February 25th, 2020) – there is no button for stopping the node. Just terminate in case you are on a tight budget.

2. Blockchain tables

Blockchain tables are append-only tables in which only insert operations are allowed. Deleting rows is either prohibited or restricted based on time. Rows in a blockchain table are made tamper-resistant by special sequencing & chaining algorithms. Users can verify that rows have not been tampered. Have a look at an example I used to create a blockchain table:

Here is how to manage blockchain tables.

Most important is to specify the Retention Period for the Blockchain Table by using the NO DROP clause in the CREATE BLOCKCHAIN TABLE statement. Also specify the Retention Period for Rows in the Blockchain Table: use the NO DELETE clause in a CREATE BLOCKCHAIN TABLE statement.

3. A multitenant container database is the only supported architecture in Oracle Database 20c. While the documentation is being revised, legacy terminology may persist. In most cases, “database” and “non-CDB” refer to a CDB or PDB, depending on context. In some contexts, such as upgrades, “non-CDB” refers to a non-CDB from a previous release. Check the changes in Oracle 20c for Oracle Multitenant.

4. Data Pump

– Oracle Data Pump 20c can include and exclude objects in the same export or import operation meaning that now, Oracle Data Pump commands can include both INCLUDE and EXCLUDE parameters in the same operation. By enabling greater specificity about what is being migrated, this enhancement makes it easier to migrate to Oracle Cloud, or to another on-premises Oracle Database.

Note: when you include both parameters in a command, Oracle Data Pump processes the INCLUDE parameter first, and includes all objects identified by the parameter. Then it processes the EXCLUDE parameters, eliminating the excluded objects from the included set. Here is an example of including only 2 tables but excluding all indexes except the PKs (real use case: you want to enable Oracle Auto Indexing in ADB and while importing the data you need to drop all indexes except the PKs):


– Oracle Data Pump 20c resumes transportable tablespace export and import jobs that are stopped

– Oracle Data Pump 20c supports parallel export and import operations for Transportable Tablespace (TTS) metadata

– Oracle Data Pump 20c supports optional index compression on imports, including for Oracle Autonomous Database

– Oracle Data Pump 20c supports adding, changing and eliminating table compression

– Oracle Database 20c supports index compression as well by introducing a new TRANSFORM parameter clause, INDEX_COMPRESSION_CLAUSE

– Oracle Data Pump 20c can perform exports from Oracle Autonomous Database into dump files in a cloud object store

– Starting with Oracle Database 20c, a checksum is now added to the dumpfile – you can use the checksum to help to confirm that the file is valid after a transfer to or from the object store and also after saving dumpfiles on on-premises and that it has no
accidental or malicious changes

5. Small improvements and changes in 20c:

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

– A new dynamic view called V$PMEM_FILESTORE displays information about Persistent Memory Filestores

– Certain predefined columns of unified audit records from common unified audit policies can be written to the UNIX SYSLOG destination – to enable this feature, you set UNIFIED_AUDIT_COMMON_SYSTEMLOG, a new CDB level init.ora parameter (added in Oracle 19c (19.3) but not included in the References)

– You now can set the TABLESPACE_ENCRYPTION_DEFAULT_ALGORITHM dynamic parameter to define the default encryption algorithm for tablespace creation operations

– Database Vault: a DV_OWNER common user in the CDB root can prevent local users from creating Oracle Database Vault controls on common objects in a PDB

– AutoShrink: Oracle Automatic Storage Management Cluster File System (Oracle ACFS) automatic shrinking automatically shrinks an Oracle ACFS file system based on policy, providing there is enough free storage in the volume

– The Oracle Grid Infrastructure feature Automatic Storage Management Cluster File System (Oracle ACFS) is desupported with Microsoft Windows

– An Oracle Database installation configures all Oracle Database homes in read-only mode by default

– Traditional auditing is deprecated in Oracle Database 20c thus Oracle recommend that we use unified auditing, which enables selective and more effective auditing inside Oracle Database

– The package DBMS_OBFUSCATION_TOOLKIT is desupported, and replaced with DBMS_CRYPTO

– Older encryption and hashing algorithms contained within DBMS_CRYPTO are deprecated

– The Large Object (LOB) features DBMS_LOB.LOADFROMFILE and LOB buffering are desupported

– You can configure database clients to maintain multiple Secure Sockets Layer (SSL) sessions using different SSL certificates

– In the DBMS_ROLLING.set_parameter(), there is a new parameter, called BLOCK_UNSUPPORTED – by default, BLOCK_UNSUPPORTED
is set to 1 [YES], indicating that operations performed on tables that are unsupported by Transient Logical Standby will be blocked on the primary database. If set to 0 [OFF], then the DBMS_ROLLING package does not block operations on unsupported tables

– In order to coordinate with the Oracle GoldenGate feature OGG EXTRACT, the LOGICAL_REPLICATION clause now provides support for automatic extract of tables

– Two new views, DBA_OGG_AUTO_CAPTURED_TABLES, and USER_OGG_AUTO_CAPTURED_TABLES, provide you with tools to query which tables are enabled for Oracle GoldenGate automatic capture

6. Finally, her are the 7 new init.ora parameters in Oracle 20.2.0:

DBNEST_ENABLE (DbNest is OS resource and file system isolation for PDBs)
DIAGNOSTICS_CONTROL (meant to be used with Oracle Support)
MAX_IDLE_BLOCKER_TIME (maximum number of minutes before a blocking session is automatically terminated)

The DBMS_CLOUD and UTL_SMTP packages in the Autonomous Database

In Autonomous, Cloud, DBA, PL/SQL on January 16, 2020 at 16:07

New Autonomous Database features are being added all the time. For now, ADB for shared infrastructure supports 18c while 19c can be used only in preview mode.

The preview period for ADB 19c ended yeaterday: January 15th, 2020. From now on, it is no longer possible to provision new preview instances nor clone existing instances to a preview instance. However, existing preview instances will remain available until January 30th when the final termination process will happen. And now, we are awaiting for the Oracle 20c preview version.

So, what else is new in the Autonomous Database (Shared Infrastructure):

The DBMS_CLOUD REST API functions provide a generic API that lets you call any REST API with the following supported cloud services:

Oracle Cloud Infrastructure
Amazon Web Services
Azure Cloud
Oracle Cloud Infrastructure Classic

DBMS_CLOUD supports GET, PUT, POST, HEAD and DELETE HTTP methods. The REST API method to be used for an HTTP request is typically documented in the Cloud REST API documentation.

Check also the summary of the DBMS_CLOUD_ADMIN package and especially the CREATE_DATABASE_LINK procedure as this is the supported way to create a database link in ADB.

To run DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK with a user other than ADMIN you need to grant EXECUTE and CREATE DATABASE LINK privileges to that user. For example, run the following command as ADMIN to grant privileges to JULIAN:


Behind the curtains, Oracle runs C##CLOUD$SERVICE.DBMS_CLOUD_DBLINK_INTERNAL.

Note that packages like DBMS_CLOUD, DBMS_CLOUD_ADMIN, DBMS_CLOUD_CORE, DBMS_CLOUD_DBLINK, etc. are owned by C##CLOUD$SERVICE (owns 23 packages), not by SYS!

In ADB, there are few restrictions for some PL/SQL packages. Oracle have removed the UTL_TCP package.

UTL_HTTP Restrictions:

Connections through IP addresses are not allowed
– Only HTTPS connections are allowed (HTTP and HTTP_PROXY are disallowed)
– The only allowed ports are 443 and 8443

UTL_SMTP Restrictions:

– The only supported email provider is Oracle Cloud Infrastructure Email Delivery service
– Mail with an IP address in the host name is not allowed
– The only allowed ports are 25 and 587


– Granting ACL privileges on IP addresses is not allowed
– The http_proxy and use_passwords ACL privileges are not allowed

This Oracle example is using DBMS_CLOUD.SEND_REQUEST in order to create and delete an Oracle Cloud Infrastructure Object Storage bucket.

Here are 2 new additions to the ADB feature list:

1. Access Control Lists Honored By ADB Built-in Tools

You can control and restrict access to your Autonomous Database by setting network access control lists (ACLs). When you provision your Autonomous Database you can either choose the database to be accessible from all IP addresses or you can restrict access to your database to a whitelisted set of clients. You can change your decision after provisioning and set or change the access rules.

Here are 3 links for additional details:

1. VCNs and Subnets for details on Virtual Cloud Networks (VCN).
2. Oracle Services: Service Gateway Access for details on setting up a Service Gateway.
3. Transit Routing: Private Access to Oracle Services for details on Transit Routing.

2. Send Emails from ADB using UTL_SMTP

There are 4 steps you must follow in order to send an email from ADB:

1. Configure Email Delivery Service
2. Allow SMTP Access for ADMIN via an Access Control Entry (ACE)
3. Create a PL/SQL Procedure to Send Email
4. Send a Test Email

Check How to Send an Email using UTL_SMTP in Autonomous Database by Can Tuzla for all the details and examples.

At the end, note that now we have the “Next Maintenance” field which shows the date and time for the upcoming maintenance window. All ADB instances are automatically assigned to a maintenance window and different instances can have different maintenance windows.