Oracle Materialized View Operations sample

1. Permissions required to create materialized views:

[SQL] View plaincopy

GRANT CREATE materialized VIEW to user_name;

2. Establish materialized view log in source table

[SQL] View plaincopy

CREATE materialized VIEW LOG on DAVE

Tablespace&bisoncu_space--Log space

With PRIMARY KEY; --Specifies the primary key type

3. Authorize to intermediary users

[SQL] View plaincopy

GRANT SELECT on DAVE to Anqing;

GRANT SELECT on Mlog$_dave to Anqing;

4. Create materialized VIEW on the target database

[SQL] View plaincopy

CREATE materialized VIEW Aics_dave

Tablespace&bisoncs_space

REFRESH FAST

On DEMAND

--The first time to refresh

--start with To_date (' 2012-01-01 20:00:00 ', ' yyyy-mm-dd hh24:mi:ss ')

START with Sysdate

--Refresh time interval. Refresh every 1 days with a time of 2 o'clock in the morning

--next TRUNC (sysdate, ' DD ') +1+2/24

NEXT SYSDATE+1/24/20

With PRIMARY KEY

--using DEFAULT Local Rollbacksegment

DISABLE QUERY REWRITE as

SELECT model_id, Status,model_name, manu_id, DESCRIPTION, Create_time, Update_time, sw_version

From Aics_dave@link_dave;

5. Create an index on the target materialized view

[SQL] View plaincopy

CREATE INDEX idx_t_dv_ct

On Aics_dev_info (Create_time, Update_time)

Tablespace &BISON_IDX;

CREATE INDEX Idx_t_dv_ut

On Aics_dev_info (Update_time)

Tablespace &BISON_IDX;

CREATE INDEX I_T_DV_MSISDN

On Aics_dev_info (MSISDN)

Tablespace &BISON_IDX;

6. Materialized View Refresh Description

(1) Manual refresh using Dbms_mview.refresh

Such as:

[SQL] View plaincopy

EXEC Dbms_mview. REFRESH (' Mv_dave ');

--Full Refresh

EXEC Dbms_mview. REFRESH (LIST => ' Mv_dave ', Method => ' C ');

EXEC Dbms_mview. REFRESH (' Mv_dave ', ' C ');

--Quick Refresh

EXEC Dbms_mview. REFRESH (LIST => ' Mv_dave ', Method => ' F ');

EXEC Dbms_mview. REFRESH (' Mv_dave ', ' F ');

(2) Use Dbms_refresh.refresh process to batch refresh MV

If we specify the refresh time for start and next times in the process of creating materialized views, Oracle automatically creates a refreshed job and takes a dbms_refresh.refresh approach.

You need to make the refresh group before you can refresh it by using this method.

Example:

Suppose there are materialized views mv_t1, Mv_t2, Mv_t3. The syntax for creating the refresh group is as follows:

[SQL] View plaincopy

Sql> EXEC Dbms_refresh. Make (' Rep_test ', ' mv_t1,mv_t2,mv_t3 ', Sysdate, ' sysdate+ 1 ')

--Refreshes the entire refresh group group:

Sql> EXEC Dbms_refresh. REFRESH (' Rep_test ')

7. Delete materialized views and logs

[SQL] View plaincopy

--Delete materialized view logs:

DROP materialized VIEW LOG on DAVE;

--Delete materialized views

DROP materialized VIEW Mv_dave;

8. View materialized view refresh status information

[SQL] View plaincopy

Sql> SELECT mview_name, last_refresh_date, staleness fromuser_mviews;

Sql> SELECT NAME, Last_refresh from User_mview_refresh_times;

9. Query materialized View log:

[SQL] View plaincopy

SELECT * from Mlog$_dave;

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/