There is an MVIEW (MV_FACT_SALES) in my personal database, direct update is not allowed unless in replication, following error will be reported:
SQL> DESC MV_FACT_SALES
Name Null? Type
----------------------- -------- -------------
F_MONTH VARCHAR2(7)
M_AMOUNT1 NUMBER
M_AMOUNT2 NUMBER
SQL> INSERT INTO MV_FACT_SALES VALUES ('3000-1',1500,1600);
INSERT INTO MV_FACT_SALES VALUES ('3000-1',1500,1600)
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
But we can do DML through exchange partition, like manually refresh the MVIEW's data, check the MVIEW status first:
SQL> SELECT STALENESS,STALE_SINCE FROM USER_MVIEWS;
STALENESS STALE_SINCE
------------------- ------------
FRESH
Let's create a partitioned table to perform partition exchange:
SQL> CREATE TABLE P_MV_FACT_SALES
2 (
3 F_MONTH VARCHAR2(7),
4 M_AMOUNT1 NUMBER,
5 M_AMOUNT2 NUMBER
6 )
7 PARTITION BY RANGE (F_MONTH)
8 (
9 PARTITION P_MAX VALUES LESS THAN (MAXVALUE)
10 )
11 /
Table created.
SQL> ALTER TABLE P_MV_FACT_SALES EXCHANGE PARTITION P_MAX WITH TABLE MV_FACT_SALES;
Table altered.
SQL> SELECT STALENESS,STALE_SINCE FROM USER_MVIEWS;
STALENESS STALE_SINCE
------------------- ------------
FRESH
Now perform some modification to the partitioned table, and then exchange back, check the status again:
SQL> INSERT INTO P_MV_FACT_SALES VALUES ('3000-1',1500,1600);
1 row created.
SQL> commit;
Commit complete.
SQL> ALTER TABLE P_MV_FACT_SALES EXCHANGE PARTITION P_MAX WITH TABLE MV_FACT_SALES;
Table altered.
SQL> SELECT STALENESS,STALE_SINCE FROM USER_MVIEWS;
STALENESS STALE_SINCE
------------------- ------------
FRESH
MVIEW does have it's own refresh method, but in huge data warehouse, this kind of partial refresh may be a good way for special usage.