January 14 2014

oracle 物化视图

SELECT * FROM all_db_links

CREATE public database link ucjmh
connect TO ucjmh identified by ucjmh
using ‘(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)))
(CONNECT_DATA =(SERVICE_NAME = orcl)))’;

SELECT * FROM v$database@ucjmh;
DROP MATERIALIZED  VIEW  TBL_TEST;
CREATE  materialized view TBL_TEST
refresh force on demand
start with sysdate next to_date( concat( to_char( sysdate+1,’dd-mm-yyyy’),’ 22:00:00′),’dd-mm-yyyy hh24:mi:ss’)
–start with sysdate next sysdate+1/1440 with primary KEY  每分钟
AS SELECT * FROM TBL_TEST@ucjmh; –创建一个每天晚上10点刷新

SELECT * FROM tbl_test

create materialized view mv_name refresh force on commit as select * from table_name;
–备注:实际创建过程中,基表需要有主键约束,否则会报错(ORA-12014)。
–刷新的方法有四种:FAST、COMPLETE、FORCE和NEVER。
–FAST刷新采用增量刷新,只刷新自上次刷新以后进行的修改。
–COMPLETE刷新对整个物化视图进行完全的刷新。
–FORCE方式,则Oracle在刷新时会去判断是否可以进行快速刷新,如果可以则采用FAST方式,否则采用COMPLETE的方式。
–NEVER指物化视图不进行任何刷新。
–对于已经创建好的物化视图,可以修改其刷新方式,比如把物化视图mv_name的刷新方式修改为每天晚上12点刷新一次:
ALTER  MATERIALIZED VIEW TBL_TEST REFRESH FORCE ON DEMAND START WITH SYSDATE
 NEXT  to_date(CONCAT(to_char(SYSDATE+1,’dd-mm-yyyy’),’ 23:00:00′),’dd-mm-yyyy hh24:mi:ss’);
 
 –那么 他的内部是怎么的呢 是不是用Job呢?
select job,log_user,last_date,last_sec,next_date,next_sec,interval,what from user_jobs;
–1 4 COGNOS 2014/6/26 16:48:06 16:48:06 2014/6/26 16:52:41 16:52:41 to_date(CONCAT(to_char(SYSDATE+1,’dd-mm-yyyy’),’ 24:00:00′),’dd-mm-yyyy hh24:mi:ss’)  dbms_refresh.refresh(‘”COGNOS”.”TBL_TEST”‘);
–果然是
create materialized view log on TBL_TEST; –会报错 说是没有主键、 那么我们给那加个主键
ALTER TABLE TBL_TEST@Ucjmh MODIFY ID  NUMBER PRIMARY KEY–不可以对行程的数据库做DDL  ORA 02021

BEGIN
   dbms_refresh.refresh(‘”COGNOS”.”TBL_TEST”‘); 
 END;
 –手动刷新一下
 
 
create materialized view log on TBL_TEST; —我擦 还是报错 说主键。。。。
–删除重来
DROP MATERIALIZED  VIEW  TBL_TEST;
CREATE MATERIALIZED  VIEW  TBL_TEST
 REFRESH  FORCE  ON  COMMIT  AS  SELECT  * FROM  TBL_TEST@Ucjmh;–如果物化视图和基表不在同一个数据库中,
 –是无法建立ON COMMIT刷新模式的物化视图的
 
 –自己不可以建自己on commitr的物化视
 –自己想建别人on commit的视图需要授权
 –不可以建别的库的on commit的
 CREATE MATERIALIZED  VIEW  TBL_TEST
 REFRESH  force   ON  COMMIT  AS  SELECT  * FROM  scott.dept
 
 —
 DROP MATERIALIZED  VIEW  TBL_TEST;

SELECT * FROM TBL_TEST

create materialized view log on TBL_TEST; — 你妹 这下可以了。。。。

SQL> alter user scott account unlock;

用户已更改。
SQL> alter user scott identified by scott;

用户已更改。

SQL> conn scott/scott
已连接。

SELECT * FROM mlog$_TBL_TEST — 0

SELECT COUNT(*) FROM tbl_test; –4

SELECT * FROM tbl_test;

 

SQL> conn scott/scott
已连接。
SQL> INSERT INTO dept VALUES (50,’aaaa’,’bb’);

已创建 1 行。

SQL> commit
  2  ;
commit
*
第 1 行出现错误:
ORA-12018: 在创建 “COGNOS”.”TBL_TEST” 的代码时出现以下错误
ORA-01031: 权限不足
SQL> conn / as sysdba
已连接。
SQL> GRANT ON COMMIT REFRESH ON scott.dept TO cognos;

授权成功。

SQL> conn scott/scott
已连接。
SQL> select count(*) from dept;

  COUNT(*)
———-
         4

SQL> INSERT INTO dept VALUES (50,’aaaa’,’bb’);

已创建 1 行。

SQL> commit;
commit
*
第 1 行出现错误:
ORA-12018: 在创建 “COGNOS”.”TBL_TEST” 的代码时出现以下错误
ORA-01031: 权限不足。

SQL>

QL> conn / as sysdba
已连接。
SQL> grant select any table to cognos
  2  ;

授权成功。

SQL> conn scott/scott
已连接。
SQL>  drop MATERIALIZED  VIEW  TEST1;

实体化视图已删除。

SQL> delete from dept where deptno=50;

已删除 1 行。

SQL> commit
  2  ;

提交完成。

SQL>  INSERT INTO dept VALUES (50,’aaaa’,’bb’);

已创建 1 行。

SQL> commit;

提交完成。

 

SQL> select table_name from user_tables;

TABLE_NAME
————————————————————
RUPD$_DEPT–
MLOG$_DEPT–
SALGRADE
BONUS
EMP
DEPT

已选择6行。

 

SQL> SELECT * FROM mlog$_dept;
未选定行
SQL> select * from rupd$_dept;
未选定行
–我很好奇 为什么呢 any table 中的哪一张是关键呢  是mlog$_dept;还是 rupd$_dept; 、

SQL> conn / as sysdba
已连接。

SQL> revoke select any table from cognos;

撤销成功。

SQL> grant select on scott.dept to cognos;

授权成功。

SQL> conn scott/scott
已连接。
SQL>  INSERT INTO dept VALUES (60,’aaaa’,’bb’)
  2  ;

已创建 1 行。

SQL> commit;
commit
*
第 1 行出现错误:
ORA-12008: 实体化视图的刷新路径中存在错误
ORA-00942: 表或视图不存在

–我擦 证明需要这两张 最少一张
SQL> conn / as sysdba
已连接。
SQL> grant select on scott.RUPD$_DEPT to cognos;

授权成功。

SQL> conn scott/scott
已连接。
SQL>  INSERT INTO dept VALUES (60,’aaaa’,’bb’)
  2  ;

已创建 1 行。

SQL> commit;
commit
*
第 1 行出现错误:
ORA-12008: 实体化视图的刷新路径中存在错误
ORA-00942: 表或视图不存在

–我擦 不是这张
SQL> grant select on scott.mlog$_dept to cognos;

SQL>  INSERT INTO dept VALUES (60,’aaaa’,’bb’);

已创建 1 行。

SQL> commit;

提交完成。
–是两张都要 还是只要一张就可以。 我收一张试一下

–sys
SQL> revoke select on scott.RUPD$_DEPT from cognos;

撤销成功
。–scott
SQL>  INSERT INTO dept VALUES (70,’aaaa’,’bb’);

已创建 1 行。

SQL> commit;

提交完成。
看来只要张就可以了



Copyright 2019. All rights reserved.

Posted 2014年1月14日 by ucjmh in category "oracle

Leave a Reply

Your email address will not be published. Required fields are marked *