U0100029: Materialized views cannot be refreshed in the target database

Description

Materialized views cannot be automatically refreshed in the target GaussDB database.

Database Type and Version

• Source database type and version: Oracle versions supported by UGO
• Target database type and version: GaussDB versions supported by UGO

Syntax Example

Oracle script:

CREATE MATERIALIZED VIEW mv_hourly_sales
REFRESH FAST
START WITH SYSDATE NEXT SYSDATE + 1/24
AS
SELECT product_id, SUM(quantity) total_quantity, SUM(amount) total_amount
FROM sales
GROUP BY product_id;

Suggestion

1. Astore must be used to store materialized views.

2. Comment out MATERIALIZED VIEW LOG.

3. In the UGO conversion configuration item, comment out the REFRESH clause of materialized views.

4. Create a procedure to periodically refresh materialized views.

5. Create a scheduled task in either of the following ways:

Method 1 (recommended): DBE_SCHEDULER.CREATE_JOB

Method 2: dbe_task.id_submit

Script compatible with GaussDB:

---Comment out automated refresh of materialized views.
CREATE MATERIALIZED VIEW mv_hourly_sales
/*REFRESH FAST
START WITH SYSDATE NEXT SYSDATE + 1/24*/ 
AS
SELECT product_id, SUM(quantity) total_quantity, SUM(amount) total_amount
FROM sales
GROUP BY product_id;
---Create a procedure to periodically refresh materialized views.
CREATE OR REPLACE PROCEDURE refresh_mv_hourly_sales
AS
BEGIN
    REFRESH MATERIALIZED VIEW mv_hourly_sales;
    RAISE NOTICE 'Materialized view mv_hourly_sales refreshed at %', now();
END;
/
-- Method 1: Use DBE_SCHEDULER.CREATE_JOB to create a scheduled task.
BEGIN 
PERFORM DBE_SCHEDULER.CREATE_JOB('MY_JOB',
job_type=>'PLSQL_BLOCK', job_action=>
'BEGIN refresh_mv_hourly_sales; END;'
, number_of_arguments=>0,
start_date=>sysdate, 
repeat_interval=> 
 'FREQ=HOURLY; INTERVAL=1', 
end_date=>NULL,
job_class=>'DEFAULT_JOB_CLASS', 
enabled=>TRUE, 
auto_drop=>TRUE,comments=>
NULL
);
END; 
/
-- Method 2: Use dbe_task.id_submit to create a scheduled task.
call dbe_task.id_submit(100,'call refresh_mv_hourly_sales(); ', sysdate, 'interval ''1 hour''');