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
DON’T MISS OUT!
Subscribe To Newsletter
Be the first to get latest updates and exclusive content straight to your email inbox.
Stay Updated
Give it a try, you can unsubscribe anytime.

Check Also

Oracle 10g to 11g Enhancements (Difference between Oracle 10g and 11g)

Difference between Oracle 10g and 11g 1- Enhanced Automatic Memory Management System Oracle 9i automated …

Leave a Reply

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

DON’T MISS OUT!
Subscribe To Newsletter
Be the first to get latest updates and exclusive content straight to your email inbox.
Stay Updated
Give it a try, you can unsubscribe anytime.
close-link
GET OUR LATEST CONTENT IN YOUR INBOX

SUBSCRIBE 
Your information will never be shared
close-link
Enquiry
Submit
close-link
Sign-up for exclusive content. Be the first to hear about DBA Centre Blogs.
Subscribe