Month: November 2025

SQL | PLSQL

Annotations in Enterprise Environments: Advanced Use Cases

Annotations (introduced in March’s post) become particularly powerful in enterprise environments where data governance, self-service tooling, and schema documentation are critical. Let’s look at advanced patterns.

Data classification for security:

ALTER TABLE customers MODIFY (
    ssn         ANNOTATIONS (ADD DataClass 'PII', ADD Sensitivity 'Critical', ADD EncryptionRequired 'true'),
    email       ANNOTATIONS (ADD DataClass 'PII', ADD Sensitivity 'High'),
    birth_date  ANNOTATIONS (ADD DataClass 'PII', ADD Sensitivity 'Medium'),
    customer_id ANNOTATIONS (ADD DataClass 'Internal')
);

A data catalog tool can query USER_ANNOTATIONS_USAGE to automatically generate a data classification report without anyone maintaining a separate spreadsheet.

Deprecation management:

ALTER TABLE legacy_orders ANNOTATIONS (ADD Deprecated 'true', ADD DeprecatedSince '2024-01-01', ADD MigratedTo 'orders_v2');

CI/CD pipelines can query for deprecated objects and fail builds that reference them.

UI metadata for low-code applications:

ALTER TABLE employees MODIFY (
    first_name ANNOTATIONS (ADD UILabel 'First Name', ADD DisplayOrder '1', ADD UIRequired 'true'),
    last_name  ANNOTATIONS (ADD UILabel 'Last Name',  ADD DisplayOrder '2', ADD UIRequired 'true'),
    salary     ANNOTATIONS (ADD UILabel 'Annual Salary', ADD UIReadOnly 'true', ADD UIFormat 'currency')
);

Low-code platforms like Oracle APEX can read these annotations to auto-generate forms with the correct labels, ordering, and validation.

Queryable catalog:

-- Find all columns marked as PII across the schema
SELECT owner, object_name, column_name, annotation_value AS data_class
FROM   dba_annotations_usage
WHERE  annotation_name = 'DataClass'
AND    annotation_value = 'PII'
ORDER BY owner, object_name;

Annotations turn the Oracle data dictionary into a live, queryable governance layer — no external catalog required.

SQL | PLSQL

SQL Domains in Practice: Real-World Patterns

We introduced SQL Domains in February. Let’s look at how they work in realistic enterprise scenarios — because the design decisions around domain adoption matter as much as the feature itself.

Building a domain library:

Think of SQL Domains as a shared type system for your database. The first step is identifying recurring patterns:

-- Financial amount
CREATE DOMAIN d_amount_usd AS NUMBER(15,4)
    DEFAULT 0
    CONSTRAINT ck_amount_positive CHECK (VALUE >= 0);

-- Percentage (0-100)
CREATE DOMAIN d_percentage AS NUMBER(5,2)
    CONSTRAINT ck_pct_range CHECK (VALUE BETWEEN 0 AND 100);

-- Status fields
CREATE DOMAIN d_active_flag AS CHAR(1)
    DEFAULT 'Y'
    CONSTRAINT ck_active_flag CHECK (VALUE IN ('Y', 'N'));

-- Email
CREATE DOMAIN d_email AS VARCHAR2(254)
    CONSTRAINT ck_email_fmt CHECK (VALUE LIKE '%@%.%');

-- ISO country code
CREATE DOMAIN d_country_code AS CHAR(2)
    CONSTRAINT ck_country_upper CHECK (VALUE = UPPER(VALUE));

Using domains across tables:

CREATE TABLE invoices (
    invoice_id      NUMBER PRIMARY KEY,
    amount          d_amount_usd,
    tax_rate        d_percentage,
    customer_email  d_email,
    ship_country    d_country_code,
    is_paid         d_active_flag
);

CREATE TABLE purchase_orders (
    po_id           NUMBER PRIMARY KEY,
    total_value     d_amount_usd,     -- same domain reused
    billing_email   d_email,          -- same domain reused
    origin_country  d_country_code    -- same domain reused
);

Viewing domain usage across the schema:

SELECT table_name, column_name, domain_name
FROM   user_tab_columns
WHERE  domain_name IS NOT NULL
ORDER BY domain_name, table_name;

Governance tip: Store domain definitions in a versioned migration script, just like table DDL. Treat them as first-class schema objects that need change management. When a domain constraint needs updating, the change propagates automatically to all columns using it.

Database, SQL | PLSQL

Blockchain Tables: What’s New in Oracle 23ai

Oracle introduced Blockchain Tables in Oracle 21c — append-only, tamper-evident tables where rows cannot be modified or deleted (until a configurable retention period expires). Oracle 23ai adds several enhancements to this feature.

Quick recap of Blockchain Tables:

CREATE BLOCKCHAIN TABLE audit_records (
    record_id     NUMBER,
    event_time    TIMESTAMP,
    user_name     VARCHAR2(100),
    action        VARCHAR2(500)
)
NO DROP UNTIL 365 DAYS IDLE   -- can't drop while rows exist within 365 days
NO DELETE UNTIL 90 DAYS AFTER INSERT  -- rows immutable for 90 days
HASHING USING "SHA2_512" VERSION "v1";

Each row includes a cryptographic hash of the previous row, forming a chain. Any attempt to modify historical data breaks the chain and is detectable.

Oracle 23ai: Immutable Tables

23ai also introduces Immutable Tables — a lighter-weight alternative to blockchain tables that provides tamper protection without the full cryptographic chaining overhead:

CREATE IMMUTABLE TABLE compliance_log (
    log_id    NUMBER,
    log_time  TIMESTAMP,
    log_msg   VARCHAR2(4000)
)
NO DROP UNTIL 180 DAYS IDLE
NO DELETE UNTIL 60 DAYS AFTER INSERT;

Key difference: Immutable Tables don’t maintain a hash chain — rows can’t be modified or deleted within the retention window, but the tamper detection mechanism is Oracle’s internal row-change tracking rather than cryptographic chaining. Simpler, lower overhead, still compliant for most audit requirements.

23ai Blockchain enhancements:

  • Counter-signing support for third-party verification
  • Improved DBMS_BLOCKCHAIN_TABLE.VERIFY_ROWS performance
  • Integration with Oracle’s SQL Firewall for combined protection

For regulatory compliance, financial audit trails, and healthcare record retention, these features provide database-level immutability without external blockchain infrastructure.

SQL | PLSQL

Oracle 23ai Lightweight Tables: When You Don’t Need Everything

Oracle 23ai introduces guidance and improvements around Lightweight Tables — a design philosophy and set of options for creating tables that omit expensive features when they’re not needed, resulting in lower overhead for specific workloads.

The concept isn’t entirely new, but 23ai formalizes and extends the options:

Heap tables without segment-level features:

CREATE TABLE event_log (
    log_time   TIMESTAMP DEFAULT SYSTIMESTAMP,
    log_level  VARCHAR2(10),
    log_msg    VARCHAR2(4000)
)
SEGMENT CREATION IMMEDIATE
NOCOMPRESS
NOLOGGING    -- skip redo logging (use only if data is recoverable from source)
NOMONITORING;

Deferred segment creation:

CREATE TABLE staging_area (
    id     NUMBER,
    data   VARCHAR2(4000)
) SEGMENT CREATION DEFERRED;
-- No space allocated until the first row is inserted

Index-Organized Tables (IOT) for key-based access:

For tables where you always access by primary key and rarely do full scans:

CREATE TABLE session_cache (
    session_token  VARCHAR2(64) PRIMARY KEY,
    user_id        NUMBER,
    expiry         TIMESTAMP,
    payload        VARCHAR2(4000)
) ORGANIZATION INDEX
  COMPRESS 1;

The primary key is the physical storage key — lookups are O(log n) with no separate index, and the data itself is B-tree ordered.

23ai enhancement: Oracle 23ai improves the optimizer’s ability to choose between heap, IOT, and external table access paths automatically, reducing the manual tuning burden for these decisions.

Recommendation: For staging tables, working sets, cache-like structures, and append-only logs, consider which table features you actually need. Removing what you don’t need is always faster than adding features you’ll never use.