The DBA’s Guide to Oracle Agentic AI Foundations (Oracle AI Database)

Agentic AI Foundations Series · Part 6 of 6

Part 6: Oracle AI Database – The Agent's Memory

1Z0-1157-26 exam prep for Oracle DBAs

Oracle AI Database stores vectors right next to your tables, JSON, and graphs. An agent can search by meaning and run SQL in one place, under the security you already manage. For a DBA, this is home ground.

What is a vector, really?

An embedding is a list of numbers that captures meaning. "Tablespace full" and "no space left in datafile" use different words but land close together. "Listener down" lands far away. Searching by closeness = semantic search.

The new SQL pieces

• VECTOR data type – a column for embeddings, e.g. VECTOR(768, FLOAT32).
• VECTOR_EMBEDDING() – creates embeddings inside the database using an ONNX model you load with DBMS_VECTOR.LOAD_ONNX_MODEL. You can also call external models such as OCI Generative AI.
• DBMS_VECTOR_CHAIN – the pipeline helpers: UTL_TO_TEXT (extract), UTL_TO_CHUNKS (split), UTL_TO_EMBEDDINGS (embed).
• VECTOR_DISTANCE() – measures closeness. Order by it and fetch the top rows:

SELECT doc_id, chunk_text
FROM   runbook_chunks
ORDER  BY VECTOR_DISTANCE(embedding, :q_vec, COSINE)
FETCH  FIRST 5 ROWS ONLY;

Distance metrics: COSINE (most common for text), EUCLIDEAN, DOT, MANHATTAN, and HAMMING/JACCARD for binary vectors.

Vector indexes: think B-tree vs bitmap

Just as you choose index types for workloads, you choose vector indexes:

• HNSW – an in-memory graph of neighbors. Fastest and very accurate. Needs the vector memory pool (VECTOR_MEMORY_SIZE).
• IVF – groups vectors into partitions on disk. Scales to huge data with less memory.

Exact search compares every vector: 100% accurate, slower. Approximate search uses the index: FETCH APPROX FIRST 5 ROWS ONLY WITH TARGET ACCURACY 90.

Exam hook: HNSW = memory + speed. IVF = partitions + scale.

RAG in eight steps

Be ready to put these in order:

1. Load documents
2. Chunk them
3. Embed the chunks
4. Store in a VECTOR column and index it
5. Embed the user's question
6. Similarity search for the top-k chunks
7. Send question + chunks to the LLM
8. Return a grounded answer with sources

Select AI: talk to your data in English

• Package DBMS_CLOUD_AI; you create an AI profile (provider, credential, model, allowed tables).
• Actions: runsql (default, runs it), showsql (shows SQL only), explainsql, narrate (answer in plain language), chat.
• Select AI with RAG adds vector search over your documents; Select AI Agent builds agents with tools inside the database.

Why keep the agent's memory in the database?

One source of truth, transactional consistency, and your existing privileges, VPD, and auditing apply to every agent query. No separate vector store to sync.

Remember for the exam

• VECTOR type, VECTOR_EMBEDDING, VECTOR_DISTANCE; COSINE is the usual metric.
• HNSW in memory, IVF partitioned; approximate = index, exact = full compare.
• Select AI = DBMS_CLOUD_AI + AI profile; showsql shows without running.

Self-check

1. Which vector index lives in memory? (HNSW)
2. Which Select AI action shows the generated SQL without running it? (showsql)
3. In RAG, what happens right after chunking? (Embedding the chunks)

That's the series. Take the free practice exam on Oracle MyLearn next.