物化视图的自动刷新问题 我创建了一个物化视图,选择的刷新方式是ON COMMNET,但是当我的数据发生改变时,发现数据并没有刷新,就是说我通过这个物化视图查询的数据和我创建相同的视图所查询出来的结果不一致!请高手帮忙阿! 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 SQL> create materialized view mview_emp 2 refresh fast on demand 3 as 4 select * from scott.emp 5 /Materialized view created.SQL>SQL> drop materialized view mview_emp 2 /Materialized view dropped.SQL>SQL> create materialized view mview_emp 2 refresh fast on commit 3 as 4 select * from scott.emp 5 /select * from scott.emp *ERROR at line 4:ORA-01031: insufficient privileges refrence:http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1167235330355 我创建了一个物化视图,选择的刷新方式是ON COMMNET,但是当我的数据发生改变时,发现数据并没有刷新,就是说我通过这个物化视图查询的数据和我创建相同的视图所查询出来的结果不一致!请高手帮忙阿! 你创建的sql代码是怎样的,是不是有疏忽?alert.log里面有警告或出错信息吗?我测试是没有问题的,楼主试试:SQL> create materialized view mv_emp1 refresh force on commit as select * from scott.emp;Materialized view created.SQL> select * from mv_emp1; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- ------------ ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 1014 rows selected.SQL> update emp set comm = 1 where empno = 7844;1 row updated.SQL> select * from mv_emp1; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- ------------ ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 1014 rows selected.SQL> commit;Commit complete.SQL> select * from mv_emp1; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- ------------ ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 1 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 1014 rows selected. 关于SQL备份文件怎样在Oracle Home 8i 上还原的问题 数据库连接问题ORA-12638 为什么我的db_link建立后,查询时候提示无效的用户和密码。 将Excel表导入oracle数据库方法? 关于oracle中数据的空格问题 知道的请支持——Oracle9i中在SQL*PLUS界面中怎样导出存储过程? oracle中表空间和表的问题 powerdesigner创建的oracle数据库表为什么查询不了呢? 想学Oracle不知道够不够格 ★★急,如何将oracle816的导出文件导入oracle734★★ 急!!!!!!!!!!!!!!!1 急! 急!!!!!!!!1
2 refresh fast on demand
3 as
4 select * from scott.emp
5 /Materialized view created.SQL>
SQL> drop materialized view mview_emp
2 /Materialized view dropped.SQL>
SQL> create materialized view mview_emp
2 refresh fast on commit
3 as
4 select * from scott.emp
5 /
select * from scott.emp
*
ERROR at line 4:
ORA-01031: insufficient privileges
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1167235330355
alert.log里面有警告或出错信息吗?我测试是没有问题的,楼主试试:SQL> create materialized view mv_emp1 refresh force on commit as select * from scott.emp;Materialized view created.SQL> select * from mv_emp1; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 1014 rows selected.SQL> update emp set comm = 1 where empno = 7844;1 row updated.SQL> select * from mv_emp1; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 1014 rows selected.SQL> commit;Commit complete.SQL> select * from mv_emp1; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 1 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 1014 rows selected.