Oracle Database 23ai is the next long-term support release and offers over 300 new features. Originally named Oracle Database 23i, it was rebranded to Oracle Database 23ai with a heavy focus on artificial intelligence (AI). To optimize many of the database’s key functions, Oracle Database 23ai uses AI for improved timing estimates and resource costings. There are also new features such as AI Vector Search to allow data queries on semantics and not keywords.
For developers, Oracle 23ai introduced JSON Relational Duality to leverage both relational data and the simplicity of JSON within a single app. A new developer role makes it easy to quickly assign developers privileges required for application development.
For SQL, there are new features like data types, aliases for GROUP BY clauses, joins for DELETE and UPDATE statements, IF [NOT] EXISTS DDL clause, and FUZZY_MATCH, PHONIC_ENCODE to mention a few.
There are some behavior changes starting with Oracle Database 23ai. For example, after installing the software-only Oracle Database, the Oracle home can be configured to read-only mode, SYSDATE and SYSTIMESTAMP can be managed separately in each pluggable database, and newly created databases use BIGFILE as the default for the SYSTEM, SYSAUX, and USER tablespaces.
Some features will be depreciated in Oracle 23ai, so be prepared to adapt around that. The original Export Utility (EXP), Oracle Enterprise Manager Database Express (EM Express), 10G database password verifier, 32-bit Oracle Database clients, and Database Upgrade Assistant (DBUA) are all desupported. For the full list consult the Oracle documentation under Desupported Features in Oracle Database 23ai (https://docs.oracle.com/en/database/oracle/oracle-database/23/upgrd/oracle-database-changes-deprecations-desupports.html).
These are just a few of the features I’m excited about in Oracle Database 23ai. I would suggest checking out Oracle Database Release 23 New Features documentation for the full list.
As an Oracle Database Administrator, I would like to point out a few of the new features that stuck out for me.
IF [NOT] EXISTS DDL Clause
The IF [NOT] EXISTS DDL clause makes the SQL command easier to create or drop objects that exist or do not exist without receiving an error. This SQL command works on many different types of objects such as users, tables, views, procedures, and sequences.
Below is a comparison of how this feature changes things, starting with what it looks like pre-Oracle 23ai:
SQL> create table test_table (id number);
create table test_table (id number)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> create user testuser identified by Testuser!123;
create user testuse1 identified by testuser
*
ERROR at line 1:
ORA-01920: user name 'TESTUSER' conflicts with another user or role name
And what it looks like with the new Oracle 23ai feature:
SQL> create table if not exists test_table (id number);
Table created.
SQL> drop table if exists test_table;
Table dropped.
SQL> create user if not exists testuser identified by Testuser!123;
User created.
SQL> drop user if exists testuser cascade;
User dropped.
Create Read-Only Users in the Local PDB
Oracle Database 23ai has a new clause for user accounts to limit privileges to be either read only or read write. With read only access, the user cannot execute any write operations. The mode is easy to alter between read write or read only. To identify this mode, the DBA_USERS dictionary view has a new READ_ONLY column.
In the example below, the user is granted the new DB_DEVELOPER_ROLE role. This new role gives the full set of system, object, predefined roles, PL/SQL package, and tracing privileges required for application development.
Create user example -
SQL> create user testuser identified by Testuser!123 quota 10M on users read only;
User created.
SQL> grant db_developer_role to testuser;
Conn testuser/ Testuser!123
SQL> create table test_table (id number);
*
ERROR at line 1:
ORA-28194: Can perform read operations only
It is easy to change between the modes with:
SQL> alter user testuser read write;
SQL> alter user testuser read only;
Oracle 23ai Schema Privileges
The new schema privileges allow for easier grants at the schema level, making it simpler to secure database privileges. A user account can be granted privileges on another schema. When new objects such as tables or views added to the schema are created, schema-level privileges can dynamically adapt to the new changes. The following accounts are excluded from schema privilege grants: SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSDG, and SYSKM.
To easily find the information on the schema privileges grants, there are new dictionary views - DBA_SCHEMA_PRIVS, ROLE_SCHEMA_PRIVS, USER_SCHEMA_PRIVS, SESSION_SCHEMA_PRIVS, and V$ENABLEDSCHEMAPRIVS.
Here is an example of how to grant schema-level privileges in Oracle 23ai:
grant select any table on schema testuser to testuser2;
grant insert any table on schema testuser to testuser2;
grant update any table on schema testuser to testuser2;
grant delete any table on schema testuser to testuser2;
SELECT Without FROM Clause
The SELECT Without FROM Clause allows for excluding the FROM DUAL in many cases.
Some examples are:
SELECT sysdate;
SELECT 1+2;
SELECT my_fuction();
Improved TNS Error Messages
The TNS error messages have been enhanced with detailed information to troubleshoot, such as the cause of the error and the corresponding action.
I hope you enjoyed my little introduction to the many new features Oracle Database 23ai has to offer. Please take the time to dig into all the new features for Oracle Database 23ai to find the ones that fit best for your line of work.
Want to connect with the author or explore other database resources? Visit Datavail.com for more.