Mastering Oracle Database 23ai New Features

Explore Oracle database 23ai features, including production vs free edition licensing, multi-tenant and DBCA improvements, and practice labs with autonomous database ATP, OCI DB system, and on-premises free edition.

Prepare your course environment by importing a linux-based 23ai vm in VirtualBox, enabling host-only networking and a shared folder, then verify HR and SOE access via SQL Developer.

Shrink small file tablespaces in oracle database 23.ai to reclaim unused space and combat fragmentation, using online or offline shrink with analyze mode and target sizing.

Create and populate a small file table space named Reclaim TS, analyze potential space savings from fragmentation, then shrink the table space to reclaim unused space while monitoring progress.

Enable hybrid read-only mode for PDBs; common users see read-write, locals see read-only, and open the PDB in hybrid read-only from the root as sys, then revert to read-write.

Demonstrate hybrid read-only mode for pluggable databases in Oracle Database 23.ai by using common and local users to perform or restrict modifications, and verify modes via V$PDBS and V$Container topology.

Explore Oracle Database 23ai blockchain tables, an append-only, tamper-resistant central ledger with cryptographic hashing and retention clauses such as no drop and no delete, plus optional signatures and SQL access.

Explore blockchain table features in Oracle Database 23ai, including row versions, user chains, long idle retention time, flashback data archive integration, and countersignature and delegate signer for verifiable data integrity.

Practice using blockchain tables in Oracle Database 23.ai to create and alter blockchain tables, manage retention thresholds, and implement user chains and row versions for secure, tamper-resistant data history.

Discover how lock-free reservations handle high-concurrency updates to a reservable quantity. Learn about the journal table, reservable columns, and constraints that ensure correct QOH updates.

Explore look-free reservations in Oracle Database 23ai by simulating concurrent ticket bookings, using a reservable tickets left column and a reservation journal to prevent blocking.

Discover how fast ingest in Oracle database 23ai speeds high-velocity inserts by buffering in the large pool, bypassing redo and cache, with optional mem_optimize for write.

Demonstrate fast ingest in Oracle Database 23.ai by creating a mem-optimize-for-write table, inserting 1000 rows, flushing to disk, and verifying data, memory, and high-water-mark behavior.

Explore wide tables in Oracle Database 23ai, covering Y-tables with up to 4096 columns, their features and benefits, and best practices for indexing and performance in data warehousing and analytics.

Create and manage wide tables in Oracle Database 23ai by adjusting the maxColumns parameter to extended, then revert to standard while handling the recycle bin and purge options.

Explore the Oracle 23ai boolean data type in SQL and PL/SQL, creating tables with boolean columns, using seven true and false syntaxes, and filtering and converting booleans.

Oracle 23ai introduces if not exists and if exists clauses to manage create, drop, and alter statements with object-level presence checks for robust, repeatable deployments.

Describe automatic transaction rollback and its use cases, and configure priorities, wait times, and rollback modes. Monitor events with v$session, v$stat, and the alert log to observe blocking sessions.

Learn how Oracle Database 23.ai automatically rolls back blocking transactions based on priority, using configurable wait times, PDB level parameters, and system views to monitor rollback behavior.

Explore the DML returning into clause in Oracle 23ai, using insert, update, and delete with bulk collect, dynamic SQL, and aggregation to capture generated values and row counts.

Learn time and date handling changes in Oracle Database 23.ai, including efficient time zone upgrades and linking sysdate and systime stamp to the database time zone with timeAtDBTimeZone.

Demonstrate using SysDate and SysTimeStamp with dbTimeZone to enable database timezone. Observe that SysDate and SysTimeStamp follow the database timezone (UTC) rather than OS clock, and note ORA30079 restriction.

Explore aggregation and analytic functions on interval data types in Oracle Database 23AI, including min, max, sum over, and avg over interval columns, with steps from table creation to cleanup.

Develop practical skills with direct joins for update and delete statements in Oracle Database 23ai, using from-clause joins for cleaner, faster SQL.

Master Oracle Database 23ai by learning how direct path loads lift restrictions in ASSM uniform extent table spaces, allowing multiple queries and DML in one transaction, unlike non-ASSM spaces.

Oracle Database 23.ai table value constructors (TVC) let you define multiple rows in a single values clause, enabling insert, select, and merge with atomic, network-efficient execution.

Explore table value constructors (TVC) in Oracle Database 23ai to insert multiple rows, and use TVC in select, with, and merge statements with a color hex example, then clean up.

Practice default on null for insert and update in Oracle Database 23ai by creating a table and testing how defaults apply to nulls, including trigger overrides.

Learn about unrestricted parallel DML in Oracle database 23.ai, enabling multiple DML operations in a session, outlining improvements, enabling and disabling techniques, and benefits for large tables and data warehousing.

Learn how Oracle database 23ai lifts direct load restrictions, enabling multiple queries and DML on the same heap table in a session with multi-append and automatic segment space management.

Explore Oracle database 23ai annotations that attach structured, machine-readable metadata to tables, views, and columns, enabling UI labels, classification tags, PII indicators, and searchable, JSON-ready metadata without downtime.

Create, modify, and query annotations using the new annotations clause in Oracle Database 23.ai across tables, views, indexes, and columns, using json-formatted key-value and name-only annotations.

Create two users and objects in user1 schema, grant system privileges on that schema via a role to user2, and verify schema privileges across schemas in Oracle Database 23.ai.

Explore Oracle database 23.ai's sql firewall, a live in-line security feature that blocks unauthorized sql with a positive security model, logs violations, and integrates with DataSafe for centralized management.

Learn to enable and use sql firewall in Oracle Database, simulate trusted and untrusted workloads, capture and review logs, generate an allow list, and enforce block mode to handle violations.

Explore TrueCache, an in-memory, read-only cache in Oracle Database 23.ai that reduces query response times. Learn deployment options and steps to configure, including uniform and partitioned setups and JDBC.

Explore ai vector search in Oracle Database 23ai, from embedding data and vector columns to similarity search with hnsw and ivf indexes, enabling semantic search and rag pipelines.

Demonstrate AI vector search in Oracle Database 23AI by loading an ONNX embedding model, creating a vector column, inserting product descriptions, and performing cosine similarity searches.

Adds PDB open-order priorities, read-only sessions, and a DB developer role to streamline development for Oracle Database 23.ai. It adds column-level auditing, Microsoft IntraID passwordless authentication, and 1024-byte passwords.