The database maintains data in materialized views by refreshing them after changes to the base tables. Refreshing a materialized view automatically updates all its indexes. In the case of full refresh, this requires temporary sort space to rebuild all indexes during refresh.

Types of Refresh for Materialized Views:

The following is an incremental refresh methods for materialized views that use cloud links:

• log-based refresh

For different refreshing methods, you can refer to the Refreshing Materialized Views chapter.

The incremental refresh is commonly called FAST refresh because it usually performs faster than the complete refresh.

An incremental refresh eliminates the need to rebuild materialized views from scratch. Thus, processing only the changes can result in a very fast refresh time. For materialized views that use the log-based fast refresh method, a materialized view log keep a record of changes to the base tables. A materialized view log is a schema object that records changes to a base table so that a materialized view defined on the base table can be refreshed incrementally. Each materialized view log is associated with a single base table. The materialized view log resides in the same database and schema as its base table. You can enable Oracle Autonomous AI Database to define and refresh materialized views on remote tables accessed via cloud links.

Autonomous AI Database Serverless performs incremental refresh of materialized views using FAST refresh. This method applies only the changes made since the last refresh, rather than performing a complete refresh of the entire materialized view.

Refer to Types of Materialized Views for various types of materialized views that support incremental refresh with cloud links.

You must register a cloud link to indicate that materialized view fast refresh is supported. Use the REGISTER procedure in the DBMS_CLOUD_LINK package with MV_FAST_REFRESH argument to register the cloud link.

BEGIN
   DBMS_CLOUD_LINK.REGISTER(
    schema_name     => 'CLOUDLINK',
    schema_object   => 'SALES_VIEW_AGG',
    namespace       => 'REGIONAL_SALES', 
    name            => 'SALES_AGG',
    description     => 'Aggregated regional sales information.',
    scope           => 'MY$TENANCY',
    auth_required   =>  FALSE,
    data_set_owner  =>  'tomholl@example.com',
    mv_fast_refresh =>  TRUE );
END;
/

Refer to REGISTER Procedure for more information.

You can update a cloud link to indicate that materialized view fast refresh is supported on the cloud link.

BEGIN
   DBMS_CLOUD_LINK.REGISTER(
    schema_name    => 'CLOUDLINK',
    schema_object  => 'SALES_ALL',
    namespace      => 'TRUSTED_COMPARTMENT', 
    name           => 'SALES',
    description    => 'Trusted Compartment, only accessible within my compartment. Early sales data.',
    scope          => 'MY$COMPARTMENT',
    auth_required  =>  FALSE,
    data_set_owner =>  'tomholl@example.com' );
END;
/

Assume that a materialized view log table is later created on the SALES_ALL table in CLOUDLINK schema. The cloud link can be updated to enable fast refresh capability by passing TRUE value for MV_FAST_REFRESH argument in the UPDATE_REGISTRATION procedure.

BEGIN
   DBMS_CLOUD_LINK.UPDATE_REGISTRATION(
    namespace       => 'TRUSTED_COMPARTMENT', 
    name            => 'SALES',
    mv_fast_refresh => TRUE );
END;
/

The UPDATE_REGISTRATION procedure enables materialized view fast refresh over cloud links by ensuring that the materialized view log table is accessible over the cloud link. To make it accessible, you must pass a value of TRUE for the MV_FAST_REFRESH argument.

Refer to UPDATE_REGISTRATION Procedure for more information.

Since the provider has created the required materialized view logs. You must refresh the link’s metadata to ensure it reflects the new log tables.

This use case demonstrates how to share data between two Oracle Autonomous AI Database instances using Cloud Link and fast-refresh materialized views.

Consider an organization where the sales team Producer AI Autonomous Database (PRODUCER_DB) stores live sales transactions in a table called SALES_ALL within the CLOUDLINK schema. The analytics team Consumer AI Autonomous Database (CONSUMER_DB) in the same OCI compartment accesses this sales data through a registered Cloud Link and creates a fast-refresh materialized view TRUSTED_SALES_MV on the Cloud Link and queries live data for reporting. After inserting a new Desk Lamp sale on Producer, a fast refresh syncs just the change to Consumer for instant reporting.

Prerequisites

Before implementing incremental refresh for materialized views over cloud links, ensure the following prerequisites are met.

• Cloud links are properly configured and tested.
• Appropriate privileges are granted to users who will create and refresh materialized views.
• The provider database has materialized view logging configured for the base tables.

The following sections outline the detailed workflow for sharing data between two Oracle Autonomous AI Database instances using Cloud Link and fast-refresh materialized views in an example practical use case. This workflow and the associated code examples can be modified and implemented according to your requirements.

Step 1: Create Table and Load Initial Sales Data

The Sales team creates the SALES_ALL table that stores every checkout transaction coming from the organization, capturing the sale ID, product name, quantity, unit price, and sale date. It loads ten initial transactions that ranges from Wireless Mouse to Bluetooth Speaker.

CREATE TABLE SALES_ALL (
    sale_id       NUMBER PRIMARY KEY,
    product_name  VARCHAR2(100),
    quantity      NUMBER,
    unit_price    NUMBER(10, 2),
    sale_date     DATE DEFAULT SYSDATE
);

INSERT INTO SALES_ALL (sale_id, product_name, quantity, unit_price, sale_date) VALUES (1, 'Wireless Mouse', 2, 25.50, TO_DATE('2026-01-29', 'YYYY-MM-DD'));
INSERT INTO SALES_ALL (sale_id, product_name, quantity, unit_price, sale_date) VALUES (2, 'Mechanical Keyboard', 1, 89.99, TO_DATE('2026-01-20', 'YYYY-MM-DD'));
INSERT INTO SALES_ALL (sale_id, product_name, quantity, unit_price, sale_date) VALUES (3, 'USB-C Hub', 3, 45.00, TO_DATE('2026-01-21', 'YYYY-MM-DD'));
INSERT INTO SALES_ALL (sale_id, product_name, quantity, unit_price, sale_date) VALUES (4, '27-inch Monitor', 2, 299.99, TO_DATE('2026-01-22', 'YYYY-MM-DD'));
INSERT INTO SALES_ALL (sale_id, product_name, quantity, unit_price, sale_date) VALUES (5, 'Ergonomic Chair', 1, 350.00, TO_DATE('2026-01-23', 'YYYY-MM-DD'));
INSERT INTO SALES_ALL (sale_id, product_name, quantity, unit_price, sale_date) VALUES (6, 'HD Webcam', 5, 65.25, TO_DATE('2026-01-24', 'YYYY-MM-DD'));
INSERT INTO SALES_ALL (sale_id, product_name, quantity, unit_price, sale_date) VALUES (7, 'Laptop Stand', 2, 39.95, TO_DATE('2026-01-25', 'YYYY-MM-DD'));
INSERT INTO SALES_ALL (sale_id, product_name, quantity, unit_price, sale_date) VALUES (8, 'Noise Cancelling Headphones', 1, 199.00, TO_DATE('2026-01-26', 'YYYY-MM-DD'));
INSERT INTO SALES_ALL (sale_id, product_name, quantity, unit_price, sale_date) VALUES (9, 'External SSD 1TB', 4, 120.00, TO_DATE('2026-01-27', 'YYYY-MM-DD'));
INSERT INTO SALES_ALL (sale_id, product_name, quantity, unit_price, sale_date) VALUES (10, 'Bluetooth Speaker', 3, 55.00, TO_DATE('2026-01-28', 'YYYY-MM-DD'));


COMMIT;

Step 2: Register the Cloud Link

To share sales data with the analytics team, the lead DBA registers a Cloud Link on the SALES_ALL table. This Cloud Link named SALES within the TRUSTED_COMPARTMENT namespace is a read-only window into the Producer data. Since both Producer and Consumer Autonomous AI Databases are in the same OCI compartment, the DBA sets the scope to MY$COMPARTMENT.

BEGIN
   DBMS_CLOUD_LINK.REGISTER(
    schema_name    => 'CLOUDLINK',
    schema_object  => 'SALES_ALL',
    namespace      => 'TRUSTED_COMPARTMENT', 
    name           => 'SALES',
    description    => 'Trusted Compartment, only accessible within my compartment. Early sales data.',
    scope          => 'MY$COMPARTMENT',
    auth_required  =>  FALSE,
    data_set_owner =>  'tomholl@example.com' );
END;
/

Step 3: Create Materialized View Log

To enable incremental refresh updates, the DBA creates a materialized view log on SALES_ALL. This log captures every insert, update, and delete, recording the primary key, ROWID, and sequence information needed for fast refresh.

CREATE MATERIALIZED VIEW LOG ON SALES_ALL WITH PRIMARY KEY, ROWID, SEQUENCE INCLUDING NEW VALUES;

With the materialized view log instead of scanning the entire Producer table, the Consumer can request only the changes since the last refresh.

Step 4: Update Cloud Link for Fast Refresh

With the materialized view log, the DBA updates the Cloud Link registration to enable fast refresh for any materialized views built on this link.

BEGIN
   DBMS_CLOUD_LINK.UPDATE_REGISTRATION(
    namespace       => 'TRUSTED_COMPARTMENT', 
    name            => 'SALES',
    mv_fast_refresh => TRUE );
END;
/

Enabling fast refresh at the Cloud Link level allows all consumers to benefit from incremental refresh updates.

Step 5: Create Materialized View

In the Consumer Autonomous AI Database instance, the analytics team creates a materialized view called TRUSTED_SALES_MV that selects all columns from the Cloud Link. The materialized view is configured with REFRESH FAST ON DEMAND, which uses the Producer’s materialized view log to apply only incremental changes during each refresh.

CREATE MATERIALIZED VIEW TRUSTED_SALES_MV REFRESH FAST ON DEMAND AS SELECT * from TRUSTED_COMPARTMENT.SALES@CLOUD$LINK;

The analysts can run complex aggregations, joins, and filters on TRUSTED_SALES_MV without impacting the Producer. Since it is configured for fast refresh, the Consumer can stay in sync with the Producer.

Step 6: Query the Materialized View

The analysts query TRUSTED_SALES_MV to verify if they can see the initial ten sales transactions. The query returns product names, quantities, and unit prices for items like Wireless Mouse, Ergonomic Chair, and External SSD 1TB matching the data loaded into the Producer's SALES_ALL table.

SELECT PRODUCT_NAME, QUANTITY, UNIT_PRICE FROM TRUSTED_SALES_MV;

PRODUCT_NAME				QUANTITY	UNIT_PRICE
Wireless Mouse				2			25.50
Mechanical Keyboard			1			89.99
USB-C Hub					3			45.00
27-inch Monitor				2			299.99
Ergonomic Chair				1			350.00
HD Webcam					5			65.25
Laptop Stand				2			39.95
Noise Cancelling Headphones	1			199.00
External SSD 1TB			4			120.00
Bluetooth Speaker			3			55.00

The output displays the sales data visible in Consumer materialized view. The consumers can now access sales data without touching the Producer database.

Step 7: Insert New Sales Data

Later in the day, the organization launches a sale on Desk Lamps to clear excess inventory. New transactions are processed and are inserted into SALES_ALL in the Producer. The materialized view log automatically captures this new row, tracking the change for the next Consumer refresh.

INSERT INTO SALES_ALL (sale_id, product_name, quantity, unit_price, sale_date) VALUES (11, 'Desk Lamp', 10, 24.99, TO_DATE('2026-01-29', 'YYYY-MM-DD'));

COMMIT;

The Producer continues handling live transactions independently of the Consumer. The materialized view log records the change, allowing the Consumer to obtain the information during the next refresh.

Step 8: Fast Refresh the Materialized View

The Consumer Autonomous AI Database triggers a fast refresh of TRUSTED_SALES_MV using the DBMS_MVIEW.REFRESH procedure with the 'F' (fast) option. During the refresh process, TRUSTED_SALES_MV is updated locally with only the new Desk Lamp row from the Producer's materialized view log.

BEGIN
  DBMS_MVIEW.REFRESH('TRUSTED_SALES_MV', 'F');
END;
/

Instead of rereading all ten (or thousands of) rows, the Consumer Autonomous AI Database refresh retrieves only the new row that was added since the last refresh.

Step 9: Query Updated Materialized View

The analysts run the same query again and now see eleven rows, including the newly added Desk Lamp with a quantity of 10 and unit price of 24.99. The sale data is immediately available for reporting.

SELECT PRODUCT_NAME, QUANTITY, UNIT_PRICE FROM TRUSTED_SALES_MV;

PRODUCT_NAME				QUANTITY	UNIT_PRICE
Wireless Mouse				2			25.50
Mechanical Keyboard			1			89.99
USB-C Hub					3			45.00
27-inch Monitor				2			299.99
Ergonomic Chair				1			350.00
HD Webcam					5			65.25
Laptop Stand				2			39.95
Noise Cancelling Headphones	1			199.00
External SSD 1TB			4			120.00
Bluetooth Speaker			3			55.00
Desk Lamp					10			24.99

The output displays updated sales data after fast refresh, including new Desk Lamp sale.

The organization successfully implemented a data share using Oracle Autonomous AI Database Cloud Link and fast-refresh materialized views.