Supported Oracle 21c new features in Toad© for Oracle 15.1

Quest© Toad© for Oracle 15.1 supports a variety of Oracle 21c new features. This overview walks through the basics of each of these supported features and offers some select use cases.

Support for blockchain tables

Oracle 21c database introduced new Blockchain tables. Blockchain is a shared and immutable ledger that facilitates the process of recording transactions and tracking assets in a business network. A blockchain table in Oracle 21c is a tamper-proof insert-only table with an associated table level and row-level retention period.

Briefly, a blockchain table works as follows: Each row except the first row is chained to the previous row in the chain using a cryptographic hash (SHA2-512). The hash is based on the data in the current row plus the hash value of the previous row in the chain. The hash is stored in the row's metadata. Therefore, the hash can verify that a row has not been tampered with because as soon as the data changes, the hash will calculate a different value.

You can add rows at the end of the table, but you can't update those rows. You can also disable row deletions altogether or allow rows to be deleted beyond a time threshold.

The table creation looks like a regular table except that you need to add the BLOCKCHAIN keyword to the table creation command.

CREATE BLOCKCHAIN TABLE BCT_demo1
(employee_id NUMBER, salary NUMBER)
NO DROP UNTIL 31 DAYS IDLE
NO DELETE UNTIL 16 DAYS AFTER INSERT
HASHING USING “SHA2_512” VERSION “v1”;

–NO DELETE LOCKED

There are three clauses to be aware of in the blockchain table:

• The DROPTABLE clause determines how long the table is protected from deletion. It only affects the drop table command. The table will still be dropped if a DROP USER CASCADE command is issued to drop the entire schema. NO DROP by itself means that the table can never be dropped. On the other hand, NO DROP UNTIL 31 DAYS IDLE means that the table can’t be dropped until no rows have been inserted for that number of days.
• The row retention clause determines how long each row will be protected from the NO DELETE This clause alone means that the rows are kept forever.
• The clause NO DELETE UNTIL 16 DAYS AFTER INSERT protects rows for the specified number of days. The minimum, in this case, is 16 days.

When would you use these tables?

• You need a tamper-proof table in your application that is an insert-only table.
• You want to take advantage of the trust associated with blockchain centrally instead of having multiple client applications that need to manage the blockchains individually. Using a blockchain table allows you to centralize that trust.
• You can add the trust associated with blockchain to existing applications without having to worry about recoding them.

Support for immutable tables

Another Oracle 21c new feature is immutable tables. These are insert-only tables in which existing data can't be modified. The operation to delete rows in the table is prohibited or restricted depending on the insertion time of the rows.

In addition, immutable tables protect data from unauthorized modifications by people inside the organization. This type of people includes database administrators and compromised users who have access to internal credentials. In addition, Oracle 21c immutable tables also prevent accidental data modification that can be caused by human error.

They differ from the blockchain table because their rows are not linked to each other as they are in blockchain tables.

As part of the definition of the immutable table, you must specify a retention period for the immutable table and the rows within the immutable table. When an immutable table, or row, exceeds its specified retention period, it is considered stale, and you may remove it from the database.

CREATE IMMUTABLE TABLE cmo_demo

(id NUMBER, name VARCHAR2(50), amount NUMBER)

NO DROP UNTIL 30 DAYS IDLE

NO DELETE UNTIL 20 DAYS AFTER INSERT;

You can perform the following actions on immutable tables:

• Increase the retention period
• Increase the retention period for rows
• Delete a row that is beyond its retention period
• Drop an immutable table that is beyond its retention period

The following actions are not allowed on Oracle 21c immutable tables:

• Modify rows
• Delete a row that is within its retention period
• Drop table that is within its retention period
• Modify the column definitions
• Truncate the immutable table
• Convert an immutable table to a regular table or vice versa

Support for password rollover time in profiles (gradual database password changes)

With Oracle 21c, an application can change its database passwords without an administrator having to schedule downtime.

To enable the new user profile parameter, you must configure a non-zero limit in the PASSWORD_ROLLOVER_TIME parameter. You can set this parameter using the CREATE PROFILE or ALTER PROFILE statements.

After setting the time for the password rolling over a period, you can use the ALTER USER statement to change the user’s password and propagate the new password to all clients before the time specified in the PASSWORD_ROLLOVER_TIME parameter expires.

The value of this parameter is expressed in days. Therefore, if you want to specify hours, you must define them as a fraction of a day. So, for example, if you set 8 hours, it would be 8/24.

The granularity of the PASSWORD_ROLLOVER_TIME limit value is one second. So, for example, you can have a limit of two hours plus five minutes and three seconds by providing an expression like this: ((2/24) + (5/1440) + (3/86400) ).

It is important to mention that the default setting for PASSWORD_ROLLOVER_TIME is 0, which means that gradual password rollover is disabled.

Like any parameter, the PASSWORD_ROLLOVER_TIME in Oracle 21c has its limitations:

• To disable the parameter, you must configure it with a zero value; to enable it, configure it with a value greater than zero
• The value of the parameter must be positive
• The minimum value of the parameter is one hour
• The parameter value can't exceed 60 days

If you want to see which users are currently in the password rollover period, perform an SQL query by selecting the ACCOUNT_STATUS column from the DBA_USERS data dictionary view. The value of the column must be ROLLOVER.

The password rollover period begins the moment the user changes their password.

Support table IN-MEMORY clause for external partitioned tables

In this Oracle 21c new feature, support for in-memory external tables has been enhanced to include partitioned external tables.

The IN-MEMORY clause is supported at the table and partition level of a partitioned external table or hybrid external table. The IN-MEMORY table-level attribute applies to all partitions for hybrid tables, whether internal or external.

A hybrid partitioned table is a partitioned table with internal partitions that reside in the database and external partitions that reside in external files outside of the database.

In-memory external tables are helpful in the following cases:

• If you need to repeatedly scan data that is short-term and does not require retention in the Oracle 21c database
• If external data needs to be joined with relational data for fast analytical processing
• For data that is accessed through analytic queries both in Oracle 21c Database and external tools, and that does not need to be materialized in the database storage

On the other hand, there are also restrictions for external tables in memory:

• Sub-partitions are not supported for in-memory external tables
• Some IN-MEMORY subclauses for external tables are not valid, including the column clause, distribute clause and priority clause
• Join groups are not supported for in-memory external tables
• In-memory optimized arithmetic does not support external tables
• IM expressions are not supported for in-memory external tables
• In-memory external tables do not support the DISTRIBUTE … FOR SERVICE clause for Oracle Active Data Guard instances

The advantage of in-memory external tables is that you don't have to materialize the table in the Oracle 21c database. Instead, you can directly populate an external table from your external files to the IM column store. You can then operate on that data with internal data and take advantage of the rich set of Oracle 21c new features to query and analyze that data.

Support ILM clause on indexes

With Information Lifecycle Management (ILM), you can manage data in the Oracle 21c Database using the rules and regulations that apply to that data. This solution deals with all data in an organization.

In many cases, organizations need their data to be kept for 30 years or more. Oracle 21c can automatically do things with the data after it ages, like move it to a lower-cost tablespace. This feature has been supported on tables since Oracle version 12.1.0.2.

In this version, the database now supports indexes. Lifecycle management of data in indexes allows organizations to set policies that automatically optimize index storage.

The index ILM clause has some options that the table ILM clause does not:

• You can use the index_ilm_clause to add or remove an ILM policy on an index
• You can add an ILM policy to an index after creating the ALTER INDEX statement
• When creating an ILM policy index, you can only add a new policy. To add more policies to an index or modify existing policies on the index, you must use the ALTER INDEX
• You can't modify an ILM policy at the index partition level. The index level change will be cascaded to all partitions.

Example:

CREATE INDEX [schema.]empno_idx ILM_POLICY

Like all clauses, there are restrictions, and in this case they are:

• You can't add an ILM policy on cluster indexes and IOTs
• You can't add an ILM policy on domain indexes and bitmap indexes

Support for mandatory profiles

When you specify the MANDATORY keyword when creating a profile, you create a mandatory profile in CDB$ROOT format.

You can use the mandatory profile to enforce password complexity requirements for database user accounts across the entire CDB or individual PDBs using the password_verify_function profile parameter.

The mandatory profile adds the password complexity requirement to the existing profile limits for common and local users. A PDB administrator can't remove the password complexity requirement and then allow users to set shorter and weaker passwords; because mandatory profiles, like common profiles, can only be modified in CDB$ROOT.

You can only use the password_verify_function and password_grace_time parameters of the profile to define the mandatory profile limits.

You would use the password_grace_time profile parameter to specify a grace period for user accounts that violate mandatory password complexity requirements and whose passwords need to be changed. User accounts imported using the datapump utility are checked for password compliance with the mandatory profile and are forced to change their passwords. If the password is not changed within the grace period, users will not be able to reconnect.

The default value for password_verify_function is null. The default value for password_grace_time is 0.

Create a mandatory profile:

CREATE MANDATORY PROFILE c##cdb_profile LIMIT PASSWORD_VERIFY_FUNCTION my_mandatory_function CONTAINER = ALL ;

Apply mandatory profile to all CBD:

You must be inside CDB$ROOT to execute this statement.

ALTER SYSTEM SET MANDATORY_USER_PROFILE=c##cdb_profile;

Apply the Mandatory Profile to an Individual PDB:

MANDATORY_USER_PROFILE=c##cdb_profile;

There are some restrictions to keep in mind. One of them is that only ordinary users who have been granted the ALTER PROFILE privilege of the system can modify or delete the mandatory profile, and they can only do it from the CDB root. Another restriction is that only a common user who has been granted ALTER SYSTEM privilege or has administrator privilege as SYSDBA can modify the MANDTORY_USER_PROFILE in the init.ora file.

Support for multi-value indexes on JSON columns

In Oracle 21c, there is a new index creation syntax, CREATE MULTIVALUE INDEX, which allows us to create a functional index on arrays of strings or numbers within a column of type JSON. Each unique value within the array will be converted to a search index entry.

Indexes based on multivalued functions allow us to index multiple scalar values in JSON documents stored in the JSON data type. This avoids the need for full JSON scans to find values within arrays in JSON columns when searching with theJSON_EXISTS or JSON_QUERY operators.

Support for JSON datatype

The JSON data type is an Oracle-optimized binary JSON format called OSON. This data type is optimized for query processing and DML. It can also lead to significant improvements in database performance for processing JSON data.

This means that instead of parsing JSON on read or update operations, the parsing only happens on the insert, and the JSON is kept in an internal binary format which makes access much faster. This can result in 4-5 times faster read and update operations, and 20X-30X speed improvement for updates of extensive JSON documents.

The following areas in Toad have been changed to support the JSON datatype:

• Create/Alter Table – A new JSON option appears in the column list.
• Export DDL – JSON datatypes should be supported in table DDL.
• Data Grids – JSON can be selected already in grids, but it comes back as BLOB so user can't see the data. An option has been added to View -> Toad Options -> Schema Browser -> DataTab so that JSON data can be converted to Varchar2 or BLOB, and either formatted or compact.
• Popup Text Editor – The popup text editor, when called from Schema Browser -> Tables/Views/MViews-Data-tab, now supports editing of JSON datatypes.  
• Export Dataset – When called from Schema Browser-Tables/Views/MViews-Data, data is exported in a readable format, and insert statements produce valid SQL.
• Compare Table Data, Compare Multiple Tables – Now supports JSON datatype

Support for these Oracle 21c new features in Toad makes it even easier for database professionals to optimize overall effectiveness and productivity.