ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SYS模式下对象的特性

SYS模式下对象的特性

原创 Linux操作系统 作者:redhouser 时间:2012-06-04 14:37:43 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-05-26

  • 博文量
    211
  • 访问量
    787118