Mar 22, 2022 9:25:12 AM by Clarisa Maman Orfali
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.
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:
When would you use these 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:
The following actions are not allowed on Oracle 21c immutable tables:
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:
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.
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:
On the other hand, there are also restrictions for external tables in memory:
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.
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 220.127.116.11.
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:
CREATE INDEX [schema.]empno_idx ILM_POLICY
Like all clauses, there are restrictions, and in this case they are:
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:
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.
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 the JSON_EXISTS or JSON_QUERY operators.
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:
Support for these Oracle 21c new features in Toad makes it even easier for database professionals to optimize overall effectiveness and productivity.
Get Toad for Oracle Base Subscription today
Subscription / eStore: buy up to 10 licenses at a time, get auto update, support, announcements/invites to education.
Talk to our professionals: demos, custom solutions, volume discounts.
Not ready to buy? Get Toad for Oracle a 3rd way ... try it free for 30 days.
Tags: Toad for Oracle
Written by Clarisa Maman Orfali
Clarisa is a System Engineer with more than 24 years of experience as a developer, teacher, and consultant in Information Technology. She was a Founder and CEO between 2013 to 2020 at ClarTech Solutions, Inc., a consulting firm specializing in Oracle APEX and Open Source technologies. Clarisa entered the world of Oracle technologies in 2009. After a few years, she discovered the power of application development with Oracle Application Express (APEX) and specialized in that area.
Clarisa is from Argentina, and she is living and working in Irvine, California, in the United States. She's also a Co-Founder of the Argentina Oracle User Group (AROUG). In addition, she had actively participated in large and popular events such as the OTN Tour Argentina and Oracle APEX Tour Latin America after called Oracle Developer Tour. She's also authored the first three Spanish books about Oracle Application Express and recorded and published several Online Video Courses.
She has a great passion for Oracle technologies and wants to expand that passion and influence, transferring her knowledge and experience to the entire Latin American and worldwide Oracle communities. You can always contact her through her Twitter account (@Clari707).
Clarisa loves spending time with her family by enjoying outdoor activities with her two adored children, Melanie and Nicolas, as well as hanging out with her friends. Also, she loves traveling with her husband Julio worldwide and to takes new challenges at every moment of her life.