oracle10g materialized view物化视图示例

最新推荐文章于 2021-08-10 16:02:16 发布
最新推荐文章于 2021-08-10 16:02:16 发布
阅读量147 收藏
点赞数
文章标签： 数据库
SQL> create table t_test(a int,b int);

Table created.

--构建物化视图基表必须有pk主键
SQL> create materialized view mv_t_test refresh fast as select a,b from t_test;
create materialized view mv_t_test refresh fast as select a,b from t_test
                                                                   *
ERROR at line 1:
ORA-12014: table 'T_TEST' does not contain a primary key constraint

--添加主键
SQL> alter table t_test add primary key(a)
  2  ;

Table altered.

--增量刷新（refresh fast）必须构建物化视图日志
SQL> create materialized view mv_t_test refresh fast as select a,b from t_test;
create materialized view mv_t_test refresh fast as select a,b from t_test
                                                                   *
ERROR at line 1:
ORA-23413: table "SCOTT"."T_TEST" does not have a materialized view log


SQL> create materialized view mv_t_test refresh force as select a,b from t_test;

Materialized view created.

---查看物化视图相关信息
select owner,mview_name,updatable,update_log from user_mviews

OWNER                          MVIEW_NAME                     U UPDATE_LOG
------------------------------ ------------------------------ - ------------------------------
SCOTT                          MV_T_TEST                      N

SQL> 


SQL> select mview_name,rewrite_enabled,rewrite_capability from user_mviews;

MVIEW_NAME                     R REWRITE_C
------------------------------ - ---------
MV_T_TEST                      N GENERAL


SQL> select mview_name,refresh_mode,refresh_method,build_mode from user_mviews;

MVIEW_NAME                     REFRES REFRESH_ BUILD_MOD
------------------------------ ------ -------- ---------
MV_T_TEST                      DEMAND FORCE    IMMEDIATE


SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select mview_name,fast_refreshable,last_refresh_type,last_refresh_date from user_mviews;

MVIEW_NAME                     FAST_REFRESHABLE   LAST_REF LAST_REFRESH_DATE
------------------------------ ------------------ -------- -------------------
MV_T_TEST                      DML                COMPLETE 2013-07-18 10:55:11


SQL> exec dbms_mview.refresh('mv_t_test');

PL/SQL procedure successfully completed.

SQL> select mview_name,fast_refreshable,last_refresh_type,last_refresh_date from user_mviews;

MVIEW_NAME                     FAST_REFRESHABLE   LAST_REF LAST_REFRESH_DATE
------------------------------ ------------------ -------- -------------------
MV_T_TEST                      DML                COMPLETE 2013-07-18 11:05:15


--增量刷新报错，第二个参数指定refresh mode,值为：f表示fast；c表示complete;?表示force;p表示基于分区刷新pct
SQL> exec dbms_mview.refresh('mv_t_test','f');
BEGIN dbms_mview.refresh('mv_t_test','f'); END;

*
ERROR at line 1:
ORA-23413: table "SCOTT"."T_TEST" does not have a materialized view log
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 1


SQL> exec dbms_mview.refresh('mv_t_test','c');

PL/SQL procedure successfully completed.

SQL> exec dbms_mview.refresh('mv_t_test','?');

PL/SQL procedure successfully completed.

SQL> exec dbms_mview.refresh('mv_t_test','x');
BEGIN dbms_mview.refresh('mv_t_test','x'); END;

*
ERROR at line 1:
ORA-01760: illegal argument for function
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 1


SQL> exec dbms_mview.refresh('mv_t_test','p');
BEGIN dbms_mview.refresh('mv_t_test','p'); END;

*
ERROR at line 1:
ORA-12047: PCT FAST REFRESH cannot be used for materialized view "SCOTT"."MV_T_TEST"
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 1


SQL> 
--构建物化视图日志
SQL> create materialized view log on t_test;

Materialized view log created.

SQL> exec dbms_mview.refresh('mv_t_test','f');
BEGIN dbms_mview.refresh('mv_t_test','f'); END;

*
ERROR at line 1:
ORA-12034: materialized view log on "SCOTT"."T_TEST" younger than last refresh
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 1

SQL> select a,snaptime$$,dmltype$$,old_new$$,change_vector$$ from mlog$_t_test;

no rows selected

--构建物化视图日志后先要全量刷新，才可以进行增量刷新
SQL> !oerr ora 12034
12034, 0000, "materialized view log on \"%s\".\"%s\" younger than last refresh"
// *Cause:  The materialized view log was younger than the last refresh.
// *Action: A complete refresh is required before the next fast refresh.
//


SQL> exec dbms_mview.refresh('mv_t_test','c');

PL/SQL procedure successfully completed.

SQL> select a,snaptime$$,dmltype$$,old_new$$,change_vector$$ from mlog$_t_test;

no rows selected

--全量刷新后增量刷新即可
SQL> exec dbms_mview.refresh('mv_t_test','f');

PL/SQL procedure successfully completed.


SQL> select mview_name,fast_refreshable,last_refresh_type,last_refresh_date from user_mviews;

MVIEW_NAME                     FAST_REFRESHABLE   LAST_REF LAST_REFRESH_DATE
------------------------------ ------------------ -------- -------------------
MV_T_TEST                      DML                FAST     2013-07-18 11:19:49

SQL> exec dbms_mview.refresh('mv_t_test');

PL/SQL procedure successfully completed.

--构建物化视图日志后自动进行增量刷新
SQL> select mview_name,fast_refreshable,last_refresh_type,last_refresh_date from user_mviews;

MVIEW_NAME                     FAST_REFRESHABLE   LAST_REF LAST_REFRESH_DATE
------------------------------ ------------------ -------- -------------------
MV_T_TEST                      DML                FAST     2013-07-18 11:22:26

--插入记录不提交
SQL> insert into t_test select level,level+3 from dual connect by level<=20;

20 rows created.

--未提交前在物化视图日志插入记录
select a,snaptime$$,dmltype$$,old_new$$,change_vector$$ from mlog$_t_test

         A SNAPTIME$$          D O CHANGE_VEC
---------- ------------------- - - ----------
         1 4000-01-01 00:00:00 I N FE
         2 4000-01-01 00:00:00 I N FE
         3 4000-01-01 00:00:00 I N FE
         4 4000-01-01 00:00:00 I N FE
         5 4000-01-01 00:00:00 I N FE
         6 4000-01-01 00:00:00 I N FE
         7 4000-01-01 00:00:00 I N FE
         8 4000-01-01 00:00:00 I N FE
         9 4000-01-01 00:00:00 I N FE
        10 4000-01-01 00:00:00 I N FE
        11 4000-01-01 00:00:00 I N FE

         A SNAPTIME$$          D O CHANGE_VEC
---------- ------------------- - - ----------
        12 4000-01-01 00:00:00 I N FE
        13 4000-01-01 00:00:00 I N FE
        14 4000-01-01 00:00:00 I N FE
        15 4000-01-01 00:00:00 I N FE
        16 4000-01-01 00:00:00 I N FE
        17 4000-01-01 00:00:00 I N FE
        18 4000-01-01 00:00:00 I N FE
        19 4000-01-01 00:00:00 I N FE
        20 4000-01-01 00:00:00 I N FE

20 rows selected.

SQL> commit;

Commit complete.

--提交后仍保存物化视图日志
SQL> select a,snaptime$$,dmltype$$,old_new$$,change_vector$$ from mlog$_t_test;

         A SNAPTIME$$          D O CHANGE_VEC
---------- ------------------- - - ----------
         1 4000-01-01 00:00:00 I N FE
         2 4000-01-01 00:00:00 I N FE
         3 4000-01-01 00:00:00 I N FE
         4 4000-01-01 00:00:00 I N FE
         5 4000-01-01 00:00:00 I N FE
         6 4000-01-01 00:00:00 I N FE
         7 4000-01-01 00:00:00 I N FE
         8 4000-01-01 00:00:00 I N FE
         9 4000-01-01 00:00:00 I N FE
        10 4000-01-01 00:00:00 I N FE
        11 4000-01-01 00:00:00 I N FE

         A SNAPTIME$$          D O CHANGE_VEC
---------- ------------------- - - ----------
        12 4000-01-01 00:00:00 I N FE
        13 4000-01-01 00:00:00 I N FE
        14 4000-01-01 00:00:00 I N FE
        15 4000-01-01 00:00:00 I N FE
        16 4000-01-01 00:00:00 I N FE
        17 4000-01-01 00:00:00 I N FE
        18 4000-01-01 00:00:00 I N FE
        19 4000-01-01 00:00:00 I N FE
        20 4000-01-01 00:00:00 I N FE

20 rows selected.

--刷新物化视图
SQL> exec dbms_mview.refresh('mv_t_test');

PL/SQL procedure successfully completed.

--刷新物化视图则有
SQL> select * from mv_t_test;

         A          B
---------- ----------
         1          4
         6          9
        11         14
        13         16
         2          5
        14         17
        20         23
         4          7
         5          8
         8         11
        17         20

         A          B
---------- ----------
         3          6
         7         10
        18         21
         9         12
        10         13
        12         15
        15         18
        16         19
        19         22

20 rows selected.

--刷新物化视图后则删除物化视图日志记录
SQL> select a,snaptime$$,dmltype$$,old_new$$,change_vector$$ from mlog$_t_test;

no rows selected


--更新不提交
SQL> update t_test set b=44 where a=1;

1 row updated.


--通过物化视图日志列可区别源表的不同操作类型，
SQL> select a,snaptime$$,dmltype$$,old_new$$,change_vector$$ from mlog$_t_test;

         A SNAPTIME$$          D O CHANGE_VEC
---------- ------------------- - - ----------
         1 4000-01-01 00:00:00 U U 04
         

SQL> rollback;

Rollback complete.

--回滚也清除物化视图日志内容
SQL> select a,snaptime$$,dmltype$$,old_new$$,change_vector$$ from mlog$_t_test;

no rows selected


SQL> delete from t_test where a=1;

1 row deleted.

---删除不提交在物化视图日志也会记录对应的内容
SQL> select a,snaptime$$,dmltype$$,old_new$$,change_vector$$ from mlog$_t_test;

         A SNAPTIME$$          D O CHANGE_VEC
---------- ------------------- - - ----------
         1 4000-01-01 00:00:00 D O 00
         
         
---删除物化视图        
SQL> drop materialized view  mv_t_test;

Materialized view dropped.

--构建聚合物化视图
SQL> create materialized view mv_t_test enable query rewrite as select a,count(b) from t_test group by a;

Materialized view created.

--可知聚合物化视图首次是全量刷新，且增量刷新是有限制条件的
SQL> select mview_name,fast_refreshable,last_refresh_type,last_refresh_date from user_mviews;

MVIEW_NAME                     FAST_REFRESHABLE   LAST_REF LAST_REFRESH_DATE
------------------------------ ------------------ -------- -------------------
MV_T_TEST                      DIRLOAD_LIMITEDDML COMPLETE 2013-07-18 11:42:44

SQL> select * from t_test;

         A          B
---------- ----------
         2          5
         3          6
         4          7
         5          8
         6          9
         7         10
         8         11
         9         12
        10         13
        11         14
        12         15

         A          B
---------- ----------
        13         16
        14         17
        15         18
        16         19
        17         20
        18         21
        19         22
        20         23

19 rows selected.

--物化视图已有值
SQL> select * from mv_t_test;

         A   COUNT(B)
---------- ----------
         6          1
        11          1
        13          1
         2          1
        14          1
        20          1
         4          1
         5          1
         8          1
        17          1
         3          1

         A   COUNT(B)
---------- ----------
         7          1
        18          1
         9          1
        10          1
        12          1
        15          1
        16          1
        19          1

19 rows selected.

--插入一条记录
SQL> insert into t_test values(21,28);

1 row created.

--刷新物化视图
SQL> exec dbms_mview.refresh('mv_t_test');

PL/SQL procedure successfully completed.

SQL> select mview_name,fast_refreshable,last_refresh_type,last_refresh_date from user_mviews;

MVIEW_NAME                     FAST_REFRESHABLE   LAST_REF LAST_REFRESH_DATE
------------------------------ ------------------ -------- -------------------
MV_T_TEST                      DIRLOAD_LIMITEDDML COMPLETE 2013-07-18 11:48:04


--即使未提交刷新物化视图也会同步新数据
SQL> select * from mv_t_test;

         A   COUNT(B)
---------- ----------
         6          1
        11          1
        13          1
         2          1
        14          1
        20          1
        21          1
         4          1
         5          1
         8          1
        17          1

         A   COUNT(B)
---------- ----------
         3          1
         7          1
        18          1
         9          1
        10          1
        12          1
        15          1
        16          1
        19          1

20 rows selected.




--增量刷新报错
SQL> exec dbms_mview.refresh('mv_t_test','f');
BEGIN dbms_mview.refresh('mv_t_test','f'); END;

*
ERROR at line 1:
ORA-12032: cannot use rowid column from materialized view log on "SCOTT"."T_TEST"
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 1




SQL> !oerr ora 12032
12032, 0000, "cannot use rowid column from materialized view log on \"%s\".\"%s\""
// *Cause:  The materialized view log either does not have ROWID columns
//          logged, or the timestamp associated with the ROWID columns is
//          more recent than the last refresh time.
// *Action: A complete refresh is required before the next fast refresh.
//          Add ROWID columns to the materialized view log, if required.
//

SQL> exec dbms_mview.refresh('mv_t_test','c');

PL/SQL procedure successfully completed.

--完全刷新后增量刷新依旧报错
SQL> exec dbms_mview.refresh('mv_t_test','f');
BEGIN dbms_mview.refresh('mv_t_test','f'); END;

*
ERROR at line 1:
ORA-12032: cannot use rowid column from materialized view log on "SCOTT"."T_TEST"
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 1

--添加rowid到物化视图日志
SQL> alter materialized view log force on t_test add rowid;

Materialized view log altered.

---物化视图日志多了一个列m_row$$
SQL> desc mlog$_t_test;
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 A                                                                                                                          NUMBER
 SNAPTIME$$                                                                                                                 DATE
 DMLTYPE$$                                                                                                                  VARCHAR2(1)
 OLD_NEW$$                                                                                                                  VARCHAR2(1)
 CHANGE_VECTOR$$                                                                                                            RAW(255)
 M_ROW$$   
 
 
 SQL> exec dbms_mview.refresh('mv_t_test','f');
BEGIN dbms_mview.refresh('mv_t_test','f'); END;

*
ERROR at line 1:
ORA-12034: materialized view log on "SCOTT"."T_TEST" younger than last refresh
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 1


SQL> exec dbms_mview.refresh('mv_t_test','c');

PL/SQL procedure successfully completed.

SQL> exec dbms_mview.refresh('mv_t_test','f');
BEGIN dbms_mview.refresh('mv_t_test','f'); END;

*
ERROR at line 1:
ORA-32401: materialized view log on "SCOTT"."T_TEST" does not have new values
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 1


--添加新值到物化视图日志
SQL> alter materialized view log force on t_test including new values;

Materialized view log altered.


SQL> exec dbms_mview.refresh('mv_t_test','f');
BEGIN dbms_mview.refresh('mv_t_test','f'); END;

*
ERROR at line 1:
ORA-12034: materialized view log on "SCOTT"."T_TEST" younger than last refresh
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 1


SQL> exec dbms_mview.refresh('mv_t_test','c');

PL/SQL procedure successfully completed.

--添加新值到物化视图日志后仍报错
SQL> exec dbms_mview.refresh('mv_t_test','f');
BEGIN dbms_mview.refresh('mv_t_test','f'); END;

*
ERROR at line 1:
ORA-12033: cannot use filter columns from materialized view log on "SCOTT"."T_TEST"
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 1


SQL> !oerr ora 12033
12033, 0000, "cannot use filter columns from materialized view log on \"%s\".\"%s\""
// *Cause:  The materialized view log either did not have filter columns
//          logged, or the timestamp associated with the filter columns was
//          more recent than the last refresh time.
// *Action: A complete refresh is required before the next fast refresh.
//          Add filter columns to the materialized view log, if required.
//


SQL> alter materialized view log force on t_test add rowid(a);

Materialized view log altered.


SQL> alter materialized view log force on t_test add rowid(a);

Materialized view log altered.

--添加过滤列仍报错
SQL> exec dbms_mview.refresh('mv_t_test','f');
BEGIN dbms_mview.refresh('mv_t_test','f'); END;

*
ERROR at line 1:
ORA-12033: cannot use filter columns from materialized view log on "SCOTT"."T_TEST"
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 1

--添加过滤列
SQL> alter materialized view log force on t_test add (b);

Materialized view log altered.

SQL> exec dbms_mview.refresh('mv_t_test','f');
BEGIN dbms_mview.refresh('mv_t_test','f'); END;

*
ERROR at line 1:
ORA-12033: cannot use filter columns from materialized view log on "SCOTT"."T_TEST"
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 1


--添加过滤列然后全量刷新之后增量即可成功
SQL> exec dbms_mview.refresh('mv_t_test','c');

PL/SQL procedure successfully completed.

SQL> exec dbms_mview.refresh('mv_t_test','f');

PL/SQL procedure successfully completed.

--可知上次是增量刷新
SQL> select mview_name,fast_refreshable,last_refresh_type,last_refresh_date from user_mviews;

MVIEW_NAME                     FAST_REFRESHABLE   LAST_REF LAST_REFRESH_DATE
------------------------------ ------------------ -------- -------------------
MV_T_TEST                      DIRLOAD_LIMITEDDML FAST     2013-07-18 12:08:10


SQL> exec dbms_mview.refresh('mv_t_test');

PL/SQL procedure successfully completed.

--不加第二个参数也是增量刷新
SQL> select mview_name,fast_refreshable,last_refresh_type,last_refresh_date from user_mviews;

MVIEW_NAME                     FAST_REFRESHABLE   LAST_REF LAST_REFRESH_DATE
------------------------------ ------------------ -------- -------------------
MV_T_TEST                      DIRLOAD_LIMITEDDML FAST     2013-07-18 12:11:15

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-766609/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-766609/

clg10051
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
oracle materialized view 不刷新,Oracle Materialized view
weixin_32165903的博客
04-03 159
Third party system has one table, we want to fetch new changeddata into one table.It means that the new table should contain all added data or deleted data or updated data.1.Source systemCreate table ...
oracle 复杂视图创建,Oracle 物化视图之--复杂物化视图创建
weixin_35719260的博客
04-15 431
@H_502_8@包含聚集的物化视图1.必须满足所有快速刷新物化视图都满足的条件；2.物化视图查询的所有表必须建立物化视图日志，且物化视图日志必须满足下列限制：(1)包含物化视图查询语句中的所有列，包括SELECT列表中的列和WHERE语句中的列；(2)必须指明ROWID和INCLUDING NEW VALUES；(3)如果对基本的操作同时包括INSERT、UPDATE和DELETE操作(即不是只...
Oracle 错误总结及问题解决 ORA
热门推荐
基于Oracle11G
07-08 18万+
ORA 错误大全
ORACLE 错误一览表(3)
jianhai1229的博客
04-18 1184
ORA-09751: pw_attachPorts: 服务器调用 pws_attach 失败 ORA-09752: pw_attachPorts: port_allocate 失败 ORA-09753: spwat: 无效的进程号 ORA-09754: sppst: 传送给 sppst 的进程号无效 ORA-09755: osngpn: 端口配置失败 ORA-09756: osnpns: 名服务器...
mysql触发器实现oracle物化视图示例代码
09-10
在Oracle数据库中，物化视图（Materialized View）是一种非常有用的数据对象，它提供了对查询结果的预先计算和存储，以提高数据检索速度。物化视图与普通的视图不同，视图是基于基表的逻辑表示，而物化视图则是物理...
ORACLE物化视图的技术应用.pdf
10-10
物化视图日志（Materialized View Log）的创建也很关键，它允许数据库跟踪对基础表的更改，从而支持快速刷新： ```sql CREATE MATERIALIZED VIEW LOG ON EMPLOYEE; ``` 物化视图日志必须与主表关联，以便在刷新物化...
Oracle怎么根据物化视图日志快速刷新物化视图
09-06
在Oracle数据库中，物化视图(Materialized View)是一种特殊的对象，它存储了预计算查询的结果，从而可以提高查询性能。物化视图通常用于汇总查询、复杂查询等场景下，它可以显著减少实时查询所需的时间。 物化视图...
Oracle数据库中物化视图的原理剖析.pdf
11-20
Oracle数据库中的物化视图（Materialized View, MV）是一种数据对象，它预先计算并存储了一个查询的结果，以便后续查询可以更快地访问这些数据，而无需每次请求时都执行原始查询。物化视图在数据仓库和数据分析场景...
ORACLE使用物化视图和查询重写功能
07-04
- **实现**：使用CREATE MATERIALIZED VIEW命令创建物化视图，并配置适当的刷新选项。 ##### 4. 测试查询重写 - **编写查询**：构造一些复杂的查询语句，尝试使用物化视图进行优化。 - **分析结果**：观察查询执行...
idam oracle_Oracle 错误代码详解及解决方式--ORA
weixin_39620252的博客
12-18 4648
ORA-00001: 违反惟一约束条件 (.)错误说明：当在惟一索引所对应的列上键入重复值时，会触发此异常。ORA-00017: 请求会话以设置跟踪事件ORA-00018: 超出最大会话数ORA-00019: 超出最大会话许可数ORA-00020: 超出最大进程数 ()ORA-00021: 会话附属于其它某些进程；没法转换会话ORA-00022: 无效的会话 ID；访问被拒绝ORA-00023: ...
无法复制和rename ASM磁盘组上的数据文件处理方法
Samdy_Chan的专栏
07-21 5002
由于SYSTEM 数据文件丢失，启动数据库时会报如下无法标识或找不到数据文件1的错误： 03:47:02 SYS@racdb1*SQL> startup; ORACLE instance started. Total System Global Area          413372416 bytes Fixed Size                          221
Oracle 物化视图之--复杂物化视图创建
天涯客*Blog
12-07 5183
Oracle 物化视图之--复杂物化视图创建 包含聚集的物化视图 1.必须满足所有快速刷新物化视图都满足的条件； 2.物化视图查询的所有表必须建立物化视图日志，且物化视图日志必须满足下列限制： (1)包含物化视图查询语句中的所有列，包括SELECT列表中的列和WHERE语句中的列； (2)必须指明ROWID和INCLUDING NEW VALUES； (3)如果对基本的操作同时包括
oracle 中的INTERVAL 函数详解
wkc168的专栏
04-30 3万+
INTERVAL YEAR TO MONTH数据类型 Oracle语法: INTERVAL integer [- integer] {YEAR | MONTH} [(precision)][TO {YEAR | MONTH}] 该数据类型常用来表示一段时间差, 注意时间差只精确到年和月. precision为年或月的精确域, 有效范围是0到9, 默认值为2. eg: INTERVAL 123
[2021-08-10]oracle物化视图创建例子和常用关联语句
m15217321304的博客
08-10 642
物化视图常用于同步远端数据到本地，同样也适用创建本地表的物化视图（应该很少用）。 #####创建物化视图相关语法 CREATE MATERIALIZED VIEW语法 CREATE MATERIALIZED VIEW [ schema. ] materialized_view [ column_alias [ENCRYPT [encryption_spec]] [, column_alias [ENCRYPT [encryption_spec]] ]... ] [ OF[ schema. ...
定位导致物化视图无法快速刷新的原因
ljw198827的博客
07-05 142
物化视图的快速刷新采用了增量的机制，在刷新时，只针对基表上发生变化的数据进行刷新。因此快速刷新是物化视图刷新方式的首选。 但是快速刷新具有较多的约束，而且对于采用ON COMMIT模式进行快速刷新的物化视图更是如此。对于包含聚集和包含连接的物化视图的快速刷新机制并不相同，而且对于多层嵌套的物化视图的快速刷新更是有额外的要求。 如此多的限制一般很难记全，当建立物化视图失败时，Oracle...
ORA-12052: cannot fast refresh materialized view DCS_BIZ.MV_TMP_16
minwang593的专栏
02-14 1141
CREATE MATERIALIZED VIEW LOG ON tmp_mv1 WITH rowid INCLUDING NEW VALUES;     SQL> create materialized view MV_TMP_16   2  refresh fast on demand   3  start with to_date('14-02-2014 17:03:16',
最近发再一个MVLOG大到13G了。
henrybai的专栏--大亨利白--大码
09-08 2393
刷新变得异常的慢，删除重新建立方才解决问题。After I recreated the MV log, I manually ran the reporter partitionmaintenance - it finished in about 3 minutes. I found this from an Oracle Note:179469.1 : " If the master
oracle 物化视图 on demand,【案例】Oracle物化视图 on prebuilt table故障常见解决办法...
weixin_39631094的博客
04-02 657
天萃荷净分享一篇关于Oracle物化视图 on prebuilt table故障常见解决办法，包含：ORA-12059,ORA-23413,ORA-12058三种错误的解决方式1、ORA-12059执行语句：CREATE MATERIALIZED VIEW mv_t2on prebuilt TABLEASSELECT * FROM SCOTT.emp错误提示：ORA-12059: prebuilt...
oracle 物化视图示例
最新发布
03-29
以下是一个 Oracle 物化视图的示例： CREATE MATERIALIZED VIEW mv_sales_data BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND AS SELECT s.product_id, p.product_name, s.sale_date, SUM(s.sale_amount...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值