Oracle 23ai's Phantom Vector Memory: A Troubleshooting Guide

Source: Dev.to

Pre‑Flight Checklist

Before we taxi down the runway, here’s your flight plan. Keep this handy to navigate your flight path. Welcome aboard the cloud!

Table of Contents

1. Prerequisites
2. A Quick Primer on Oracle Architecture
3. The Mission: Allocate Vector Memory
4. The First Attempt: ALTER SYSTEM
5. The Roadblock: A Two‑Layer Problem
6. The Breakthrough: The docker exec Recovery
7. The Proof: Trust the Startup Log, Not the Parameter
8. Understanding Oracle Parameter and Memory Views
9. Recovery Cheat Sheet
10. Conclusion
11. The “IAM Policy”: Why Our dev User Needed GRANTs

Introduction

As part of my work with Oracle Cloud Infrastructure (OCI) and its powerful database features, I’ve been diving into the new AI Vector Search capabilities in Oracle Database 23ai. The free containerized version is the perfect lab for this.

To get started with high‑performance HNSW indexes, you need to allocate a dedicated memory pool by setting the vector_memory_size parameter. A single SQL command and a restart led me down a rabbit hole of cryptic errors, misleading outputs, and a locked‑out database. It turned into a fantastic troubleshooting session that taught me valuable lessons about the inner workings of Oracle’s multitenant architecture, especially in the Free edition.

If you’ve ever stared at a vector_memory_size that stubbornly reads 0 or fought the dreaded ORA‑12514 error, this log of my troubleshooting journey is for you.

Assumptions

• You have already installed and are running the Oracle 23ai Free container, as described in The Ultimate Guide to Oracle 23ai on Apple Silicon.
• The environment from Part 1 is up and running. If you haven’t set up your dev user yet, pause here and go back.

Oracle Terminology Primer

Understanding these concepts is crucial to grasp why the problem occurs and how the solution works.

The Mission: Allocate Vector Memory

The first step to using AI Vector Search is to tell Oracle to set aside a dedicated chunk of RAM for it by setting the vector_memory_size parameter.

⚡ Note
The 23ai Free edition is limited to 2 GB of RAM. Dedicating too much memory to vectors can starve other critical database processes. A setting of 1 GB (≈ 50 % of total memory) is risky. It’s wiser to start with a more conservative value.

I decided to start with 500 M.

ALTER SYSTEM

-- Connect using my sql-sys alias or as SYS
ALTER SYSTEM SET vector_memory_size = 500M SCOPE = SPFILE;

The system responded with a reassuring “System altered.” To apply the change, a database restart is required. The easiest way with Docker is to restart the container itself:

docker restart oracle-free

This is where the journey should have ended. Instead, it’s where the trouble began.

The First Wall: Permissions

After the container restarts, a developer’s natural first step is to connect as the day‑to‑day dev user and check if the vector_memory_size parameter was applied.

-- Connecting as the dev user from Part 1
SQL> SHOW PARAMETER vector_memory_size;
ORA-00942: table or view does not exist

Lesson #1 – Our dev user (with basic CONNECT and RESOURCE roles) can create tables but cannot see instance‑level configuration. This is a security feature, not a bug.

Switch to SYS

-- Now connected as SYS (e.g., via sql-sys alias)
SQL> SHOW PARAMETER vector_memory_size;

NAME                 TYPE          VALUE
-------------------  ------------  ------------------------------
vector_memory_size   big integer   0

The value is still 0 – our change appears to have vanished.

The Second Wall: Configuration Layer

-- 1️⃣ Check current container name
SQL> SHOW CON_NAME;

CON_NAME
--------
FREEPDB1

-- 2️⃣ Check if the parameter is PDB‑modifiable
SQL> SELECT NAME, ISPDB_MODIFIABLE
     FROM V$SYSTEM_PARAMETER
     WHERE NAME = 'vector_memory_size';

NAME                ISPDB_MODIFIABLE
------------------- ----------------
vector_memory_size  TRUE

Interpretation – I was connected to the PDB (FREEPDB1), and the parameter is PDB‑modifiable. Yet SHOW PARAMETER still reported 0. This discrepancy between the stored parameter value and the actual runtime allocation is a common quirk in the Oracle Free edition.

Permissions Fix for the dev User

Non‑administrative users need explicit grants to query the data‑dictionary views that SHOW PARAMETER relies on.

-- Run as SYS
GRANT SELECT ON SYS.V_$PARAMETER          TO dev;
GRANT SELECT ON SYS.V_$SYSTEM_PARAMETER   TO dev;
GRANT CREATE INDEX                       TO dev;

These grants allow the dev user to query V$PARAMETER and V$SYSTEM_PARAMETER, which are owned by SYS.

The Breakthrough: Using docker exec to Inspect the Startup Log

Instead of relying on SHOW PARAMETER, I inspected the startup log inside the container, which accurately reflects the SGA composition.

docker exec -it oracle-free bash
# Inside the container
cat $ORACLE_BASE/diag/rdbms/*/*/trace/alert*.log | grep -i vector_memory_size

The log showed the vector memory pool correctly allocated to 500 M, confirming that the ALTER SYSTEM command had succeeded; the issue was only with visibility from the dev session.

Recovery Cheat Sheet

Conclusion

• The vector_memory_size parameter was applied; the apparent “zero” value was a visibility issue caused by:
  1. Connecting to the PDB instead of the CDB.
  2. Insufficient privileges for the dev user to query the underlying V$ views.
• The startup log is the most reliable source for confirming memory allocation in the Oracle 23ai Free edition.
• Granting the appropriate SELECT privileges to the dev user resolves the “parameter not visible” symptom, allowing developers to continue their work without needing full SYS rights.

The “IAM Policy”: Why Our dev User Needed GRANTs

In OCI, IAM policies control what a user can do at the cloud‑level, but inside the database we still need SQL‑level grants for data‑dictionary access. The dev user required the explicit SELECT grants on SYS.V_$PARAMETER and SYS.V_$SYSTEM_PARAMETER to read configuration values, and CREATE INDEX to actually build vector indexes.

Happy Flying!

Now you have a clean, reproducible path to allocate vector memory, verify it, and avoid the common pitfalls that can leave you staring at ORA‑12514 or a mysterious “0” value. Safe travels! 🚀

Overview

The following notes document a troubleshooting session for an Oracle Free Docker container that became unavailable after a CDB‑wide configuration change.

Problem

After applying a vector memory change at the CDB root and performing a full SHUTDOWN/STARTUP, the instance failed to start:

Database closed.
Database dismounted.
ORACLE instance shut down.
ERROR:
ORA-12514: Cannot connect to database. Service freepdb1 is not registered with
the listener at host 127.0.0.1 port 1521.

Attempts to reconnect with the sql-sys alias produced the same error:

>>> sql-sys 

Copyright (c) 1982, 2025, Oracle. All rights reserved. 
ERROR: ORA-12514: Cannot connect to database. 
Service freepdb1 is not registered with the listener at host 127.0.0.1 port 1521. 
(CONNECTION_ID=Qgik7CZCByPgYwIAEaywBw==) 
Help: https://docs.oracle.com/error-help/db/ora-12514/ 

Enter user-name:

A quick check of the Docker container status confirmed the issue:

docker ps

The container was marked unhealthy, indicating the database inside was not fully operational.

What ORA‑12514 Really Means

ORA‑12514 means the service name (freepdb1) has not registered with the listener. This occurs when:

• The CDB instance is down, or
• The Pluggable Database (PDB) is closed.

In the Free edition, after a CDB restart the PDB does not auto‑open, so FREEPDB1 stayed closed and never registered with the listener.

Note – In Part 1 we restarted the container, which also closed the PDB. That’s why we must open it manually now.

Recovery Steps

1. Re‑enter the container as SYSDBA

docker exec -it oracle-free sqlplus / as sysdba

You will see:

Connected to an idle instance.

2. Start the database

STARTUP;

3. Open the pluggable databases

ALTER PLUGGABLE DATABASE ALL OPEN;

4. (Optional) Switch to your PDB

ALTER SESSION SET CONTAINER = FREEPDB1;

5. Verify vector memory allocation

SELECT pool, alloc_bytes, used_bytes
FROM V$VECTOR_MEMORY_POOL;

6. Verify overall SGA breakdown

SHOW SGA;

Grant Statements (Reference)

These GRANT statements provide a developer with the specific, read‑only access they need without making them an administrator.

GRANT SELECT ON v$vector_memory_pool TO dev_user;
GRANT SELECT ON v$pdbs               TO dev_user;
GRANT SELECT ON v$system_parameter   TO dev_user;

TL;DR

1. Shutdown the CDB caused the PDB to close → listener could not register FREEPDB1.
2. Re‑enter the container as SYSDBA, STARTUP the instance.
3. Open the PDB (ALTER PLUGGABLE DATABASE ALL OPEN).
4. Verify vector memory allocation via V$VECTOR_MEMORY_POOL and SHOW SGA.

The vector memory pool is now correctly allocated (~512 MB), and the database is fully operational.

Understanding Oracle Views for Vector Memory

V$PARAMETER

Shows the intended configuration of system parameters. The ISPDB_MODIFIABLE column indicates whether a parameter can be changed at the PDB level.

V$SYSTEM_PARAMETER

Similar to V$PARAMETER, but displays the actual values for the current session. For system‑level parameters like vector_memory_size, its value will often mirror V$PARAMETER.

V$SGA

Provides a breakdown of the System Global Area (SGA) components and their sizes. After a successful STARTUP with vector_memory_size configured, you should see Vector Memory Area listed with its allocated size.

SQL> SHOW SGA;

Example excerpt

Total System Global Area   1603373280 bytes
Fixed Size                  5007584 bytes
Variable Size             352321536 bytes
Database Buffers          704643072 bytes
Redo Buffers                4530176 bytes
Vector Memory Area        536870912 bytes

V$VECTOR_MEMORY_POOL

The most direct source for confirming the active vector memory allocation.

SELECT pool, alloc_bytes, used_bytes
FROM V$VECTOR_MEMORY_POOL;

If this view shows your configured size, you can be confident that vector search memory is active.

Recovery Sequence When Locked Out with ORA‑12514

1. Check Docker container status (likely unhealthy).

   docker ps

2. Connect to the idle instance

   docker exec -it oracle-free sqlplus / as sysdba

3. Start the database

   STARTUP;

4. Open the pluggable databases

   ALTER PLUGGABLE DATABASE ALL OPEN;

5. (Optional) Verify vector memory pool allocation

   SELECT pool, alloc_bytes, used_bytes
   FROM V$VECTOR_MEMORY_POOL;

6. (Optional) Verify overall SGA breakdown

   SHOW SGA;

⚡️ A Note on Privileges

The troubleshooting steps above were performed with SYSDBA privileges. This illustrates the principle of least privilege:

• Day‑to‑day developers should not have permission to alter system parameters or restart the database.
• Administrative tasks are reserved for the superuser, ensuring a secure workflow that separates experimental and operational access.

Key takeaways

• Trust the startup log, V$SGA, and V$VECTOR_MEMORY_POOL for the ground truth of what’s happening in the SGA.
• SHOW PARAMETER and V$SYSTEM_PARAMETER are useful for intended configuration, but the startup sequence and direct memory‑pool queries reveal the real runtime allocation.
• Understanding the roles of the CDB and PDB, and mastering the Docker‑exec workflow, prevents lock‑outs and streamlines vector memory provisioning.