Re: ABOUT MATERIALIZED VIEW

27 views

Skip to first unread message

ddf

unread,

Oct 1, 2012, 10:00:06 AM10/1/12

to oracle_db...@googlegroups.com

Comments embedded.

On Saturday, September 29, 2012 1:15:19 AM UTC-6, Manoj wrote:

Dear Experts,

When i am trying to replicate the column from 1A databse to 1B database.the column was not replicated.and also am not getting any errors.

I am following this Procedure

1B(SCOTT)>DROP MATERIALIZED VIEW SCOTT_EMP;

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

1A(SYS)>CREATE MATERIALIZED VIEW LOG ON SCOTT.EMP TABLESPACE USERS;( why am using like SCOTT.EMP i am not having privilege to connact directly to scoot in 1A its a client system).

1B(SCOTT)>CREATE MATERIALIZED VIEW SCOTT_EMP
BUILD IMMEDIATE
REFRESH FAST
AS SELECT * FROM SCOTT_EMP@SCOTT_LNK;

You cannot select from the materialized view you haven't yet created so if this is the actual command you are using I am surprised you are not getting an error.

and also tried as like this

1B(SCOTT)>CREATE MATERIALIZED VIEW SCOTT_EMP
BUILD IMMEDIATE
REFRESH FAST
AS SELECT
EMPNO,
ENAME,
DEPTNO,
DEPTNAME
FROM SCOTT_EMP@SCOTT_LNK;

Again, you can't select from a non-existent materialized view (SCOTT_EMP) to create a materialized view (SCOTT_EMP).

Now my Question is

here i am trying to replicate the column DEPTNAME but it is not replicated and also am not getting error in creation, also there is No error in Alert log.

the log was created,all the columns added except new column.

what is this reason.

You provide no output to prove your statements and I have no problem creating a materialized view with those columns, even when I modify the the base table AFTER creating the materialized veiw log:

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

Table altered.

SQL>

SQL> create materialized view log on emp tablespace users;

Materialized view log created.

SQL>

SQL> create materialized view scott_emp

2 build immediate

3 refresh fast

4 as select * from emp;

Materialized view created.

SQL>

SQL> select * from scott_emp;

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 09-DEC-82 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

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

7876 ADAMS CLERK 7788 12-JAN-83 1100 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

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 10

14 rows selected.

SQL>

SQL> drop materialized view scott_emp;

Materialized view dropped.

SQL>

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

Table altered.

SQL>

SQL> update emp e

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

14 rows updated.

SQL>

SQL> commit;

Commit complete.

SQL>

SQL> create materialized view scott_emp

2 build immediate

3 refresh fast

4 as select empno, ename, deptno, deptname

5 from emp;

Materialized view created.

SQL>

SQL> select * from scott_emp;

EMPNO ENAME DEPTNO DEPTNAME

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

7369 SMITH 20 RESEARCH

7499 ALLEN 30 SALES

7521 WARD 30 SALES

7566 JONES 20 RESEARCH

7654 MARTIN 30 SALES

7698 BLAKE 30 SALES

7782 CLARK 10 ACCOUNTING

7788 SCOTT 20 RESEARCH

7839 KING 10 ACCOUNTING

7844 TURNER 30 SALES

7876 ADAMS 20 RESEARCH

EMPNO ENAME DEPTNO DEPTNAME

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

7900 JAMES 30 SALES

7902 FORD 20 RESEARCH

7934 MILLER 10 ACCOUNTING

14 rows selected.

SQL>

I try again, this time altering the table to include the deptname column before creating the materialized view log:

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 dname from dept where deptno = e.deptno);

14 rows updated.

SQL>

SQL> commit;

Commit complete.

SQL>

SQL> create materialized view log on emp tablespace users;

Materialized view log created.

SQL>

SQL> create materialized view scott_emp

2 build immediate

3 refresh fast

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>

SQL> drop materialized view scott_emp;

Materialized view dropped.

SQL>

SQL> create materialized view scott_emp

2 build immediate

3 refresh fast

4 as select empno, ename, deptno, deptname

5 from emp;

Materialized view created.

SQL>

SQL> select * from scott_emp;

EMPNO ENAME DEPTNO DEPTNAME

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

7369 SMITH 20 RESEARCH

7499 ALLEN 30 SALES

7521 WARD 30 SALES

7566 JONES 20 RESEARCH

7654 MARTIN 30 SALES

7698 BLAKE 30 SALES

7782 CLARK 10 ACCOUNTING

7788 SCOTT 20 RESEARCH

7839 KING 10 ACCOUNTING

7844 TURNER 30 SALES

7876 ADAMS 20 RESEARCH

EMPNO ENAME DEPTNO DEPTNAME

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

7900 JAMES 30 SALES

7902 FORD 20 RESEARCH

7934 MILLER 10 ACCOUNTING

14 rows selected.

SQL>

There is no issue populating this column and it's not a LOB or a LONG the way I have it defined. You provide no table definition, no sample data, no material with which to work to try and replicate your issue.

No one can give you an answer until you provide more data, including the create table command, to test this.