0,背景
SYS schema下包含数据字典,不支持部分特性,或在SYS下有特殊规则。比如:
(1)Tables in the SYS and SYSTEM schema cannot be redefined online,《Database Administrator's Guide
10g Release 2》,15 Managing Tables,Restrictions for Online Redefinition of Tables。
(2)SYS不支持flashback drop;
(3)<>NLS_LENGTH_SEMANTICS 不适用于SYS、SYSTEM下的表,数据字典总使用字节语义。
今天在测试物化视图时,发现不带count(*)的快速刷新物化视图,在普通用户下和SYS下有不同的结果。
《Database Data Warehousing Guide 10g Release 2 (10.2)》,8 Basic Materialized Views,
Restrictions on Fast Refresh on Materialized Views with Aggregates:
COUNT(*) must be specified.
但在SYS用户下测试时,快速刷新没有发现异常;在普通用户下,基表有更新、删除时,会报如下错误:
ORA-32314: REFRESH FAST of "MH"."MV_T_RF2" unsupported after deletes/updates
1,以SYS用户测试:
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as mh@CTG_BNET_97_96 AS SYSDBA
SQL>
SQL> drop table t;
drop table t
ORA-00942: table or view does not exist
SQL> create table t
2 as
3 select * from dba_objects;
Table created
SQL> create materialized view log on t
2 with rowid,sequence(owner,object_type)
3 including new values;
Materialized view log created
SQL> drop materialized view mv_t_rf;
drop materialized view mv_t_rf
ORA-12003: materialized view "SYS"."MV_T_RF" does not exist
SQL> create materialized view mv_t_rf refresh fast
2 as
3 select owner,object_type,count(*) cnt
4 from t
5 group by owner,object_type;
Materialized view created
SQL> drop materialized view mv_t_rf2;
drop materialized view mv_t_rf2
ORA-12003: materialized view "SYS"."MV_T_RF2" does not exist
SQL> create materialized view mv_t_rf2 refresh fast
2 as
3 select owner,object_type
4 from t
5 group by owner,object_type;
Materialized view created
SQL> col owner for a10
SQL> col mview_name for a15
SQL> col fast_refreshable for a25
SQL> col last_refresh_type for a10
SQL> SELECT owner, mview_name, refresh_mode, fast_refreshable, last_refresh_type
2 FROM dba_mviews where mview_name like 'MV%';
OWNER MVIEW_NAME REFRESH_MODE FAST_REFRESHABLE LAST_REFRE
---------- --------------- ------------ ------------------------- ----------
SYS MV_T_RF DEMAND DIRLOAD_DML COMPLETE
SYS MV_T_RF2 DEMAND DIRLOAD_LIMITEDDML COMPLETE
SQL>
SQL> insert into t
2 select * from t where rownum=1;
1 row inserted
SQL> commit;
Commit complete
SQL> begin
2 dbms_mview.refresh('MV_T_RF');
3 end;
4 /
PL/SQL procedure successfully completed
SQL> begin
2 dbms_mview.refresh('MV_T_RF2');
3 end;
4 /
PL/SQL procedure successfully completed
SQL> SELECT owner, mview_name, refresh_mode, fast_refreshable, last_refresh_type
2 FROM dba_mviews where mview_name like 'MV%';
OWNER MVIEW_NAME REFRESH_MODE FAST_REFRESHABLE LAST_REFRE
---------- --------------- ------------ ------------------------- ----------
MH MV_T_COMPLETE DEMAND NO COMPLETE
MH MV_T_RF2 DEMAND DIRLOAD_LIMITEDDML COMPLETE
SYS MV_T_RF DEMAND DIRLOAD_DML FAST
MH MV_T_RF DEMAND DIRLOAD_DML FAST
SYS MV_T_RF2 DEMAND DIRLOAD_LIMITEDDML FAST
SQL>
SQL> delete from t where rownum<100;
99 rows deleted
SQL> commit;
Commit complete
SQL> begin
2 dbms_mview.refresh('MV_T_RF');
3 end;
4 /
PL/SQL procedure successfully completed
SQL> begin
2 dbms_mview.refresh('MV_T_RF2');
3 end;
4 /
PL/SQL procedure successfully completed
SQL>
SQL> update t set wner='sys' where rownum<100;
99 rows updated
SQL> commit;
Commit complete
SQL> begin
2 dbms_mview.refresh('MV_T_RF');
3 end;
4 /
PL/SQL procedure successfully completed
SQL> begin
2 dbms_mview.refresh('MV_T_RF2');
3 end;
4 /
PL/SQL procedure successfully completed
SQL> SELECT owner, mview_name, refresh_mode, fast_refreshable, last_refresh_type
2 FROM dba_mviews where mview_name like 'MV%';
OWNER MVIEW_NAME REFRESH_MODE FAST_REFRESHABLE LAST_REFRE
---------- --------------- ------------ ------------------------- ----------
MH MV_T_COMPLETE DEMAND NO COMPLETE
MH MV_T_RF2 DEMAND DIRLOAD_LIMITEDDML COMPLETE
SYS MV_T_RF DEMAND DIRLOAD_DML FAST
MH MV_T_RF DEMAND DIRLOAD_DML FAST
SYS MV_T_RF2 DEMAND DIRLOAD_LIMITEDDML FAST
2,在普通用户下测试:
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as mh@CTG_BNET_97_96
SQL>
SQL> drop table t;
Table dropped
SQL> create table t
2 as
3 select * from dba_objects;
Table created
SQL> create materialized view log on t
2 with rowid,sequence(owner,object_type)
3 including new values;
Materialized view log created
SQL> drop materialized view mv_t_rf;
Materialized view dropped
SQL> create materialized view mv_t_rf refresh fast
2 as
3 select owner,object_type,count(*) cnt
4 from t
5 group by owner,object_type;
Materialized view created
SQL> drop materialized view mv_t_rf2;
Materialized view dropped
SQL> create materialized view mv_t_rf2 refresh fast
2 as
3 select owner,object_type
4 from t
5 group by owner,object_type;
Materialized view created
SQL>
SQL> col owner for a10
SQL> col mview_name for a15
SQL> col fast_refreshable for a25
SQL> col last_refresh_type for a10
SQL> SELECT owner, mview_name, refresh_mode, fast_refreshable, last_refresh_type
2 FROM dba_mviews where mview_name like 'MV%';
OWNER MVIEW_NAME REFRESH_MODE FAST_REFRESHABLE LAST_REFRE
---------- --------------- ------------ ------------------------- ----------
MH MV_T_COMPLETE DEMAND NO COMPLETE
MH MV_T_RF2 DEMAND DIRLOAD_LIMITEDDML COMPLETE
MH MV_T_RF DEMAND DIRLOAD_DML COMPLETE
SQL>
SQL> insert into t
2 select * from t where rownum=1;
1 row inserted
SQL> commit;
Commit complete
SQL> begin
2 dbms_mview.refresh('MV_T_RF');
3 end;
4 /
PL/SQL procedure successfully completed
SQL> begin
2 dbms_mview.refresh('MV_T_RF2');
3 end;
4 /
PL/SQL procedure successfully completed
SQL> SELECT owner, mview_name, refresh_mode, fast_refreshable, last_refresh_type
2 FROM dba_mviews where mview_name like 'MV%';
OWNER MVIEW_NAME REFRESH_MODE FAST_REFRESHABLE LAST_REFRE
---------- --------------- ------------ ------------------------- ----------
MH MV_T_COMPLETE DEMAND NO COMPLETE
MH MV_T_RF2 DEMAND DIRLOAD_LIMITEDDML FAST
MH MV_T_RF DEMAND DIRLOAD_DML FAST
SQL>
SQL>
SQL>
SQL>
SQL> delete from t where rownum<100;
99 rows deleted
SQL> commit;
Commit complete
SQL> begin
2 dbms_mview.refresh('MV_T_RF');
3 end;
4 /
PL/SQL procedure successfully completed
SQL> begin
2 dbms_mview.refresh('MV_T_RF2');
3 end;
4 /
begin
dbms_mview.refresh('MV_T_RF2');
end;
ORA-32314: REFRESH FAST of "MH"."MV_T_RF2" unsupported after deletes/updates
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 3
SQL> SELECT owner, mview_name, refresh_mode, fast_refreshable, last_refresh_type
2 FROM dba_mviews where mview_name like 'MV%';
OWNER MVIEW_NAME REFRESH_MODE FAST_REFRESHABLE LAST_REFRE
---------- --------------- ------------ ------------------------- ----------
MH MV_T_COMPLETE DEMAND NO COMPLETE
MH MV_T_RF2 DEMAND DIRLOAD_LIMITEDDML FAST
MH MV_T_RF DEMAND DIRLOAD_DML FAST
SQL>
SQL> begin
2 dbms_mview.refresh('MV_T_RF2','C');
3 end;
4 /
PL/SQL procedure successfully completed
SQL>
SQL> SELECT owner, mview_name, refresh_mode, fast_refreshable, last_refresh_type
2 FROM dba_mviews where mview_name like 'MV%';
OWNER MVIEW_NAME REFRESH_MODE FAST_REFRESHABLE LAST_REFRE
---------- --------------- ------------ ------------------------- ----------
MH MV_T_COMPLETE DEMAND NO COMPLETE
MH MV_T_RF2 DEMAND DIRLOAD_LIMITEDDML COMPLETE
MH MV_T_RF DEMAND DIRLOAD_DML FAST
SQL>
SQL> update t set wner='sys' where rownum<100;
99 rows updated
SQL> commit;
Commit complete
SQL> begin
2 dbms_mview.refresh('MV_T_RF');
3 end;
4 /
PL/SQL procedure successfully completed
SQL> begin
2 dbms_mview.refresh('MV_T_RF2');
3 end;
4 /
begin
dbms_mview.refresh('MV_T_RF2');
end;
ORA-32314: REFRESH FAST of "MH"."MV_T_RF2" unsupported after deletes/updates
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 3
SQL>
SQL> SELECT owner, mview_name, refresh_mode, fast_refreshable, last_refresh_type
2 FROM dba_mviews where mview_name like 'MV%';
OWNER MVIEW_NAME REFRESH_MODE FAST_REFRESHABLE LAST_REFRE
---------- --------------- ------------ ------------------------- ----------
MH MV_T_COMPLETE DEMAND NO COMPLETE
MH MV_T_RF2 DEMAND DIRLOAD_LIMITEDDML COMPLETE
MH MV_T_RF DEMAND DIRLOAD_DML FAST
SQL>
SQL> begin
2 dbms_mview.refresh('MV_T_RF2','C');
3 end;
4 /
PL/SQL procedure successfully completed
SQL> SELECT owner, mview_name, refresh_mode, fast_refreshable, last_refresh_type
2 FROM dba_mviews where mview_name like 'MV%';
OWNER MVIEW_NAME REFRESH_MODE FAST_REFRESHABLE LAST_REFRE
---------- --------------- ------------ ------------------------- ----------
MH MV_T_COMPLETE DEMAND NO COMPLETE
MH MV_T_RF2 DEMAND DIRLOAD_LIMITEDDML COMPLETE
MH MV_T_RF DEMAND DIRLOAD_DML FAST
SQL>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/18922393/viewspace-731855/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/18922393/viewspace-731855/