The new 23c version of the Oracle Database comes with 3 new database architecture features: all targeted towards both DBAs and Developers. There are also new Oracle certifications which might be of interest to both parties in the sense that after OCM 12c, there has not been any (or clearly upcoming) master certification with the newer releases. Let me list the 3 new architecture changes in 23c and their restrictions and limitations, and at the end the provide a list of the new (free) OCI/Database certifications:
Let us have a look at the three database architecture changes in Oracle 23c:
1. The first one is reservable columns. The idea is the introduction of lock-free reservations which enables concurrent transactions to proceed without being blocked on updates of heavily updated rows. Lock-free reservations are held on the rows instead of locking them. Lock-free reservations verifies if the updates can succeed and defers the updates until the transaction commit time. In the beginning this new column type was called escrow columns but the official name is now reservable columns.
There are several restrictions on reservable columns. Most importantly, reservable column can be specified for only Oracle numeric data type (NUMBER, INTEGER, and FLOAT), indexes are not supported on reservable columns, you cannot partition them, and you can have at most 10 on a table.
For more details on reservable columns, check these 2 articles:
Lock-free reservation in 23c: how to start with by Ulrike Schwinn and Stephane Duprat
Testing Oracle 23c Lock-Free Reservations by Eduardo Claro
2. The second architectural change is wide columns. The maximum number of columns allowed in a database table or view has been increased to 4096. This new feature is handled via a new parameter called MAX_COLUMNS which has a default value of STANDARD. The new value EXTENDED allows you create a table with up to 4096 columns.
The COMPATIBLE initialization parameter must be set to 23.0.0 or higher in order to set MAX_COLUMNS = EXTENDED. In RAC, multiple instances must use the same value.
The increased column limit of 4096 is supported by Oracle Database 23c clients such as SQL*Plus, OCI, JDBC-OCI, unmanaged ODP.NET, and open source drivers. Older client versions (pre-Oracle Database 23c) do not support the increased column limit and cannot access more than 1000 columns in a table or view.
Check the following 2 articles on extended columns:
MAX_COLUMNS : Increase the Maximum Number of Columns for a Table (Wide Tables) in Oracle Database 23c by Tim Hall
Oracle Database 23c Free – Wide Tables by Andy Rivenes
3. The third architectural changes is Unrestricted Direct Loads. Prior to this feature, after a direct load and prior to a commit, queries and additional DMLs were not allowed on the same table for the same session or for other database sessions. This enhancement allows the loading session to query and perform DML on the same table that was loaded. Other sessions are also able to concurrently perform direct loads and DML. Rollback to a savepoint is also supported.
Check out this example by Tim Hall: Removal of Touch-Once Restriction after Parallel DML (Unrestricted Direct Loads) in Oracle Database 23c
Here is what you can do immediately after a direct-path insert:
Query the same table multiple times within the same session
Perform conventional DML (serial and parallel) on the same table within the same session
Multiple direct loads on the same table in the same session
However, the restrictions on the above operations still apply when:
The tables are IOT or clustered tables – the table must be a heap table
The tablespace is not under Automatic Segment Space Management (ASSM) – temporary tables are not under ASSM, so this includes them as well
The tablespace is of uniform extent – a rare case when uniform extent TSs are a disadvantage
The restrictions on multiple queries and DML/PDML operations as well as the restriction on multiple direct-path inserts in the same session can be reinstated when needed by including the NO_MULTI_STATEMENT hint in SQL statements.
For what is deprecated/desupported in 23c, check Tim Hall’s article Deprecated and Desupported Features in Oracle Database 23c.
With all these multiple architectural changes and new features after 12c, it makes sense to look into the new Oracle database certification paths.
There is a free certification for OCI from June 1 – August 31, 2023. Out of all these exams, there are 3 professional certification on the Oracle database which are worth pursuing:
1Z0-931-23 | Oracle Autonomous Database Cloud Professional
1Z0-1094-23 | Oracle Cloud Database Migration and Integration 2022 Professional
1Z0-1093-23 | Oracle Base Database Services 2023 Professional
How to prepare for these ones? Check what Alex Zaballa has to say. After all, he is the King of Oracle Certification!
About the OCM certification, may be you are wondering on what is happening to that one? Check what Bruno wrote few months ago! Recently, I was asked which is easier: (1) become an Oracle Certified Master or (2) become a Master of Oracle Administration. You decide for yourself!
Read MoreJulian Dontcheff’s Database Blog
