My video demonstration on using python-oracledb to extract data from an Oracle Database and read it into a Pandas Dataframe
I am an Oracle Database Specialist in Singapore.
Please note that this site uses cookies.
06 September, 2026
08 August, 2026
New JOIN TO ONE clause in 26.2 SELECT
I've just published a short video on the new JOIN TO ONE clause in SELECT statements in Oracle 26ai 26.2
This clause allows you to let the database automatically determine JOIN columns based on Primary Key and Foreign Key relationships configured in the database. JOIN TO ONE defaults to doing a LEFT OUTER JOIN so I also demonstrate how to use it for INNER JOINs
05 August, 2026
Tracing a Power BI DirectQuery Refresh in Oracle
In today's video I have demonstrated how Power BI can use DirectQuery to query an Oracle database and refresh reports without actually storing the data in the Power BI file (as would be done if "Import" was used instead of DirectQuery).
I have used SQL Tracing in the Database Instance to identify the SQL statement that Power BI executes
For the first visual in Power BI which shows total salary by Department, the Power BI module and SQL statement are identified as :
MODULE NAME:(msmdsrv.exe)
CLIENT DRIVER:(ODPM.NET : 23.6.0.0.0)
sqlid='c8a9qd2dzks8h'
SELECT * FROM (
SELECT
*
FROM
(
SELECT
"t1"."DEPARTMENT_NAME" "c6", SUM ( "t4"."SALARY" )
"a0"
FROM
((
select "$Table"."EMPLOYEE_ID" as "EMPLOYEE_ID",
"$Table"."FIRST_NAME" as "FIRST_NAME",
"$Table"."LAST_NAME" as "LAST_NAME",
"$Table"."EMAIL" as "EMAIL",
"$Table"."PHONE_NUMBER" as "PHONE_NUMBER",
"$Table"."HIRE_DATE" as "HIRE_DATE",
"$Table"."JOB_ID" as "JOB_ID",
"$Table"."SALARY" as "SALARY",
"$Table"."COMMISSION_PCT" as "COMMISSION_PCT",
"$Table"."MANAGER_ID" as "MANAGER_ID",
"$Table"."DEPARTMENT_ID" as "DEPARTMENT_ID"
from "HR"."EMPLOYEES" "$Table"
) "t4"
LEFT OUTER JOIN
(
select "$Table"."DEPARTMENT_ID" as "DEPARTMENT_ID",
"$Table"."DEPARTMENT_NAME" as "DEPARTMENT_NAME",
"$Table"."MANAGER_ID" as "MANAGER_ID",
"$Table"."LOCATION_ID" as "LOCATION_ID"
from "HR"."DEPARTMENTS" "$Table"
) "t1" on
(
"t4"."DEPARTMENT_ID" = "t1"."DEPARTMENT_ID"
)
)
GROUP BY "t1"."DEPARTMENT_NAME"
)
"MainTable"
WHERE
(
NOT(
(
"a0" IS NULL
)
)
)
ORDER BY "a0"
DESC
,"c6"
ASC
) WHERE ROWNUM (lessthan) 1001
MODULE NAME:(msmdsrv.exe)
CLIENT DRIVER:(ODPM.NET : 23.6.0.0.0)
sqlid='dh7nbfqsy942q'
SELECT
"t1"."DEPARTMENT_NAME" "c6",
COUNT("t4"."EMPLOYEE_ID")
"a0"
FROM
((
select "$Table"."EMPLOYEE_ID" as "EMPLOYEE_ID",
"$Table"."FIRST_NAME" as "FIRST_NAME",
"$Table"."LAST_NAME" as "LAST_NAME",
"$Table"."EMAIL" as "EMAIL",
"$Table"."PHONE_NUMBER" as "PHONE_NUMBER",
"$Table"."HIRE_DATE" as "HIRE_DATE",
"$Table"."JOB_ID" as "JOB_ID",
"$Table"."SALARY" as "SALARY",
"$Table"."COMMISSION_PCT" as "COMMISSION_PCT",
"$Table"."MANAGER_ID" as "MANAGER_ID",
"$Table"."DEPARTMENT_ID" as "DEPARTMENT_ID"
from "HR"."EMPLOYEES" "$Table"
) "t4"
LEFT OUTER JOIN
(
select "$Table"."DEPARTMENT_ID" as "DEPARTMENT_ID",
"$Table"."DEPARTMENT_NAME" as "DEPARTMENT_NAME",
"$Table"."MANAGER_ID" as "MANAGER_ID",
"$Table"."LOCATION_ID" as "LOCATION_ID"
from "HR"."DEPARTMENTS" "$Table"
) "t1" on
(
"t4"."DEPARTMENT_ID" = "t1"."DEPARTMENT_ID"
)
)
GROUP BY "t1"."DEPARTMENT_NAME"
Thus, every refresh runs a number of queries -- some to synchronise the schema from Oracle to Power BI and others to refresh the numbers to present in the Visuals.
This proves that the actual load of computing the GROUP BY and aggregations is in the *database instance* (because that is where the data actually resides) and not in the Power BI file (because no data is copied into the Power BI file)24 May, 2026
WAIT Clause for DMLs in 26.2
Oracle 26ai 26.2 now introduces the WAIT (and NOWAIT) Clause for INSERT / UPDATE / DELETE / MERGE DMLs. We have had a WAIT Clause for SELECT FOR UPDATE statements but not for these "simple" DML statements.
This is my Video Demo : Wait Clause for DMLs in 26.2
03 May, 2026
Domains and Annotations in 26ai
In a previous blog post I had demonstrated Domains for Columns and an Annotation for a table in 23ai.
Domains are "Data Use Case Domains" -- predefined Dictionary Objects that encapsulate properties and constraints for the data being stored in the target columns.
Here's the 26ai documentation on Data Use Case Domains : Data Use Case Domains
"An data use case domain is a high-level dictionary object that belongs to a schema and encapsulates a set of optional properties and constraints."
"...a column can be declared both with a primitive data type such as NUMBER, as well as with a domain for data usage, such as "Temperature" or "Credit Score". Such a use case domain can optionally be associated with different usage properties such as check constraints, display properties, ordering rules, and others."
There are 109 Pre-Built Domains (as in 23.26.0 Free).
SQL> select owner, builtin, count(*) from all_domains group by owner, builtin order by 1,2;
OWNER BUILTIN COUNT(*) ------------ ----------- ---------- SYS TRUE 109 SQL>
But you can add your own Custom Domains. In this demonstration, I show two custom domains. Although the SALARY_D domain could have been a simple CHECK Constraint on the EMPLOYEES_TBL, I could use this domain in, say, DEPARTMENTS (for MAX_SALARY) or SALES_COMMISSION or CONTRACTOR_REMUNERATION etc.
I build the SALARY_D just as a simple example domain.
Annotations are "Metadata" -- that define and identify the data. Annotations are very good use cases for implementing AI where Natural Language queries from users are translated to SQL based on Annotations.
Here's the 26ai documentation on Annotations as a New Feature : Schema Annotations
"Oracle AI Database schema annotations are a lightweight declarative facility for developers to centrally register usage properties for database schema objects. Annotations are stored directly inside the database in dictionary tables alongside the data model definition and the data itself, and available to any applications in order to standardize behavior across common data, but are not interpreted by the database in any way. They should be thought of as lightweight standardized markup for database metadata, for use by applications to register and process extended and custom usage properties."
Here is one example of a table using Domains and Annotations :
SQL> @Domains_and_Annotations_Demo.sql
SQL> connect system/oracle
Connected.
SQL> grant create domain to hr
2 /
Grant succeeded.
SQL>
SQL> connect hr/oracle
Connected.
SQL>
SQL> drop table employees_tbl
2 /
Table dropped.
SQL>
SQL> create domain if not exists age_d as integer
2 constraint age_min check (age_d >= 18)
3 /
Domain created.
SQL>
SQL> create domain if not exists salary_d as number
2 constraint salary_max check (salary_d le 20000) -- I have changed the "less-than-or-equal-to" sign to "le" to preserve HTML parsing
3 /
Domain created.
SQL>
SQL>
SQL> create table employees_tbl
2 (
3 employee_id number generated by default on null as identity annotations (PrimaryKey, Sequence),
4 first_name varchar2(250) not null annotations (Display 'Employee First Name'),
5 middle_name varchar2(250) annotations (Display 'Employee Middle Name [optional]'),
6 last_name varchar2(250) not null annotations (Display 'Employee Last Name'),
7 join_age integer domain age_d annotations (Display 'Age at Start of Employment'),
8 employee_email_id varchar2(512) domain email_d annotations (Sensitive),
9 dept_id number not null annotations (Display 'Department ID'),
10 citizen_country varchar2(3) domain country_code_d annotations (Case 'UPPER', Display 'Citizenship Country ISO Code'),
11 resident_country varchar2(3) domain country_code_d annotations (Case 'UPPER', Display 'Residence Country ISO Code'),
12 current_salary number domain salary_d annotations (Display 'Employee Current Salary'),
13 salary_date date not null annotations (Display 'Current Salary Begin Date')
14 )
15 annotations (display 'Employees Table')
16 /
Table created.
SQL>
SQL>
SQL> describe employees_tbl
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
EMPLOYEE_ID NOT NULL NUMBER
FIRST_NAME NOT NULL VARCHAR2(250)
MIDDLE_NAME VARCHAR2(250)
LAST_NAME NOT NULL VARCHAR2(250)
JOIN_AGE NUMBER(38) HR.AGE_D
EMPLOYEE_EMAIL_ID VARCHAR2(512) SYS.EMAIL_D
DEPT_ID NOT NULL NUMBER
CITIZEN_COUNTRY VARCHAR2(3) SYS.COUNTRY_CODE_D
RESIDENT_COUNTRY VARCHAR2(3) SYS.COUNTRY_CODE_D
CURRENT_SALARY NUMBER HR.SALARY_D
SALARY_DATE NOT NULL DATE
SQL>
SQL>
SQL> set pagesize 600
SQL> set linesize 132
SQL> set long 2000
SQL> set longchunksize 1000
SQL> set serveroutput on
SQL> select dbms_metadata.get_ddl('TABLE','EMPLOYEES_TBL')
2
SQL>
SQL> col columm_name format a21
SQL> col annotation_name format a21
SQL> col domain_name format a21
SQL> col annotation_value format a31
SQL>
SQL> select column_name, domain_name, annotation_name, annotation_value
2 from user_annotations_usage
3 where object_name = 'EMPLOYEES_TBL'
4 and object_type = 'TABLE'
5 order by column_name, annotation_name
6 /
COLUMN_NAME DOMAIN_NAME ANNOTATION_NAME ANNOTATION_VALUE
------------------------------ --------------------- --------------------- -------------------------------
CITIZEN_COUNTRY COUNTRY_CODE_D ADDRESS
CITIZEN_COUNTRY CASE UPPER
CITIZEN_COUNTRY DISPLAY Citizenship Country ISO Code
CURRENT_SALARY DISPLAY Employee Current Salary
DEPT_ID DISPLAY Department ID
EMPLOYEE_EMAIL_ID EMAIL_D PERSON_INFO
EMPLOYEE_EMAIL_ID SENSITIVE
EMPLOYEE_ID PRIMARYKEY
EMPLOYEE_ID SEQUENCE
FIRST_NAME DISPLAY Employee First Name
JOIN_AGE DISPLAY Age at Start of Employment
LAST_NAME DISPLAY Employee Last Name
MIDDLE_NAME DISPLAY Employee Middle Name [optional]
RESIDENT_COUNTRY COUNTRY_CODE_D ADDRESS
RESIDENT_COUNTRY CASE UPPER
RESIDENT_COUNTRY DISPLAY Residence Country ISO Code
SALARY_DATE DISPLAY Current Salary Begin Date
DISPLAY Employees Table
18 rows selected.
SQL>
SQL> -- the output above does NOT show the Custom Domains, only the preseeded COUNTRY_CODE_D and EMAIL_D
SQL> col domain_name format a21
SQL>
SQL> select name as domain_name, builtin, type
2 from user_domains
3 order by name
4 /
DOMAIN_NAME BUILTIN TYPE
--------------------- ----------- ----------
AGE_D FALSE REGULAR
SALARY_D FALSE REGULAR
SQL>
SQL>
SQL>
So, I have now created the Table using Domains and Annotations.
Now, I test the defined Domains for validation.
SQL> -- test the AGE_D domain for Join Age
insert into employees_tbl
(first_name, last_name,join_age,employee_email_id,dept_id,citizen_country,resident_country,current_salary,salary_date)
values
('ABC','XYZ',17,'abc@co.com',10,'US','US',8000,sysdate)
/
SQL> 2 3 4 5 insert into employees_tbl
*
ERROR at line 1:
ORA-11534: check constraint (HR.SYS_C0013676) involving column JOIN_AGE due to domain constraint HR.AGE_MIN of domain HR.AGE_D
violated
Help: https://docs.oracle.com/error-help/db/ora-11534/
SQL>
SQL> insert into employees_tbl
(first_name, last_name,join_age,employee_email_id,dept_id,citizen_country,resident_country,current_salary,salary_date)
values
('ABC','XYZ',21,'abc@co.com',10,'US','US',8000,sysdate)
/
2 3 4 5
1 row created.
SQL>
-- test the SALARY domain for Salary
SQL> insert into employees_tbl
(first_name, last_name,join_age,employee_email_id,dept_id,citizen_country,resident_country,current_salary,salary_date)
values
('ABC','XYZ',21,'abc@co.com',10,'US','US',32000,sysdate)
/
2 3 4 5 insert into employees_tbl
*
ERROR at line 1:
ORA-11534: check constraint (HR.SYS_C0013674) involving column CURRENT_SALARY due to domain constraint HR.SALARY_MAX of domain
HR.SALARY_D violated
Help: https://docs.oracle.com/error-help/db/ora-11534/
SQL>
SQL> insert into employees_tbl
(first_name, last_name,join_age,employee_email_id,dept_id,citizen_country,resident_country,current_salary,salary_date)
values
('ABC','XYZ',21,'abc@co.com',10,'US','US',19000,sysdate)
/ 2 3 4 5
1 row created.
SQL>
-- test the EMAIL_D domain for Email Address
insert into employees_tbl
(first_name, last_name,join_age,employee_email_id,dept_id,citizen_country,resident_country,current_salary,salary_date)
values
('ABC','XYZ',21,'abc@xyz',10,'US','US',32000,sysdate)
/
SQL> 2 3 4 5 insert into employees_tbl
*
ERROR at line 1:
ORA-11534: check constraint (HR.SYS_C0013675) involving column EMPLOYEE_EMAIL_ID due to domain constraint SYS.SYS_DOMAIN_C0030 of
domain SYS.EMAIL_D violated
Help: https://docs.oracle.com/error-help/db/ora-11534/
SQL>
SQL> insert into employees_tbl
(first_name, last_name,join_age,employee_email_id,dept_id,citizen_country,resident_country,current_salary,salary_date)
values
('ABC','XYZ',21,'abc@co.com',10,'US','US',19000,sysdate)
/
2 3 4 5
1 row created.
SQL>
SQL> -- test the COUNTRY_CODE_D
insert into employees_tbl
(first_name, last_name,join_age,employee_email_id,dept_id,citizen_country,resident_country,current_salary,salary_date)
values
('ABC','XYZ',21,'abc@co.com',10,'XYZ','US',19000,sysdate)
/
SQL> 2 3 4 5
1 row created.
SQL>
-- Note that the country_code_d does not seem tocheck validity against real ISO codes in my current 23.26.0 FREE release. It seems to check only that a string of characters is entered as a COUNTRY_CODE
So, the Domain Rules for AGE_D (custom domain defined by me), SALARY_D (custom domain defined by me) and EMAIL_D (system domain preseeded in the database) are all working as expected.15 February, 2026
Partition by Expression
Oracle 26.1 now allows using an Expression in the Partitioning Clause. In earlier releases, you could do this with a Virtual Column defined in the table. However, 26.1 does not require the Virtual Column if it is used simply for the Partitioning Clause.
Here is a demo in 19.12 comparing the two methods :
-- using a Virtual Column "ORDER_MONTH"
SQL> l
1 CREATE TABLE ORDERS_2026_old (
2 order_id NUMBER NOT NULL,
3 order_date DATE NOT NULL,
4 customer_id NUMBER,
5 order_month NUMBER GENERATED ALWAYS AS (TO_NUMBER(TO_CHAR(order_date, 'MM'))) VIRTUAL
6 )
7 PARTITION BY RANGE (order_month)
8 (
9 PARTITION orders_q1 VALUES LESS THAN (4), -- Months 1, 2, 3
10 PARTITION orders_q2 VALUES LESS THAN (7), -- Months 4, 5, 6
11 PARTITION orders_q3 VALUES LESS THAN (10), -- Months 7, 8, 9
12 PARTITION orders_q4 VALUES LESS THAN (13) -- Months 10, 11, 12
13* )
SQL> /
Table created.
SQL>
-- try the Expression in the Partitioning Clause
SQL> l
1 CREATE TABLE ORDERS_2026_new (
2 order_id NUMBER NOT NULL,
3 order_date DATE NOT NULL,
4 customer_id NUMBER
5 )
6 PARTITION BY RANGE ((TO_NUMBER(TO_CHAR(order_date, 'MM'))))
7 (
8 PARTITION orders_q1 VALUES LESS THAN (4), -- Months 1, 2, 3
9 PARTITION orders_q2 VALUES LESS THAN (7), -- Months 4, 5, 6
10 PARTITION orders_q3 VALUES LESS THAN (10), -- Months 7, 8, 9
11 PARTITION orders_q4 VALUES LESS THAN (13) -- Months 10, 11, 12
12* )
SQL> /
PARTITION BY RANGE ((TO_NUMBER(TO_CHAR(order_date, 'MM'))))
*
ERROR at line 6:
ORA-00904: : invalid identifier
SQL>
And now in 26.1 :
-- using the Virtual Column "ORDER_MONTH", works as expected
SQL> l
1 CREATE TABLE ORDERS_2026_old (
2 order_id NUMBER NOT NULL,
3 order_date DATE NOT NULL,
4 customer_id NUMBER,
5 order_month NUMBER GENERATED ALWAYS AS (TO_NUMBER(TO_CHAR(order_date, 'MM'))) VIRTUAL
6 )
7 PARTITION BY RANGE (order_month)
8 (
9 PARTITION orders_q1 VALUES LESS THAN (4), -- Months 1, 2, 3
10 PARTITION orders_q2 VALUES LESS THAN (7), -- Months 4, 5, 6
11 PARTITION orders_q3 VALUES LESS THAN (10), -- Months 7, 8, 9
12 PARTITION orders_q4 VALUES LESS THAN (13) -- Months 10, 11, 12
13* )
SQL> /
Table created.
SQL>
-- using the Expression for the Partitioning Clause
SQL> l
1 CREATE TABLE ORDERS_2026_NEW (
2 order_id NUMBER NOT NULL,
3 order_date DATE NOT NULL,
4 customer_id NUMBER
5 )
6 PARTITION BY RANGE ((TO_NUMBER(TO_CHAR(order_date, 'MM'))))
7 (
8 PARTITION orders_q1 VALUES LESS THAN (4), -- Months 1, 2, 3
9 PARTITION orders_q2 VALUES LESS THAN (7), -- Months 4, 5, 6
10 PARTITION orders_q3 VALUES LESS THAN (10), -- Months 7, 8, 9
11 PARTITION orders_q4 VALUES LESS THAN (13) -- Months 10, 11, 12
12* )
SQL> /
Table created.
SQL>
SQL> insert into orders_2026_new values (1, sysdate, 1);
1 row created.
SQL>
QL> insert into orders_2026_new values (2, to_date('15-JUN-2026','DD-MON-RRRR'),2);
1 row created.
SQL> commit;
Commit complete.
SQL>
-- gather statistics
SQL> exec dbms_stats.gather_table_stats('','ORDERS_2026_NEW');
PL/SQL procedure successfully completed.
SQL>
-- check number of rows in each partition
SQL> col partition_name format a32
SQL> select partition_name, num_rows
2 from user_tab_partitions
3 where table_name = 'ORDERS_2026_NEW'
4 /
PARTITION_NAME NUM_ROWS
-------------------------------- ----------
ORDERS_Q1 1
ORDERS_Q2 1
ORDERS_Q3 0
ORDERS_Q4 0
SQL>
-- check rows in the specific partitions
-- get all the rows where the month number is less than 4
SQL> select * from orders_2026_new
2 partition for (3)
3 /
ORDER_ID ORDER_DATE CUSTOMER_ID
---------- ------------------ -----------
1 15-FEB-26 1
SQL>
-- get all the rows where the month number is less than 7
SQL> select * from orders_2026_new
2 partition for (6)
3 /
ORDER_ID ORDER_DATE CUSTOMER_ID
---------- ------------------ -----------
2 15-JUN-26 2
SQL>
Since partitioning is by Month Number (would have been the Virtual Column ORDER_MONTH) in the 19c example, I can use the "PARTITION FOR (<month_number>)" in my SELECT statement to query a specific partition.
I can confirm that ORDER_ID 1 is in the first partition (Month Number less than 4) and ORDER_ID 2 is in the second partition (Month Number less than 7)
08 February, 2026
The new FILTER clause in Aggregations
Oracle 26.1 introduces the new FILTER clause that can be used in aggregations.
Thus, for example, in a SUM aggregation you can use FILTER WHERE ...
Here is a quick example :
SQL> create table obj_list as select * from dba_objects;
Table created.
SQL> select sum(object_id) from obj_list;
SUM(OBJECT_ID)
--------------
2604221816
SQL> select object_type, sum(object_id) from obj_list group by object_type order by 1;
OBJECT_TYPE SUM(OBJECT_ID)
----------------------- --------------
CLUSTER 3036
CONSUMER GROUP 435231
CONTEXT 1048288
...... deleted a number of object types
SEQUENCE 7551346
SYNONYM 473741064
TABLE 44661426 --> sum of object_ids for object_type TABLE
TABLE PARTITION 15047252
TABLE SUBPARTITION 603760
...... deleted a few more object types
XML SCHEMA 511732
46 rows selected.
SQL> select sum(object_id) filter (where object_type = 'TABLE') as Table_ObjID_Sum from obj_list;
TABLE_OBJID_SUM
---------------
44661426
SQL>
1. Oracle Documentation New Features Guide: Aggregation Filters
2. Oracle Documentation SQL For Application Developers : 14.2 Aggregation Filters
3. Markus Winand's Modern SQL Reference : Filter
28 December, 2025
Video Demonstration of Retrieval (in RAG)
I have created a Video Demo of the Retrieval for RAG, based on what I have posted here blog posts.
16 November, 2025
RAG with Vector Index in 26ai
Updating my previous demo that was in 23ai to run in Oracle AI Database 26ai with two enhancements :
vector_memory_size set to 512MB (yes, this is a very small on-premises Free 26ai image)
INMEMORY NEIGHBOR GRAPH Index using Hierarchical Navigable Small World (HNSW)
[oracle@localhost ~]$ sqlplus vector_demo/vector_demo
SQL*Plus: Release 23.26.0.0.0 - Production on Sun Nov 16 09:37:39 2025
Version 23.26.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Last Successful login time: Sun Nov 16 2025 09:32:43 +00:00
Connected to:
Oracle AI Database 26ai Free Release 23.26.0.0.0 - Develop, Learn, and Run for Free
Version 23.26.0.0.0
SQL> set echo on
SQL> !ls *sql
Create_Vector_Index.sql Query_Vectors.sql
SQL> @Create_Vector_Index.sql
SQL> CREATE VECTOR INDEX my_data_vectors_ndx ON my_data_vectors (sentence_vector)
2 ORGANIZATION INMEMORY NEIGHBOR GRAPH
3 DISTANCE COSINE
4 WITH TARGET ACCURACY 95
5 /
Index created.
SQL> show parameter vector_memory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
vector_memory_size big integer 512M
SQL> @Query_Vectors.sql
SQL> set pages600
SQL> set linesize 156
SQL> col my_sentence format a148 wrap
SQL>
SQL> ACCEPT text_input CHAR PROMPT 'Enter your query : '
Enter your query : image processing
SQL> VARIABLE text_variable VARCHAR2(1000)
SQL> VARIABLE query_vector VECTOR
SQL> BEGIN
2 :text_variable := '&text_input';
3 SELECT vector_embedding(ALL_MINILM_L12_V2_AUGMENTED USING :text_variable as data) into :query_vector;
4 END;
5 /
old 2: :text_variable := '&text_input';
new 2: :text_variable := 'image processing';
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> SELECT my_sentence, vector_distance(sentence_vector , :query_vector, COSINE) as Calc_Vector_Distance
2 FROM my_data_vectors
3 ORDER BY 2
4 FETCH FIRST 3 ROWS ONLY;
MY_SENTENCE
----------------------------------------------------------------------------------------------------------------------------------------------------
CALC_VECTOR_DISTANCE
--------------------
VanceAI.com image enhancement
5.16E-001
Stable Diffusion: An open source model that generates high quality images from text or other images, offering customization and control
5.51E-001
Hotpot.ai AI image editing
6.109E-001
SQL>
SQL> SELECT my_sentence, vector_distance(sentence_vector , :query_vector, COSINE) as Calc_Vector_Distance
2 FROM my_data_vectors
3 ORDER BY 2
4 FETCH APPROX FIRST 3 ROWS ONLY;
MY_SENTENCE
----------------------------------------------------------------------------------------------------------------------------------------------------
CALC_VECTOR_DISTANCE
--------------------
VanceAI.com image enhancement
5.16E-001
Stable Diffusion: An open source model that generates high quality images from text or other images, offering customization and control
5.51E-001
Hotpot.ai AI image editing
6.109E-001
SQL>
SQL> select * from dbms_xplan.display_cursor();
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1z2ujsrc9xsb0, child number 0
-------------------------------------
SELECT my_sentence, vector_distance(sentence_vector , :query_vector,
COSINE) as Calc_Vector_Distance FROM my_data_vectors ORDER BY 2 FETCH
APPROX FIRST 3 ROWS ONLY
Plan hash value: 3894957757
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 3 | 6024 | 2 (50)| 00:00:01 |
|* 3 | SORT ORDER BY STOPKEY | | 3 | 4938 | 2 (50)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| MY_DATA_VECTORS | 3 | 4938 | 1 (0)| 00:00:01 |
| 5 | VECTOR INDEX HNSW SCAN | MY_DATA_VECTORS_NDX | 3 | 4938 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=3)
3 - filter(ROWNUM<=3)
25 rows selected.
SQL>
Here I demonstrate querying the same set of 130 sentences about AI as in the previous demo, but now with a Vector Index configured as an In-Memory Neighbour Vector Graph Index and a Target Accuracy of 95% based on COSINE Distance.
My next run would be with a much larger data set (instead of just 130 sentences)
07 November, 2025
Incremental Backups of a Standby Database
In response to a comment on a previous Blog Post RMAN Backup of a Standby Database, I show incremental Backups as well below
RMAN> backup as compressed backupset incremental level 0 database;
backup as compressed backupset incremental level 0 database;
Starting backup at 07-NOV-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=219 device type=DISK
channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/Standby_DB/oradata/STDBY/datafile/o1_mf_system_n16dxho8_.dbf
input datafile file number=00003 name=/Standby_DB/oradata/STDBY/datafile/o1_mf_sysaux_n16dxhob_.dbf
input datafile file number=00004 name=/Standby_DB/oradata/STDBY/datafile/o1_mf_undotbs1_n16dxhom_.dbf
input datafile file number=00009 name=/Standby_DB/oradata/STDBY/datafile/o1_mf_undotbs2_n16dxhon_.dbf
input datafile file number=00007 name=/Standby_DB/oradata/STDBY/datafile/o1_mf_users_n16dxhoo_.dbf
channel ORA_DISK_1: starting piece 1 at 07-NOV-25
channel ORA_DISK_1: finished piece 1 at 07-NOV-25
piece handle=/Standby_DB/FRA/STDBY/backupset/2025_11_07/o1_mf_nnnd0_TAG20251107T055853_njv2nxdx_.bkp tag=TAG20251107T055853 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=/Standby_DB/oradata/STDBY/1476C653214704CFE0635A38A8C08494/datafile/o1_mf_system_n16dz6sq_.dbf
input datafile file number=00011 name=/Standby_DB/oradata/STDBY/1476C653214704CFE0635A38A8C08494/datafile/o1_mf_sysaux_n16dz6sr_.dbf
input datafile file number=00012 name=/Standby_DB/oradata/STDBY/1476C653214704CFE0635A38A8C08494/datafile/o1_mf_undotbs1_n16dz6t1_.dbf
input datafile file number=00013 name=/Standby_DB/oradata/STDBY/1476C653214704CFE0635A38A8C08494/datafile/o1_mf_undo_2_n16dz6t2_.dbf
input datafile file number=00014 name=/Standby_DB/oradata/STDBY/1476C653214704CFE0635A38A8C08494/datafile/o1_mf_users_n16dz6tc_.dbf
channel ORA_DISK_1: starting piece 1 at 07-NOV-25
channel ORA_DISK_1: finished piece 1 at 07-NOV-25
piece handle=/Standby_DB/FRA/STDBY/1476C653214704CFE0635A38A8C08494/backupset/2025_11_07/o1_mf_nnnd0_TAG20251107T055853_njv2q8kr_.bkp tag=TAG20251107T055853 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00060 name=/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-SYSTEM_FNO-60.dbf
input datafile file number=00061 name=/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-SYSAUX_FNO-61.dbf
input datafile file number=00062 name=/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-UNDOTBS1_FNO-62.dbf
input datafile file number=00063 name=/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-UNDO_2_FNO-63.dbf
input datafile file number=00064 name=/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-USERS_FNO-64.dbf
channel ORA_DISK_1: starting piece 1 at 07-NOV-25
channel ORA_DISK_1: finished piece 1 at 07-NOV-25
piece handle=/Standby_DB/FRA/STDBY/38AE431466FE1FDBE0635A38A8C085D8/backupset/2025_11_07/o1_mf_nnnd0_TAG20251107T055853_njv2rzo7_.bkp tag=TAG20251107T055853 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/Standby_DB/oradata/STDBY/14769E258FBB5FD8E0635A38A8C09D43/datafile/o1_mf_system_n16f20wr_.dbf
input datafile file number=00006 name=/Standby_DB/oradata/STDBY/14769E258FBB5FD8E0635A38A8C09D43/datafile/o1_mf_sysaux_n16f20x3_.dbf
input datafile file number=00008 name=/Standby_DB/oradata/STDBY/14769E258FBB5FD8E0635A38A8C09D43/datafile/o1_mf_undotbs1_n16f20xg_.dbf
channel ORA_DISK_1: starting piece 1 at 07-NOV-25
channel ORA_DISK_1: finished piece 1 at 07-NOV-25
piece handle=/Standby_DB/FRA/STDBY/14769E258FBB5FD8E0635A38A8C09D43/backupset/2025_11_07/o1_mf_nnnd0_TAG20251107T055853_njv2tprh_.bkp tag=TAG20251107T055853 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
Finished backup at 07-NOV-25
Starting Control File and SPFILE Autobackup at 07-NOV-25
piece handle=/Standby_DB/FRA/STDBY/autobackup/2025_11_07/o1_mf_s_1216533563_njv2wg2k_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 07-NOV-25
RMAN>
RMAN> exit
exit
RMAN Client Diagnostic Trace file : /u01/app/oracle/diag/clients/user_oracle/host_4144547424_110/trace/ora_2133_140607082957312.trc
Recovery Manager complete.
[oracle@stdby trace]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 7 06:04:10 2025
Version 19.25.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.25.0.0.0
SQL> set pages600 linesize 132
SQL> select database_role, open_mode from v$database
2 /
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED
SQL>
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.25.0.0.0
[oracle@stdby trace]$
[oracle@stdby trace]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Nov 7 06:04:55 2025
Version 19.25.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: RACDB (DBID=1162136313, not open)
RMAN> list backup of datafile 1;
list backup of datafile 1;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
320 Incr 0 1.08G DISK 00:01:10 07-NOV-25
BP Key: 320 Status: AVAILABLE Compressed: YES Tag: TAG20251107T055853
Piece Name: /Standby_DB/FRA/STDBY/backupset/2025_11_07/o1_mf_nnnd0_TAG20251107T055853_njv2nxdx_.bkp
List of Datafiles in backup set 320
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 0 Incr 13423959 07-NOV-25 NO /Standby_DB/oradata/STDBY/datafile/o1_mf_system_n16dxho8_.dbf
RMAN>
RMAN> select current_scn from v$database;
select current_scn from v$database;
CURRENT_SCN
-----------
13442704
RMAN>
[oracle@stdby trace]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 7 06:07:11 2025
Version 19.25.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.25.0.0.0
SQL> select current_scn, database_role, open_mode from v$database;
CURRENT_SCN DATABASE_ROLE OPEN_MODE
----------- ---------------- --------------------
13442704 PHYSICAL STANDBY MOUNTED
SQL> /
CURRENT_SCN DATABASE_ROLE OPEN_MODE
----------- ---------------- --------------------
13443585 PHYSICAL STANDBY MOUNTED
SQL>
SQL> SQL> l
1* select current_scn, database_role, open_mode from v$database
SQL> /
CURRENT_SCN DATABASE_ROLE OPEN_MODE
----------- ---------------- --------------------
13443585 PHYSICAL STANDBY MOUNTED
SQL> /
CURRENT_SCN DATABASE_ROLE OPEN_MODE
----------- ---------------- --------------------
13449819 PHYSICAL STANDBY MOUNTED
SQL> /
CURRENT_SCN DATABASE_ROLE OPEN_MODE
----------- ---------------- --------------------
13449819 PHYSICAL STANDBY MOUNTED
SQL>
[oracle@stdby trace]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Nov 7 06:10:58 2025
Version 19.25.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: RACDB (DBID=1162136313, not open)
RMAN> backup as compressed backupset incremental level 1 database;
backup as compressed backupset incremental level 1 database;
Starting backup at 07-NOV-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=219 device type=DISK
channel ORA_DISK_1: starting compressed incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/Standby_DB/oradata/STDBY/datafile/o1_mf_system_n16dxho8_.dbf
input datafile file number=00003 name=/Standby_DB/oradata/STDBY/datafile/o1_mf_sysaux_n16dxhob_.dbf
input datafile file number=00004 name=/Standby_DB/oradata/STDBY/datafile/o1_mf_undotbs1_n16dxhom_.dbf
input datafile file number=00009 name=/Standby_DB/oradata/STDBY/datafile/o1_mf_undotbs2_n16dxhon_.dbf
input datafile file number=00007 name=/Standby_DB/oradata/STDBY/datafile/o1_mf_users_n16dxhoo_.dbf
channel ORA_DISK_1: starting piece 1 at 07-NOV-25
channel ORA_DISK_1: finished piece 1 at 07-NOV-25
piece handle=/Standby_DB/FRA/STDBY/backupset/2025_11_07/o1_mf_nnnd1_TAG20251107T061125_njv3dg1n_.bkp tag=TAG20251107T061125 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting compressed incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=/Standby_DB/oradata/STDBY/1476C653214704CFE0635A38A8C08494/datafile/o1_mf_system_n16dz6sq_.dbf
input datafile file number=00011 name=/Standby_DB/oradata/STDBY/1476C653214704CFE0635A38A8C08494/datafile/o1_mf_sysaux_n16dz6sr_.dbf
input datafile file number=00012 name=/Standby_DB/oradata/STDBY/1476C653214704CFE0635A38A8C08494/datafile/o1_mf_undotbs1_n16dz6t1_.dbf
input datafile file number=00013 name=/Standby_DB/oradata/STDBY/1476C653214704CFE0635A38A8C08494/datafile/o1_mf_undo_2_n16dz6t2_.dbf
input datafile file number=00014 name=/Standby_DB/oradata/STDBY/1476C653214704CFE0635A38A8C08494/datafile/o1_mf_users_n16dz6tc_.dbf
channel ORA_DISK_1: starting piece 1 at 07-NOV-25
channel ORA_DISK_1: finished piece 1 at 07-NOV-25
piece handle=/Standby_DB/FRA/STDBY/1476C653214704CFE0635A38A8C08494/backupset/2025_11_07/o1_mf_nnnd1_TAG20251107T061125_njv3do5w_.bkp tag=TAG20251107T061125 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting compressed incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00060 name=/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-SYSTEM_FNO-60.dbf
input datafile file number=00061 name=/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-SYSAUX_FNO-61.dbf
input datafile file number=00062 name=/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-UNDOTBS1_FNO-62.dbf
input datafile file number=00063 name=/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-UNDO_2_FNO-63.dbf
input datafile file number=00064 name=/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-USERS_FNO-64.dbf
channel ORA_DISK_1: starting piece 1 at 07-NOV-25
channel ORA_DISK_1: finished piece 1 at 07-NOV-25
piece handle=/Standby_DB/FRA/STDBY/38AE431466FE1FDBE0635A38A8C085D8/backupset/2025_11_07/o1_mf_nnnd1_TAG20251107T061125_njv3dw8p_.bkp tag=TAG20251107T061125 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting compressed incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/Standby_DB/oradata/STDBY/14769E258FBB5FD8E0635A38A8C09D43/datafile/o1_mf_system_n16f20wr_.dbf
skipping datafile 00005 because it has not changed
input datafile file number=00006 name=/Standby_DB/oradata/STDBY/14769E258FBB5FD8E0635A38A8C09D43/datafile/o1_mf_sysaux_n16f20x3_.dbf
skipping datafile 00006 because it has not changed
input datafile file number=00008 name=/Standby_DB/oradata/STDBY/14769E258FBB5FD8E0635A38A8C09D43/datafile/o1_mf_undotbs1_n16f20xg_.dbf
skipping datafile 00008 because it has not changed
channel ORA_DISK_1: backup cancelled because all files were skipped
Finished backup at 07-NOV-25
Starting Control File and SPFILE Autobackup at 07-NOV-25
piece handle=/Standby_DB/FRA/STDBY/autobackup/2025_11_07/o1_mf_s_1216534110_njv3dzjt_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 07-NOV-25
RMAN>
RMAN> list backup of datafile 1;
list backup of datafile 1;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
320 Incr 0 1.08G DISK 00:01:10 07-NOV-25
BP Key: 320 Status: AVAILABLE Compressed: YES Tag: TAG20251107T055853
Piece Name: /Standby_DB/FRA/STDBY/backupset/2025_11_07/o1_mf_nnnd0_TAG20251107T055853_njv2nxdx_.bkp
List of Datafiles in backup set 320
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 0 Incr 13423959 07-NOV-25 NO /Standby_DB/oradata/STDBY/datafile/o1_mf_system_n16dxho8_.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
325 Incr 1 2.93M DISK 00:00:05 07-NOV-25
BP Key: 325 Status: AVAILABLE Compressed: YES Tag: TAG20251107T061125
Piece Name: /Standby_DB/FRA/STDBY/backupset/2025_11_07/o1_mf_nnnd1_TAG20251107T061125_njv3dg1n_.bkp
List of Datafiles in backup set 325
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 1 Incr 13449820 07-NOV-25 NO /Standby_DB/oradata/STDBY/datafile/o1_mf_system_n16dxho8_.dbf
RMAN>
RMAN> exit
exit
Recovery Manager complete.
[oracle@stdby trace]$ export NLS_DATE_FORMAT=DD_MON_RR_HH24_MI
[oracle@stdby trace]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Nov 7 06:15:24 2025
Version 19.25.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: RACDB (DBID=1162136313, not open)
RMAN> list backup of datafile 1;
list backup of datafile 1;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
320 Incr 0 1.08G DISK 00:01:10 07_NOV_25_06_00
BP Key: 320 Status: AVAILABLE Compressed: YES Tag: TAG20251107T055853
Piece Name: /Standby_DB/FRA/STDBY/backupset/2025_11_07/o1_mf_nnnd0_TAG20251107T055853_njv2nxdx_.bkp
List of Datafiles in backup set 320
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------------- ----------- ------ ----
1 0 Incr 13423959 07_NOV_25_05_56 NO /Standby_DB/oradata/STDBY/datafile/o1_mf_system_n16dxho8_.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
325 Incr 1 2.93M DISK 00:00:05 07_NOV_25_06_11
BP Key: 325 Status: AVAILABLE Compressed: YES Tag: TAG20251107T061125
Piece Name: /Standby_DB/FRA/STDBY/backupset/2025_11_07/o1_mf_nnnd1_TAG20251107T061125_njv3dg1n_.bkp
List of Datafiles in backup set 325
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------------- ----------- ------ ----
1 1 Incr 13449820 07_NOV_25_06_08 NO /Standby_DB/oradata/STDBY/datafile/o1_mf_system_n16dxho8_.dbf
RMAN>
After the Level-0 Backup I waited for some time to confirm that the SCN at the Standby had advanced.
Then, I ran an Level-1 Backup.
The final listing shows two backups of datafile 1, the first being a Level-0 backup at SCN 13423959 at 05:56 of 07-Nov and the second being a Level-1 backup at SCN 13449820 at 06:08 of 07-Nov/
Of course, I *must* backup ArchiveLogs at the Standby.
Note : Oracle's recommendation is to use an RMAN Catalog when running Database Backups.
20 October, 2025
The "Retrieval" for RAG using Semantic Search
Reusing the sample code from my previous demo, I build a table with 130 sentences describing AI Tools and then use SQL to run the sort of queries that you'd expect RAG to use against an "internal knowledge source".
Here's a preview of the sentences loaded :
SQL> select count(*) from my_data_source;
COUNT(*)
----------
130
SQL> select my_sentence from my_data_source fetch first 5 rows only;
MY_SENTENCE
----------------------------------------------------------------------------------------------------------------------------------------------------
Adobe Firefly: A family of generative AI models integrated into Adobe's Creative Cloud suite. It is used for professional image editing and graphic
design
AIVA (Artificial Intelligence Virtual Artist): An AI composer that generates music in various styles for content creators and brands
Amazon CodeWhisperer: An AI coding assistant from Amazon Web Services that provides contextual code recommendations
Canva Magic Media: An AI image generator integrated into the user friendly design platform. It is used for creating visuals for social media and pr
esentations
ChatGPT: An AI chatbot from OpenAI. It can engage in human like conversations, write code, summarize text, and create content such as stories and e
ssays
SQL> select my_sentence from my_data_source where id > 126 ;
MY_SENTENCE
----------------------------------------------------------------------------------------------------------------------------------------------------
Zubtitle.com captions for video
Cohere.ai large language models
Grok.com personal AI assistant
Claude.ai advanced AI chatbot
SQL>
The embeddings are then computed and loaded into the target table using the same query (the HuggingFace model ALL_MINILM_L12_V2_AUGMENTED has already been loaded with the ONNX RunTime engine with the same code as in the previous blog post) :
insert into my_data_vectors
select ds.id as id, my_sentence, to_vector(et.embed_vector) sentence_vector
from
my_data_source ds,
dbms_vector_chain.utl_to_embeddings(
dbms_vector_chain.utl_to_chunks(dbms_vector_chain.utl_to_text(ds.my_sentence), json('{"normalize":"all"}')),
json('{"provider":"database", "model":"ALL_MINILM_L12_V2_AUGMENTED"}')) t,
JSON_TABLE(t.column_value, '$[*]' COLUMNS (embed_id NUMBER PATH '$.embed_id', embed_data VARCHAR2(4000) PATH '$.embed_data', embed_vector CLOB PATH '$.embed_vector')) et
/
I now test a few "Retrieval" queries thus : (Note how the queries return different results with smaller VECTORE_DISTANCE as I refine them)
SQL> ACCEPT text_input CHAR PROMPT 'Enter your query : '
Enter your query : Video
SQL> VARIABLE text_variable VARCHAR2(1000)
SQL> VARIABLE query_vector VECTOR
SQL> BEGIN
2 :text_variable := '&text_input';
3 SELECT vector_embedding(ALL_MINILM_L12_V2_AUGMENTED USING :text_variable as data) into :query_vector;
4 END;
5 /
old 2: :text_variable := '&text_input';
new 2: :text_variable := 'Video';
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> SELECT my_sentence, vector_distance(sentence_vector , :query_vector, COSINE) as Calc_Vector_Distance
2 FROM my_data_vectors
3 ORDER BY 2
4 FETCH FIRST 3 ROWS ONLY;
MY_SENTENCE
----------------------------------------------------------------------------------------------------------------------------------------------------
CALC_VECTOR_DISTANCE
--------------------
Clipchamp.com quick video creation
4.635E-001
Lumen5.com AI powered social video
5.277E-001
Synths.video convert blogs into videos
5.39E-001
SQL>
----------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------
SQL> ACCEPT text_input CHAR PROMPT 'Enter your query : '
Enter your query : What AI Tool can I use to edit Videos ?
SQL> VARIABLE text_variable VARCHAR2(1000)
SQL> VARIABLE query_vector VECTOR
SQL> BEGIN
2 :text_variable := '&text_input';
3 SELECT vector_embedding(ALL_MINILM_L12_V2_AUGMENTED USING :text_variable as data) into :query_vector;
4 END;
5 /
old 2: :text_variable := '&text_input';
new 2: :text_variable := 'What AI Tool can I use to edit Videos ?';
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> SELECT my_sentence, vector_distance(sentence_vector , :query_vector, COSINE) as Calc_Vector_Distance
2 FROM my_data_vectors
3 ORDER BY 2
4 FETCH FIRST 3 ROWS ONLY;
MY_SENTENCE
----------------------------------------------------------------------------------------------------------------------------------------------------
CALC_VECTOR_DISTANCE
--------------------
Wisecut.video auto edit with AI
2.62E-001
Runwayml.com AI video editing
2.777E-001
Gling.ai YouTube video editor
3.413E-001
SQL>
SQL>
----------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------
SQL> ACCEPT text_input CHAR PROMPT 'Enter your query : '
Enter your query : image
SQL> VARIABLE text_variable VARCHAR2(1000)
SQL> VARIABLE query_vector VECTOR
SQL> BEGIN
2 :text_variable := '&text_input';
3 SELECT vector_embedding(ALL_MINILM_L12_V2_AUGMENTED USING :text_variable as data) into :query_vector;
4 END;
5 /
old 2: :text_variable := '&text_input';
new 2: :text_variable := 'image';
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> SELECT my_sentence, vector_distance(sentence_vector , :query_vector, COSINE) as Calc_Vector_Distance
2 FROM my_data_vectors
3 ORDER BY 2
4 FETCH FIRST 3 ROWS ONLY;
MY_SENTENCE
----------------------------------------------------------------------------------------------------------------------------------------------------
CALC_VECTOR_DISTANCE
--------------------
VanceAI.com image enhancement
5.268E-001
Canva Magic Media: An AI image generator integrated into the user friendly design platform. It is used for creating visuals for social media and pr
esentations
5.459E-001
PicWish.com photo editing
5.696E-001
SQL>
SQL>
----------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------
SQL> ACCEPT text_input CHAR PROMPT 'Enter your query : '
Enter your query : How can I use AI to edit Images ?
SQL> VARIABLE text_variable VARCHAR2(1000)
SQL> VARIABLE query_vector VECTOR
SQL> BEGIN
2 :text_variable := '&text_input';
3 SELECT vector_embedding(ALL_MINILM_L12_V2_AUGMENTED USING :text_variable as data) into :query_vector;
4 END;
5 /
old 2: :text_variable := '&text_input';
new 2: :text_variable := 'How can I use AI to edit Images ?';
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> SELECT my_sentence, vector_distance(sentence_vector , :query_vector, COSINE) as Calc_Vector_Distance
2 FROM my_data_vectors
3 ORDER BY 2
4 FETCH FIRST 3 ROWS ONLY;
MY_SENTENCE
----------------------------------------------------------------------------------------------------------------------------------------------------
CALC_VECTOR_DISTANCE
--------------------
Hotpot.ai AI image editing
2.805E-001
Runwayml.com AI video editing
3.711E-001
Wisecut.video auto edit with AI
3.921E-001
SQL>
SQL>
----------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------
SQL> ACCEPT text_input CHAR PROMPT 'Enter your query : '
Enter your query : Can I chat with an AI ?
SQL> VARIABLE text_variable VARCHAR2(1000)
SQL> VARIABLE query_vector VECTOR
SQL> BEGIN
2 :text_variable := '&text_input';
3 SELECT vector_embedding(ALL_MINILM_L12_V2_AUGMENTED USING :text_variable as data) into :query_vector;
4 END;
5 /
old 2: :text_variable := '&text_input';
new 2: :text_variable := 'Can I chat with an AI ?';
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> SELECT my_sentence, vector_distance(sentence_vector , :query_vector, COSINE) as Calc_Vector_Distance
2 FROM my_data_vectors
3 ORDER BY 2
4 FETCH FIRST 3 ROWS ONLY;
MY_SENTENCE
----------------------------------------------------------------------------------------------------------------------------------------------------
CALC_VECTOR_DISTANCE
--------------------
Claude.ai advanced AI chatbot
2.989E-001
Claude.ai conversational assistant
3.828E-001
Grok.com personal AI assistant
4.025E-001
SQL>
SQL>
----------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------
SQL> ACCEPT text_input CHAR PROMPT 'Enter your query : '
Enter your query : coding
SQL> VARIABLE text_variable VARCHAR2(1000)
SQL> VARIABLE query_vector VECTOR
SQL> BEGIN
2 :text_variable := '&text_input';
3 SELECT vector_embedding(ALL_MINILM_L12_V2_AUGMENTED USING :text_variable as data) into :query_vector;
4 END;
5 /
old 2: :text_variable := '&text_input';
new 2: :text_variable := 'coding';
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> SELECT my_sentence, vector_distance(sentence_vector , :query_vector, COSINE) as Calc_Vector_Distance
2 FROM my_data_vectors
3 ORDER BY 2
4 FETCH FIRST 3 ROWS ONLY;
MY_SENTENCE
----------------------------------------------------------------------------------------------------------------------------------------------------
CALC_VECTOR_DISTANCE
--------------------
Amazon CodeWhisperer: An AI coding assistant from Amazon Web Services that provides contextual code recommendations
5.543E-001
Replit.com write and run code
5.548E-001
GitHub Copilot: An AI coding assistant that provides code suggestions and autocompletion within an IDE
5.677E-001
SQL>
SQL>
----------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------
SQL> ACCEPT text_input CHAR PROMPT 'Enter your query : '
Enter your query : Tell me of a Coding Assistant
SQL> VARIABLE text_variable VARCHAR2(1000)
SQL> VARIABLE query_vector VECTOR
SQL> BEGIN
2 :text_variable := '&text_input';
3 SELECT vector_embedding(ALL_MINILM_L12_V2_AUGMENTED USING :text_variable as data) into :query_vector;
4 END;
5 /
old 2: :text_variable := '&text_input';
new 2: :text_variable := 'Tell me of a Coding Assistant';
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> SELECT my_sentence, vector_distance(sentence_vector , :query_vector, COSINE) as Calc_Vector_Distance
2 FROM my_data_vectors
3 ORDER BY 2
4 FETCH FIRST 3 ROWS ONLY;
MY_SENTENCE
----------------------------------------------------------------------------------------------------------------------------------------------------
CALC_VECTOR_DISTANCE
--------------------
GitHub Copilot: An AI coding assistant that provides code suggestions and autocompletion within an IDE
4.306E-001
Amazon CodeWhisperer: An AI coding assistant from Amazon Web Services that provides contextual code recommendations
4.702E-001
Replit: An online IDE with an integrated AI assistant that helps with code generation, debugging, and project creation
4.734E-001
SQL>
SQL>
In the second pair, I change my query from "image" to "How can I use AI to edit Images ?" I get an improved set of responses (again with a smaller VECTOR_DISTANCE).
In the last pair, when I change my query from "coding" to "Tell me of a Coding Assistant", I get responses with a better match to my query.
As noted in my previous blog post, the "LLM" is actually a "MinLM" called "all-MiniLM-L12-v2" from HuggingFace.
21 September, 2025
AI / LLM : Semantic Similarity Search using Hugging Face with Vectors in an Oracle 23ai Database
As I have recently gone through a course on LLMs for Chatbots and RAG on OCI (Oracle Cloud Infrastructure), I thought about putting together a quick demo that uses
A The Hugging Face all-MiniLM-L12-v2 model (with some augmentations done by Oracle for download)
B The ONNX (Open Neural Network Exchange) Runtime Engine that runs in Oracle 23ai (and, yes, in the 23ai Free downloadable edition, not requiring the Oracle Cloud)
C Vectors stored in an Oracle Database using the Vector Datatype
D Use the Hugging Face model to generate Vectors for Similarity / Semantic Searches
but DOES NOT USE Oracle Cloud Infrastructure. (To use OCI for Embedding -- generating "Vectors" from "Tokens", I have to book a Dedicated AI Cluster to "Fine Tune" an existing model, so I am currently staying away from this expensive method)
[Similarly, I cannot currently build a Generative AI as I have to setup a model and server resources]
The sequence is :
1. Setup an Oracle 23ai database (either in the Oracle Cloud OR on an Exadata machine OR the Free edition for On-Premises testing)
2. Create a User to hold the source data and vectors
3. Import the augmented Hugging Face model
4. Load the Source Data as words or sentences or chunks -- here I use a few sentences
5. Compute Vectors for the target column
6. Optional but recommended : Build Vector Index -- for this demo with very few rows I haven't built a Vector Index but if and/or when I do build a demo with a few hundred or thousand sentences or chunks of data, I will create an Index on the Vectors
7. Use Oracle's VECTOR_DISTANCE Function for a Semantic Similarity Search
So here goes :
Using the 23ai FREEPDB downloadable VM, I created the user with these grants : grant connect to vector_demo identified by password ; alter user vector_demo default tablespace users ; alter user vector_demo quota unlimited on users ; grant DB_DEVELOPER_ROLE to vector_demo ; grant CREATE MINING MODEL to vector_demo; CREATE OR REPLACE DIRECTORY DM_DUMP as '/tmp/Load_Model'; -- on the Linux VM GRANT READ ON DIRECTORY dm_dump TO vector_demo; GRANT WRITE ON DIRECTORY dm_dump TO vector_demo;
Download and Import the Model :
Download the ZIP file from https://adwc4pm.objectstorage.us-ashburn-1.oci.customer-oci.com/p/VBRD9P8ZFWkKvnfhrWxkpPe8K03-JIoM5h_8EJyJcpE80c108fuUjg7R5L5O7mMZ/n/adwc4pm/b/OML-Resources/o/all_MiniLM_L12_v2_augmented.zip It also contains a README Place the extracted ONNX file on the Linux Server at /tmp/Load_Model SQL> !ls -l /tmp/Load_Model total 249932 -rw-r--r--. 1 oracle oinstall 122597346 Jul 15 2024 all_MiniLM_L12_v2_augmented.zip -rw-r--r--. 1 oracle oinstall 133322334 Jul 15 2024 all_MiniLM_L12_v2.onnx -rw-r--r--. 1 oracle oinstall 4232 Jul 15 2024 README-ALL_MINILM_L12_V2-augmented.txt SQL> If necessary, delete the previously imported model from the database (logged in to the SQL command line as vector_demo) : exec DBMS_VECTOR.DROP_ONNX_MODEL(model_name => 'ALL_MINILM_L12_V2_AUGMENTED', force => true); Import the model file : SQL> EXECUTE DBMS_VECTOR.LOAD_ONNX_MODEL(- directory=>'DM_DUMP',- file_name=>'all_MiniLM_L12_v2.onnx',- model_name=>'ALL_MINILM_L12_V2_AUGMENTED'- metadata => JSON('{"function" : "embedding", "embeddingOutput" : "embedding", "input": {"input": ["DATA"]}}')- ); The "model_name" is the User Defined Name (i.e. defined by the DBA). In many online demos and the online demo, I see the model name specified as "doc_model". I preferred to use the name "ALL_MINILM_L12_V2_AUGMENTED". You could use any meaningful name Verify the imported model with these queries : SQL> l 1 SELECT model_name, attribute_name, attribute_type, data_type, vector_info 2 FROM user_mining_model_attributes 3 WHERE model_name = 'ALL_MINILM_L12_V2_AUGMENTED' 4* ORDER BY ATTRIBUTE_NAME SQL> / MODEL_NAME ATTRIBUTE_NAME ATTRIBUTE_TY DATA_TYPE -------------------------------- ---------------- ------------ ---------------- VECTOR_INFO -------------------------------------------------------- ALL_MINILM_L12_V2_AUGMENTED DATA TEXT VARCHAR2 ALL_MINILM_L12_V2_AUGMENTED ORA$ONNXTARGET VECTOR VECTOR VECTOR(384,FLOAT32) SQL> SQL> l 1 SELECT MODEL_NAME, MINING_FUNCTION, ALGORITHM, 2 ALGORITHM_TYPE, MODEL_SIZE 3 FROM user_mining_models 4 WHERE model_name = 'ALL_MINILM_L12_V2_AUGMENTED' 5* ORDER BY MODEL_NAME SQL> / MODEL_NAME MINING_FUNCTION ALGORITHM ALGORITHM_ MODEL_SIZE -------------------------------- ------------------------------ ------------------------------ ---------- ---------- ALL_MINILM_L12_V2_AUGMENTED EMBEDDING ONNX NATIVE 133322334 SQL> SQL>SELECT * FROM DM$VMALL_MINILM_L12_V2_AUGMENTED ORDER BY NAME ; NAME VALUE ------------------ ------------------------------------------------ Graph Description Graph combining tokenizer and main_graph tokenizer main_graph Graph Name tokenizer_main_graph Input[0] input:string[?] Output[0] embedding:float32[?,384] Producer Name onnx.compose.merge_models Version 1 6 rows selected. SQL> SQL> SELECT * FROM DM$VPALL_MINILM_L12_V2_AUGMENTED ORDER BY NAME; NAME VALUE ------------------ ------------------------------------------------ embeddingOutput embedding function embedding SQL> SQL> SELECT * FROM DM$VJALL_MINILM_L12_V2_AUGMENTED; METADATA -------------------------------------------------------------------------------- {"function":"embedding","embeddingOutput":"embedding","input":{"input":["DATA"]} SQL>
Test generation of Embeddings with the Model :
SQL> SELECT VECTOR_EMBEDDING(ALL_MINILM_L12_V2_AUGMENTED USING 'The quick brown fox jumped' as DATA) AS embedding;
EMBEDDING
------------------------------------------------------------------------------------------------------------------------------------
[1.65517051E-002,3.19098569E-002,-1.96293015E-002,-3.56926955E-002,
SQL>
SQL> SELECT VECTOR_DIMS(VECTOR_EMBEDDING(ALL_MINILM_L12_V2_AUGMENTED USING 'The quick brown fox jumped' as DATA)) as Dimensions_Count;
DIMENSIONS_COUNT
----------------
384
SQL>
note : The output is actually a list of 384 dimensions, I copied the whole output from SQL Developer put it into a Python List and
then did a LEN(list) to count the number of elements
SQL> drop table my_data_source purge;
Table dropped.
SQL> create table my_data_source(id number primary key, my_sentence varchar2(4000));
Table created.
SQL> insert into my_data_source values (1,'The quick brown fox jumped over the lazy dog');
1 row created.
SQL> insert into my_data_source values (2,'she sells sea-shells at the seashore');
1 row created.
SQL> insert into my_data_source values (3,'the fox and dog are brown friends');
1 row created.
SQL> insert into my_data_source values (4,'the elephant knows the fox and dog');
1 row created.
SQL> insert into my_data_source values (5,'the fox, dog and elephant live together');
1 row created.
SQL> insert into my_data_source values (6,'aeroplanes fly in the sky');
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> select * from my_data_source;
ID
----------
MY_SENTENCE
------------------------------------------------------------------------------------------------------------------------------------
1
The quick brown fox jumped over the lazy dog
2
she sells sea-shells at the seashore
3
the fox and dog are brown friends
4
the elephant knows the fox and dog
5
the fox, dog and elephant live together
6
aeroplanes fly in the sky
6 rows selected.
SQL>
Create the Table to hold the computed Vectors, using the model that has been loaded
SQL> drop table my_data_vectors;
Table dropped.
SQL> create table my_data_vectors (id number primary key, my_sentence varchar2(4000), sentence_vector vector);
Table created.
SQL>
SQL> insert into my_data_vectors
select ds.id as id, my_sentence, to_vector(et.embed_vector) sentence_vector
from
my_data_source ds,
dbms_vector_chain.utl_to_embeddings(
dbms_vector_chain.utl_to_chunks(dbms_vector_chain.utl_to_text(ds.my_sentence), json('{"normalize":"all"}')),
json('{"provider":"database", "model":"ALL_MINILM_L12_V2_AUGMENTED"}')) t,
JSON_TABLE(t.column_value, '$[*]' COLUMNS (embed_id NUMBER PATH '$.embed_id', embed_data VARCHAR2(4000) PATH '$.embed_data', embed_vector CLOB PATH '$.embed_vector')) et;
2 3 4 5 6 7 8
6 rows created.
SQL>
SQL> commit
2 /
Commit complete.
SQL> select * from my_data_vectors;
ID
----------
MY_SENTENCE
------------------------------------------------------------------------------------------------------------------------------------
SENTENCE_VECTOR
------------------------------------------------------------------------------------------------------------------------------------
1
The quick brown fox jumped over the lazy dog
[1.70537992E-003,5.95331714E-002,-2.32173726E-002,3.02353837E-002,
2
she sells sea-shells at the seashore
[1.2790652E-002,5.97022101E-002,8.24511051E-002,3.93822305E-002,
3
the fox and dog are brown friends
[7.67209902E-002,-7.82399923E-002,-2.40087509E-002,-2.37390138E-002,
4
the elephant knows the fox and dog
[4.04452085E-002,-4.15055361E-003,-1.71641614E-002,2.63017584E-002,
5
the fox, dog and elephant live together
[9.44276601E-002,-4.74944711E-002,2.31287945E-002,2.9055763E-002,
6
aeroplanes fly in the sky
[1.1147093E-001,2.18752325E-002,-4.58196662E-002,-2.64751501E-002,
6 rows selected.
SQL>
Do a Semantic Similarity Search for certain phrases :
SQL> l
1 SELECT VECTOR_DISTANCE(
2 VECTOR_EMBEDDING(ALL_MINILM_L12_V2_AUGMENTED USING 'The quick brown fox jumped' as DATA),
3 VECTOR_EMBEDDING(ALL_MINILM_L12_V2_AUGMENTED USING 'The quicker yellow fox jumped' as DATA),
4* COSINE) as Vector_Distance
SQL> /
VECTOR_DISTANCE
---------------
1.76127E-001
SQL>
SQL> l
1 SELECT VECTOR_DISTANCE(
2 VECTOR_EMBEDDING(ALL_MINILM_L12_V2_AUGMENTED USING 'The quick brown fox jumped' as DATA),
3 VECTOR_EMBEDDING(ALL_MINILM_L12_V2_AUGMENTED USING 'seashells' as DATA),
4* COSINE) as Vector_Distance
SQL> /
VECTOR_DISTANCE
---------------
9.81778E-001
SQL>
SQL> l
1 SELECT VECTOR_DISTANCE(
2 VECTOR_EMBEDDING(ALL_MINILM_L12_V2_AUGMENTED USING 'The quick brown fox jumped' as DATA),
3 VECTOR_EMBEDDING(ALL_MINILM_L12_V2_AUGMENTED USING 'moon elephants' as DATA),
4* COSINE)as Vector_Distance
SQL> /
VECTOR_DISTANCE
---------------
7.51297E-001
SQL>
--- "the quicker yellow fox jumped" is not very far away from "the quick brown fox jumped"
--- "moon elephants" is far away from the "the quick brown fox jumped".
--- "seashells" is much further away.
--- likely because the prebuilt model recognises tnat "fox" and "elephant" have some similarity being animals
SQL> l
1 SELECT VECTOR_DISTANCE(
2 VECTOR_EMBEDDING(ALL_MINILM_L12_V2_AUGMENTED USING 'The quick brown fox jumped' as DATA),
3 VECTOR_EMBEDDING(ALL_MINILM_L12_V2_AUGMENTED USING 'houshold electricals' as DATA),
4* COSINE)as Vector_Distance
SQL> /
VECTOR_DISTANCE
---------------
9.97313E-001
SQL>
-- "household" electricals is further away
SQL> l
1 SELECT id , my_sentence, vector_distance(sentence_vector , VECTOR_EMBEDDING(ALL_MINILM_L12_V2_AUGMENTED USING 'sea-shells' as DATA), COSINE) as Vector_Distance
2 FROM my_data_vectors
3 ORDER BY vector_distance(sentence_vector , VECTOR_EMBEDDING(ALL_MINILM_L12_V2_AUGMENTED USING 'sea-shells' as DATA), COSINE)
4* FETCH FIRST 2 ROWS ONLY
SQL> /
ID MY_SENTENCE VECTOR_DISTANCE
------------ ------------------------------------------------ ---------------
2 she sells sea-shells at the seashore 3.65937E-001
6 aeroplanes fly in the sky 8.43676E-001
SQL>
SQL> l
1 SELECT id , my_sentence, vector_distance(sentence_vector , VECTOR_EMBEDDING(ALL_MINILM_L12_V2_AUGMENTED USING 'she sells seashells' as DATA), COSINE) as Vector_Distance
2 FROM my_data_vectors
3 ORDER BY vector_distance(sentence_vector , VECTOR_EMBEDDING(ALL_MINILM_L12_V2_AUGMENTED USING 'she sells seashells' as DATA), COSINE)
4* FETCH FIRST 2 ROWS ONLY
SQL> /
ID MY_SENTENCE VECTOR_DISTANCE
------------ ------------------------------------------------ ---------------
2 she sells sea-shells at the seashore 1.46406E-001
6 aeroplanes fly in the sky 9.26212E-001
SQL>
--- If I refine my query from "sea-shells" to "she sells seashells", I can get a better match, with the Vector Distance dropping from 3.6*10^-1 to 1.4*10^-1
SQL> l
1 SELECT id , my_sentence, vector_distance(sentence_vector , VECTOR_EMBEDDING(ALL_MINILM_L12_V2_AUGMENTED USING 'quick fox' as DATA), COSINE) as Vector_Distance
2 FROM my_data_vectors
3 ORDER BY vector_distance(sentence_vector , VECTOR_EMBEDDING(ALL_MINILM_L12_V2_AUGMENTED USING 'quick fox' as DATA), COSINE)
4* FETCH FIRST 2 ROWS ONLY
SQL> /
ID MY_SENTENCE VECTOR_DISTANCE
------------ ------------------------------------------------ ---------------
1 The quick brown fox jumped over the lazy dog 3.22912E-001
4 the elephant knows the fox and dog 5.21092E-001
SQL>
--- "quick fox" has a better similarity with sentence 1 than sentence 4
What LLMs do when "generating" the next word (known as "token") is to find the "token" that has a higher probability -- a lower COSINE Distance
COSINE Distance measures the Angle between the Vectors
There are other Methods such as DOT PRODUCT, EUCLIDEAN (L1/L2) etc
Some Useful References :
Oracle Docs and Examples :
Oracle Blog :
External Blog :
Hugging Face :
ONNX :
Oracle SQL Reference :
21 August, 2025
Cross-Instance Deadlock in RAC
I've created a video demonstration of a Cross-Instance Deadlock in a 2-node RAC environment and how to read the trace file.
Note : Only the SQL that has been returned the "deadlock" error is failed, the session and transaction are still valid.
06 August, 2025
An introduction to Oracle Cloud Infrastructure
Description:
This session will cover the fundamentals of Oracle Cloud Infrastructure (OCI) including cloud concepts, OCI structures, and core OCI services. OCI service areas that will be discussed include Identity and Access Management (IAM), networking, compute, storage, and database. You will also learn about Users, Groups, and Policies, along with the core tenets of virtual network design that will help set the foundations for application deployment to OCI. We will also cover terms and definitions for each of the topic areas to establish a foundation for additional sessions.
17 July, 2025
Configuring a RAC Service tied to a PDB and specific Instance
I have created a new Video demonstrating how you can add new Services to an existing database. In RAC, you can choose to have the Service running on all or selected or a single node of the Cluster. The service can be bound to a PDB.
This allows you to separate different applications / groups of users via Services and manage availability via Startup/Shutdown/Relocation/Failover of services.
The comand to configure a service to a single PDB and Instance is :
srvctl add service -db RACDB -service prdapps1 -preferred RACDB1 -available RACDB2 -tafpolicy BASIC -failovertype SELECT -clbgoal SHORT -pdb PDAPPTST
06 July, 2025
Converting a "NO Standby" to "YES Standby" database
In my previous blog post and video, I had demonstrated creating a PDB (as a clone from an existing PDB) to be used for some testing/validation for a short period of time, without needing protection of a Standby database.
But what if you do decide to provide Standby protection ?
First, here's the status of the Pluggable Database at the Primary :
SQL> select con_id, pdb_name, creation_time, force_logging
2 from cdb_pdbs
3 where pdb_name = 'PRDAPPTST'
4 /
CON_ID PDB_NAME CREATION_ FORCE_LOGGING
---------- ------------ --------- ---------------------------------------
4 PRDAPPTST 29-JUN-25 NO
SQL>
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES
SQL>
SQL> alter pluggable database prdapptst enable force logging;
Pluggable database altered.
SQL>
SQL> select con_id, pdb_name, creation_time, force_logging
2 from cdb_pdbs
3 where pdb_name = 'PRDAPPTST'
4 /
CON_ID PDB_NAME CREATION_ FORCE_LOGGING
---------- ------------ --------- ---------------------------------------
4 PRDAPPTST 29-JUN-25 YES
SQL>
Note :In this case, the "ENABLE FORCE LOGGING" was strictly not required because the CDB-wide FORCE LOGGING was already enforced (query on V$DATABASE at the CDB$ROOT).
Here's the status at the Standby :
SQL> l
1 select con_id, name, open_mode, recovery_status
2 from v$pdbs
3* where name ='PRDAPPTST'
SQL> /
CON_ID NAME OPEN_MODE RECOVERY
---------- ------------ ---------- --------
4 PRDAPPTST MOUNTED DISABLED
SQL>
SQL> l
1 select con_id, file#, status, plugged_in, name
2 from v$datafile
3 where con_id=4
4* order by file#
SQL> /
CON_ID FILE# STATUS PLUGGED_IN NAME
---------- ---------- ------- ---------- --------------------------------
4 60 SYSOFF 0 /u01/app/oracle/product/19.0.0/d
bhome_1/dbs/UNNAMED00060
4 61 RECOVER 0 /u01/app/oracle/product/19.0.0/d
bhome_1/dbs/UNNAMED00061
4 62 RECOVER 0 /u01/app/oracle/product/19.0.0/d
bhome_1/dbs/UNNAMED00062
4 63 RECOVER 0 /u01/app/oracle/product/19.0.0/d
bhome_1/dbs/UNNAMED00063
4 64 RECOVER 0 /u01/app/oracle/product/19.0.0/d
bhome_1/dbs/UNNAMED00064
SQL>
Meaning that although the Standby is "aware" of the PDB, not datafiles are present so it is not being recovered. If you recall from the Video demonstration, the alert log at the Standby presented these entries when the PDB was created at the Primary with STANDBYS=NONE :
Recovery created pluggable database PRDAPPTST PRDAPPTST(4):Tablespace-SYSTEM during PDB create skipped since source is in r/w mode or this is a refresh clone PRDAPPTST(4):File #60 added to control file as 'UNNAMED00060'. Originally created as: PRDAPPTST(4):'+DATA/RACDB/38AE431466FE1FDBE0635A38A8C085D8/DATAFILE/system.299.1205032267' PRDAPPTST(4):because the pluggable database was created with nostandby PRDAPPTST(4):or the tablespace belonging to the pluggable database is PRDAPPTST(4):offline. PRDAPPTST(4):Tablespace-SYSAUX during PDB create skipped since source is in r/w mode or this is a refresh clone PRDAPPTST(4):File #61 added to control file as 'UNNAMED00061'. Originally created as: PRDAPPTST(4):'+DATA/RACDB/38AE431466FE1FDBE0635A38A8C085D8/DATAFILE/sysaux.280.1205032267' PRDAPPTST(4):because the pluggable database was created with nostandby PRDAPPTST(4):or the tablespace belonging to the pluggable database is PRDAPPTST(4):offline. PRDAPPTST(4):Tablespace-UNDOTBS1 during PDB create skipped since source is in r/w mode or this is a refresh clone PRDAPPTST(4):File #62 added to control file as 'UNNAMED00062'. Originally created as: PRDAPPTST(4):'+DATA/RACDB/38AE431466FE1FDBE0635A38A8C085D8/DATAFILE/undotbs1.285.1205032267' PRDAPPTST(4):because the pluggable database was created with nostandby PRDAPPTST(4):or the tablespace belonging to the pluggable database is PRDAPPTST(4):offline. PRDAPPTST(4):Tablespace-TEMP during PDB create skipped since source is in r/w mode or this is a refresh clone PRDAPPTST(4):Tablespace-UNDO_2 during PDB create skipped since source is in r/w mode or this is a refresh clone PRDAPPTST(4):File #63 added to control file as 'UNNAMED00063'. Originally created as: PRDAPPTST(4):'+DATA/RACDB/38AE431466FE1FDBE0635A38A8C085D8/DATAFILE/undo_2.284.1205032267' PRDAPPTST(4):because the pluggable database was created with nostandby PRDAPPTST(4):or the tablespace belonging to the pluggable database is PRDAPPTST(4):offline. PRDAPPTST(4):Tablespace-USERS during PDB create skipped since source is in r/w mode or this is a refresh clone PRDAPPTST(4):File #64 added to control file as 'UNNAMED00064'. Originally created as: PRDAPPTST(4):'+DATA/RACDB/38AE431466FE1FDBE0635A38A8C085D8/DATAFILE/users.279.1205032267' PRDAPPTST(4):because the pluggable database was created with nostandby PRDAPPTST(4):or the tablespace belonging to the pluggable database is PRDAPPTST(4):offline.
So, the current status is that the PDB *is* registered at the Standby but, in the absence of datafiles, no Redo Apply is being done at the Standby.
For the purposes of today's test, I created a few more rows in my target table in that PDB :
SQL> connect hemant/hemant@newtestpdb Connected. SQL> @INS_QRY 1 row created. SQL> l 1* insert into my_txn_table select systimestamp, substr(p.name,1,12), substr(i.host_name,1,12), 'XXX' from v$pdbs p, v$instance i SQL> set pages600 linesize 132 SQL> select * from my_txn_table; TXN_TIMESTAMP TARGET_PDB_NAME TARGET_HOST_NAM TXN_DETAILS --------------------------------------------------------------------------- --------------- --------------- --------------- 29-JUN-25 03.06.43.264562 AM PDB1 srv2 XXX 29-JUN-25 03.15.26.694170 AM PRDAPPTST srv1 XXX 06-JUL-25 08.31.33.903344 AM PRDAPPTST srv2 XXX 29-JUN-25 03.27.26.855776 AM PRDAPPTST srv2 XXX SQL> select * from my_txn_table order by txn_timestamp; TXN_TIMESTAMP TARGET_PDB_NAME TARGET_HOST_NAM TXN_DETAILS --------------------------------------------------------------------------- --------------- --------------- --------------- 29-JUN-25 03.06.43.264562 AM PDB1 srv2 XXX 29-JUN-25 03.15.26.694170 AM PRDAPPTST srv1 XXX 29-JUN-25 03.27.26.855776 AM PRDAPPTST srv2 XXX 06-JUL-25 08.31.33.903344 AM PRDAPPTST srv2 XXX SQL> commit; Commit complete. SQL> connect hemant/hemant@newtestpdb Connected. SQL> @INS_QRY 1 row created. SQL> commit; Commit complete. SQL> connect hemant/hemant@newtestpdb Connected. SQL> @INS_QRY 1 row created. SQL> commit; Commit complete. SQL> select * from my_txn_table order by txn_timestamp; TXN_TIMESTAMP TARGET_PDB_NAME TARGET_HOST_NAM TXN_DETAILS --------------------------------------------------------------------------- --------------- --------------- --------------- 29-JUN-25 03.06.43.264562 AM PDB1 srv2 XXX 29-JUN-25 03.15.26.694170 AM PRDAPPTST srv1 XXX 29-JUN-25 03.27.26.855776 AM PRDAPPTST srv2 XXX 06-JUL-25 08.31.33.903344 AM PRDAPPTST srv2 XXX 06-JUL-25 08.32.29.154557 AM PRDAPPTST srv1 XXX 06-JUL-25 08.32.41.404084 AM PRDAPPTST srv2 XXX 6 rows selected. SQL>
So, today, 06-Jul-25, I have created 3 new rows, with connections to both instances.
(To understand "newtestpdb" as the connect-string or service name for the PDB PRDAPPTST see the previous Blog Post or Video)
Now, I run a backup of this PDB at the Primary and will then copy it and restore it to the Standby.
[oracle@srv1 ~]$ rman target / Recovery Manager: Release 19.0.0.0.0 - Production on Sun Jul 6 08:35:56 2025 Version 19.25.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: RACDB (DBID=1162136313) RMAN> backup as compressed backupset backup as compressed backupset 2> pluggable database PRDAPPTST pluggable database PRDAPPTST 3> format '/tmp/PRDAPPTST_DB_%U.bak';
And restore it at the Standby (after copying the backup pieces)
[oracle@stdby tmp]$ ls -ltr *bak
-rw-r-----. 1 oracle oinstall 849739776 Jul 6 08:38 PRDAPPTST_DB_ch3tsbcj_401_1_1.bak
[oracle@stdby tmp]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sun Jul 6 08:39:12 2025
Version 19.25.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: RACDB (DBID=1162136313, not open)
RMAN> catalog start with '/tmp';
catalog start with '/tmp';
using target database control file instead of recovery catalog
searching for all files that match the pattern /tmp
List of Files Unknown to the Database
=====================================
File Name: /tmp/PRDAPPTST_DB_ch3tsbcj_401_1_1.bak
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /tmp/PRDAPPTST_DB_ch3tsbcj_401_1_1.bak
RMAN>
RMAN> list backup of pluggable database PRDAPPTST;
list backup of pluggable database PRDAPPTST;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
319 Full 810.37M DISK 00:00:43 06-JUL-25
BP Key: 319 Status: AVAILABLE Compressed: YES Tag: TAG20250706T083635
Piece Name: /tmp/PRDAPPTST_DB_ch3tsbcj_401_1_1.bak
List of Datafiles in backup set 319
Container ID: 4, PDB Name: PRDAPPTST
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
60 Full 12491346 06-JUL-25 NO /u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00060
61 Full 12491346 06-JUL-25 NO /u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00061
62 Full 12491346 06-JUL-25 NO /u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00062
63 Full 12491346 06-JUL-25 NO /u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00063
64 Full 12491346 06-JUL-25 NO /u01/app/oracle/product/19.0.0/dbhome_1/dbs/UNNAMED00064
RMAN>
RMAN> run
run
2> {
{
3> set newname for pluggable database PRDAPPTST to '/Standby_DB/oradata/STDBY/PRDAPPTST/%U.dbf';
set newname for pluggable database PRDAPPTST to '/Standby_DB/oradata/STDBY/PRDAPPTST/%U.dbf';
4> restore pluggable database PRDAPPTST;
restore pluggable database PRDAPPTST;
5> switch datafile all;
switch datafile all;
6> }
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00060 to /Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-SYSTEM_FNO-60.dbf
channel ORA_DISK_1: restoring datafile 00061 to /Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-SYSAUX_FNO-61.dbf
channel ORA_DISK_1: restoring datafile 00062 to /Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-UNDOTBS1_FNO-62.dbf
channel ORA_DISK_1: restoring datafile 00063 to /Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-UNDO_2_FNO-63.dbf
channel ORA_DISK_1: restoring datafile 00064 to /Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-USERS_FNO-64.dbf
channel ORA_DISK_1: reading from backup piece /tmp/PRDAPPTST_DB_ch3tsbcj_401_1_1.bak
channel ORA_DISK_1: piece handle=/tmp/PRDAPPTST_DB_ch3tsbcj_401_1_1.bak tag=TAG20250706T083635
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 06-JUL-25
datafile 60 switched to datafile copy
input datafile copy RECID=54 STAMP=1205746519 file name=/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-SYSTEM_FNO-60.dbf
datafile 61 switched to datafile copy
input datafile copy RECID=55 STAMP=1205746519 file name=/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-SYSAUX_FNO-61.dbf
datafile 62 switched to datafile copy
input datafile copy RECID=56 STAMP=1205746519 file name=/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-UNDOTBS1_FNO-62.dbf
datafile 63 switched to datafile copy
input datafile copy RECID=57 STAMP=1205746519 file name=/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-UNDO_2_FNO-63.dbf
datafile 64 switched to datafile copy
input datafile copy RECID=58 STAMP=1205746519 file name=/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-USERS_FNO-64.dbf
RMAN>
Note :If you see my previous blog posts and videos on RAC, you can see that I have demonstrated that
1. A RAC Primary can have a Non-RAC (Single Instance) Standby and vice-versa as well in my video demonstrations of DataGuard Switchover
2. A Primary on ASM can have a Standby on Filesystem and vice-versa
Now that I have restored the database to the Standby, I must ENABLE RECOVERY for this PDB
[oracle@srv1 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jul 6 10:32:27 2025 Version 19.25.0.0.0 Copyright (c) 1982, 2024, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.25.0.0.0 SQL> alter pluggable database prdapptst enable recovery; alter pluggable database prdapptst enable recovery * ERROR at line 1: ORA-65046: operation not allowed from outside a pluggable database SQL> alter session set container=PRDAPPTST; Session altered. SQL> alter pluggable database prdapptst enable recovery; Pluggable database altered. SQL> connect hemant/hemant@newtestpdb Connected. SQL> @INS_QRY 1 row created. SQL> set pages600 linesize 132 SQL> commit; Commit complete. SQL> select * from my_txn_table order by 1; TXN_TIMESTAMP TARGET_PDB_NAME TARGET_HOST_NAM TXN_DETAILS --------------------------------------------------------------------------- --------------- --------------- --------------- 29-JUN-25 03.06.43.264562 AM PDB1 srv2 XXX 29-JUN-25 03.15.26.694170 AM PRDAPPTST srv1 XXX 29-JUN-25 03.27.26.855776 AM PRDAPPTST srv2 XXX 06-JUL-25 08.31.33.903344 AM PRDAPPTST srv2 XXX 06-JUL-25 08.32.29.154557 AM PRDAPPTST srv1 XXX 06-JUL-25 08.32.41.404084 AM PRDAPPTST srv2 XXX 06-JUL-25 09.53.06.164510 AM PRDAPPTST srv1 XXX 06-JUL-25 10.33.28.132217 AM PRDAPPTST srv2 XXX 8 rows selected. SQL> SQL> connect / as sysdba Connected. SQL> alter system archive log current; System altered. SQL>
I can now go to the Standby, Bring Datafiles Online, Resume Recovery and query the PDB
[oracle@stdby trace]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jul 6 10:39:50 2025 Version 19.25.0.0.0 Copyright (c) 1982, 2024, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.25.0.0.0 SQL> alter database recover managed standby database cancel; Database altered. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 2147480256 bytes Fixed Size 9179840 bytes Variable Size 486539264 bytes Database Buffers 1644167168 bytes Redo Buffers 7593984 bytes Database mounted. SQL> alter session set container=PRDAPPTST; Session altered. SQL> alter database datafile '/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-SYSTEM_FNO-60.dbf' online; alter database datafile '/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-SYSTEM_FNO-60.dbf' online * ERROR at line 1: ORA-01156: recovery or flashback in progress may need access to files SQL> alter database datafile '/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-SYSAUX_FNO-61.dbf' online; alter database datafile '/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-SYSAUX_FNO-61.dbf' online * ERROR at line 1: ORA-01156: recovery or flashback in progress may need access to files SQL> alter database datafile '/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-UNDOTBS1_FNO-62.dbf' online; alter database datafile '/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-UNDOTBS1_FNO-62.dbf' online * ERROR at line 1: ORA-01156: recovery or flashback in progress may need access to files SQL> alter database datafile '/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-UNDO_2_FNO-63.dbf' online; alter database datafile '/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-UNDO_2_FNO-63.dbf' online * ERROR at line 1: ORA-01156: recovery or flashback in progress may need access to files SQL> alter database datafile '/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-USERS_FNO-64.dbf' online; alter database datafile '/Standby_DB/oradata/STDBY/PRDAPPTST/data_D-RACDB_TS-USERS_FNO-64.dbf' online * ERROR at line 1: ORA-01156: recovery or flashback in progress may need access to files SQL> SQL> connect / as sysdba Connected. SQL> shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 2147480256 bytes Fixed Size 9179840 bytes Variable Size 486539264 bytes Database Buffers 1644167168 bytes Redo Buffers 7593984 bytes Database mounted. SQL> alter session set container=PRDAPPTST; Session altered. SQL> alter pluggable database enable recovery; alter pluggable database enable recovery * ERROR at line 1: ORA-01156: recovery or flashback in progress may need access to files SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.25.0.0.0 [oracle@stdby trace]$ dgmgrl DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sun Jul 6 11:05:42 2025 Version 19.25.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> connect / as sysdba Connected to "STDBY" Connected as SYSDBA. DGMGRL> edit database STDBY set state='apply-off'; Succeeded. DGMGRL> exit [oracle@stdby trace]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jul 6 11:06:48 2025 Version 19.25.0.0.0 Copyright (c) 1982, 2024, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.25.0.0.0 SQL> alter session set container=PRDAPPTST; Session altered. SQL> alter pluggable database enable recovery; Pluggable database altered. SQL> SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.25.0.0.0 [oracle@stdby trace]$ dgmgrl DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sun Jul 6 11:07:32 2025 Version 19.25.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> connect / as sysdba Connected to "STDBY" Connected as SYSDBA. DGMGRL> edit database stdby set state='apply-on'; Succeeded. DGMGRL> exit [oracle@stdby trace]$ [oracle@stdby trace]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jul 6 11:08:09 2025 Version 19.25.0.0.0 Copyright (c) 1982, 2024, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.25.0.0.0 SQL> select name, open_mode from v$pdbs; NAME -------------------------------------------------------------------------------- OPEN_MODE ---------- PDB$SEED MOUNTED PDB1 MOUNTED PRDAPPTST MOUNTED SQL> alter database open read only; Database altered. SQL> alter pluggable database prdapptst open read only; Pluggable database altered. SQL> SQL> alter session set container=PRDAPPTST; Session altered. SQL> set pages600 linesize 132 SQL> select * from hemant.my_txn_table order by 1; TXN_TIMESTAMP TARGET_PDB_NAME TARGET_HOST_NAM TXN_DETAILS --------------------------------------------------------------------------- --------------- --------------- --------------- 29-JUN-25 03.06.43.264562 AM PDB1 srv2 XXX 29-JUN-25 03.15.26.694170 AM PRDAPPTST srv1 XXX 29-JUN-25 03.27.26.855776 AM PRDAPPTST srv2 XXX 06-JUL-25 08.31.33.903344 AM PRDAPPTST srv2 XXX 06-JUL-25 08.32.29.154557 AM PRDAPPTST srv1 XXX 06-JUL-25 08.32.41.404084 AM PRDAPPTST srv2 XXX 06-JUL-25 09.53.06.164510 AM PRDAPPTST srv1 XXX 06-JUL-25 10.33.28.132217 AM PRDAPPTST srv2 XXX 8 rows selected. SQL>
Now that the database is restored and recovery resumed, data is available in PRDAPPTST at the Standby as well !
Subscribe to:
Posts (Atom)