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

Posted on by — No Comments ↓

☁️ 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!

🌥️ Takeoff

  • Prerequisites
  • A Quick Primer on Oracle Architecture
  • The Mission: Allocate Vector Memory

⛅️ Cruising Altitude

  • The First Attempt: ALTER SYSTEM
  • The Roadblock: A Two-Layer Problem
  • The Breakthrough: The docker exec Recovery
  • The Proof: Trust the Startup Log, Not the Parameter

🌤️ Landing & Taxi

  • Understanding Oracle Parameter and Memory Views
  • Recovery Cheat Sheet
  • Conclusion
  • The “IAM Policy”: Why Our dev User Needed GRANTs

Enjoy your flight! ☁️

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 some valuable lessons about the inner workings of Oracle’s multitenant architecture, especially in the Free edition.

If you’ve found yourself staring at a vector_memory_size that stubbornly reads 0 or fighting the dreaded ORA-12514 error, this log of my troubleshooting journey is for you.

Prerequisites

This article assumes you have already successfully installed and are running the Oracle 23ai Free container, as detailed in the “The Ultimate Guide to Oracle 23ai on Apple Silicon.”. This article begins where that one left off.

This guide assumes you have the environment from Part 1 running. If you haven’t set up your dev user yet, pause here and go back.

A Quick Primer on Oracle Architecture

Before we dive into the troubleshooting, let’s clarify a few key Oracle terms. Understanding these concepts is crucial to grasping why the problem occurs and how the solution works.

  • CDB (Container Database): Think of this as the master database that manages the overall instance. In the free container, its name is FREE. You rarely connect here for development, but it’s where major configuration, like memory allocation, is ultimately managed.

  • PDB (Pluggable Database): This is an isolated, independent database that runs inside the CDB. For all application application development, you connect to the PDB. In our case, its name is freepdb1. The ORA-12514 error happens because this PDB isn’t available when the main CDB instance is down.

  • SGA (System Global Area): This is the shared memory region that a database instance uses to store data and control information. When we set vector_memory_size, we are telling Oracle to carve out a piece of the SGA specifically for vector indexes. The STARTUP log shows the actual composition of the SGA, which is why it’s more reliable than the SHOW PARAMETER command.

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 1G (50% of total memory) is risky. It’s wiser to start with a more conservative value.

I decided to start with 500M.

The First Attempt: ALTER SYSTEM

Following the documentation, I connected using my sql-sys alias and ran the standard ALTER SYSTEM command. Using SCOPE=SPFILE is the robust way to make a configuration change persist across restarts. 

-- 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. But instead, it’s where the trouble began.

The Roadblock: A Two-Layer Problem

After the container restarts, a developer’s natural first step is to connect as their day-to-day dev user and check if the vector_memory_size parameter was applied. This is where we hit our first wall: a permissions layer. 

-- Connecting as the dev user from Part 1
SQL> show parameter vector_memory_size;

ORA-00942: table or view does not exist

This is our first critical lesson. 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. To investigate further, we must switch to our administrative SYSuser.

Now, continuing as SYS, we can properly check the parameter. However, this reveals the second part of our problem: a configuration layer. 

-- 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

It was still zero. My change seemed to have vanished. To understand why, I ran some diagnostic checks: 

--1ST CHECK
SQL> SHOW CON_NAME;

CON_NAME
------
FREEPDB1

--2ND CHECK
SQL>SELECT NAME, ISPDB_MODIFIABLE
FROM V$SYSTEM_PARAMETER
WHERE NAME = 'vector_memory_size';

NAME
---
ISPDB
---
vector_memory_size
TRUE

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

🌩️ A Note on the Permissions Fix
Before proceeding, let’s quickly fix the issue for our dev user. As SYS, run the following GRANT statements:

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

This is necessary because commands like SHOW PARAMETER query special, protected data dictionary views (e.g., V$PARAMETER) that are owned by the SYS user. For security and stability, non-administrative users like our dev account do not have permission to see this instance-wide configuration by default.

These GRANT statements provide our developer with the specific, read-only access they need to do their job, without making them an administrator.

My investigation into CDB vs. PDB settings led me to try applying the change at the root and performing a full SHUTDOWN/STARTUP.

This led to a catastrophic failure: 

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.

I was locked out. Trying to reconnect with my sql-sys alias failed with the same ORA-12514 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

STATUS
Up 19 minutes (unhealthy)

This command showed the oracle-free container as (unhealthy), further indicating that the database instance inside was not fully operational.

🌩️ What ORA-12514 Really Means
This error means the listener is running, but the database service you’re asking for (freepdb1) hasn’t registered with it. This happens when the main database instance is down or the Pluggable Database (PDB) is closed. In my case, the entire instance was down because PDBs do not auto-open by default in the Free edition after a CDB restart. Therefore, FREEPDB1 remained closed, leading to its listener unregistration.

Did you know? Remember how we restarted the container in Part 1? That actually closes our PDB, which is why we need to manually open it here.

The Breakthrough: The docker exec Recovery

This is the critical lesson for anyone running Oracle in Docker. When you SHUTDOWN the instance from within a SQL session, you can’t just run STARTUP because your client is no longer connected to anything. I encountered this with the SP2-0640: Not connected error when attempting STARTUP after the shutdown.

You must re-enter the container and start the instance as SYSDBA.

  1. Exec into the container with sqlplus as sysdba: 

    docker exec -it oracle-free sqlplus / as sysdba

  2. You’ll be greeted with a message that tells you everything you need to know: 

    Connected to an idle instance.

  3. Start the database: 

    STARTUP;

The Proof: Trust the Startup Log, Not the Parameter

This is the moment of truth. The output that followed was the breakthrough: 

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  <-- VOILA!
Database mounted.
Database opened.

There it was: Vector Memory Area 536870912 bytes. That’s our 500M!

🌩️ The Deceptive Parameter
My ALTER SYSTEM command had worked from the beginning. The SHOW PARAMETER view in the PDB is misleading for this setting in the Free edition. The startup log and V$VECTOR_MEMORY_POOL are the authoritative sources. Trust the startup log and V$VECTOR_MEMORY_POOL as the authoritative sources!

Final Steps: Bringing the Database Fully Online

The instance was up, but the PDB was still closed. You must open it manually. 

-- 1. Open the Pluggable Database
ALTER PLUGGABLE DATABASE ALL OPEN;

-- 2. Verify the PDB Status
SELECT name, open_mode FROM v$pdbs;
-- You should see FREEPDB1 in READ WRITE mode

-- 3. Switch into the PDB for your work
ALTER SESSION SET CONTAINER = FREEPDB1;

-- 4. Final verification of vector memory pool allocation within the PDB
SELECT pool, alloc_bytes, used_bytes FROM V$VECTOR_MEMORY_POOL;

-- 5. Verify total SGA and vector memory allocation
SHOW SGA;

Expected Outputs for Step 4 & 5:

-- STEP4
POOL         ALLOC_BYTES   USED_BYTES
------------ ------------  ----------
1MB POOL     469762048     0
64KB POOL     50331648     0

-- STEP 5
Total System Global Area 1603373280 bytes
Fixed Size                  5007584 bytes
Variable Size             520093696 bytes
Database Buffers          536870912 bytes
Redo Buffers                4530176 bytes
Vector Memory Area        536870912 bytes

Explanation:

  • Each pool manages a specific block size of vector memory.
  • ALLOC_BYTES shows total allocated memory for that pool.
  • USED_BYTES shows how much memory is currently in use. 0 usage indicates the system is ready but no active vector operations are running yet.
  • The “Vector Memory Area” line appears because of the ALTER SYSTEM SET vector_memory_size=500M SCOPE=SPFILE;
    • It confirms that ~512 MB (close to your configured 500 MB) was successfully allocated on startup.
    • This memory is used for AI Vector Search features like storing vector embeddings and building vector indexes.
    • The ALLOC_BYTES in Step 4 (~520 MB combined) matches this total, confirming your vector memory configuration is active and healthy.

Final Steps

Understanding Oracle Parameter and Memory Views

Oracle provides several ways to inspect configuration parameters and memory allocation, but their scope and reliability can differ. Understanding these views is crucial for accurate troubleshooting.

First, a quick note on Oracle’s V$ views: these are dynamic performance views that provide access to current performance, resource usage, and session activity information. The V typically stands for “view,” and the $ is part of Oracle’s naming convention for these special views, which are often built on top of underlying X$ tables. They give you a real-time snapshot of the database instance’s state.

SHOW PARAMETER <parameter_name>

This SQL*Plus command is a convenient way to quickly check a parameter’s value. However, as we saw with vector_memory_size in the Free edition, it can sometimes report a default or 0 even when the parameter is actively allocated at runtime. Its output often reflects the value stored in the SPFILE or a default, not necessarily the currently active runtime value, especially for dynamically allocated memory components.

V$SYSTEM_PARAMETER

This dynamic performance view shows the current system-wide parameter values, typically reflecting what’s in the SPFILE or the last ALTER SYSTEM command. It’s useful for understanding the intended configuration. The ISPDB_MODIFIABLE column indicates if a parameter can be changed at the PDB level.

V$PARAMETER

Similar to V$SYSTEM_PARAMETER, but it shows parameter values for the current session. For system-level parameters like vector_memory_size, its value will often mirror V$SYSTEM_PARAMETER.

V$SGA

This view 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 here with its allocated size. This is a reliable source for confirming the actual memory allocation. 

SQL> SHOW SGA;
Example Output (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  <--Confirms allocation within SGA

This output directly correlates with the STARTUP log and visually confirms that the Vector Memory Area has been carved out of the total SGA. Remember, in the Oracle Free edition, the total SGA is limited, so increasing one component might implicitly reduce others if SGA_TARGET is constrained.

V$VECTOR_MEMORY_POOL

This view is specifically designed to show the details of the vector memory pool. It’s the most direct and authoritative source for confirming the active vector memory allocation. If this view shows your configured size, you can be confident that vector search memory is active. 

SELECT pool, alloc_bytes, used_bytes FROM V$VECTOR_MEMORY_POOL;

Recovery Cheat Sheet

If you get locked out with ORA-12514, here is your recovery sequence: 

# 1. (Optional) Check Docker container status - it will likely be unhealthy
docker ps

# 2. Connect to the idle instance from your host terminal
docker exec -it oracle-free sqlplus / as sysdba

Then, inside the SQL> prompt: 

-- 3. Start the database instance
STARTUP;

-- 4. Open the pluggable databases
ALTER PLUGGABLE DATABASE ALL OPEN;

-- 5. (Optional) Switch to your PDB to continue work
ALTER SESSION SET CONTAINER = FREEPDB1;

--6. (Optional) Verify vector memory pool allocation
SELECT pool, alloc_bytes, used_bytes FROM V$VECTOR_MEMORY_POOL;

-- 7. (Optional) Verify overall SGA breakdown
SHOW SGA;

With the database fully online and vector memory confirmed, I was finally ready to get back to building.

🌩️ A Note on Privileges: It’s important to note that this entire troubleshooting process was done with SYSDBA privileges. This is a prime example of the principle of least privilege in action. My day-to-day dev user rightfully lacks the permissions to alter system parameters or restart the database. Using the superuser for these administrative tasks is not only necessary but is also a validation of a secure and well-designed workflow, separating experimental and operational access models.

Conclusion

The key takeaway is to trust the startup log, V$SGA, and V$VECTOR_MEMORY_POOL. These provide the ground truth of what’s happening in the SGA. While SHOW PARAMETER and V$SYSTEM_PARAMETER are useful for intended configuration, the startup sequence and direct memory pool queries reveal the real story of runtime allocation. By understanding the roles of the CDB and PDB and mastering the docker exec recovery flow, you can confidently manage your Oracle instance and unlock its most powerful features.

Happy building and vectorizing! ☁️

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

In a cloud environment like OCI, a Cloud Architect never gives a developer or service full administrative access. Instead, they create fine-grained IAM (Identity and Access Management) policies. An IAM policy explicitly states who can do what on which resources.

Our GRANT commands in Part 2 were the database equivalent of an IAM policy. We encountered an ORA-00942 error because our dev user, by default, had no policy allowing it to view the system’s configuration.

The fix was not to grant the DBA role, but to create a precise policy:
GRANT SELECT ON SYS.V_$PARAMETER TO dev;

This is the Principle of Least Privilege in action, and it’s the most important concept in cloud security.

Object vs. System Privileges: A Cloud Analogy

Oracle’s security model, which separates Object Privileges (acting on a table) from System Privileges (acting on the whole database), mirrors the separation of concerns in OCI.

  • Object Privileges are like permissions on a specific OCI resource (e.g., writing to a specific Object Storage bucket). The RESOURCE role lets you create your own resources.
  • System Privileges are like tenancy-level permissions (e.g., the ability to launch new Compute instances or view network configurations).

Our dev user could manage its own “resources” (tables) but needed a specific policy (GRANT) to view “tenancy-level” configuration (V$PARAMETER).

Anatomy of a Secure Developer Role

This is the final “IAM policy” for our developer, enabling them to build AI applications securely. 

-- These SQL commands should be executed once as the SYS user
-- in your SQL client (e.g., SQLcl, SQL Developer, or via 'docker exec -it oracle-free sqlplus / as sysdba').
-- Ensure you are connected to the FREEPDB1 container if running from SYS.

-- 1. The right to log in (authenticate).
GRANT CONNECT TO dev;

-- 2. A role to create their own resources (tables, views).
GRANT RESOURCE TO dev;

-- 3. The right to use storage space (a quota on a resource).
ALTER USER dev QUOTA UNLIMITED ON dev_data;

-- 4. The policy to inspect specific system configurations.
GRANT SELECT ON SYS.V_$PARAMETER TO dev;
GRANT SELECT ON SYS.V_$SYSTEM_PARAMETER TO dev;

-- 5. The policy to create a specific type of resource (an index).
GRANT CREATE INDEX TO dev;

With this secure foundation, our developer is ready to build.

Cover Photo by BoliviaInteligente on Unsplash

Leave a Reply