Friday , July 21 2017
Home / Oracle DBA / Oracle Materialized Views

Oracle Materialized Views

Oracle Materialized Views

Oracle Materialized Views

  1. It is an object used to pull remote database’s data frequently in specified time which is called as refreshing the data using materialized views
  2. Snapshot is the object which used to do the same till 8i, but the disadvantage is time constraint in pulling huge no.of rows
  3. MV uses MV log to store information of already transferred rows. MVLOG will store rowid’s of table rows which helps in further refresh
  4. MV should be created in the database where we store the data abd MVLOG will be created automatically in remote database
  5. MVLOG is a table not a file and its name always will start with MV$LOG
  6. MV refresh can happen in following three modes
    1. Complete – pulling entire data
    2. Fast – pulling non transferred rows
    3. Force – it will do fast refresh and in case any failure, it will go for complete refresh
  7. When MV refresh happening very slow, check the size of table and compare that with MVLOG size
  8. If MVLOG size is more than table size, then drop and recreate only MVLOG
Note: A complete refresh is required after the recreation of MVLOG
Note: we can use “refresh fast on commit” in order to transfer the data to remote database without waiting
# To check the table size
SQL> select sum(bytes/1024/1024) from dba_segments where segment_name=’EMP’;
Read More:
Creating new Listener manually
TYPES OF ERRORS in Oracle database
NETWORKING WITH ORACLE

Comments

comments

Check Also

DATA BASE LINKS

DATABASE LINKS: What are the db links? create a link from one database to another …

Leave a Reply

Your email address will not be published. Required fields are marked *