SYS模式下物件的特性
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）<
今天在測試物化檢視時，發現不帶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 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
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- expdp時不能匯出sys使用者下的物件！物件
- Oracle sott模式下的物件建立Oracle模式物件
- 【BBED】 sys.bootstrap$ 物件的恢復boot物件
- 設計模式（二）：物件導向及其特性分析設計模式物件
- 通過07_DICTIONARY_ACCESSIBILITY控制對SYS下物件的訪問限制物件
- 【Mysql】mysql5.7新特性之-sys schema的作用MySql
- DB2刪除模式下物件DB2模式物件
- sys使用者不支援flashback table特性！
- **Mysql5.7新特性之—– 淺談Sys庫**MySql
- Typescript玩轉設計模式 之 物件行為型模式（下）TypeScript設計模式物件
- 將SYS和SYSTEM的物件都KEEP到shared pool物件
- golang 物件導向特性Golang物件
- JavaScript Math物件新增特性JavaScript物件
- SYS使用者物件不支援延遲段物件
- 物件儲存服務的Lambda特性物件
- Windows Driver Framework物件模型以及物件的一般特性WindowsFramework物件模型
- 物件建立模式物件模式
- 空物件模式物件模式
- 建立物件的幾種模式物件模式
- 第十八章 物件導向的特性物件
- 物件儲存服務的事件通知特性物件事件
- 物件導向有哪些特性物件
- JavaScript 建立物件的四種常見模式（附《高程3》下載地址）JavaScript物件模式
- Linux下建立GPIO(/sys/class/gpio）Linux
- linux下sys log 不產生了Linux
- 物件建立模式和類建立模式的區別物件模式
- Java 11 的新特性(下)Java
- 遞迴物件的設計模式遞迴物件設計模式
- 關於物件的建造模式物件模式
- 物件的克隆——原型模式（四）物件原型模式
- 物件的克隆——原型模式（三）物件原型模式
- 物件的克隆——原型模式（二）物件原型模式
- 物件的克隆——原型模式（一）物件原型模式
- 【Oracle】sys下缺失和無效物件導致exp、expdp和RMAN等備份功能全部報錯Oracle物件
- ES6新特性：JavaScript中的Reflect物件JavaScript物件
- Javascript的函式式和麵向物件特性JavaScript函式物件
- 物件儲存服務的影像處理特性物件
- Java物件建立模式Java物件模式