Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to refresh the materialized view of storage in Job on a regular basis in oracle". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "how to store and refresh the materialized view regularly by Job in oracle".

(1) question:

In a recent project, we need to deal with old data in our system, but there are some objective limitations: (all Oracle11.2.0.2)

(1) these old data are stored in other people's databases.

(2) these old data will be updated continuously.

(3) do not touch other people's database

(4) We need to link to the two tables in other people's database.

Finally, we decided to use DBLink to create a database link to the old database, and then create a materialized view, which is updated every 5 minutes, giving the left link to the two tables, so we can only use the refresh method of complete.

But after a few days of work, I found through select * from user_jobs that my materialized view was no longer updated automatically, and failure failed 16 times. I don't know if it's because we shut down both the old database and the new database used by our system after work every day.

(2) solution:

Anyway, I decided to create a Job, execute a stored procedure periodically, refresh the materialized view in the stored procedure, and log the exception information to 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 timed execution stored procedure:

If you want to delete Job, use the remove command and pass in the Job number (checked through select * from user_jobs).

At this point, I believe you have a deeper understanding of "how to regularly perform storage refresh materialized view in Job in oracle". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.