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

This article mainly explains the "Oracle materialized view management and important views how to understand", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "Oracle materialized view management and important views how to understand" it!

MView important View

Link Materialized View and Refresh Group Views for MView-related views in an Oracle document.

Related views on the source database side

DBA_BASE_TABLE_MVIEWS

This view corresponds to the system view SYS.SLOG$.

The view DBA_BASE_TABLE_MVIEWS records information about refreshes that use MView Log to access the base table. In other words, it records the information of MView that has used MView Log and has done a fast refresh, and must meet both the conditions of having MView Log and having done a fast refresh, both of which are indispensable.

Column name describes the name of the OWNERMASTER base table of the OWNER base table MVIEW_LAST_REFRESH_TIME based on the last quick refresh time of the MView of the base table MVIEW_ID based on the ID of the MView of the base table in the database, which can be associated with DBA_REGISTERED_MVIEWS to find the name of the corresponding MView

Note: the role of SYS.SLOG$ SYS.SLOG$ records the information of the MView registered in the source database that can be quickly refreshed. This information is available during a quick refresh and is also used to maintain the MView Log information of the base table.

DBA_REGISTERED_MVIEWS

This view corresponds to the system view SYS.REG_SNAP$.

The view DBA_REGISTERED_MVIEWS records all the information about the MView registered on the main site, and what is stored here is only informative. When MView is created, Oracle automatically writes MView information to this view, but it does not rule out failures, which need to be manually registered using the REGISTER_MVIEW in the DBMS_MVIEW package.

Detailed column description will not be listed, see the document for details.

Note: the role of SYS.REG_SNAP$ SYS.REG_SNAP$ records the information of the MView registered on the source database side, and this table is only used to provide information.

DBA_MVIEW_LOGS

This view corresponds to the system view SYS.mlog$.

This view records information about all the MView Log in the source database.

Detailed column description will not be listed, see the document for details.

Related views on the MView database side

DBA_MVIEWS

This view corresponds to the system view SYS.SNAP$.

We have been exposed to this view before, and all the MView information in the database is stored in the view. Most of the materialized view queries we do are based on this view.

See the documentation for detailed column descriptions.

DBA_MVIEW_REFRESH_TIMES

This view corresponds to the system view SYS.SNAP_REFTIME$.

This view records when the MView was last refreshed, which is useful when we check that the MView is properly refreshed out of date.

See the documentation for detailed column descriptions.

DBA_REFRESH and DBA_REFRESH_CHILDREN

We have been in contact with these two views before, mainly recording the information related to the refresh group.

MView related package

DBMS_MVIEW and DBMS_REFRESH are the two packages most commonly used in MView maintenance, the former for MView maintenance and the other for MView refresh group maintenance.

Related Oracle documents

DBMS_MVIES

DBMS_REFRESH

Some problems related to MView maintenance

SNAPSHOT vs. Materialized View

SNAPSHOT = = Materialized View (the MView in the old version of Oracle is called SNAPSHOT)

DBMS_SNAPSHOT = = DBMS_MVIEW

CREATE SNAPSHOT = = CREATE MATERIALIZED VIEW

Clean up invalid MView Log

1. Query MV table information

SELECT * FROM DBA_BASE_TABLE_MVIEWS

ALL_BASE_TABLE_MVIEWS: All materialized views with log (s) in the database that the user can see

two。 Query registered snapshot information

Col owner for a15

Col name for a30

Col snapshot_site for a15

Col refresh_method for a15

Select owner, name, snapshot_site, refresh_method fromdba_registered_snapshots

3. Compare the above two results to find out the MV ID that has not been refreshed for a long time, and then UNREGISTER_MVIEW the operation.

EXEC DBMS_MVIEW.UNREGISTER_MVIEW (mviewowner, mviewname, mviewsite)

Mviewowner: the OWNER of MView.

Mviewname: the name of MView.

Mviewsite: this can be seen in MVIEW_SITE in DBA_REGISTERED_MVIEWS.

4. Clean up MVLOG records

SQL > EXEC DBMS_MVIEW.PURGE_MVIEW_FROM_LOG (MVID)

Query the size of the MView Log

Set serveroutput on

Declare v_output number

Begin

Dbms_output.put_line ('Owner MV Table Name MV Log Size')

Dbms_output.put_line ('-)

For c_cursor in (select owner, object_name from all_objects whereobject_name like 'MLOG$_%') LOOP

Execute immediate 'select count (*) from' | | c_cursor.owner | |'. | | c_cursor.object_name into v_output

Dbms_output.put_line (

Rpad (substr (cymcursor.ownerpr. 0pje 20), 20jre') | |''

| | rpad (substr (canticursor.objectcursor.objectcursor.objectcalliname7, 40), 40,') |

|'| | v_output)

End loop

End

/

Set serveroutput off

Check the refresh compatibility of MV

-- build table MV_CAPABILITIES_TABLE

SQL > @? / rdbms/admin/utlxmv.sql

-- explain mv

TRUNCATE TABLE MV_CAPABILITIES_TABLE

EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW ('mv_t3')

-- Or like this

TRUNCATE TABLE MV_CAPABILITIES_TABLE

BEGIN

DBMS_MVIEW.EXPLAIN_MVIEW ('SELECT * FROM oe.orders@orc1.world o

WHERE EXISTS (SELECT * FROM oe.customers@orc1.world c

WHERE o.customer_id = c.customer_id AND c.credit_limit > 500)')

END

/

-- View the results

COL MVOWNER FOR A10

COL MVNAME FOR A20

COL MSGTXT FOR A65

SELECT MVOWNER,MVNAME,CAPABILITY_NAME,POSSIBLE,MSGTXT FROMMV_CAPABILITIES_TABLE

Query MView refresh delay

Set linesize 130

Column interval format 999999

Column "rgroup owner" format A20

Column "refresh group" format A20

Column "minutes behind" format 9999.99

Column "master link" format A20

Column "mview owner" format A15

Column next_date format a20

SELECT

-int.rowner "rgroup owner"

-int.rname "refresh group"

Mv.owner as "mview owner"

Mv.mview_name as "mview name"

Mv.master_link as "master link"

Round (1440 * (sysdate-mv.last_refresh_date)) as "minutes behind"

To_char (int.next_date, 'yyyy-mm-dd hh34:mi:ss') "next date"

Round (int.interval*1440) "interval"

FROM dba_mviews mv

(

SELECT child.owner, child.name, child.rowner, child.rname,job.next_date, job.next_date-job.last_date as interval

FROM dba_refresh ref, dba_refresh_children child, dba_jobs job

WHERE ref.rname = child.rname AND ((upper (job.what) LIKE'%' | | ref.rname | |'%')

OR (upper (job.what) LIKE'%'| | ref.rname | |'%'))

) int

WHERE mv.owner = int.owner (+) AND mv.mview_name = int.name (+)

ORDER BY (sysdate-mv.last_refresh_date) * 1440 DESC, mv.owner,mv.mview_name

Thank you for your reading, the above is the content of "Oracle materialized view management and how to understand important views". After the study of this article, I believe you have a deeper understanding of Oracle materialized view management and how to understand this problem, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.