truncate分区以后，物化视图快速刷新出错
drop分区以后，物化视图快速刷新出错
测试Oralce9i的情况:
SQL> create table T_PART
2 (
3 C1 NUMBER,
4 C2 NUMBER,
5 C3 NUMBER
6 )
7 partition by range (C2)
8 (
9 partition T_P2 values less than (20)
10 ,
11 partition T_P3 values less than (30)
12 )
13 ;
Table created.
SQL> insert into t_part values(1,2,3);
1 row created.
SQL> insert into t_part values(1,25,3);
1 row created.
SQL> insert into t_part values(1,18,3);
1 row created.
SQL> commit;
Commit complete.
SQL> create materialized view log on t_part with rowid;
Materialized view log created.
SQL> create materialized view mv_t_part refresh with rowid as select * from t_part;
Materialized view created.
SQL> select * from t_part;
C1 C2 C3
---------- ---------- ----------
1 2 3
1 18 3
1 25 3
SQL> select * from mv_t_part;
C1 C2 C3
---------- ---------- ----------
1 2 3
1 18 3
1 25 3
SQL> alter table t_part truncate partition t_p2;
Table truncated.
SQL> exec dbms_mview.refresh("mv_t_part","f");
BEGIN dbms_mview.refresh("mv_t_part","f"); END;
*
ERROR at line 1:
ORA-32313: REFRESH FAST of "SYS"."MV_T_PART" unsupported after PMOPs
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at line 1
这里出现错误。
ORA-32313 REFRESH FAST of "string"."string" unsupported after PMOPs
Cause: A Partition Maintenance Operation (PMOP) has been performed on a detail table, and the specified materialized view does not support fast refersh after PMOPs.
Action: Use REFRESH COMPLETE. You can determine why your materialized view does not support fast refresh after PMOPs using the DBMS_MVIEW.EXPLAIN_MVIEW() API.
再来测试Oracle10g的:
[Oracle@danaly ~]$ sqlplus eygle/eygle
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Dec 13 22:10:15 2005
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
SQL> create table T_PART
2 (
3 C1 NUMBER,
4 C2 NUMBER,
5 C3 NUMBER
6 )
7 partition by range (C2)
8 (
9 partition T_P2 values less than (20)
10 ,
11 partition T_P3 values less than (30)
12 )
13 ;
Table created.
SQL> insert into t_part values(1,2,3);
1 row created.
SQL> insert into t_part values(1,25,3);
1 row created.
SQL> insert into t_part values(1,18,3);
1 row created.
SQL> create materialized view log on t_part with rowid;
Materialized view log created.
SQL> create materialized view mv_t_part refresh with rowid as select * from t_part;
Materialized view created.
SQL> select * from t_part;
C1 C2 C3
---------- ---------- ----------
1 2 3
1 18 3
1 25 3
SQL> select * from mv_t_part;
C1 C2 C3
---------- ---------- ----------
1 2 3
1 18 3
1 25 3
SQL> alter table t_part truncate partition t_p2;
Table truncated.
SQL> exec dbms_mview.refresh("mv_t_part","f");
PL/SQL procedure successfully completed.
看来在物化视图方面，Oracle10g的确已经增强