CODEWITHSUNDEEP
oracle-database
Java Programer
Java Programer

Reputation: 25

AUTO-Scynhronize a table based on view - ORACLE DATABASES

I want to ask you if there is a solution to auto-synchronize a table ,e.g., every one minute based on view created in oracle. This view is using data from another table. I've created a trigger but I noticed a big slowness in database whenever a user update a column or insert a row. Furthermore, I've tested to create a job schedule on the specified table (Which I wanted to be synchronized with the view), however we don't have the privilege to do this. Is there any other way to keep data updated between the table and the view ? PS : I'm using toad for oracle V 12.9.0.71

Upvotes: 0

Views: 64

Answers (1)

scott yu
scott yu

Reputation: 125

A materialized view in Oracle is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data. Materialized views, which store data based on remote tables, are also known as snapshots.

Example:

SQL> CREATE MATERIALIZED VIEW mv_emp_pk
    REFRESH FAST START WITH SYSDATE 
    NEXT  SYSDATE + 1/48
    WITH PRIMARY KEY 
    AS SELECT * FROM emp@remote_db;

You can use cronjob or dbms_jobs to schedule a snapshot.

Upvotes: 1

Related Questions