I just finished reading the Oracle database 12.2 new features manual. I have postponed looking at 12.2 until now because for a long time 12.2 was not available for download even though it was available in Oracle’s cloud. Once the download became available I installed 12.2 in a test virtual machine (in Oracle VM) but did not get any further than that. But, the first quarterly update of 12.2 is supposed to come out soon so I thought that I would at least read about the new features to start learning about it.

Partitioning – alter table to partitioned table

In 12.2 there is a single “alter table” command to transform a regular table into a partitioned one. The “alter table” includes the partition type and key clauses, and it simply alters the table. I didn’t manage to get an answer on how it is done behind the scenes, my guess is that this command creates a new partitioned table and moves all the data into it (similar to the “alter table … move” command).

Partitioning – create table for exchange

Until now, when we wanted to create a table to perform “exchange partition” we had to create the table ourselves. Usually it worked, but sometimes it didn’t. This was caused usually because the partitioned table had unused columns or something similar. In 12.2, a new “create table” syntax was introduced to allow us to create a table based on an existing partitioned table, and Oracle will take care of the structure so we won’t have any problems with the exchange.

Standby Database – In-Memory option

12c introduced the in-memory option, however it wasn’t available in active standby. Now it is available, allowing us to configure in-memory column store on the active standby side (with same or different memory size) for reports and analytic.

In-Memory – dynamic resizing

In 12c, when Oracle introduced the In-Memory option, the size of the In-Memory column store was static. If we wanted to change its size we needed to restart the database. In 12.2 we can increase the column store size dynamically. We can’t reduce the size dynamically, but who wanted to decrease memory sizes anyway, right?

In-Memory – fast start

Another improvement of the In-Memory option is that now Oracle saves the data stored in the In-Memory column store in a lob on the disk. This allows the database to populate the In-Memory cache much quicker when the database starts. Without this feature, the database needs to work very hard to read the data from the regular tables, reconstruct it, compress and more before storing it in the column store.

Security – online encryption

Encryption is important, but until now, had to be done offline for existing data. Oracle 12.2 allows online encryption and re-key operations. The command will effect a datafile, copying it to a new encrypted one, then will change the database to start using the new encrypted file, and everything will be done online.

Security – internal tablespace encryption

Now it is possible to encrypt internal tablespaces (system, sysaux, undo, etc) as well.

Security – DB vault simulation

DB vault is a great security tool, but it is quite a big project to implement it. When implementing DB vault on an existing application, there is always the risk that we missed things and the application will break. DB vault 12.2 can be enabled in simulation mode. While in simulation mode, nothing will break, instead, DB vault will log every command that should have been blocked. That way we can make sure that everything works and fix all the problems before the actual implementation.

Dictionary – long identifiers

You’ve probably heard about this one. It is so annoying that object and column names are limited to 30 bytes, right? Well, not anymore, 128 bytes is the new limit!

Non-PDB Support

In 12.2 the old non-PDB architecture still exists but is not supported anymore. The plan is to remove this architecture completely in the next major release. Without paying the extra money for this feature, we can still use this architecture, but with a single PDB. For additional PDBs, a specific license needs to be purchased.

PDB – resource management

One of the fears when using PDBs to consolidate different databases into the same instance, is that one or a few PDBs will consume all the resources and leave none to the others. In 12.2 there is a very simple command to limit IOPS per PDB (and I think they mentioned a similar thing for CPU and memory as well). To use that, simply execute the command: “alter system set max_iops=…” when connected to a specific PDB.

PDB – hot clone (finally!)

The concept of cloning a PDB is great. However, in 12c, in order to clone a PDB it should be open in read-only mode, and this prevents us from using the feature on production environments. In 12.2, the clone is hot and done when the PDB is in read-write mode. It is even possible to keep updating the cloned PDB with changes as long as the cloned PDB itself is in read-only mode.

PDB – local undo

When using the PDB architecture, the PDBs share several resources, including the undo tablespace. In 12.2 we can set the environment so every PDB will contain its own undo tablespace. This is done by the “alter database local undo on” command.

PDB – flashback

Another great feature of PDB is that we can now flashback a single PDB without affecting all other PDBs in the same instance.

PDB – characterset

And last feature in the PDB architecture, we can set a different characterset per PDB. There is a requirement for that though, the root has to use AL32UTF8 (which is the super-set of all others, so it kinda make sense, it is also the default characterset now).

Index Monitoring

Index monitoring before 12.2 was quite simple and had many problems. In 12.2, Oracle improved that a lot (finally!). Index monitoring is now enabled by default, tracks the usage at execution level (as opposed to parse level) and keeps a lot of information, such as number of times the index was used, last usage time and much more. All the information is recorded in the DBA_INDEX_USAGE data dictionary view.

Materialized Views – real time refresh

Mv real time refresh allows queries against the base table to be rewritten and use the MV, even if it is not up-to-date, while retrieving the up-to-date data. Oracle goes to the MV log, gets the data from it and merges it into the MV to return the correct results. This is done at the statement level, so it does not update the MV itself. If we query the MV directly, we can also use the FRESH_MV hint to do the same and get the up-to-date data.

External Tables – partitions

External tables are now can be partitioned by using a set of files (instead of the single one), each representing a table partition.