ABOUT MATERIALIZED VIEW

[MANOJ K]

Dear Expert,

• We are having two productions servers 1A and 1B.

• Using Database Link we are creating Materialized views in 1B , the exact copy of 1A Tables

                    (eg: 1A: emp table  1B: emp materialized view)

Issue

Client has updated one column in table emp(1A)

we drop and recreated the MView emp in 1B, but column was not updated in 1B MView emp.

i am following the bellow procedure.

1B(SCOTT)>DROP MATERIALIZED VIEW EMP;

1A(SYS)>DROP MATERIALIZED VIEW LOG ON SCOTT.EMP;

1A(SYS)>CREATE MATERIALIZED VIEW LOG ON SCOTT.EMP  TABLESPACE TABLESPACE NAME;

1B(SCOTT)>CREATE MATERIALIZED VIEW EMP

                   BUILD IMMEDIATE

                   REFRESH FAST

                   AS SELECT * FROM EMP@LINK;

after doing all this process the column is not adding and there is no error  and also there is no errors in Alert log.

Please give me the solution for this ASAP.

--
Thanks & Regards,

Manoj.K,

[Sanjay Dharmadhikari]

Have you tried running DBMS_MVIEW.REFRESH procedure ?

 

Also, your other e-mail says that the the column DEPTNAME was not updated. This column is in DEPT table. You will to create MV Logs on DEPT table and change the select statement in CREATE MAERIALZIED VIEW  to join to the 2 tables on DEPTNO.

 

Hope this helps.

 

- sanjay

--
You received this message because you are subscribed to the Google Groups "ORACLE_DBA_EXPERTS" group.
To post to this group, send email to oracle_db...@googlegroups.com.
To unsubscribe from this group, send email to oracle_dba_expe...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/oracle_dba_experts?hl=en.

[ddf]

No one can give you anything ASAP except a request for MORE data and a table definition.  I cannot duplicate your problem (see your prior post).

David Fitzjarrell 

[Kaushal Dave]

Hello Manoj,

 

Try this one...

 

 

1A(SYS)>CREATE MATERIALIZED VIEW LOG ON SCOTT.EMP WITH ROWID INCLUDING NEW VALUES TABLESPACE TABLESPACE NAME;

 

 

1B(SCOTT)>CREATE MATERIALIZED VIEW EMP BUILD IMMEDIATE

REFRESH FAST ON COMMIT

AS SELECT * FROM EMP@LINK;

--

You received this message because you are subscribed to the Google Groups "ORACLE_DBA_EXPERTS" group.

To view this discussion on the web visit https://groups.google.com/d/msg/oracle_dba_experts/-/w7dN9Co2eTQJ.

To post to this group, send email to oracle_db...@googlegroups.com.
To unsubscribe from this group, send email to oracle_dba_expe...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/oracle_dba_experts?hl=en.

--
Best Regards,

Kaushal Dave.
Oracle 11g Certified DBA.
Mobile : 09552585653 - Mumbai.
http://kaushal4oracle.blogspot.com

[ddf]

To unsubscribe from this group, send email to oracle_dba_experts+unsub...@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/oracle_dba_experts?hl=en.

--
Best Regards,

Kaushal Dave.
Oracle 11g Certified DBA.
Mobile : 09552585653 - Mumbai.
http://kaushal4oracle.blogspot.com

My original reply provided an example based on the code originally posted; such code would imply that a primary key has been established on the emp table and thus the materialized veiw log won't  need the ROWID qualifier:

SQL> alter table emp add constraint emp_pk primary key(empno);

Table altered.

SQL>

SQL> alter table emp add deptname varchar2(12);

Table altered.

SQL>

SQL> update emp e

  2  set e.deptname = (select d.dname from dept d where d.deptno = e.deptno);

14 rows updated.

SQL>

SQL> commit;

Commit complete.

SQL>

SQL> create materialized view log on emp including new values;

Materialized view log created.

SQL>

SQL> create materialized view scott_emp

  2  build immediate

  3  refresh fast on commit

  4  as select * from emp;

Materialized view created.

SQL>

SQL> select * from scott_emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO DEPTNAME

---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ------------

      7369 SMITH      CLERK           7902 17-DEC-80        800                    20 RESEARCH

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30 SALES

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30 SALES

      7566 JONES      MANAGER         7839 02-APR-81       2975                    20 RESEARCH

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30 SALES

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30 SALES

      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10 ACCOUNTING

      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20 RESEARCH

      7839 KING       PRESIDENT            17-NOV-81       5000                    10 ACCOUNTING

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30 SALES

      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20 RESEARCH

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO DEPTNAME

---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ------------

      7900 JAMES      CLERK           7698 03-DEC-81        950                    30 SALES

      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20 RESEARCH

      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10 ACCOUNTING

14 rows selected.

SQL>

My original example provided the same  output as your suggestion which still does not provide us the table definition the original poster is using nor any sample data he might have inserted into his source table. Without that description and sample data any response is guesswork,  nothing more.