Oracle jobs regularly execute stored procedures to refresh materialized views and record exceptions

In Oracle, jobs regularly execute stored procedures to refresh the materialized view and record exceptions (My materialized view cannot be refreshed by myself)

In Oracle, jobs regularly execute stored procedures to refresh the materialized view and record exceptions (My materialized view cannot be refreshed by myself)

In Oracle, jobs regularly execute stored procedures to refresh the materialized view and record exceptions (My materialized view cannot be refreshed by myself)

(1) Problem:

In the latest project, we need to process old data in our system, but there are some objective restrictions: (both Oracle11.2.0.2)

(1) The old data is stored in others' databases.

(2) The old data will be continuously updated.

(3) databases that cannot touch people

(4) We need to make a left link to the two tables in the database.

In the end, we decided to use DBLink to create a database link to change the old database, and then create a materialized view. The materialized view is updated every minute, and the left link of the two tables is given, therefore, you can only use the complete refresh method.

After working for a few days, I found through select * from user_jobs that my materialized view is no longer automatically updated, and the number of failures of failure is 16, I don't know if it is because we close the old database and the new database used by our system every day after work.

Oracle performance problems caused by improper definition of materialized views

Oracle materialized view test

Connection-based materialized views of Oracle [refresh regularly]

Oracle materialized view creation reports ORA-00942 error resolution

Refresh the materialized view of Oracle stored procedures

Use of Oracle Materialized View

(2) solution:

No matter what, I decided to create a Job and regularly execute a stored procedure. In this stored procedure, I refreshed this materialized view and recorded the exception information in the database table:

1. Create a database table for storing exception information:

2. Create a stored procedure to refresh the Materialized View:

3. Create a Job and regularly execute the stored procedure:

If you want to delete a Job, run the remove command to pass in the Job number (view it by select * from user_jobs.

Next, let's wait a few days to see if there are any exceptions in this materialized view.

,