Forem: Vahid Yousefzadeh

Automatic temp file creation on the standby - Oracle AI Database 26ai

Vahid Yousefzadeh — Thu, 26 Feb 2026 06:35:29 +0000

In Oracle Database 19c, when a tempfile is added on the primary database, it is not automatically created on the physical standby database. This is because Oracle does not generate redo for tempfile-related DDL operations (such as creating, adding, resizing, or dropping tempfiles).

Behavior in Oracle Database 19c

For example, when creating a temporary tablespace on the primary database(19c):

— Primary

SQL> create temporary tablespace TEMP1404;
Tablespace created.

SQL> select ts# from v$tablespace where NAME='TEMP1404';
       TS#
----------
        15

SQL> select name from v$tempfile where ts#=15;
NAME
--------------------------------------------------------------------------------
+DATA/HUME/B3AE49A3735D8867E053088A210A341D/TEMPFILE/temp1404.807.1225812657

On the standby database, although the tablespace metadata exists, the tempfile itself is not created:

— Data Guard

SQL> select ts# from v$tablespace where NAME='TEMP1404';
       TS#
----------
        15

SQL> select name from v$tempfile where ts#=15;
no rows selected

If we add another tempfile on the primary:

— Primary

SQL> alter tablespace TEMP1404 add tempfile size 100m;
Tablespace altered.

SQL> select name from v$tempfile where ts#=15;
NAME
--------------------------------------------------------------------------------
+DATA/HUME/B3AE49A3735D8867E053088A210A341D/TEMPFILE/temp1404.807.1225812657
+DATA/HUME/B3AE49A3735D8867E053088A210A341D/TEMPFILE/temp1404.808.1225812823

Again, on the standby database:

— Data Guard

SQL> select name from v$tempfile where ts#=15;
no rows selected

Automatic Tempfile Creation in Oracle AI Database 26ai

Starting with Oracle AI Database 26ai, tempfile creation is automatically handled on the standby database.

To enable this feature, the following parameters must be configured on the standby:

standby_file_management = AUTO
db_create_file_dest must be defined

— Data Guard

SQL> alter system set standby_file_management=AUTO;
System altered.

SQL> alter system set db_create_file_dest='/oracle/base/oradata';
System altered.

Now, when creating a temporary tablespace on the primary:

— Primary

SQL> create temporary tablespace TEMP1404;
Tablespace created.

SQL> select ts# from v$tablespace where NAME='TEMP1404';
       TS#
----------
         5

SQL> select name from v$tempfile where ts#=5;
NAME
--------------------------------------------------------------------------------
/oracle/base/oradata/DB2/4A3B7527123A1205E0630288200AA7B8/datafile/o1_mf_temp1404_nsx3wdt7_.tmp

The tempfile is automatically created on the standby database:

— Data Guard

SQL> select name from v$tempfile where ts#=5;
NAME
--------------------------------------------------------------------------------
/oracle/base/oradata/DG1/4A3B7527123A1205E0630288200AA7B8/datafile/o1_mf_temp1404_nsx3wbyx_.dbf

This enhancement eliminates the need for manual synchronization of tempfile configurations between primary and standby databases, simplifying Data Guard administration.

Managing Alert Log Size in Oracle AI Database 26ai

Vahid Yousefzadeh — Mon, 23 Feb 2026 10:32:09 +0000

Starting with Oracle AI Database 26ai (Release Update 23.9), it is possible to control the maximum size of the alert log using the ALERT_LOG_MAX_SIZE parameter. The default value for this parameter is 1000 MB, which means the XML alert log can grow up to a maximum of 20 segments, each 50 MB in size.

In my test, I configured the alert log maximum size to 100 MB:

SQL> ALTER SYSTEM SET alert_log_max_size = 100M;
System altered.

***This parameter is modifiable using ALTER SYSTEM, but it is not modifiable at the PDB level.

To increase the size of the alert log and force file generation, I executed the following PL/SQL block to continuously write messages into the alert log:

begin
  for i in 1 .. 1000000 loop
    begin
      sys.dbms_system.ksdwrt(2, 'Vahid Yousefzadeh');
    end;
  end loop;
end;
/

After running this block, there are several XML files in the alert directory and each file size is approximately 50MB.

[root@OEL9-DB2 alert]# ls -lh
-rw-r----- 1 oracle oinstall 51M Feb 23 11:57 log_20.xml
-rw-r----- 1 oracle oinstall 51M Feb 23 11:57 log_21.xml
-rw-r----- 1 oracle oinstall 16M Feb 23 11:57 log.xml

In addition, I checked the trace directory and observed multiple alert text log files:

[root@OEL9-DB2 trace]# ls -lh alert*.log
-rw-r----- 1 oracle oinstall 4.1M Feb 23 11:57 alert_db26ai_20.log
-rw-r----- 1 oracle oinstall 4.1M Feb 23 11:57 alert_db26ai_21.log
-rw-r----- 1 oracle oinstall 1.3M Feb 23 11:57 alert_db26ai.log

This behavior indicates that the size and number of alert text log files are driven by the XML log files.

Controlling XML Segment Size

There is also a hidden parameter called: _alert_segment_size

This parameter allows us to control the size of each XML alert log segment.

SQL> ALTER SYSTEM SET "_alert_segment_size" = 65;
System altered.

SQL> ALTER SYSTEM SET alert_log_max_size = 100M;
System altered.

After generating alert activity again, the results were:

Alert (XML) directory:

[root@OEL9-DB2 alert]# ll
-rw-r----- 1 oracle oinstall 68157489 Feb 23 12:05 log_25.xml
-rw-r----- 1 oracle oinstall 68157489 Feb 23 12:05 log_26.xml
-rw-r----- 1 oracle oinstall  2656020 Feb 23 12:05 log.xml

Trace directory:

[root@OEL9-DB2 trace]# ls -lh alert*.log
-rw-r----- 1 oracle oinstall 5.3M Feb 23 12:05 alert_db26ai_25.log
-rw-r----- 1 oracle oinstall 5.3M Feb 23 12:05 alert_db26ai_26.log
-rw-r----- 1 oracle oinstall 211K Feb 23 12:05 alert_db26ai.log

As shown above, each XML segment is now approximately 66 MB, which reflects the new _alert_segment_size value.

Disabling the Alert Log Size Limit

If we want to return to unlimited alert log growth, we can set the parameter to 0:

SQL> ALTER SYSTEM SET alert_log_max_size = 0;
System altered.

Excluding LOB Columns Data in Data Pump

Vahid Yousefzadeh — Fri, 20 Feb 2026 02:17:58 +0000

We intend to create a dump from a table in which some columns contain LOB data. However, due to the large volume of LOB data in this table — and for reasons such as higher speed, better security, or reduced space allocation — we want to prevent this data from being included in the dump file, or at least prevent it from being restored during the import process.

What is the solution?

Excluding LOB column data during the export operation in Oracle 12c can be easily controlled. This can be done by creating a view and using the VIEWS_AS_TABLES parameter. However, if the dump file already contains LOB data, this method cannot be used during import. In such cases, the REMAP_DATA parameter is the solution.

Previously, we explained that the REMAP_DATA parameter can be used for data masking and data manipulation during export and import operations. In this article, we will see how the same parameter can be used to exclude LOB column data during both export and import processes.

Let’s look at the following example.

Example
We want to exclude the data stored in the ADDRESS and RESUME columns of the PERSON table during expdp.

SQL> desc person

Name           Type       

——-  ————————

ID              NUMBER     

NAME       VARCHAR2(10)

ADDRESS   CLOB       

RESUME    CLOB    


SQL> select * from person;

ID    NAME       ADDRESS      RESUME

—- ———- ———-  ———–

1      usef      LOB_DATA    LOB_DATA

To achieve this, we create a package that returns NULL for each field in these two columns:

SQL> create or replace package without_CLOB as

  2   function null_clob (soton_clob in clob) return clob;

  3   end;

  4  /

Package created


SQL> create or replace package body without_CLOB as

  2   function null_clob (soton_clob in clob) return clob

  3   is

  4    nulllll   clob := empty_clob ();

  5  begin

  6      return  nulllll;

  7   end;

  8  end;

  9  /

Package body created

After creating the WITHOUT_CLOB package, we generate a dump from the PERSON table using the REMAP_DATA parameter:

[oracle@ol7 ~]$ expdp directory=tk dumpfile=dmp_without_LOB tables=usef.person REMAP_DATA=usef.person.ADDRESS:usef.without_CLOB.null_clob REMAP_DATA=usef.person.RESUME:usef.without_CLOB.null_clob

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production

Starting “SYS”.”SYS_EXPORT_TABLE_01″:  sys/******** AS SYSDBA directory=tk dumpfile=dmp_without_LOB tables=usef.person REMAP_DATA=usef.person.ADDRESS:usef.without_CLOB.null_clob REMAP_DATA=usef.person.RESUME:usef.without_CLOB.null_clob

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported “USEF”.”PERSON”                             6.398 KB       1 rows

Master table “SYS”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_TABLE_01 is:

  /home/oracle/dmp_without_LOB.dmp

Job “SYS”.”SYS_EXPORT_TABLE_01″ successfully completed at Mon Jul 6 07:38:24 2020 elapsed 0 00:00:42

The export completes successfully, and the dump file is created.

Now, when we import the table again:

[oracle@ol7 ~]$ impdp directory=tk dumpfile=dmp_without_LOB tables=usef.person remap_table=person:person2

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported “USEF”.”PERSON2″                            6.398 KB       1 rows


SQL> select ADDRESS from usef.person2;

No Rows Selected.


SQL> select * from usef.person2;

        ID NAME       ADDRESS    RESUME

———- ———- ———- ———-

         1 usef

As shown, the ADDRESS and RESUME columns are empty.

Excluding LOB columns during the impdp operation is also possible.

Restoring a Datafile Using Archivelogs

Vahid Yousefzadeh — Tue, 10 Feb 2026 16:48:12 +0000

Suppose a datafile that was recently added to the database is lost for some reasons. In this case, if the archive logs from the time the datafile was created are available, it is possible to recover that datafile.

However, as mentioned, this operation has a very strict requirement:

“All archive logs from the time the datafile was created must be available.”
Below, we will walk through a scenario related to this topic.

SQL> archive log list;

Database log mode              Archive Mode

SQL> alter tablespace USEF_TBS add datafile ‘/acfs/test2.dbf’ size 10m;

With the following commands, we create a new table that uses space from the new datafile so that after recovery we can verify that no data was lost.

SQL> create table usef_tbl1  tablespace usef_tbs as select * from dba_tables where 1=2;

SQL>  alter  table usef_tbl1 ALLOCATE EXTENT(DATAFILE ‘/acfs/test2.dbf’  size 5m);

Table altered.

SQL> insert into usef_tbl1 select * from dba_tables;

2323 rows created.

SQL> commit;

SQL> select count(*) from usef_tbl1;

      2323

At this stage, we delete the datafile and restart the database.

rm –rf   /acfs/test2.dbf

SQL> startup

ORACLE instance started.

Total System Global Area 8351150080 bytes

Fixed Size                  2701528 bytes

Variable Size            7348422440 bytes

Database Buffers          989855744 bytes

Redo Buffers               10170368 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 6 – see DBWR trace file

ORA-01110: data file 6: ‘/acfs/test2.dbf’

SQL> alter database datafile  ‘/acfs/test2.dbf’  offline;

Database altered.

SQL> alter database open;

Database altered

As the following command shows, the created table is not accessible.

SQL> select count(*) from usef_tbl1;

ERROR at line 1:

ORA-00376: file 6 cannot be read at this time

ORA-01110: data file 6: ORA-01110: data file 6: ‘/acfs/test2.dbf’

SQL> alter database create datafile ‘/acfs/test2.dbf’  ;

Database altered.
The recovery is performed with the following command:

SQL> recover datafile  ‘/acfs/test2.dbf‘;

Media recovery complete.

SQL> alter database datafile  ‘/acfs/test2.dbf‘ ONLINE;

Database altered.

SQL> select count(*) from usef_tbl1;

      2323

Oracle AI Database 26ai — Managing AWR in Active Data Guard

Vahid Yousefzadeh — Mon, 09 Feb 2026 14:58:41 +0000

Starting with Oracle Database 12.2, generating AWR reports on an Active Data Guard (ADG) became possible by using the Remote Management Framework (RMF). In this model, AWR data had to be manually transported from the standby to a primary database. Although functional, the setup was complex and required multiple configuration steps, making AWR analysis on physical standby databases operationally challenging for DBAs.

With Oracle AI Database 26ai, AWR support for Active Data Guard Standby databases is fully integrated and enabled by default. AWR snapshots are automatically collected on the ADG standby and transparently transported to the primary database, without any manual intervention or RMF configuration. By default, snapshots are taken once per hour, and the retention period is 8 days.

This enhancement allows DBAs to work with AWR on an Active Data Guard standby in the same way as on the primary database.

AWR Architecture for ADG in 26ai

Each Active Data Guard standby is automatically assigned a unique AWR ID.
This AWR ID is stored in the DBID column of the AWR tables and is used to distinguish AWR data coming from different databases.

The AWR ID of the current database can be obtained using:

— ADG Standby:

SQL> select dbms_workload_repository.get_awr_id() awr_id;
 AWR_ID
----------
3586104896

Although AWR data is physically stored in the primary database, it becomes visible and usable on the standby after redo apply.

Generating an AWR Report on ADG Standby

An AWR report can be generated directly on the Active Data Guard standby using the standard awrrpt.sql script. During report generation, Oracle automatically lists all available AWR repositories and prompts for the appropriate DBID, including the standby AWR ID.

SQL> @?/rdbms/admin/awrrpt.sql
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats.  Please enter the
name of the format at the prompt.  Default value is 'html'.
'html'          HTML format (default)
'text'          Text format
'active-html'   Includes Performance Hub active report

Enter value for report_type:
Type Specified:  html


Current Instance
~~~~~~~~~~~~~~~~
DB Id          DB Name        Inst Num       Instance       Container Name
-------------- -------------- -------------- -------------- --------------
 4152038052     DB26AI                      1 DG1            CDB$ROOT


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num   DB Name      Instance     Host
------------ ---------- ---------    ----------   ------
  3586104896     1      DB26AI       DG1          DG1
  4152038052     1      DB26AI       db26ai       OEL9-DB2


Using 3586104896 for database Id
Using          1 for instance number

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days:
Listing all Completed Snapshots
Instance     DB Name      Snap Id       Snap Started    Snap Level
------------ ------------ ---------- ------------------ ----------
DG1          DB26AI               1  09 Feb 2026 10:30     1
                                  2  09 Feb 2026 10:40     1

A portion of the AWR report output is shown below.

Viewing AWR Settings for ADG Standby

Each ADG standby can have its own AWR snapshot settings.
These settings are visible in the AWR_CDB_WR_CONTROL view on the primary database.

Example:

SQL> select c.dbid,c.snap_interval,c.retention,c.topnsql,c.tablespace_name from awr_cdb_wr_control c where src_dbname='DG1';
      DBID SNAP_INTERVAL        RETENTION            TOPNSQL    TABLESPACE_NAME
---------- -------------------- -------------------- ---------- ------------------------------
3586104896 +00000 01:00:00.0    +00008 00:00:00.0    DEFAULT    SYSAUX

This confirms the default 1-hour snapshot interval and 8-day retention

Modifying Snapshot Settings on ADG Standby

Snapshot settings can be modified directly on the physical standby.

For example, to change the snapshot interval from 60 minutes to 30 minutes:

— ADG Standby:

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval => 30);
PL/SQL procedure successfully completed.

SQL>  select c.dbid,c.snap_interval,c.retention,c.topnsql,c.tablespace_name from awr_cdb_wr_control c where src_dbname='DG1';
      DBID SNAP_INTERVAL        RETENTION            TOPNSQL    TABLESPACE_NAME
---------- -------------------- -------------------- ---------- ------------------------------
3586104896 +00000 00:30:00.0    +00008 00:00:00.0    DEFAULT    SYSAUX

Modifying AWR Retention from the Primary Database

AWR settings for an ADG standby can also be modified from the primary database by specifying the standby’s AWR ID.

For example, to change the retention period from 8 days to 60 days:

— Primary Database

SQL> exec dbms_workload_repository.modify_snapshot_settings(dbid => 3586104896,retention => 86400);
PL/SQL procedure successfully completed

After redo apply, the updated settings are visible on the standby:

— ADG Standby

SQL>  select c.dbid,c.snap_interval,c.retention,c.topnsql,c.tablespace_name from awr_cdb_wr_control c where src_dbname='DG1';
      DBID SNAP_INTERVAL        RETENTION            TOPNSQL    TABLESPACE_NAME
---------- -------------------- -------------------- ---------- ------------------------------
3586104896 +00000 00:30:00.0    +00060 00:00:00.0    DEFAULT    SYSAUX

Creating Manual AWR Snapshots on ADG Standby

In addition to automatic snapshots, manual AWR snapshots can be created on an Active Data Guard standby, just like on a primary database:

— ADG Standby

SQL> exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed.

Generating AWR Reports in an Active Data Guard using RMF(12.2)

Vahid Yousefzadeh — Mon, 09 Feb 2026 14:53:14 +0000

As you know, generating AWR reports in an Active Data Guard (ADG) environment is not normally possible. This is logically due to the inability to create snapshots on a database that is in read-only mode. In Oracle 12c Release 2, a solution was introduced, and it became possible to generate AWR reports for ADG databases.

In this new feature, a snapshot is first created and workload data from the Data Guard database is stored in a database that is in read-write mode (there is no mandatory requirement to use the primary database for this). After a defined time interval, a second snapshot is created, and the data collection is completed. Finally, the AWR report can be generated. In the following sections, we explain how to perform this process.

This capability (remote snapshot) uses the Remote Management Framework (RMF). Before generating the AWR report, several steps are required to create and configure the RMF topology. These steps, along with the other required procedures, are described below.

Step 1: Check the Status of the SYS$UMF User

SQL> select username,account_status from dba_users where username=‘SYS$UMF’;

USERNAME ACCOUNT_STATUS
SYS$UMF EXPIRED & LOCKED

As shown, this user is expired and locked by default. In the first step, it must be opened:

SQL> alter user sys$umf identified by a account unlock;

User altered

Step 2: Create Two-Way Database Links Between Primary and ADG

For this test, the primary database is used as the source (read-write database).

First, configure the tnsnames.ora file on both the primary and ADG databases:

ADG=

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = ADG_HOST)(PORT = 1521))

    )

    (CONNECT_DATA =

      (sid=ADG)

    )

  )

PRIM=

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = PRIM_HOST)(PORT = 1521))

    )

    (CONNECT_DATA =

      (sid=PRIM)

    )

  )

After configuring tnsnames.ora, create the database links:

–PRIMARY Database

SQL> create database link PRIM_TO_ADG CONNECT TO sys$umf IDENTIFIED BY root using ‘ADG’;

Database link created.

SQL> create database link ADG_TO_PRIM CONNECT TO sys$umf IDENTIFIED BY root using ‘PRIM’;

Database link created.

Test the database links:

SQL>  select database_role from v$database@PRIM_TO_ADG;

DATABASE_ROLE

—————-

PHYSICAL STANDBY

SQL> select database_role from v$database@ADG_TO_PRIM;

DATABASE_ROLE

—————-

PRIMARY

Step 3: Configure RMF Nodes

Before creating the RMF topology, each database must be assigned a unique node name.

— primary database:

SQL> exec dbms_umf.configure_node (‘PRIM_NODE’);

PL/SQL procedure successfully completed.
**— ADG Standby:**

SQL> exec dbms_umf.configure_node(‘ADG_NODE’,’ADG_TO_PRIM’);

PL/SQL procedure successfully completed.

Step 4: Create the RMF Topology

— primary database:

SQL> exec DBMS_UMF.create_topology (‘TOPOL1’);

PL/SQL procedure successfully completed.

To view the list of topologies:

select * from dba_umf_topology;

TOPOLOGY_NAME TARGET_ID TOPOLOGY_VERSION TOPOLOGY_STATE
TOPOL1 3967923579 1 ACTIVE

Step 5: Register the Standby Node in the Topology

SQL> exec DBMS_UMF.register_node (‘TOPOL1’, ‘ADG_NODE’, ‘PRIM_TO_ADG’, ‘ADG_TO_PRIM’, ‘FALSE’, ‘FALSE’);

PL/SQL procedure successfully completed.

Step 6: Enable the AWR Service for Data Guard

SQL> exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>’ADG_NODE’);

PL/SQL procedure successfully completed.
Verify the topology status:

SQL> select * from dba_umf_registration;

TOPOLOGY_NAME NODE_NAME NODE_ID NODE_TYPE AS_SOURCE AS_CANDIDATE_TARGET STATE
TOPOL1 PRIM_NODE 3967923579 0 FALSE FALSE OK
TOPOL1 ADG_NODE 1195199270 0 FALSE FALSE OK

SQL> select * from dba_umf_service;

TOPOLOGY_NAME NODE_ID SERVICE_ID
TOPOL1 1195199270 AWR

SQL> select * from dba_umf_link;

TOPOLOGY_NAME FROM_NODE_ID TO_NODE_ID LINK_NAME
TOPOL1 3967923579 1195199270 PRIM_TO_ADG
TOPOL1 1195199270 3967923579 ADG_TO_PRIM

Step 7: Create Remote Snapshots from ADG

SQL> exec dbms_workload_repository.create_remote_snapshot(‘ADG_NODE’);

PL/SQL procedure successfully completed.

The first snapshot for ADG is now created in the primary environment. After generating workload on ADG and waiting for a suitable interval, create the second snapshot:

SQL> exec dbms_workload_repository.create_remote_snapshot(‘ADG_NODE’)

PL/SQL procedure successfully completed.

Step 8: Generate the AWR Report

SQL> @?/rdbms/admin/awrrpti.sql

Specify the Report Type

~~~~~~~~~~~~~~~~~~~~~~~

AWR reports can be generated in the following formats.  Please enter the

name of the format at the prompt. Default value is ‘html’.

   ‘html’          HTML format (default)

   ‘text’          Text format

   ‘active-html’   Includes Performance Hub active report

Enter value for report_type:

Type Specified: html

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  DB Id      Inst Num   DB Name      Instance     Host

———— ———- ———    ———-   ——

  1195199270     1      ORCL          ADG         ADG_HOST

* 1112303074     1      ORCL          PRIM        PRIM_HOST

Enter value for dbid: 1195199270

Using 1195199270 for database Id

Enter value for inst_num: 1

Using 1 for instance number

Specify the number of days of snapshots to choose from

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed.  Pressing <return> without

specifying a number lists all completed snapshots.

Enter value for num_days: 1

Listing the last day’s Completed Snapshots

Instance     DB Name      Snap Id       Snap Started    Snap Level

———— ———— ———- —————— ———-

ADG           ORCL                1  30 Sep 2018 15:47    1

                                  2  30 Sep 2018 15:55    1

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 1

Begin Snapshot Id specified: 1

Enter value for end_snap: 2

End   Snapshot Id specified: 2

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is awrrpt_1_1_2.html.  To use this name,

press <return> to continue, otherwise enter an alternative.

Enter value for report_name: /home/oracle/awrstb970708.html

Using the report name /home/oracle/awrstb970708.html

A portion of the AWR report output is shown below.

Automatic Flashback (or PITR) Standby

Vahid Yousefzadeh — Sun, 08 Feb 2026 15:46:46 +0000

Beginning with Oracle 19c, when executing FLASHBACK DATABASE command or performing a Point-In-Time Recovery (PITR) on the primary database, the Data Guard standby will also be automatically flashed back, ensuring it does not fall out of sync — provided that Flashback Database is enabled.
This feature is controlled by the hidden parameter _standby_auto_flashback.

In the following text, we compare the Data Guard behavior in Oracle 18c vs 19c when performing a flashback or PITR on the primary database.

Effect of Flashback Operations on the Standby (Oracle 18c)

Primary — Oracle 18c

SQL*Plus: Release 18.0.0.0.0 – Production on Thu Jul 2 08:56:22 2020

SQL> select current_scn from v$database;

             CURRENT_SCN

————————

   784527897542

SQL> create table mt1 as select * from v$datafile;

Table created.

SQL> startup force;

SQL> startup mount force;

SQL> flashback database to scn 784527897542;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

–Data Guard 18c:

rfs (PID:15413): Opened log for T-1.S-2 dbid 998481737 branch 1044945942

2020-07-05T02:45:43.677410-04:00

 rfs (PID:15411): A new recovery destination branch has been registered

 rfs (PID:15411): Standby in the future of new recovery destination branch(resetlogs_id) 1044945942

 rfs (PID:15411): Incomplete Recovery SCN: 784528084868

 rfs (PID:15411): Resetlogs SCN: 784527782003

 rfs (PID:15411): Standby Became Primary SCN: 784526779211

 rfs (PID:15411): New Archival REDO Branch(resetlogs_id): 1044945942  Prior: 1044696841

 rfs (PID:15411): Archival Activation ID: 0x3da0b826 Current: 0x3d9d153c

 rfs (PID:15411): Effect of primary database OPEN RESETLOGS

 rfs (PID:15411): Managed Standby Recovery process is active

Deleted Oracle managed file /oracle/fra2/STB18C/archivelog/2020_07_05/o1_mf_1_1_hj2xrqk9_.arc

2020-07-05T02:45:59.240446-04:00

 rfs (PID:15413): Archived Log entry 76 added for T-1.S-2 rlc 1044945942 ID 0x3da0b826 LAD:2

2020-07-05T02:46:03.692442-04:00

Deleted file /18c/home/dbs/archreact_test_3b839f49.arc

2020-07-05T02:46:10.335173-04:00

PR00 (PID:6211): MRP0: Incarnation has changed! Retry recovery…

2020-07-05T02:46:10.335386-04:00

Errors in file /18c/base/diag/rdbms/stb18c/stb18c/trace/stb18c_pr00_6211.trc:

ORA-19906: recovery target incarnation changed during recovery

Recovery interrupted!

stopping change tracking

2020-07-05T02:46:10.482972-04:00

Errors in file /18c/base/diag/rdbms/stb18c/stb18c/trace/stb18c_pr00_6211.trc:

ORA-19906: recovery target incarnation changed during recovery

2020-07-05T02:46:10.652829-04:00

 Started logmerger process

2020-07-05T02:46:10.687194-04:00

IM on ADG: Start of Empty Journal

IM on ADG: End of Empty Journal

PR00 (PID:15444): Managed Standby Recovery not using Real Time Apply

Warning: Recovery target destination is in a sibling branch

of the controlfile checkpoint. Recovery will only recover

changes to datafiles.

Datafile 1 (ckpscn 784528084868) is orphaned on incarnation#=5

PR00 (PID:15444): MRP0: Detected orphaned datafiles!

PR00 (PID:15444): Recovery will possibly be retried after flashback…

2020-07-05T02:46:10.701103-04:00

Errors in file /18c/base/diag/rdbms/stb18c/stb18c/trace/stb18c_pr00_15444.trc:

ORA-19909: datafile 1 belongs to an orphan incarnation

ORA-01110: data file 1: ‘/18c/base/oradata/DB18C/system01.dbf’

stopping change tracking

2020-07-05T02:46:11.854465-04:00

Recovery Slave PR00 previously exited with exception 19909

2020-07-05T02:46:11.854984-04:00

Errors in file /18c/base/diag/rdbms/stb18c/stb18c/trace/stb18c_mrp0_6205.trc:

ORA-19909: datafile 1 belongs to an orphan incarnation

ORA-01110: data file 1: ‘/18c/base/oradata/DB18C/system01.dbf’

2020-07-05T02:46:31.860563-04:00

Background Media Recovery process shutdown (stb18c)

As shown in the alert log messages, the Data Guard standby exits managed recovery mode.
Even after mounting the standby database, the following errors continue:

SQL> startup mount force;

ORACLE instance started.

Total System Global Area 3741316368 bytes

Fixed Size                  8664336 bytes

Variable Size            1291845632 bytes

Database Buffers         2432696320 bytes

Redo Buffers                8110080 bytes

Database mounted.

SQL> alter database recover managed standby database ;         

ORA-00283: recovery session canceled due to errors

ORA-19909: datafile 1 belongs to an orphan incarnation

ORA-01110: data file 1: ‘/18c/base/oradata/DB18C/system01.dbf’

Fixing the issue in Oracle 18c
To resolve this problem in Oracle 18c, you must manually flash back the standby:

FLASHBACK STANDBY DATABASE TO SCN resetlogs_change# – 2;

Example:

SQL> SELECT TO_CHAR(RESETLOGS_CHANGE# – 2) FROM V$DATABASE;

TO_CHAR(RESETLOGS_CHANGE#-2)

—————————————-

20477777

SQL> flashback database to scn 784527897542;

Flashback complete.

SQL>  alter database recover managed standby database disconnect from session;

Database altered.

Alert log messages confirm successful flashback and restart of managed recovery:

flashback database to scn 784527897542

2020-07-05T04:45:33.783039-04:00

Flashback Restore Start

Flashback Restore Complete

Flashback Media Recovery Start

2020-07-05T04:45:33.947976-04:00

Setting recovery target incarnation to 7

2020-07-05T04:45:33.972208-04:00

 Started logmerger process

2020-07-05T04:45:34.195973-04:00

Parallel Media Recovery started with 8 slaves

2020-07-05T04:45:34.220994-04:00

stopping change tracking

2020-07-05T04:45:34.274863-04:00

Media Recovery Log /oracle/fra2/STB18C/archivelog/2020_07_05/o1_mf_1_9_hj34jf5o_.arc

2020-07-05T04:45:34.365747-04:00

Media Recovery Log /oracle/fra2/STB18C/archivelog/2020_07_05/o1_mf_1_10_hj34m0v8_.arc

2020-07-05T04:45:34.453660-04:00

Incomplete Recovery applied until change 784527897543 time 07/05/2020 08:41:29

2020-07-05T04:45:34.462613-04:00

Flashback Media Recovery Complete

2020-07-05T04:45:34.594961-04:00

stopping change tracking

2020-07-05T04:45:34.621168-04:00

Setting recovery target incarnation to 8

Completed: flashback database to scn 784527897542

2020-07-05T04:45:44.101033-04:00

 alter database recover managed standby database disconnect from session

2020-07-05T04:45:44.101643-04:00

WARNING: There are no standby redo logs.

Standby redo logs should be configured for real time apply. Real time apply will be ignored.

2020-07-05T04:45:44.102366-04:00

Attempt to start background Managed Standby Recovery process (stb18c)

Starting background process MRP0

2020-07-05T04:45:44.124276-04:00

MRP0 started with pid=8, OS id=27178

2020-07-05T04:45:44.126274-04:00

Background Managed Standby Recovery process started (stb18c)

2020-07-05T04:45:49.151924-04:00

 Started logmerger process

2020-07-05T04:45:49.186567-04:00

PR00 (PID:27187): Managed Standby Recovery not using Real Time Apply

2020-07-05T04:45:49.428452-04:00

Parallel Media Recovery started with 8 slaves

2020-07-05T04:45:49.457972-04:00

stopping change tracking

2020-07-05T04:45:49.487418-04:00

TT02 (PID:27205): LOG_FILE_NAME_CONVERT is not defined, stop clearing ORLs

2020-07-05T04:45:49.539145-04:00

PR00 (PID:27187): Media Recovery Log /oracle/fra2/STB18C/archivelog/2020_07_05/o1_mf_1_1_hj34n8sx_.arc

PR00 (PID:27187): Media Recovery Waiting for T-1.S-2 (in transit)

2020-07-05T04:45:50.130729-04:00

Completed:  alter database recover managed standby database disconnect from session

2020-07-05T04:46:23.560180-04:00

 rfs (PID:26959): Archived Log entry 7 added for T-1.S-2 rlc 1044952963 ID 0x3da119b1 LAD:2

 rfs (PID:26959): No SRLs created

2020-07-05T04:46:23.610183-04:00

 rfs (PID:26959): Opened log for T-1.S-3 dbid 998481737 branch 1044952963

2020-07-05T04:46:24.124546-04:00

PR00 (PID:27187): Media Recovery Log /oracle/fra2/STB18C/archivelog/2020_07_05/o1_mf_1_2_hj34n8px_.arc

PR00 (PID:27187): Media Recovery Waiting for T-1.S-3 (in transit)

Effect of Flashback Operations on the Standby (Oracle 19c)

Primary — Oracle 19c

SQL> select current_scn from v$database;

CURRENT_SCN

———–

   20070527

SQL> create table mt1 as select * from v$datafile;

Table created.

SQL> startup force;

SQL> startup mount force;

Database mounted.

SQL> flashback database to scn 20070527;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

–Data Guard

Errors in file /19c/base/diag/rdbms/stb/stb/trace/stb_tt00_25662.trc:

ORA-16009: invalid redo transport destination

2020-07-05T07:32:43.711466+00:00

TT00 (PID:25662): krsg_check_connection: Error 16009 connecting to standby ‘db19c’

2020-07-05T07:32:44.435917+00:00

 rfs (PID:25677): Primary database is in MAXIMUM PERFORMANCE mode

 rfs (PID:25677): No SRLs available for T-1

2020-07-05T07:32:44.509354+00:00

 rfs (PID:25677): Opened log for T-1.S-4 dbid 2168919747 branch 1044934295

2020-07-05T07:32:47.706889+00:00

alter database recover managed standby database disconnect from session

2020-07-05T07:32:47.729300+00:00

Attempt to start background Managed Standby Recovery process (stb)

Starting background process MRP0

2020-07-05T07:32:47.765227+00:00

MRP0 started with pid=47, OS id=25679

2020-07-05T07:32:47.767568+00:00

Background Managed Standby Recovery process started (stb)

2020-07-05T07:32:52.813125+00:00

 Started logmerger process

2020-07-05T07:32:52.834543+00:00

PR00 (PID:25681): Managed Standby Recovery starting Real Time Apply

Warning: Recovery target destination is in a sibling branch

of the controlfile checkpoint. Recovery will only recover

changes to datafiles.

Datafile 1 (ckpscn 20271807) is orphaned on incarnation#=3

PR00 (PID:25681): MRP0: Detected orphaned datafiles!

2020-07-05T07:32:52.864948+00:00

Errors in file /19c/base/diag/rdbms/stb/stb/trace/stb_pr00_25681.trc:

ORA-19909: datafile 1 belongs to an orphan incarnation

ORA-01110: data file 1: ‘/19c/base/oradata2/DB19C/system01.dbf’

PR00 (PID:25681): Managed Standby Recovery not using Real Time Apply

stopping change tracking

2020-07-05T07:32:54.027457+00:00

Recovery Slave PR00 previously exited with exception 19909

2020-07-05T07:32:54.083593+00:00

Errors in file /19c/base/diag/rdbms/stb/stb/trace/stb_mrp0_25679.trc:

ORA-19909: datafile 1 belongs to an orphan incarnation

ORA-01110: data file 1: ‘/19c/base/oradata2/DB19C/system01.dbf’

2020-07-05T07:33:14.089966+00:00

MRP0 (PID:25679): Recovery coordinator performing automatic flashback of database to SCN:0x000000000132407f (20070527)

Flashback Restore Start

2020-07-05T07:33:14.832244+00:00

Completed: alter database recover managed standby database disconnect from session

2020-07-05T07:33:15.320937+00:00

Flashback Restore Complete

Flashback Media Recovery Start

2020-07-05T07:33:15.327426+00:00

Setting recovery target incarnation to 3

2020-07-05T07:33:15.360874+00:00

 Started logmerger process

2020-07-05T07:33:15.638776+00:00

Parallel Media Recovery started with 8 slaves

2020-07-05T07:33:15.670302+00:00

stopping change tracking

2020-07-05T07:33:15.743018+00:00

Media Recovery Log /oracle/fra2/STB/archivelog/2020_07_05/o1_mf_1_5_hj30b2vr_.arc

2020-07-05T07:33:15.849711+00:00

Media Recovery Log /oracle/fra2/STB/archivelog/2020_07_05/o1_mf_1_6_hj30b2wv_.arc

2020-07-05T07:33:16.048294+00:00

Media Recovery Log /oracle/fra2/STB/archivelog/2020_07_05/o1_mf_1_7_hj30b2y1_.arc

2020-07-05T07:33:16.328287+00:00

Media Recovery Log /oracle/fra2/STB/archivelog/2020_07_05/o1_mf_1_8_hj30b30d_.arc

2020-07-05T07:33:16.450049+00:00

Media Recovery Log /oracle/fra2/STB/archivelog/2020_07_05/o1_mf_1_9_hj30b30q_.arc

2020-07-05T07:33:16.860249+00:00

Media Recovery Log /oracle/fra2/STB/archivelog/2020_07_05/o1_mf_1_10_hj30dwh1_.arc

2020-07-05T07:33:17.499131+00:00

Incomplete Recovery applied until change 20070527 time 07/05/2020 03:29:40

2020-07-05T07:33:17.504452+00:00

Flashback Media Recovery Complete

2020-07-05T07:33:17.651017+00:00

stopping change tracking

2020-07-05T07:33:17.785469+00:00

Setting recovery target incarnation to 4

2020-07-05T07:33:17.929586+00:00

 Started logmerger process

2020-07-05T07:33:17.953255+00:00

PR00 (PID:25713): Managed Standby Recovery starting Real Time Apply

2020-07-05T07:33:18.390448+00:00

Parallel Media Recovery started with 8 slaves

2020-07-05T07:33:18.412086+00:00

Media Recovery start incarnation depth : 1, target inc# : 4, irscn : 20070528

stopping change tracking

2020-07-05T07:33:18.516305+00:00

TT02 (PID:25731): LOG_FILE_NAME_CONVERT is not defined, stop clearing ORLs

2020-07-05T07:33:18.531164+00:00

PR00 (PID:25713): Media Recovery Log /oracle/fra2/STB/archivelog/2020_07_05/o1_mf_1_10_hj30dwh1_.arc

2020-07-05T07:33:18.822402+00:00

PR00 (PID:25713): Media Recovery Log /oracle/fra2/STB/archivelog/2020_07_05/o1_mf_1_1_hj30h4t7_.arc

2020-07-05T07:33:18.934168+00:00

PR00 (PID:25713): Media Recovery Log /oracle/fra2/STB/archivelog/2020_07_05/o1_mf_1_2_hj30h4st_.arc

2020-07-05T07:33:19.539777+00:00

PR00 (PID:25713): Media Recovery Log /oracle/fra2/STB/archivelog/2020_07_05/o1_mf_1_3_hj30hzx4_.arc

PR00 (PID:25713): Media Recovery Waiting for T-1.S-4 (in transit)

Data Guard — Oracle 19c

Here, the behavior is different from Oracle 18c.
After detecting an incarnation mismatch, the Data Guard instance automatically performs:

✔ Automatic Flashback of the Standby to the appropriate SCN
✔ Automatic restart of managed recovery
✔ Return to synchronized mode

Automatic Data Guard Rollback During PITR
As demonstrated earlier, in Oracle 19c, performing a manual flashback on the primary automatically triggers a flashback on the standby.
Now let’s perform PITR and observe the results.

Primary (Oracle 19c) — PITR Test

SQL>startup force

RMAN> backup database format ‘/19c/bkp/%U’;

SQL> create table km as select * from v$datafile;

Table created.

SQL> select current_scn from v$database;

CURRENT_SCN

———–

   20477778

SQL> alter system switch logfile;

System altered.

SQL> drop table km;

Table dropped.

SQL> alter system switch logfile;



[oracle@ol7 ~]$ rm -rf /19c/base/oradata/DB19C

[oracle@ol7 ~]$ rman target /

RMAN> restore database ;

RMAN> run{

2> set until scn 20477778;

3> recover database;

4> }

RMAN> alter database open resetlogs;

Statement processed

RMAN>  select count(*) from km;

  COUNT(*)

———-

         4

Data Guard(19c):

2020-07-05T10:46:07.047435+00:00

 rfs (PID:4316): New archival redo branch: 1044945955 current: 1044935053

 rfs (PID:4316): No SRLs available for T-1

2020-07-05T10:46:07.061615+00:00

 rfs (PID:4318): New archival redo branch: 1044945955 current: 1044935053

 rfs (PID:4318): Primary database is in MAXIMUM PERFORMANCE mode

2020-07-05T10:46:07.062180+00:00

 rfs (PID:4316): Opened log for T-1.S-1 dbid 2168919747 branch 1044945955

2020-07-05T10:46:07.070481+00:00

 rfs (PID:4318): No SRLs available for T-1

2020-07-05T10:46:07.079204+00:00

 rfs (PID:4318): Opened log for T-1.S-2 dbid 2168919747 branch 1044945955

2020-07-05T10:46:07.083697+00:00

 rfs (PID:4316): Standby in the future of new recovery destination branch(resetlogs_id) 1044945955

 rfs (PID:4316): Incomplete Recovery SCN:0x00000000013b8c7d

 rfs (PID:4316): Resetlogs SCN:0x0000000001387753

 rfs (PID:4316): Flashback database to SCN:0x0000000001387752 (20477778) to follow new branch

 rfs (PID:4316): New Archival REDO Branch(resetlogs_id): 1044945955  Prior: 1044935053

 rfs (PID:4316): Archival Activation ID: 0x81853ba3 Current: 0x81847647

 rfs (PID:4316): Effect of primary database OPEN RESETLOGS

 rfs (PID:4316): Managed Standby Recovery process is active

2020-07-05T10:46:07.085686+00:00

Incarnation entry added for Branch(resetlogs_id): 1044945955 (stb)

2020-07-05T10:46:07.090581+00:00

Setting recovery target incarnation to 6

2020-07-05T10:46:07.091086+00:00

 rfs (PID:4316): Archived Log entry 52 added for B-1044945955.T-1.S-1 ID 0x81853ba3 LAD:2

2020-07-05T10:46:07.915870+00:00

PR00 (PID:4086): MRP0: Incarnation has changed! Retry recovery…

2020-07-05T10:46:07.916194+00:00

Errors in file /19c/base/diag/rdbms/stb/stb/trace/stb_pr00_4086.trc:

ORA-19906: recovery target incarnation changed during recovery

PR00 (PID:4086): Managed Standby Recovery not using Real Time Apply

Recovery interrupted!

stopping change tracking

2020-07-05T10:46:08.048651+00:00

Errors in file /19c/base/diag/rdbms/stb/stb/trace/stb_pr00_4086.trc:

ORA-19906: recovery target incarnation changed during recovery

2020-07-05T10:46:08.225883+00:00

 Started logmerger process

2020-07-05T10:46:08.245531+00:00

PR00 (PID:4324): Managed Standby Recovery starting Real Time Apply

Warning: Recovery target destination is in a sibling branch

of the controlfile checkpoint. Recovery will only recover

changes to datafiles.

Datafile 1 (ckpscn 20679805) is orphaned on incarnation#=5

PR00 (PID:4324): MRP0: Detected orphaned datafiles!

2020-07-05T10:46:08.260033+00:00

Errors in file /19c/base/diag/rdbms/stb/stb/trace/stb_pr00_4324.trc:

ORA-19909: datafile 1 belongs to an orphan incarnation

ORA-01110: data file 1: ‘/19c/base/oradata2/DB19C/system01.dbf’

PR00 (PID:4324): Managed Standby Recovery not using Real Time Apply

stopping change tracking

2020-07-05T10:46:09.417192+00:00

Recovery Slave PR00 previously exited with exception 19909

2020-07-05T10:46:09.418012+00:00

Errors in file /19c/base/diag/rdbms/stb/stb/trace/stb_mrp0_4084.trc:

ORA-19909: datafile 1 belongs to an orphan incarnation

ORA-01110: data file 1: ‘/19c/base/oradata2/DB19C/system01.dbf’

2020-07-05T10:46:29.424152+00:00

MRP0 (PID:4084): Recovery coordinator performing automatic flashback of database to SCN:0x0000000001387751 (20477777)

Flashback Restore Start

Flashback Restore Complete

Flashback Media Recovery Start

2020-07-05T10:46:29.751325+00:00

Setting recovery target incarnation to 5

2020-07-05T10:46:29.780986+00:00

 Started logmerger process

2020-07-05T10:46:30.092377+00:00

Parallel Media Recovery started with 8 slaves

2020-07-05T10:46:30.117892+00:00

stopping change tracking

2020-07-05T10:46:30.172750+00:00

Media Recovery Log /oracle/fra2/STB/archivelog/2020_07_05/o1_mf_1_7_hj3cj1rg_.arc

2020-07-05T10:46:30.262732+00:00

Media Recovery Log /oracle/fra2/STB/archivelog/2020_07_05/o1_mf_1_8_hj3cj31s_.arc

2020-07-05T10:46:30.355557+00:00

Media Recovery Log /oracle/fra2/STB/archivelog/2020_07_05/o1_mf_1_9_hj3cl30z_.arc

2020-07-05T10:46:30.447894+00:00

Media Recovery Log /oracle/fra2/STB/archivelog/2020_07_05/o1_mf_1_10_hj3cl3s8_.arc

2020-07-05T10:46:30.540674+00:00

Media Recovery Log /oracle/fra2/STB/archivelog/2020_07_05/o1_mf_1_11_hj3cn284_.arc

2020-07-05T10:46:30.629217+00:00

Incomplete Recovery applied until change 20477777 time 07/05/2020 06:42:31

2020-07-05T10:46:30.633468+00:00

Flashback Media Recovery Complete

2020-07-05T10:46:30.764651+00:00

stopping change tracking

2020-07-05T10:46:30.800043+00:00

Setting recovery target incarnation to 6

2020-07-05T10:46:30.838072+00:00

 Started logmerger process

2020-07-05T10:46:30.856095+00:00

PR00 (PID:4348): Managed Standby Recovery starting Real Time Apply

2020-07-05T10:46:31.123891+00:00

Parallel Media Recovery started with 8 slaves

2020-07-05T10:46:31.139204+00:00

Media Recovery start incarnation depth : 1, target inc# : 6, irscn : 20477778

stopping change tracking

2020-07-05T10:46:31.188394+00:00

TT02 (PID:4366): LOG_FILE_NAME_CONVERT is not defined, stop clearing ORLs

2020-07-05T10:46:31.218128+00:00

PR00 (PID:4348): Media Recovery Log /oracle/fra2/STB/archivelog/2020_07_05/o1_mf_1_11_hj3cn284_.arc

2020-07-05T10:46:31.340878+00:00

PR00 (PID:4348): Media Recovery Log /oracle/fra2/STB/archivelog/2020_07_05/o1_mf_1_1_hj3cvh1w_.arc

PR00 (PID:4348): Media Recovery Waiting for T-1.S-2 (in transit)

Data Guard (19c) Behavior During PITR

Unlike Oracle 18c, the standby database in Oracle 19c automatically:

✔ Detects the new RESETLOGS branch
✔ Performs an automatic flashback to the correct SCN
✔ Resolves orphaned incarnations
✔ Restarts real-time apply
✔ Returns to full synchronization

Oracle AI Database 26ai - SQL Domain

Vahid Yousefzadeh — Sun, 08 Feb 2026 15:37:07 +0000

SQL domain can include a set of constraints and attributes, and by assigning it to a column, we can apply those constraints to that column. In other words, SQL Domain enables the extension of data types in a way that aligns with business requirements.

One of the most important use cases of this feature arises when we want to apply specific conditions to the input values of a column. For example, for an Age column of type NUMBER, we may want to prevent values less than 18 by enforcing the condition Age >= 18. Or, in a more practical example, for a column intended to store email addresses, we can apply a rule so that the column only accepts input in the format text@text.text.

Of course, in versions prior to 26ai, this could be achieved using different approaches such as triggers, check constraints, and so on. For instance, using a check constraint, we can enforce that values inserted into the Email column must match the text@text.text format:

SQL> CREATE TABLE EMAIL_ADDRESS
  2       (
  3       id     NUMBER(10),
  4          person_id NUMBER(20),
  5       email  VARCHAR2(500) constraint check_email
  6       CHECK (regexp_like (email, '^(\S+)\@(\S+)\.(\S+)$'))
  7       );
Table created
SQL> insert into EMAIL_ADDRESS values(1,34,'vahidusefzadeh');
'ORA-02290: check constraint (USEF.CHECK_EMAIL) violated'

SQL> insert into EMAIL_ADDRESS values(1,34,'vahidusefzadeh@gmail');
'ORA-02290: check constraint (USEF.CHECK_EMAIL) violated'

SQL> insert into EMAIL_ADDRESS values(1,34,'vahidusefzadeh@gmail.com');
1 row inserted

SQL> update EMAIL_ADDRESS set email='vahidusefzadeh';
'ORA-02290: check constraint (USEF.CHECK_EMAIL) violated'

In a schema, there may be multiple tables that store email addresses. In such cases, the check constraint must be repeated for every column. Now imagine we want to change the email format or disable all such constraints — clearly, managing check constraints in these scenarios becomes challenging.

SQL Domain is highly flexible in this regard and offers multiple capabilities. Once created, it can be reused across multiple tables. Each domain must include at least one data type:

SQL> create domain DMN_check_email as varchar2(500);
Domain created.

A SQL domain can include NOT NULL, NULL, or check constraints. In the following example, we create a domain with a VARCHAR2 data type and a check constraint:

SQL> create domain DMN_check_email as varchar2(500)
 constraint check_email check (regexp_like (DMN_check_email, '^(\S+)\@(\S+)\.(\S+)$'));
Domain created.

In this example, we explicitly named the constraint check_email. Naming constraints is optional; if not specified, Oracle automatically assigns a name.

To control how data is displayed, we can also use the DISPLAY clause:

SQL> create domain DMN_check_email as varchar2(500)
 constraint check_email check (regexp_like (DMN_check_email, '^(\S+)\@(\S+)\.(\S+)$'))
 display upper('Email: '||DMN_check_email);
Domain created.

After creating DMN_check_email, we can assign it to a table column. A domain can be used by multiple tables:

SQL> CREATE TABLE EMAIL_ADDRESS
  2       (
  3       id     NUMBER(10),
  4          person_id NUMBER(20),
  5       email  DMN_check_email
  6       );
Table created
SQL> desc EMAIL_ADDRESS 
 Name                   Null?    Type
 ---------------------- -------- ----------------------------
 ID                              NUMBER(10)
 PERSON_ID                       NUMBER(20)
 EMAIL                           VARCHAR2(500) USEF.DMN_CHECK_EMAIL

This can also be done in other ways:

SQL> CREATE TABLE EMAIL_ADDRESS
  2       (
  3       id     NUMBER(10),
  4       person_id NUMBER(20),
  5       email  varchar2(500) domain  DMN_check_email
  6       );
Table created
SQL> CREATE TABLE EMAIL_ADDRESS
  2       (
  3       id     NUMBER(10),
  4       person_id NUMBER(20),
  5       email  domain DMN_check_email
  6       );
Table created

Testing the domain behavior:

SQL> insert into EMAIL_ADDRESS values(1,34,'vahidusefzadeh');
ORA-11534: check constraint (USEF.SYS_C008398) due to domain constraint USEF.CHECK_EMAIL of domain USEF.DMN_CHECK_EMAIL violated

SQL> insert into EMAIL_ADDRESS values(1,34,'vahidusefzadeh@gmail');
ORA-11534: check constraint (USEF.SYS_C008398) due to domain constraint USEF.CHECK_EMAIL of domain USEF.DMN_CHECK_EMAIL violated

SQL> insert into EMAIL_ADDRESS values(1,34,'vahidusefzadeh@gmail.com');
1 row inserted

SQL> insert into EMAIL_ADDRESS values(2,46,'vahidusefzadeh@yahoo.com');
1 row created.

SQL> update EMAIL_ADDRESS set email='vahidusefzadeh';
'ORA-11534: check constraint (USEF.SYS_C008398) due to domain constraint USEF.CHECK_EMAIL of domain USEF.DMN_CHECK_EMAIL violated'
SQL> select * from EMAIL_ADDRESS;
        ID  PERSON_ID EMAIL
---------- ---------- ------------------------------
         1         34 vahidusefzadeh@gmail.com
         2         46 vahidusefzadeh@yahoo.com

To apply display formatting, we can use the DOMAIN_DISPLAY function:

SQL> select id,PERSON_ID,DOMAIN_DISPLAY(EMAIL) EMAIL from EMAIL_ADDRESS;
        ID  PERSON_ID EMAIL
---------- ---------- ----------------------------------------
         1         34 EMAIL: VAHIDUSEFZADEH@GMAIL.COM
         1         46 EMAIL: VAHIDUSEFZADEH@YAHOO.COM

Domains can also be applied to existing tables:

SQL> CREATE TABLE EMAIL_ADDRESS2
  2       (
  3       id     NUMBER(10),
  4          person_id NUMBER(20),
  5       email  varchar2(1000)
  6       );
Table created
SQL> insert into EMAIL_ADDRESS2 values(1,34,'vahidusefzadeh');
1 row inserted

SQL> insert into EMAIL_ADDRESS2 values(2,34,'vahidusefzadeh@gmail.com');
1 row inserted

SQL> commit;
Commit complete

SQL> alter table EMAIL_ADDRESS2 modify email domain DMN_CHECK_EMAIL;
'ORA-02293: cannot validate (USEF.) - check constraint violated'

SQL> delete EMAIL_ADDRESS2 where id=1;
1 row deleted

SQL> commit;
Commit complete

SQL> alter table EMAIL_ADDRESS2 modify email domain DMN_CHECK_EMAIL;
Table altered

If existing data violates the domain constraint, Oracle raises an error until invalid rows are corrected.

To view created domains:

SQL> select owner, name from user_domains;
OWNER           NAME
--------------- ---------------
USEF            DMN_CHECK_EMAIL

Domain constraints can be viewed using:

select * from user_domain_constraints where domain_name='DMN_CHECK_EMAIL';
NAME        SEARCH_CONDITION                                        STATUS
----------- ------------------------------------------------------- --------
CHECK_EMAIL regexp_like (DMN_check_email, '^(\S+)\@(\S+)\.(\S+)$')  ENABLED

The DBA_DOMAIN_COLS view is also very useful in this context.

Built-in Oracle Domains

Oracle automatically provides several predefined domains, which can be viewed via ALL_DOMAINS:

SQL> select name from all_domains where owner='SYS';
PHONE_NUMBER_D
EMAIL_D
DAY_SHORT_D
DAY_D
MONTH_SHORT_D
MONTH_D
YEAR_D
POSITIVE_NUMBER_D
NEGATIVE_NUMBER_D
NON_POSITIVE_NUMBER_D
NON_NEGATIVE_NUMBER_D
MAC_ADDRESS_D
SSN_D
CREDIT_CARD_NUMBER_D
IPV4_ADDRESS_D
IPV6_ADDRESS_D
SUBNET_MASK_D
SHA1_D
SHA256_D
SHA512_D
CIDR_D
MIME_TYPE_D
22 rows selected.

Examples include EMAIL_D, IPV4_ADDRESS_D, CREDIT_CARD_NUMBER_D, and more.

Example using IPV4_ADDRESS_D:

SQL> create table tbl1(ip_v4 IPV4_ADDRESS_D);
Table created

SQL> insert into tbl1 values('10.22.44.66');
1 row inserted

SQL> insert into tbl1 values('10.22.44.666');
ORA-11534: check constraint (USEF.SYS_C008400) due to domain constraint SYS.SYS_DOMAIN_C0015 of domain SYS.IPV4_ADDRESS_D violated

SQL> insert into tbl1 values('10.22.44.1.5');
ORA-11534: check constraint (USEF.SYS_C008400) due to domain constraint SYS.SYS_DOMAIN_C0015 of domain SYS.IPV4_ADDRESS_D violated

To extract the constraint definition of a domain, we can use DBMS_METADATA:

select dbms_metadata.get_ddl('SQL_DOMAIN', 'IPV4_ADDRESS_D','SYS') domain_ddl from dual;
CREATE DOMAIN "SYS"."IPV4_ADDRESS_D" AS VARCHAR2(15) CHECK (REGEXP_LIKE(ipv4_address_d,'^(([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])\.){3}([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])$')) ENABLE

Dropping a Domain

If a domain has not been assigned to any column, it can be dropped easily:

SQL> drop domain DMN_check_email;
Done

If the domain is in use, Oracle raises an error. Using FORCE removes the domain and also removes the associated constraints from columns:

SQL> drop domain DMN_check_email;
'ORA-11502: Message 11502 not found;  product=RDBMS; facility=ORA'

Using FORCE PRESERVE removes the domain but keeps the constraint in place:

SQL>  DESC EMAIL_ADDRESS
Name         Type
 -----------  --------------------------------------
 ID           NUMBER(10)
 PERSON_ID    NUMBER(20)
 EMAIL        VARCHAR2(500) USEF.DMN_CHECK_EMAIL            
SQL> drop domain DMN_check_email force;
Done
SQL> DESC EMAIL_ADDRESS   
 Name                      Type
 ------------------------  ---------------
 ID                        NUMBER(10)
 PERSON_ID                 NUMBER(20)
 EMAIL                     VARCHAR2(500)
SQL> insert into EMAIL_ADDRESS values(1,34,'vahidusefzadeh');
1 row inserted
Using FORCE PRESERVE removes the domain but keeps the constraint in place:

SQL> drop domain DMN_check_email force PRESERVE;
Domain dropped.
SQL> DESC EMAIL_ADDRESS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(10)
 PERSON_ID                                          NUMBER(20)
 EMAIL                                              VARCHAR2(500)


SQL> insert into EMAIL_ADDRESS values(1,34,'vahidusefzadeh');
ORA-02290: check constraint (USEF.SYS_C008407) violated

Multi-Column Domain

All examples so far were Single Column Domains. Oracle also supports Multi-Column Domains and Flexible Domains.

Example of a Multi-Column Domain:

SQL> CREATE DOMAIN IR_NORTH AS
  (
    province  AS VARCHAR2(100),
    zipcode AS NUMBER
  )
  CONSTRAINT IR_NORTH_ch CHECK(province in ('Mazandaran','Golestan','GILAN') and zipcode >1234);
Domain created.
SQL> CREATE TABLE TBL_IR_NORTH(
        id        number(10),
        province  VARCHAR2(100),
        zipcode   NUMBER,
        domain IR_NORTH(province,zipcode)
        );
Table created.
SQL> insert into TBL_IR_NORTH values(1,'Mazandaran',12345);
1 row created.

SQL> insert into TBL_IR_NORTH values(2,'Tehran',123456);
ERROR at line 1:
ORA-11534: check constraint (SYS.SYS_C008299) due to domain constraint SYS.IR_NORTH_CH of domain SYS.IR_NORTH violated

SQL> insert into TBL_IR_NORTH values(2,'Mazandaran',1);
ERROR at line 1:
ORA-11534: check constraint (SYS.SYS_C008299) due to domain constraint SYS.IR_NORTH_CH of domain SYS.IR_NORTH violated

Flexible Domain

To create a Flexible Domain, at least two domains are required:

SQL> CREATE DOMAIN IR_NORTH AS
  (
    province  AS VARCHAR2(100),
    zipcode AS NUMBER
  )
  CONSTRAINT IR_NORTH_ch CHECK(province in ('Mazandaran','Golestan','GILAN') and zipcode >1234);
Domain created.
SQL> CREATE DOMAIN IR_SOUTH AS
  (
    province  AS VARCHAR2(100),
    zipcode AS NUMBER
  )
  CONSTRAINT IR_SOUTH_CH CHECK(province in ('Khozestan','Bushehr') and zipcode between 1 and 1000);
Domain created.
SQL> create flexible domain IR_Regions (province,zipcode)
choose domain using (Regions varchar2(10))
from case
       when Regions in ('SOUTH') then IR_SOUTH(province,zipcode)
       when Regions in ('NORTH') then IR_NORTH(province,zipcode)
     end;  
Domain created.
SQL> CREATE TABLE TBL_IR_Regions(
        id        number(10),
        province  VARCHAR2(100),
        zipcode   NUMBER,
        Regions   varchar2(10),
        domain IR_Regions(province,zipcode)using (Regions)
        );  
Table created.
SQL> insert into TBL_IR_Regions values(1,'Khozestan',10,'SOUTH');
1 row created.

SQL> insert into TBL_IR_Regions values(2,'Khozestan',3000145,'SOUTH');
ORA-11534: check constraint (SYS.SYS_C008303) due to domain constraint SYS.SYS_DOMAIN_C0041 of domain SYS.IR_REGIONS violated

SQL> insert into TBL_IR_Regions values(2,'Khozestan',30,'NORTH');
ORA-11534: check constraint (SYS.SYS_C008304) due to domain constraint SYS.SYS_DOMAIN_C0040 of domain SYS.IR_REGIONS violated

SQL> insert into TBL_IR_Regions values(2,'Mazandaran',30,'NORTH');
ORA-11534: check constraint (SYS.SYS_C008304) due to domain constraint SYS.SYS_DOMAIN_C0040 of domain SYS.IR_REGIONS violated

SQL> insert into TBL_IR_Regions values(2,'Mazandaran',3098755,'NORTH');
1 row created.

SQL> insert into TBL_IR_Regions values(3,'Mazandaran',30987588,'SOUTH');
ORA-11534: check constraint (SYS.SYS_C008303) due to domain constraint SYS.SYS_DOMAIN_C

Adding PDB Resources in Grid Infrastructure

Vahid Yousefzadeh — Sun, 08 Feb 2026 15:29:24 +0000

In Oracle Database 21c, within the Grid Infrastructure environment, a new type of resource called ora.pdb.type has been introduced, which can be used to manage PDBs. These resources are named using the following format:

ora.<DB Unique Name>.<PDB Name>.pdb

Like other resources in the cluster and GI environment, PDB-related resources can be controlled and managed using the srvctl tool. For example, you can start or stop a PDB using this tool:

[grid@RAC3 ~]$ srvctl start pdb -db db21c -pdb pdb1

[grid@RAC3 ~]$ srvctl status pdb -db db21c -pdb pdb1

Pluggable database PDB1 is enabled.

Pluggable database PDB1 of database db21c is running on nodes: rac2,rac3

[grid@RAC3 ~]$ srvctl stop pdb -db db21c -pdb pdb1

[grid@RAC3 ~]$ srvctl status pdb -db db21c -pdb pdb1

Pluggable database PDB1 is enabled.

Pluggable database PDB1 of database db21c is not running.

 [oracle@RAC2 ~]$ srvctl stop pdb -db db21c -pdb pdb1 -n rac3 -stopoption IMMEDIATE

You can also configure a PDB to start automatically using srvctl, without using triggers or saved state:

SQL>  select * from dba_PDB_SAVED_STATES ;

no rows selected


[oracle@RAC3 ~]$ srvctl modify pdb -db db21c -pdb pdb1 -policy AUTOMATIC

[oracle@RAC3 ~]$ srvctl stop database -db db21c

[oracle@RAC3 ~]$ srvctl start database -db db21c


SQL*Plus: Release 21.0.0.0.0 – Production on Wed Oct 13 11:18:02 2021

Version 21.3.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

———- —————————— ———- ———-

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE NO

Using this tool, you can also manually remove or add a PDB resource from/to Grid Infrastructure:

 [oracle@RAC3 ~]$ srvctl remove pdb -db db21c -pdb pdb2

Remove the PDB pdb2? (y/[n]) y

[oracle@RAC3 ~]$ srvctl add pdb -db db21c -pdb pdb2 -startoption OPEN

However, when a new PDB is created in the cluster, its corresponding resource is automatically registered in Grid Infrastructure when the PDB is opened:

SQL> create pluggable database pdb2 admin user a identified by a;

Pluggable database created.

SQL> alter pluggable database pdb2 open;

Pluggable database altered.


[root@RAC3 bin]#  ./crsctl stat res -w “TYPE = ora.pdb.type”

NAME=ora.db21c.pdb1.pdb

TYPE=ora.pdb.type

TARGET=ONLINE        , ONLINE

STATE=ONLINE on rac2, ONLINE on rac3

NAME=ora.db21c.pdb2.pdb

TYPE=ora.pdb.type

TARGET=ONLINE        , OFFLINE

STATE=ONLINE on rac2, OFFLINE

SESSION-LEVEL SEQUENCES

Vahid Yousefzadeh — Sun, 08 Feb 2026 15:26:55 +0000

Oracle Database 12c introduces session-level sequences, which generate sequence values that are maintained independently within each database session:

CREATE SEQUENCE seq_new START WITH 1 INCREMENT BY 1 SESSION;

--session 1:
select seq_new.nextval from dual;
1

--session 2:
select seq_new.nextval from dual;
1

To change this sequence from session level to global level, and vice versa, the following commands are used:

alter sequence seq_new global;

alter sequence seq_new session;

A Brief Review of Interval Partitioning

Vahid Yousefzadeh — Sun, 08 Feb 2026 07:29:04 +0000

With the introduction of interval partitioning in Oracle Database 11g, the need to manually add partitions when inserting data outside the defined range was eliminated. This article briefly reviews several important aspects of interval partitioning.

Point 1: Requirement for an Initial Range Partition

To use range-interval partitioning, at least one range partition must be defined:

SQL> create table tbl (

     id number,

     name varchar2(10),

     date_        DATE

)

PARTITION BY RANGE (date_)

 INTERVAL(NUMTOYMINTERVAL(1, ‘YEAR’))

(

   PARTITION p1 VALUES LESS THAN (TO_DATE(‘4-6-2001’, ‘DD-MM-YYYY’))

);

After new partitions are added automatically, you might decide to drop partition p1 (which is the only range partition):

SQL> insert into tbl values(1,’test’,TO_DATE(‘4-6-2002‘, ‘DD-MM-YYYY’));

1 row inserted

SQL> insert into tbl values(1,’test’,TO_DATE(‘4-6-2003‘, ‘DD-MM-YYYY’));

1 row inserted

SQL> select PARTITION_NAME,INTERVAL from user_tab_partitions where table_name=’TBL’;

PARTITION_NAME   INTERVAL

—————- ——–

P1               NO

SYS_P701 YES

SYS_P702         YES

SQL> alter table TBL drop partition p1;

ORA-14758: Last partition in the range section cannot be dropped

In Oracle 11g, the first range partition cannot be dropped unless another range partition exists. To drop it, all interval partitions must first be converted to non-interval partitions:

SQL> alter table tbl set interval( numtoyminterval(1,’YEAR’));

Table altered

SQL> select PARTITION_NAME,INTERVAL from user_tab_partitions where table_name=’TBL’;

PARTITION_NAME   INTERVAL

—————- ——–

P1               NO

SYS_P701         NO

SYS_P702         NO

Now the p1 partition can be dropped:

SQL> alter table TBL drop partition p1;

Table altered

In Oracle 12c Release 2 and later, this behavior changed. The first range partition can be dropped directly, and Oracle automatically manages the transition:

SQL> select PARTITION_NAME,INTERVAL from user_tab_partitions where table_name=’TBL’;

PARTITION_NAME       INTERVAL

——————– ——–

P1                   NO

SYS_P17415           YES

SYS_P17416           YES

SQL> alter table TBL drop partition p1;

Table altered

SQL> select PARTITION_NAME,INTERVAL from user_tab_partitions where table_name=’TBL’;

PARTITION_NAME       INTERVAL

——————– ——–

SYS_P17415           NO

SYS_P17416           YES

Point 2: Behavior of Automatically Created Interval Partitions

Consider a table partitioned using a range-interval strategy:

SQL> create table tbl (

     id number,

     name varchar2(10),

     date_        DATE

)

PARTITION BY RANGE (date_)

INTERVAL(NUMTOYMINTERVAL(1, ‘YEAR‘))

(

   PARTITION p1 VALUES LESS THAN (TO_DATE(‘4-6-2001’, ‘DD-MM-YYYY’)),

   PARTITION p2 VALUES LESS THAN (TO_DATE(‘5-5-2003’, ‘DD-MM-YYYY’)),

   PARTITION p3 VALUES LESS THAN (TO_DATE(‘6-4-2005’, ‘DD-MM-YYYY’))

); 

Table created.

When inserting rows with dates beyond the last defined range (greater than 06–04–2005), Oracle creates new interval partitions. The day and month of the new partition boundary match those of the last range partition:

SQL> insert into tbl values(7,’test’,TO_DATE(‘9-8-2041’, ‘DD-MM-YYYY’));

1 row inserted

SQL> insert into tbl values(7,’test’,TO_DATE(’10-11-2091′, ‘DD-MM-YYYY’));

1 row inserted

SQL> select PARTITION_NAME,HIGH_VALUE from user_tab_partitions where table_name=’TBL’;

PARTITION_NAME       HIGH_VALUE

——————– ———–

P1                    2001-06-04

P2                    2003-05-05

P3                    2005-04-06

SYS_P501              2042-04-06

SYS_P521              2092-04-06

As shown, each newly created partition uses the same month and day (04–06) as the last range partition.

To change this behavior for future interval partitions:

1.Disable interval partitioning:

SQL>  alter table tbl set interval();

Table altered

2. Add a new range partition with the desired date:

SQL> alter table tbl add partition p100 values less than (TO_DATE(‘3030-01-01‘,’YYYY-MM-DD’));

Table altered

3.Re-enable interval partitioning:

SQL> alter table tbl set interval(NUMTOYMINTERVAL(1,’YEAR‘));

Table altered

Now, new interval partitions will be created using the new boundary:

SQL> insert into tbl values(855,’test’,TO_DATE(’12-12-4040′, ‘DD-MM-YYYY’));

1 row inserted

SQL> select PARTITION_NAME,HIGH_VALUE from user_tab_partitions where table_name=’TBL’;

PARTITION_NAME       HIGH_VALUE

——————– ———–

P1                   2001-06-04

P2                   2003-05-05

P3                   2005-04-06

SYS_P501             2042-04-06

SYS_P521             2092-04-06

P100                 3030-01-01

SYS_P541             4041-01-01

7 rows selected

Point 3: Naming Partitions

When adding a range partition, you can explicitly assign a name:

SQL>   alter table tbl add partition p2040 values less than (TO_DATE(‘4-6-2040’, ‘DD-MM-YYYY’));

Table altered

If you do not specify a name, Oracle assigns one automatically:

SQL>   alter table tbl add partition  values less than (TO_DATE(‘4-6-2040’, ‘DD-MM-YYYY’));

Table altered

SQL> select PARTITION_NAME,HIGH_VALUE from user_tab_partitions where table_name=’TBL’;

PARTITION_NAME       HIGH_VALUE

——————–                   ——————–

P1                                           2001-06-04

SYS_P581                             2040-06-04

However, when interval partitioning is enabled, you cannot manually add partitions using this method:

SQL> alter table tbl add partition p4020 values less than (4);

ORA-14760: ADD PARTITION is not permitted on Interval partitioned objects

Starting with Oracle 12c Release 2, you can rename automatically created interval partitions:

SQL> select PARTITION_NAME,HIGH_VALUE,INTERVAL from user_tab_partitions where table_name=’TBL’;

PARTITION_NAME       HIGH_VALUE INTERVAL

——————– ———- ——–

P1                   1          NO

SYS_P601             3          YES

SQL> alter table tbl rename partition for (2) to p3;

Table altered

SQL> select PARTITION_NAME,HIGH_VALUE,INTERVAL from user_tab_partitions where table_name=’TBL’;

PARTITION_NAME       HIGH_VALUE INTERVAL

——————– ———- ——–

P1                   1          NO

P3                   3          YES

Point 4: Manually Adding Partitions to a Range-Interval Table

You cannot manually add partitions to a range-interval partitioned table using the standard method (without performing DML). However, this can be achieved using one of the following approaches.

Method 1: Using LOCK TABLE ... PARTITION FOR

SQL> select PARTITION_NAME,HIGH_VALUE,INTERVAL from user_tab_partitions where table_name=’TBL’;

PARTITION_NAME  HIGH_VALUE            INTERVAL

————— ———–          ——–

P1               2001-06-04         NO

SQL> LOCK TABLE tbl PARTITION FOR(TO_DATE(’11-12-2021′, ‘DD-MM-YYYY’)) IN SHARE MODE;

Table(s) locked

SQL> commit;

Commit complete

SQL> select PARTITION_NAME,HIGH_VALUE,INTERVAL from user_tab_partitions where table_name=’TBL’;

PARTITION_NAME  HIGH_VALUE  INTERVAL

————— ———– ——–

P1              2001-06-04   NO

SYS_P664 2022-06-04   YES

A common use case for this method is performing a partition exchange:

SQL> create table tbl_non_partition as select * from tbl where 1=2;

Table created

SQL> insert into tbl_non_partition values(855,’test’,TO_DATE(’09-09-2021′, ‘DD-MM-YYYY’));

1 row inserted

SQL> insert into tbl_non_partition values(855,’test’,TO_DATE(’07-07-2021′, ‘DD-MM-YYYY’));

1 row inserted

SQL> insert into tbl_non_partition values(855,’test’,TO_DATE(’08-08-2021′, ‘DD-MM-YYYY’));

1 row inserted

SQL> insert into tbl_non_partition values(855,’test’,TO_DATE(’10-10-2021′, ‘DD-MM-YYYY’));

1 row inserted

SQL> commit;

Commit complete

SQL> ALTER TABLE tbl

    EXCHANGE PARTITION FOR (TO_DATE(’11-12-2021′, ‘DD-MM-YYYY’))

      WITH TABLE tbl_non_partition;

Table altered

SQL> select count(*) from tbl_non_partition;

  COUNT(*)

———-

         0

SQL> select count(*) from tbl PARTITION FOR (TO_DATE(’11-12-2021′, ‘DD-MM-YYYY’));

  COUNT(*)

———-

         4

Method 2: temporary disabling interval partitioning and adding manually partitions:

SQL> ALTER TABLE tbl SET INTERVAL ();

Table altered

SQL> alter table tbl add partition p4020 values less than (TO_DATE(’11-12-2021′, ‘DD-MM-YYYY’)) ;

Table altered

SQL> select PARTITION_NAME,HIGH_VALUE,INTERVAL from user_tab_partitions where table_name=’TBL’;

PARTITION_NAME  HIGH_VALUE  INTERVAL

————— ———-  ——–

P1              2001-06-04  NO

P4020           2021-12-11  NO

Point 5: Converting Range Partitioning to Interval Partitioning

Consider the following range-partitioned table:

create table tbl1 (

     id number,

     name varchar2(10),

     date_        DATE

)

PARTITION BY RANGE (date_)

(

   PARTITION p1 VALUES LESS THAN (TO_DATE(‘4-1-2001’, ‘DD-MM-YYYY’))

);

Attempting to insert data for the year 2030 results in an error:

SQL> insert into tbl1 values(6,’test’,TO_DATE(‘4-1-2030’, ‘DD-MM-YYYY’));

ORA-14400: inserted partition key does not map to any partition

By converting the table to interval partitioning, Oracle automatically creates new partitions for out-of-range values:

SQL> ALTER TABLE tbl1 SET INTERVAL(NUMTOYMINTERVAL(2,’YEAR’));

Table altered.

SQL> select partition_name, interval from   user_tab_partitions where  table_name = ‘TBL1’;

PARTITION_ INT

———- —

P1         NO

SQL> insert into tbl1 values(6,’test’,TO_DATE(‘4-1-2030’, ‘DD-MM-YYYY’));

1 row created.

SQL> select partition_name, interval from   user_tab_partitions where  table_name = ‘TBL1’;

PARTITION_ INT

———- —

P1         NO

SYS_P421   YES

Point 6: Converting Interval Partitioning to Range Partitioning

As shown, converting from range to interval automatically creates partition SYS_P421. To revert the table from interval to range partitioning:

SQL> ALTER TABLE tbl1 SET INTERVAL ();

Table altered.

SQL> select partition_name, interval from user_tab_partitions where  table_name = ‘TBL1’;

PARTITION_ INT

———- —

P1         NO

SYS_P421   NO

SQL>  insert into tbl1 values(7,’test’,TO_DATE(‘4-1-2040’, ‘DD-MM-YYYY’));

ORA-14400: inserted partition key does not map to any partition

Point 7: Distributing Interval Partitions Across Tablespaces

When using interval partitioning, partitions can be distributed across multiple tablespaces in a round-robin fashion using the STORE IN clause:

SQL> alter table tbl1 set STORE IN(tbs1,tbs2,tbs3,tbs4);

Table altered.

What’s New in Oracle AI Database 26ai? Exploring 50+ Major New Features

Vahid Yousefzadeh — Tue, 27 Jan 2026 20:04:12 +0000

Oracle AI Database 26ai Enterprise Edition for Linux x86–64 is now available on-premises. This release is more than just a version update — it’s a fundamental shift towards an intelligent, self-managing, and developer-friendly data platform. True to its “ai” namesake, it is infused with artificial intelligence and automation, and it also delivers a massive wave of enhancements across SQL, security, performance, manageability, and development.

In this article, we dive into over 50 major new features that define Oracle Database 26ai.

1.Oracle AI Database 26ai — SQL Domain

2.Oracle AI Database 26ai — Wide Tables

3.Oracle AI Database 26ai — Dictionary Protection

4.Reducing Unified Audit Trail Size in Oracle AI Database 26ai

5.Oracle AI Database 26ai: Filtering Analytic Function Results with the QUALIFY Clause

6.Oracle AI Database 26ai: RESETTABLE Clause

7.Oracle AI Database 26ai: Bind Variable Support in Materialized View Query Rewrite

8.Oracle AI Database 26ai — Automatic Transaction Rollback (Priority Transactions with high, medium and low priority)

9.SQL Diagnostic Report(26ai, 19.28)

10.Oracle Database 26ai,19c — RMAN Progress Status Report

11.Oracle AI Database 26ai(23.9) — GROUP BY ALL