Exploring what is available in Oracle Autonomous Database resource

Oracle Autonomous Database comes with a highly optimized and secure architecture managed automatically by Oracle. While most administration tasks are automated, DBAs and developers can still explore several internal database views to better understand the environment.

In this blog, we will explore:

• Default users available in Autonomous Database
• Locked vs Open accounts
• Available tablespaces
• SGA and PGA memory configuration
• What these components mean internally

---

Checking Database Users

One of the first things many Oracle professionals explore is the DBA_USERS view.

SELECT username, account_status
FROM dba_users;

Output

USERNAME                  ACCOUNT_STATUS
_________________________ _________________
APEX_PUBLIC_ROUTER        OPEN
ORDS_PLSQL_GATEWAY        OPEN
ORDS_PUBLIC_USER          OPEN
ODI_REPO_USER             OPEN
C##DATA$SHARE             OPEN
SYSRAC                    OPEN
SCOTT                     OPEN
ADMIN                     OPEN
ODI$PROXY                 OPEN
GRAPH$PROXY_USER          OPEN
OML$PROXY                 OPEN
RMAN$CATALOG              OPEN
SYSTEM                    LOCKED
XS$NULL                   LOCKED
SYS                       LOCKED
LBACSYS                   LOCKED
OUTLN                     LOCKED
C##CLOUD$SERVICE          LOCKED
DBSNMP                    LOCKED
APPQOSSYS                 LOCKED
VECSYS                    LOCKED
...
56 rows selected.

---

Understanding OPEN Accounts

Several accounts are marked as OPEN because they are actively used by Autonomous Database services and internal components.

Important OPEN Users

• ADMIN → Primary administrative user for Autonomous Database
• SCOTT → Sample schema provided for learning and testing
• ORDS_PUBLIC_USER → Used by Oracle REST Data Services (ORDS)
• APEX_PUBLIC_ROUTER → Supports Oracle APEX routing
• GRAPH$PROXY_USER → Used for graph analytics functionality
• OML$PROXY → Oracle Machine Learning integration
• RMAN$CATALOG → Recovery Manager metadata operations

These users support Autonomous Database features such as:

• Oracle APEX
• REST APIs
• Machine Learning
• Graph processing
• Data sharing
• Backup automation

---

Why SYS and SYSTEM are LOCKED

One interesting observation in Autonomous Database is:

• SYS is LOCKED
• SYSTEM is LOCKED

This is intentional and part of Oracle Autonomous Database security architecture.

In Autonomous Database:

• Oracle manages the infrastructure
• Direct SYS access is restricted
• Patch management is automated
• Security hardening is enforced automatically
• Users operate primarily using the ADMIN account

This reduces operational risk and prevents accidental modifications to critical internal components.

---

Interesting Internal Users

Oracle APEX Related

• APEX_240200
• FLOWS_FILES
• APEX_PUBLIC_ROUTER

These support Oracle APEX runtime and metadata management.

Security Related Users

• DVSYS
• DVF
• LBACSYS
• AUDSYS

These accounts are related to:

• Database Vault
• Label Security
• Auditing
• Compliance controls

GoldenGate Related Users

• GGSYS
• GGADMIN
• GGSHAREDCAP

These users support Oracle GoldenGate replication services.

Machine Learning and AI

• OML$PROXY
• PYQSYS
• VECSYS

These accounts are associated with:

• Oracle Machine Learning
• Python execution
• AI Vector Search capabilities

---

Exploring Tablespaces

Next, let us check available tablespaces.

SELECT name
FROM v$tablespace;

Output

NAME
_______________
SYSTEM
SYSAUX
UNDO_21871
DATA
DBFS_DATA
TEMP
SAMPLESCHEMA
UNDO_4F8D9

---

Understanding Tablespaces

Tablespace	Purpose
SYSTEM	Core Oracle data dictionary
SYSAUX	Auxiliary database components
TEMP	Temporary operations and sorting
DATA	Primary user data storage
DBFS_DATA	Database File System storage
SAMPLESCHEMA	Sample schemas and demo objects
UNDO_*	Undo management for transactions

---

Checking SGA Memory

The System Global Area (SGA) represents shared memory structures used by Oracle Database.

SHOW PARAMETER sga

Output

NAME         TYPE        VALUE
------------ ----------- -------
sga_max_size big integer 219008M
sga_min_size big integer 0
sga_target   big integer 3400M

---

Understanding SGA Parameters

• sga_target → Current target memory allocation
• sga_max_size → Maximum possible SGA allocation
• sga_min_size → Minimum guaranteed SGA

Oracle Autonomous Database dynamically manages memory allocation depending on workload and scaling operations.

---

Checking PGA Memory

The Program Global Area (PGA) contains process-specific memory used for sorting, hashing, and query execution.

SHOW PARAMETER pga

Output

NAME                 TYPE        VALUE
-------------------- ----------- ------
pga_aggregate_limit  big integer 10200M
pga_aggregate_target big integer 5100M

---

Understanding PGA Parameters

• pga_aggregate_target → Target PGA memory allocation
• pga_aggregate_limit → Maximum PGA memory allowed

PGA is crucial for:

• Sorting operations
• Hash joins
• Parallel execution
• SQL work areas

---

Autonomous Database Memory Management

One key advantage of Autonomous Database is automatic memory tuning.

Oracle automatically:

• Tunes SGA and PGA
• Optimizes workloads
• Balances memory usage
• Improves SQL execution performance
• Handles scaling internally

This reduces DBA overhead significantly compared to traditional on-premises databases.

---

Final Thoughts

Exploring internal views inside Oracle Autonomous Database provides valuable insight into how Oracle manages security, memory, services, and infrastructure automatically.

Even though Autonomous Database abstracts much of the administration layer, understanding these components helps DBAs and developers better optimize workloads and understand Oracle's cloud-native architecture.

Features such as:

• Locked SYS/SYSTEM accounts
• Automatic memory management
• Pre-configured internal schemas
• Integrated AI and Machine Learning users
• Managed tablespaces

show how Oracle Autonomous Database is designed for automation, security, and enterprise-grade scalability.

---

Conclusion

Oracle Autonomous Database is far more than just a managed database service. It includes a sophisticated ecosystem of internal schemas, automated services, and intelligent memory management working together behind the scenes.

Understanding these internals can help Oracle professionals better appreciate the architecture powering Autonomous Database in Oracle Cloud Infrastructure (OCI).

Mastering LISTAGG in Oracle Database

Oracle Database provides many powerful SQL aggregation functions, and one of the most useful among them is LISTAGG. It allows developers and DBAs to combine multiple row values into a single concatenated string — making reporting, analytics, and data formatting significantly easier.

In this blog, we will explore:

• What LISTAGG is
• Basic syntax
• Real-world examples
• Ordering results
• Grouped aggregation
• LISTAGG DISTINCT
• Overflow handling
• Analytic LISTAGG
• Best practices

---

What is LISTAGG?

LISTAGG is an Oracle SQL aggregate function that concatenates values from multiple rows into a single string.

It is commonly used for:

• Reporting
• CSV-style output
• Data summarization
• Dashboard queries
• Oracle APEX applications

---

Basic Syntax

LISTAGG(column_name, delimiter)
WITHIN GROUP (ORDER BY column_name)

---

Sample EMP Table

SELECT empno, ename, deptno
FROM emp;

Sample Output

EMPNO ENAME      DEPTNO
----- ---------- ------
7369  SMITH      20
7499  ALLEN      30
7521  WARD       30
7566  JONES      20
7654  MARTIN     30

---

Basic LISTAGG Example

Suppose we want all employee names in a single comma-separated string.

SELECT LISTAGG(ename, ', ')
       WITHIN GROUP (ORDER BY ename) AS employees
FROM emp;

Output

EMPLOYEES
------------------------------------------------
ADAMS, ALLEN, BLAKE, CLARK, FORD, JAMES, JONES

---

Understanding WITHIN GROUP

The WITHIN GROUP clause controls the ordering of concatenated values.

SELECT LISTAGG(ename, ', ')
       WITHIN GROUP (ORDER BY empno) AS employees
FROM emp;

This orders employee names using EMPNO instead of ENAME.

---

LISTAGG with GROUP BY

One of the most common use cases is grouped aggregation.

Example: Department-wise employee list

SELECT deptno,
       LISTAGG(ename, ', ')
       WITHIN GROUP (ORDER BY ename) AS employees
FROM emp
GROUP BY deptno
ORDER BY deptno;

Output

DEPTNO EMPLOYEES
------ ---------------------------------------
10     CLARK, KING, MILLER
20     ADAMS, FORD, JONES, SCOTT, SMITH
30     ALLEN, BLAKE, JAMES, MARTIN, TURNER

---

Using Different Delimiters

You are not limited to commas. You can use custom delimiters as needed.

SELECT LISTAGG(ename, ' | ')
       WITHIN GROUP (ORDER BY ename) AS employees
FROM emp;

Output

ADAMS | ALLEN | BLAKE | CLARK

---

LISTAGG DISTINCT (Oracle 19c and Later)

Starting from Oracle 19c, LISTAGG supports the DISTINCT keyword to remove duplicate values.

SELECT LISTAGG(DISTINCT job, ', ')
       WITHIN GROUP (ORDER BY job) AS jobs
FROM emp;

Output

ANALYST, CLERK, MANAGER, PRESIDENT, SALESMAN

---

Handling ORA-01489 Errors

One common issue with older LISTAGG implementations was:

ORA-01489: result of string concatenation is too long

Oracle introduced overflow handling to solve this problem.

SELECT LISTAGG(ename, ', ')
       WITHIN GROUP (ORDER BY ename)
       ON OVERFLOW TRUNCATE '...'
FROM emp;

Possible Output

ADAMS, ALLEN, BLAKE, CLARK...

---

LISTAGG with COUNT Information

You can also display the count of truncated values.

SELECT LISTAGG(ename, ', ')
       WITHIN GROUP (ORDER BY ename)
       ON OVERFLOW TRUNCATE '...' WITH COUNT
FROM emp;

Example Output

ADAMS, ALLEN, BLAKE...(5)

---

LISTAGG as an Analytic Function

LISTAGG can also work as an analytic function using the OVER() clause.

SELECT empno,
       ename,
       deptno,
       LISTAGG(ename, ', ')
       WITHIN GROUP (ORDER BY ename)
       OVER (PARTITION BY deptno) AS dept_employees
FROM emp;

Output

EMPNO ENAME  DEPTNO DEPT_EMPLOYEES
----- ------ ------ --------------------------------
7369  SMITH     20  ADAMS, FORD, JONES, SCOTT, SMITH
7566  JONES     20  ADAMS, FORD, JONES, SCOTT, SMITH

---

Performance Considerations

• Use proper ORDER BY columns
• Avoid extremely large aggregations
• Use DISTINCT carefully
• Prefer ON OVERFLOW handling
• Monitor memory usage for huge datasets

---

Common Use Cases

• Reporting applications
• HR systems
• Audit reports
• Oracle APEX dashboards
• CSV exports
• Data warehousing
• Analytics queries

---

LISTAGG vs XMLAGG

Before LISTAGG enhancements, many developers used XMLAGG for string aggregation.

SELECT RTRIM(
       XMLAGG(
         XMLELEMENT(e, ename || ', ')
       ).EXTRACT('//text()'),
       ', '
)
FROM emp;

Today, LISTAGG is simpler and more readable for most use cases.

---

Final Thoughts

LISTAGG is one of the most practical SQL functions in Oracle Database. It simplifies row-to-string aggregation and makes SQL reporting significantly more readable and powerful.

With modern enhancements like:

• DISTINCT support
• Overflow handling
• Analytic functionality

LISTAGG has become even more powerful in Oracle Database 19c, 23ai, and 26ai.

---

Quick Reference Cheat Sheet

Basic Aggregation

LISTAGG(col, ', ')
WITHIN GROUP (ORDER BY col)

Remove Duplicates

LISTAGG(DISTINCT col, ', ')

Overflow Handling

ON OVERFLOW TRUNCATE '...'

Analytic Usage

OVER (PARTITION BY deptno)

---

Conclusion

If you work with Oracle SQL regularly, LISTAGG is a must-know feature. It helps transform raw relational data into readable business-friendly output with minimal SQL effort.

Start using LISTAGG in your reporting and analytics queries to produce cleaner and smarter results.

Understanding the “Post-Quantum Key Exchange” SSH Warning in OCI

If you are using SSH to connect to your Oracle Cloud Infrastructure (OCI) instance, you might have recently encountered a warning like this:

---

🔍 Real SSH Output

(base) karandodwal@Karans-MacBook-Air ~ % ssh opc@80.225.212.15
** WARNING: connection is not using a post-quantum key exchange algorithm.
** This session may be vulnerable to "store now, decrypt later" attacks.
** The server may need to be upgraded. See https://openssh.com/pq.html
Last login: Sat May 16 17:39:53 2026 from 49.47.70.44
[opc@instance-vm1 ~]$

---

🤔 What Does This Warning Mean?

This warning is generated by your SSH client (most likely a newer version of OpenSSH on macOS). It indicates that your connection is:

• Using traditional cryptographic algorithms
• Not using post-quantum cryptography (PQC)

Post-quantum cryptography refers to encryption methods designed to remain secure even against future quantum computers.

---

🧠 What is “Store Now, Decrypt Later”?

The warning specifically mentions:

"store now, decrypt later"

This is a theoretical attack scenario where:

• An attacker captures encrypted SSH traffic today
• Stores it for future use
• Decrypts it later when quantum computers become powerful enough

👉 Important: This is not a current threat, but a future possibility.

---

⚠️ Should You Be Concerned?

Short answer: No, not for now.

• Your SSH session is still secure using modern encryption
• No practical quantum attacks exist today
• This warning is proactive and informational

For typical use cases like:

• OCI Free Tier instances
• Development environments
• Learning setups

👉 You can safely ignore this warning.

---

💡 Why Are You Seeing This Now?

Newer versions of OpenSSH have started:

• Highlighting connections that are not quantum-resistant
• Encouraging adoption of future-proof cryptography

However:

• Most servers (including many OCI images) do not yet support PQC
• So the warning appears even though everything is functioning normally

---

🔧 How to Handle This Warning

Option 1: Ignore It (Recommended)

For most users, especially in non-production environments, no action is required.

Option 2: Update SSH Server

sudo dnf update openssh-server

Then check supported key exchange algorithms:

ssh -Q kex

Look for PQC-related algorithms like:

sntrup761x25519-sha512@openssh.com

Option 3: Suppress the Warning (Client Side)

Edit your SSH config file:

~/.ssh/config

Add:

Host *
    LogLevel ERROR

⚠️ This only hides the warning—it does not improve security.

---

🧾 Key Takeaways

• This warning comes from modern OpenSSH clients
• Your connection is still secure by current standards
• The risk mentioned is future-focused (quantum computing)
• OCI instances typically do not yet support PQC
• You can safely ignore or suppress the warning

---

🔗 Learn More

• OpenSSH Post-Quantum Cryptography Details

---

🏁 Conclusion

The post-quantum SSH warning may look alarming at first, but it is simply a forward-looking security notice rather than an immediate threat.

As quantum computing evolves, we can expect cloud providers and SSH implementations to gradually adopt quantum-resistant algorithms. Until then, your current setup remains secure and fully functional.

Understanding FOR LOOP Iterand in PL/SQL

In PL/SQL, the FOR LOOP is one of the most commonly used control structures for iteration. A key concept within this loop is the iterand (also known as the loop index or loop counter). Understanding how the iterand behaves is crucial to avoid runtime errors and write efficient code.

---

What is a FOR LOOP Iterand?

The iterand in a FOR LOOP is a variable that is:

• Implicitly declared by Oracle when the loop starts
• Local to the loop (not accessible outside the loop)
• Read-only inside the loop

This means:

• You can read its value
• You cannot modify its value
• It ceases to exist after the loop ends

---

Important Rule

You cannot assign a value to the FOR LOOP iterand inside the loop.

Attempting to do so results in a compilation error because Oracle protects the loop counter from being altered manually.

---

Example: Attempt to Modify Iterand (Incorrect Usage)

SET SERVEROUTPUT ON;

BEGIN
  FOR i IN 1..3 LOOP
    IF i < 3 THEN
      DBMS_OUTPUT.PUT_LINE(TO_CHAR(i));
    ELSE
      i := 2;  -- ❌ Not allowed
    END IF;
  END LOOP;
END;
/

---

Output / Error

ERROR at line 6:
ORA-06550: line 6, column 7:
PLS-00363: expression 'I' cannot be used as an assignment target
ORA-06550: line 6, column 7:
PL/SQL: Statement ignored

---

Why Does This Error Occur?

The error occurs because:

• The loop variable i is automatically managed by the FOR LOOP
• Oracle treats it as a constant within each iteration
• Assigning a value to it violates PL/SQL rules

Specifically:

• PLS-00363 → You cannot use the iterand as an assignment target
• ORA-06550 → General compilation error pointing to invalid PL/SQL syntax

---

Correct Approach

If you need to control or modify values during iteration, use a separate variable instead of modifying the iterand.

BEGIN
  FOR i IN 1..3 LOOP
    DECLARE
      temp NUMBER;
    BEGIN
      temp := i;

      IF temp = 3 THEN
        temp := 2; -- ✅ Allowed
      END IF;

      DBMS_OUTPUT.PUT_LINE(temp);
    END;
  END LOOP;
END;
/

---

Key Takeaways

• FOR LOOP iterand is implicitly declared
• It is read-only inside the loop
• It is not accessible outside the loop
• Attempting to modify it results in PLS-00363 error
• Use a separate variable if modification is needed

---

Official Documentation

For more details on these errors, refer to Oracle documentation:

• ORA-06550 Error Details
• PLS-00363 Error Details

---

Conclusion

The FOR LOOP iterand simplifies iteration in PL/SQL by automatically handling loop control. However, this convenience comes with restrictions — most importantly, the inability to modify the iterand. Understanding this behavior helps prevent common errors and leads to cleaner, more reliable PL/SQL code.

Understanding SQL Query Output and Session Statistics in Oracle Database 26ai

When working with Oracle Database, especially in environments like Autonomous Database 26ai, it’s not just about running queries— it’s equally important to understand what happens behind the scenes.

In this article, we will:

• Run a simple query on the EMP table
• Analyze the output
• Understand session-level statistics generated by Oracle

---

Step 1: Querying the EMP Table

Let’s start with a basic query:

SQL> SELECT * FROM emp;

Query Output

   EMPNO ENAME     JOB        MGR HIREDATE     SAL   COMM DEPTNO
________ _________ __________ ____ __________ _____ _____ ______
    7698 BLAKE     MANAGER    7839 01-05-81   2850       30
    7902 FORD      ANALYST    7566 03-12-81   3000       20
    7876 ADAMS     CLERK      7788 23-05-87   1100       20
    7566 JONES     MANAGER    7839 02-04-81   2975       20
    7369 SMITH     CLERK      7902 17-12-80    800       20
    7499 ALLEN     SALESMAN   7698 20-02-81   1600   300 30
    7900 JAMES     CLERK      7698 03-12-81    950       30
    7934 MILLER    CLERK      7782 23-01-82   1300       10
    7839 KING      PRESIDENT       17-11-81   5000       10
    7521 WARD      SALESMAN   7698 22-02-81   1250   500 30
    7844 TURNER    SALESMAN   7698 08-09-81   1500     0 30
    7782 CLARK     MANAGER    7839 09-06-81   2450       10
    7788 SCOTT     ANALYST    7566 19-04-87   3000       20
    7654 MARTIN    SALESMAN   7698 28-09-81   1250  1400 30

14 rows selected.

This is a classic Oracle sample table showing employee details such as:

• EMPNO – Employee ID
• ENAME – Name
• JOB – Role
• SAL – Salary
• COMM – Commission (if applicable)
• DEPTNO – Department number

---

Step 2: Understanding SQL Execution Statistics

After executing the query, Oracle provides detailed session-level statistics that help us understand how the query was processed internally.

Statistics Output

Statistics
-----------------------------------------------------------
               1  CPU used by this session
               1  CPU used when call started
               1  DB time
            7337  RM usage by this session
               3  Requests to/from client
               8  SCN increments due to another database
               2  SQL*Net roundtrips to/from client
             296  bytes received via SQL*Net from client
           65061  bytes sent via SQL*Net to client
               2  calls to get snapshot scn: kcmgss
               2  calls to kcmgcs
               1  cursor authentications
               2  execute count
               2  global enqueue gets sync
               2  global enqueue releases
               8  non-idle wait count
               2  opened cursors cumulative
               1  opened cursors current
               2  parse count (total)
               9  process last non-idle time
               1  redo scn array scans
               2  session cursor cache count
               3  user calls

---

Breaking Down the Key Statistics

Let’s simplify what these numbers actually mean:

1. CPU & DB Time

• CPU used by this session: Amount of CPU consumed
• DB time: Total time spent executing the query

👉 In our case, both values are very low, indicating a lightweight query.

2. SQL*Net Communication

• Roundtrips: Number of back-and-forth communications between client and database
• Bytes sent/received: Data transferred over the network

👉 65KB sent shows that result data (14 rows) was returned efficiently.

3. Parse and Execute

• Parse count: How many times SQL was parsed
• Execute count: Number of executions

👉 Ideally, parsing should be minimized for better performance.

4. Cursor Activity

• Opened cursors: SQL statements held in memory
• Session cursor cache: Helps reuse SQL statements

5. Wait Events

• Non-idle wait count: Times the session waited for resources

👉 Low wait counts = good performance.

6. SCN (System Change Number)

• SCN increments: Tracks database changes for consistency

👉 Important for read consistency and transaction management.

---

Why These Statistics Matter

Even for a simple SELECT query, Oracle provides deep insights into:

• Performance behavior
• Resource utilization
• Network overhead
• Execution efficiency

For developers and DBAs, these statistics are extremely useful when:

• Troubleshooting slow queries
• Optimizing SQL performance
• Understanding database workload

---

Conclusion

A simple query like SELECT * FROM emp may look trivial, but Oracle Database captures a rich set of execution statistics behind the scenes.

Understanding these metrics helps you move from just writing SQL to mastering database performance and internals.

If you're exploring Oracle 26ai Autonomous Database, make it a habit to review these statistics—they reveal far more than just query results.

What’s Included in the Free Tier of Oracle Database 26ai Autonomous Database

In this blog, we will explore what you actually get when you provision a free tier Autonomous Database on Oracle Database 26ai. Instead of just relying on documentation, we will look at a real SQLcl session and inspect the available resources directly from the database.

Environment Details

SQLcl: Release 24.4 Production on Fri May 15 22:03:16 2026

Copyright (c) 1982, 2026, Oracle.  All rights reserved.

Last Successful login time: Fri May 15 2026 22:03:22 +05:30

Connected to:
Oracle AI Database 26ai Enterprise Edition Release 23.26.2.1.0 - Production
Version 23.26.2.1.0

Pluggable Database Information

Let’s first check the available PDB in the free tier:

SQL> show pdbs

   CON_ID CON_NAME                OPEN MODE     RESTRICTED
_________ _______________________ _____________ _____________
      600 RE2D3BGG7YWWTP7_PYDB    READ WRITE    NO

You get a single pluggable database (PDB) that is fully open in READ WRITE mode and not restricted.

CPU Allocation

Now let’s check how much CPU is allocated in the free tier:

SQL> show parameter cpu

NAME          TYPE   VALUE
------------- ------ -----
cpu_count     string 2
cpu_min_count string 2

The free tier provides:

• 2 CPUs available
• 2 CPUs guaranteed minimum

SGA (System Global Area)

Let’s inspect the memory allocated for SGA:

SQL> show parameter sga

NAME         TYPE        VALUE
------------ ----------- -------
sga_max_size big integer 219008M
sga_min_size big integer 0
sga_target   big integer 3400M

Key observations:

• SGA Target: ~3.4 GB
• SGA Max Size: Configured high but not fully utilized

PGA (Program Global Area)

Now let’s look at PGA allocation:

SQL> show parameter pga

NAME                 TYPE        VALUE
-------------------- ----------- ------
pga_aggregate_limit  big integer 10200M
pga_aggregate_target big integer 5100M

This shows:

• PGA Target: ~5.1 GB
• PGA Limit: ~10.2 GB

In-Memory & Memory Features

Finally, let’s review memory-related parameters:

SQL> show parameter memory

NAME                               TYPE        VALUE
---------------------------------- ----------- -------
inmemory_automatic_level           string      MEDIUM
inmemory_clause_default            string
inmemory_deep_vectorization        boolean     TRUE
inmemory_expressions_usage         string      ENABLE
inmemory_force                     string      DEFAULT
inmemory_graph_algorithm_execution string      DEFAULT
inmemory_optimized_arithmetic      string      DISABLE
inmemory_optimized_date            string      DISABLE
inmemory_prefer_xmem_memcompress   string
inmemory_prefer_xmem_priority      string
inmemory_query                     string      ENABLE
inmemory_size                      big integer 0
inmemory_virtual_columns           string      MANUAL
inmemory_xmem_size                 big integer 0
memory_target                      big integer 0
optimizer_inmemory_aware           boolean     TRUE
shard_apply_max_memory_size        big integer 0
vector_memory_size                 big integer 0

Important takeaways:

• In-Memory features are enabled at a logical level
• In-Memory size is 0 (not allocated in free tier)
• Advanced optimizations like vectorization are enabled

Conclusion

The free tier of Oracle Database 26ai Autonomous Database is surprisingly capable and gives you:

• 2 CPUs (guaranteed)
• ~3.4 GB SGA
• ~5 GB PGA
• Fully functional Autonomous Database (READ WRITE)
• Advanced optimizer and in-memory capabilities (partially enabled)

This makes it an excellent environment for:

• Learning Oracle Database 26ai features
• Testing SQL and PL/SQL
• Exploring AI and Autonomous capabilities

Even though it is a free tier, it still provides a powerful sandbox to experiment with modern Oracle database features.

Annotations in Oracle Database 23ai / 26ai — A Complete Guide

Oracle continues to innovate with Oracle Database 23ai and its evolution into Oracle Database 26ai. One of the most practical features introduced is Annotations — a structured and powerful way to attach metadata directly to database objects.

---

What Are Annotations?

Annotations allow you to attach custom metadata to database objects such as:

• Tables
• Columns
• Views
• Indexes
• PL/SQL objects

Think of annotations as structured, queryable comments that can be used by developers, applications, and AI tools.

---

Why Annotations Matter

Before annotations, metadata management relied on:

• DDL comments
• External documentation
• Naming conventions

This often resulted in inconsistency and poor discoverability.

With Annotations, you get:

• Centralized metadata
• Improved data governance
• Better developer productivity
• AI-ready schemas
• Self-describing database objects

---

Syntax Overview

Table Annotation Example

CREATE TABLE customers (
    customer_id NUMBER,
    name        VARCHAR2(100),
    email       VARCHAR2(100)
)
ANNOTATIONS (
    'domain' VALUE 'crm',
    'pii' VALUE 'true'
);

Column Annotation Example

CREATE TABLE employees (
    emp_id NUMBER,
    salary NUMBER ANNOTATIONS ('sensitive' VALUE 'true')
);

---

Viewing Annotations

You can query annotations using Oracle dictionary views:

SELECT *
FROM USER_ANNOTATIONS
WHERE OBJECT_NAME = 'CUSTOMERS';

Other useful views:

• ALL_ANNOTATIONS
• DBA_ANNOTATIONS

---

Modifying Annotations

ALTER TABLE customers
ADD ANNOTATIONS ('retention_period' VALUE '5 years');

---

Dropping Annotations

ALTER TABLE customers
DROP ANNOTATIONS ('pii');

---

Real-World Use Cases

1. Data Classification & Security

'sensitive' VALUE 'true'

Useful for compliance such as GDPR and internal audits.

2. AI & Automation Integration

'domain' VALUE 'finance'

Helps AI systems understand data context and improve query intelligence.

3. Data Governance

'owner' VALUE 'finance_team',
'retention' VALUE '7 years'

Track ownership and lifecycle policies.

4. Application Integration

Applications can dynamically use annotations to:

• Mask sensitive data
• Drive UI behavior
• Apply business rules

---

Best Practices

• Use standardized keys: pii, sensitive, domain, owner
• Keep values meaningful and concise
• Leverage dictionary views for automation
• Avoid overuse to keep metadata clean

---

What’s New in 26ai?

• Better AI integration
• Improved metadata indexing
• Enhanced tooling support
• Smarter automation capabilities

---

Summary

Annotations in Oracle Database 23ai and 26ai are a game-changer for metadata management. They transform how developers document, govern, and interact with database systems.

Instead of relying on external documentation, you can now embed intelligence directly into your database objects — making systems more scalable, maintainable, and AI-ready.

---

Final Thoughts

If you're working with modern Oracle environments, especially AI-driven or cloud-native architectures, annotations are quickly becoming a best practice rather than an option.