26ai – Peter's Random Topic Library

Oracle REST Data Services (ORDS) 26.1.0 is out and one of the new features is Vector Search which we’ll explore in this article. Before diving in, this post assumes you have already worked through:

👉 Getting Started with Vectors in 23ai

That article is the foundation and introduces the CCNEWS dataset (200,000 news headlines) along with embedding generation and SQL-based vector search. The step by step article is easy to follow and even involves using ORDS to define a REST service for performing that vector search. What is also nice about the article is that everything it covers is easy to implement and does not require any external services. In my case, I run all of it locally using ORDS and Oracle AI Database 26ai Free Container Image in docker.

Here, we build directly on that article and show how Oracle REST Data Services 26.1.0 simplifies exposing that Vector Search functionality over REST with no code in Oracle Database 26ai.

1. The CCNEWS Table (From the Prerequisite)

Assuming you have followed all the steps in the article you have a 26ai database with a VECTOR_USER schema that has a CCNEWS table.

So that table was created with this statement from the original blog post:

create table if not exists CCNEWS (
    id number(10) not null,
    info VARCHAR2(4000),
    vec VECTOR
);

Important: Primary Key Requirement

ORDS Vector Search on REST enabled objects requires a primary key. If not already defined, run this:

ALTER TABLE VECTOR_USER.CCNEWS
ADD CONSTRAINT CCNEWS_PK PRIMARY KEY (ID);

2. Vector Search in SQL (From the Article)

The Database & Cloud Technology article uses Oracle’s VECTOR_DISTANCE function combined with VECTOR_EMBEDDING for a similarity search:

select id, info
from CCNEWS
order by vector_distance(vec, TO_VECTOR(VECTOR_EMBEDDING(doc_model USING 'little red corvette' as data)), COSINE)
fetch approx first 5 rows only;

Key points:

VECTOR_EMBEDDING generates the query vector
VECTOR_DISTANCE computes similarity
COSINE is used as the distance metric
FETCH APPROX limits the number of rows returned using an approximate nearest neighbour search

3. The ORDS Handler Approach

To expose that vector_distance based query via ORDS previously, you needed a custom service:

BEGIN
  ORDS.define_service(
    p_module_name    => 'vectorsearch',
    p_base_path      => 'ai/',
    p_pattern        => 'ccnews/:mysentence',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_collection_feed,
    p_source         => 'SELECT id,info from CCNEWS 
                         order by vector_distance(
                           vec, 
                           TO_VECTOR(VECTOR_EMBEDDING(doc_model USING :mysentence as data)), 
                           EUCLIDEAN
                         ) 
                         fetch approx first 5 rows only',
    p_items_per_page => 0);  COMMIT;
END;
/

Call it

curl 'http://localhost:8080/ords/vector_user/ai/ccnews/little%20red%20corvette'

That will get you a response like this:

{
   "count" : 5,
   "hasMore" : false,
   "items" : [
      {
         "id" : 142622,
         "info" : "CHICAGO -- Small cars have been getting great copy lately. They're lauded for their anti-gas guzzling efficiency and improved safety features."
      },
      {
         "id" : 79167,
         "info" : "A red car will start on pole as usual, but the probability that Ferrari will end the season with yet another win, their 16th in 18 races, is not quite as grim as it sounds."
      },
      {
         "id" : 182103,
         "info" : "With her red velvet hooded cape, Cybill Shepherd could have been Little Red Riding Hood."
      },
      {
         "id" : 182000,
         "info" : "When it comes to helping the environment or saving money at the pump, it's hard to beat one of the new mini-cars, like the Nissan Versa, Toyota Yaris and Mini Cooper."
      },
      {
         "id" : 57282,
         "info" : "Customized cars that use commercial logos as a creative palette."
      }
   ],
   "limit" : 0,
   "links" : [
      {
         "href" : "http://localhost:8080/ords/vector_user/ai/ccnews/little%20red%20corvette",
         "rel" : "self"
      },
      {
         "href" : "http://localhost:8080/ords/vector_user/metadata-catalog/ai/ccnews/item",
         "rel" : "describedby"
      }
   ],
   "offset" : 0
}

Observations

The query string (:mysentence) is passed directly into VECTOR_EMBEDDING
Distance metric is explicitly set to EUCLIDEAN
ORDS handles binding, but you still manage:
- Module definition
- URL structure
- SQL text

This works—but it involves encoding a query as a service.

4. ORDS 26.1.0: Built-in Vector Search

With ORDS 26.1.0, none of that is required for basic vector search.

Step 1: Enable AutoREST

As the VECTOR_USER run this to REST Enable the CCNEWS table.

BEGIN
  ORDS.enable_object(
    p_object       => 'CCNEWS'
  );
  COMMIT;
END;
/

That will give you a huge range of RESTful services for interacting with the table without any coding.

The OpenAPI V3 document describing the operations now available for the CCNEWS table.

Step 2: Use the Built-in Endpoint

Let’s look at the POST /vectorSearch operation. To perform the Vector Search there are certain values that must be provided in the request body. The ORDS Vector Search documentation describes them all so we will not go into details here.

Performing a Vector Search in this case requires a POST

The full path for the Vector Search operation on the CCNEWS table in the VECTOR_USER will look like this:

POST /ords/vector_user/ccnews/vectorSearch

5. Equivalent Vector Search via ORDS

Instead of embedding generation happening inside SQL, the client supplies the vector:

curl -X POST http://localhost:8080/ords/vector_user/ccnews/vectorSearch \
  -H "Content-Type: application/json" \
  -d '{
    "vector": [/* embedding for "little red corvette" */],
    "vectorColumn": "VEC",
    "columns": ["ID", "INFO"],
    "limit": 5,
    "distanceMetric": "EUCLIDEAN"
  }'

The client will have to know the AI model used for populating the VECTOR data but it has a wider range of options. That includes further filtering of rows in the same manner as what can be achieved through GET on the same table. See Filtering In Queries section of the ORDS documentation.

Key Difference vs Custom Handler Approach

Custom Hander	AutoREST Vector Search
Embedding generated in SQL (`VECTOR_EMBEDDING`)	Embedding supplied by client
Custom handler required	Built-in endpoint
SQL embedded in ORDS	No SQL required
URL parameter (`:mysentence`)	JSON payload
No refinement on search criteria	Client can specify order, number of rows to return, specific columns to include in the response and additional filter criteria.
Primary Key Hyperlinks require extra coding	Automatic Primary Key Hyperlinks for each row in the response.

Keep in mind that both approaches can be used at the same time. In some scenarios the client will know the model for the table or have the means to generate a vector array to search with.

Note that as with the initial article, the services provided are not secured and the ORDS instance is not using HTTPS for traffic. That’s fine for this exercise but not for production.

6. Let’s see it in action

Since the client now has to provide the vector to search for, to perform the exact same search one must get an embedding for little red corvette using the same model. Easily done by running:

select VECTOR_EMBEDDING(doc_model USING 'litle red corvette' as data)

The vector representation of ‘little red corvette’

Using that in a cURL command will look like this:

curl --location 'http://localhost:8080/ords/vector_user/ccnews/vectorSearch' \
--header 'Content-Type: application/json' \
--data '{
  "limit": 5,
  "vector": [-3.07053532E-002,5.85529916E-002,-6.74578473E-002,1.2257684E-002,6.316524E-002,-2.40801703E-002,7.64445635E-003,3.26403342E-002,-5.40543646E-002,6.23201914E-002,8.92788619E-002,6.09119842E-003,8.08357634E-003,-2.87142694E-002,-3.36633772E-002,-1.59669053E-002,1.10877827E-001,5.03755994E-002,-3.0524137E-003,2.27314071E-003,-8.80712047E-002,4.54846956E-002,-1.76324453E-002,5.4300325E-003,-7.49083012E-002,-7.84977898E-003,-1.03094578E-001,3.23520638E-002,-5.20449802E-002,-8.81914124E-002,5.09979203E-003,-2.28075747E-004,8.27756822E-002,1.80074794E-003,-2.35566851E-002,-5.18809892E-002,2.15939945E-003,-2.15763152E-002,-4.92988527E-002,4.24234103E-003,-3.89126204E-002,-1.08256757E-001,1.64869614E-002,1.02524489E-001,9.25767645E-002,-2.06774045E-002,-1.8368477E-002,1.33422706E-002,8.78902823E-002,-6.02279529E-002,3.43158655E-002,-9.60535407E-002,-2.74095945E-002,-2.8675979E-002,4.55864891E-002,2.63328925E-002,1.72216333E-002,-1.97178405E-002,6.42947182E-002,-2.79306769E-002,-2.80832332E-002,3.09923105E-002,-2.81780143E-003,-2.21623778E-002,1.51646696E-002,-5.2848449E-003,3.95465866E-002,-3.46338265E-002,-5.43230511E-002,3.11623141E-002,3.56283262E-002,-3.72483805E-002,-3.45825478E-002,-5.79019776E-003,-5.63643761E-002,-3.12294867E-002,8.83353204E-002,-3.03997919E-002,-1.23773552E-002,-5.15865511E-004,-3.59914801E-003,-1.69582665E-002,-7.76734948E-002,-4.63231243E-002,6.63533583E-002,-3.5777919E-002,4.40812893E-002,5.63600361E-002,-5.28740976E-003,5.69755659E-002,-5.50081208E-002,6.87551275E-002,3.28732915E-002,1.58981874E-003,-5.56428321E-002,-7.57648051E-002,5.8034081E-002,-6.68441579E-002,-3.73762697E-002,1.87148571E-001,1.12300172E-001,4.12532054E-002,1.00773335E-001,5.72219677E-003,-3.81047241E-002,3.35666686E-002,7.77937025E-002,4.77051288E-002,-2.15624534E-002,-1.18911706E-001,-2.00342126E-002,7.05829309E-003,-8.89590383E-002,-2.68359855E-002,-6.41334206E-002,-4.22521196E-002,-7.08332583E-002,-5.96234351E-002,-3.76171395E-002,3.2652244E-002,-4.96881641E-002,-7.7103138E-002,-2.48034019E-002,5.12335682E-003,7.085336E-002,-1.47623777E-001,5.37105873E-002,3.64335366E-002,-2.60975622E-002,5.82129369E-003,-8.14416409E-002,6.67800754E-002,2.08732914E-002,1.19046867E-001,5.18846847E-002,9.57678165E-003,-3.64857614E-002,7.76776895E-002,-3.93842794E-002,-9.8264575E-002,-4.7032699E-002,-5.16803861E-002,3.27760428E-002,3.26377489E-002,-6.59162626E-002,-1.05144009E-001,-3.22745182E-002,-5.92551716E-002,-1.13562755E-001,1.25622064E-001,1.14990778E-001,8.03940892E-002,7.66430572E-002,5.89383543E-002,-9.20753181E-003,-3.60060111E-002,-2.22714879E-002,6.28050696E-003,3.42132933E-002,6.13801964E-002,1.75386257E-002,-1.80134606E-002,-3.39894332E-002,-2.67520901E-002,-3.81485149E-002,-1.25639021E-001,2.98075918E-002,-4.81692702E-003,-5.38430316E-003,-2.68271938E-002,-8.80813152E-002,7.9674378E-002,8.1708869E-003,4.49210731E-003,4.93363179E-002,9.08218604E-003,2.98463684E-002,1.67458635E-002,4.30809148E-002,-6.30635628E-003,-3.66868116E-002,2.24808641E-002,-1.97107941E-002,4.19183187E-002,-2.07382943E-002,7.16343299E-002,-1.17987087E-002,2.83470232E-004,5.55337667E-002,3.85905765E-002,2.60068625E-002,2.47582365E-002,5.64134903E-002,7.59846391E-003,-6.95330277E-002,-5.1294744E-002,-2.89460607E-002,3.93225159E-003,2.4446765E-002,-5.46594113E-002,4.43796702E-002,-7.10089058E-002,3.78895737E-002,-7.63406715E-005,3.07969283E-002,-1.99558139E-002,1.54345371E-002,-8.88657868E-002,8.84383637E-003,-2.23286971E-002,-2.45919079E-002,-3.978635E-002,5.16027845E-002,-2.32025366E-002,-2.45015081E-002,7.69026503E-002,-2.96384431E-002,1.53524885E-002,-2.90377047E-002,-2.80109104E-002,4.53478694E-002,-3.58900763E-002,2.99728755E-002,-1.45389412E-033,1.88392568E-002,-6.47298545E-002,1.01908617E-001,-1.31587433E-002,-1.85926091E-002,-2.86066998E-002,-1.33587588E-002,-1.80519139E-003,-1.71739608E-002,-4.6595633E-002,-4.6931956E-002,8.30989629E-002,1.53468493E-002,-5.12199767E-004,1.04117207E-001,-1.2125371E-002,-1.8739434E-002,4.73202318E-002,2.74012014E-002,8.04362372E-002,-2.32837833E-002,-7.19153807E-002,2.11932212E-002,2.4031328E-002,-3.77282277E-002,5.17850779E-002,-6.48160726E-002,3.25367749E-002,-2.89279353E-002,-6.88138753E-002,-2.91798194E-003,-4.56949286E-002,2.95383874E-002,1.14016305E-003,-3.78589332E-002,-6.03643758E-003,2.98500806E-002,-4.85490002E-002,4.10790853E-002,-2.5354486E-002,-9.72109195E-003,-2.7718816E-002,-4.33942713E-002,6.56506717E-002,9.87958163E-004,-6.58345083E-003,5.29988557E-002,-1.82564501E-002,6.05667047E-002,-2.87184888E-003,1.63399044E-003,-8.17960426E-002,2.47597024E-002,3.14391218E-002,-2.03312412E-002,4.33304794E-002,4.25058976E-002,6.37011081E-002,3.73960882E-002,-1.86836459E-002,1.70387905E-002,2.79710498E-002,-3.8476523E-002,-2.18143836E-002,-2.01852974E-002,-2.65123788E-002,-1.21113239E-002,1.73476618E-002,-2.11952161E-002,-2.85385773E-002,-8.24076403E-003,2.16407981E-002,9.9034654E-003,-4.52033617E-002,-1.22422148E-002,1.14305643E-002,9.72609892E-002,4.52578627E-002,4.41269763E-002,-2.55465172E-002,-3.49305272E-002,-5.51500618E-002,5.64764142E-002,1.05597645E-001,-1.18475445E-002,-6.03516959E-002,4.99546118E-002,4.38831076E-002,-4.56738211E-002,4.00517136E-002,1.65134482E-002,8.81506428E-002,-2.3371242E-002,9.05854441E-003,-1.08199259E-002,1.63877298E-032,-1.96534507E-002,6.13839971E-003,2.32135262E-002,-3.43828648E-002,5.41330278E-002,1.27508137E-002,5.94790131E-002,-6.85228733E-003,-9.60054323E-002,-6.71492144E-002,-5.39250374E-002,-1.74073465E-002,1.59162041E-002,-7.3701553E-002,-2.2581771E-002,3.65978405E-002,1.47042209E-002,-7.16396943E-002,7.73388445E-002,-1.16045386E-001,2.4834685E-002,7.23040178E-002,3.32084112E-002,3.31360511E-002,1.10893575E-002,-9.28622484E-002,-4.19785641E-002,2.29144283E-002,9.44261849E-002,-8.33413657E-003,2.91968007E-002,5.88697419E-002,-1.39651537E-001,-6.11314899E-004,2.57693212E-002,-4.38742265E-002,1.74329523E-002,8.57599378E-002,5.69615103E-002,-9.78489816E-002,1.14794098E-001,-1.86840519E-002,-7.20299184E-002,3.69497389E-002,1.7280845E-002,2.3233227E-003,2.18159296E-002,-7.62700588E-002,4.16742787E-002,-3.8183827E-002,2.58755721E-002,4.36592326E-002,-3.85195836E-002,-1.25658726E-002,-6.86033443E-002,-2.30002753E-003,-4.88031134E-002,5.49012981E-002,-8.51705298E-002,2.89389715E-002,-4.43107588E-003,-2.64256331E-003,-2.68490277E-002,2.34105438E-002]
}'

Since it is not easy to manage long cURL commands like this I imported the OpenAPI V3 document from http://localhost:8080/ords/vector_user/open-api-catalog/ccnews/ into Postman..

The CCNEWS operations available to be called from Postman

Making that little red corvette search now looks like this:

The request payload is just two attributes: limit and vector

Everything else, such as what columns to return or what distance metric to use, will default. The response will look like this:

Response includes the matching rows and also a link to the individual row on the table

Runtime error?

If you are getting a ORA-25716: The identifier contains a double quotation mark (") character exception when calling the /vectorSearch service then check that you have defined a primary key for the CCNEWS table.

7. Distance Metric Comparison

Notice an important shift:

Old handler used: EUCLIDEAN
SQL example used: COSINE
ORDS allows either via distanceMetric

Example

{
  "distanceMetric": "EUCLIDEAN"
}

{
  "distanceMetric": "COSINE"
}

Impact at Scale (200K Headlines)

COSINE → best semantic similarity (recommended for text)
EUCLIDEAN → slightly different ranking due to magnitude sensitivity

This is now a runtime choice, not hardcoded in SQL.

8. What Changed Architecturally?

Then

SQL handled:
- embedding generation
- similarity calculation
ORDS:
- exposed SQL via REST

Now (ORDS 26.1.0)

Database:
- stores vectors
- performs similarity search
Client:
- generates embeddings
ORDS:
- provides a native vector search endpoint

Final Thoughts

Using the same CCNEWS dataset from the prerequisite article, we’ve moved from:

Custom REST services wrapping SQL

to:

Native vector search exposed automatically via ORDS

With Oracle Database 26ai + ORDS 26.1.0, the requirements are minimal:

A VECTOR column
A primary key
One ORDS.enable_object call

And your dataset becomes:

A fully functional semantic search API

Managing Oracle database accounts is an essential part of database security and operational hygiene. One key task is identifying accounts that have not been used for extended periods, such as over 30 days. While queries against DBA_USERS or ALL_USERS provide basic metadata about database accounts, they do not reliably indicate actual usage, particularly for REST-enabled schemas. This is because Oracle REST Data Services (ORDS) and other middleware may connect to the database via a “connect through” mechanism, creating sessions on behalf of the schema without a traditional direct login.

For precise auditing of user activity, Oracle 26ai’s Unified Audit framework offers a reliable solution.

What is a Unified Audit Policy?

Unified Auditing consolidates Oracle’s auditing mechanisms into a single, consistent framework. It allows administrators to define audit policies that track database activity across sessions, users, and even system-level events.

A Unified Audit Policy is a set of rules that specifies which actions are audited. When enabled, it records events in the UNIFIED_AUDIT_TRAIL view, including logins, logouts, DML operations, and privilege usage. This provides a reliable, timestamped record of actual database activity.

Define The Policy

Oracle 26ai ships with a predefined Unified Audit Policy called ORA_LOGIN_LOGOUT. This policy is designed to track user logins and logouts. However, by default it only tracks failures unless your System Administrator has altered the policy definition. So lets verify your audit policy state.

To confirm that Unified Audit is enabled in your database run this query. It should return TRUE.

SELECT VALUE
FROM V$OPTION
WHERE PARAMETER = 'Unified Auditing';

Now check the status of the predefined ORA_LOGIN_LOGOUT policy…

SELECT * FROM audit_unified_enabled_policies where POLICY_NAME = 'ORA_LOGIN_LOGOUT';

The results could look like this indicating that only failed attempts are recorded.

If the predefined ORA_LOGIN_LOGOUT policy is not recording on SUCCESS then define a new policy to achieve that and activate it…

CREATE AUDIT POLICY ALL_LOGONS ACTIONS LOGON;

AUDIT POLICY ALL_LOGONS;

You can verify that policy definition and that it is enabled by running the following queries:

SELECT * FROM audit_unified_enabled_policies where POLICY_NAME = 'ALL_LOGONS';

select * from AUDIT_UNIFIED_POLICIES where policy_name = 'ALL_LOGONS';

You can verify that LOGON activity is now being recorded based on this policy by checking the UNIFIED_AUDIT_TRAIL:

SELECT * from UNIFIED_AUDIT_TRAIL where UNIFIED_AUDIT_POLICIES = 'ALL_LOGONS' order by EVENT_TIMESTAMP_UTC desc;

Refer to Oracle documentation for more details: Unified Audit Policies.

Identifying Inactive Users

Now that you are recording the LOGON activity you can review the audit trail view. To find database accounts that have not logged in for over 30 days, you can query the UNIFIED_AUDIT_TRAIL using the following SQL:

SELECT
    UAT.DBUSERNAME,
    MAX(UAT.EVENT_TIMESTAMP_UTC) AS LAST_LOGIN_UTC
FROM
    UNIFIED_AUDIT_TRAIL UAT,
    ALL_USERS           AU
WHERE
    UAT.ACTION_NAME = 'LOGON'
    AND UAT.DBUSERNAME = AU.USERNAME
GROUP BY
    UAT.DBUSERNAME
HAVING
    MAX(UAT.EVENT_TIMESTAMP_UTC) < SYSTIMESTAMP AT TIME ZONE 'UTC' - INTERVAL '30' DAY
ORDER BY
    LAST_LOGIN_UTC DESC;

Explanation of the query:

DBUSERNAME identifies the database account.
EVENT_TIMESTAMP_UTC captures the precise UTC timestamp of the last login.
MAX(EVENT_TIMESTAMP_UTC) gives the most recent login per user.
The join on ALL_USERS ensures you only see database users that still exist.
HAVING MAX(EVENT_TIMESTAMP_UTC) < SYSTIMESTAMP AT TIME ZONE 'UTC' - INTERVAL '30' DAY filters accounts that have been inactive for over 30 days.
ORDER BY LAST_LOGIN_UTC DESC lists the accounts from least to most recently active.

This approach is far superior to just using DBA_USERS.LAST_LOGIN or ALL_USERS because it captures all session activity, including sessions initiated via REST services like ORDS. Accounts that appear “inactive” in DBA_USERS may, in fact, be used regularly through REST-enabled access.

Why DBA_USERS or ALL_USERS Isn’t Enough

Traditional queries like:

SELECT username, last_login
FROM dba_users;

SELECT username
FROM all_users;

have limitations:

No REST context – They only reflect direct database logins, missing sessions created via middleware using “connect through” mechanisms.
Potentially outdated – DBA_USERS.LAST_LOGIN is only updated on direct logins.

Unified Auditing solves all these limitations by taking advantage of UNIFIED_AUDIT_TRAIL, recording every login event centrally, giving administrators a reliable view of database account activity.

Conclusion

By taking advantage of UNIFIED_AUDIT_TRAIL, administrators can:

Accurately determine which accounts are truly inactive.
Include REST-enabled schemas in their audits.
Improve security by identifying accounts that may no longer require access.

If it is enabled for SUCCESS then the ORA_LOGIN_LOGOUT Unified Audit Policy is the recommended approach for identifying inactive database accounts in Oracle 26ai. Otherwise, you will have to define a new policy as detailed in this article.

Enabling and managing the audit policies is a critical best practice for modern Oracle databases, particularly those supporting REST services through ORDS. It is important to also have procedures in place to archive old audit trail records so they do not fill disk space.

What audit policies for ORDS do you have in place for monitoring activity?

Tag: 26ai

Zero-Code Vector Search for Oracle Database 26ai