Home > Default > Problems in creating Materialized View from 10g R2  to 11g

Problems in creating Materialized View from 10g R2  to 11g

October 11Hits:1
Advertisement
Hi,
We have two databases in 10g Release 2. These databases are on two different servers and we use
Materialized views to replicate data between these servers. We are currently using 64 bit version of
oracle 10g release 2 for LINUX.
Oracle Database 10g Release 10.2.0.1.0 - 64 bit Production
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
We use CENTOS 5 - 64 bit edition
We upgraded one of the databases to 11g (We exported the data from old database and
imported it in new database) . Then we tried to create a Materialized view with incremental refresh
on it where the base table is in the second Oracle 10g database.
The problem is that it is not letting us create the view with FAST Refresh. It kept on giving ORA-12028
error. We tried different remedies suggested on forums like creating a view with ROWID, but still it
continued giving us the same error. We also synchronized the characterset of Oracle 10g and Oracle 11g
installations but still it did not work.
Our base table size is pretty large (over 10 Million records) and if we do a full refresh, it takes hours to
refresh it.
Without incremental refresh, we will not be able to fucntion, and to upgrade both database simultaneously
we will need a large down-time which also we can not afford.
Please advise what should we do.
Best Regards
Madhup
Edited by: Madhup on Mar 2, 2009 10:45 AM

Answers

Hello,
Yesterday I met the same problem and very sad about this, however, my story is much more complicated (I think so).
I have 6 databases:
2 - 11g
2 - 10g
2 - 9i
Every database of each release has different characterset(UTF8 and AL16UTF16).
I am trying to create Materialized view with FAST REFRESH on Oracle 11g, when master tables are located on 10g database and 9i database.
Materialized view, which points to 9i database, was successfully created.
Materialized view, which points to 10g database, gets ORA-12028
Like you, I tried many notes from METALINK and thought at the begining that the problem is my characterset. But, when the materialized views were successfully built on Oracle 9i database, I understood that the problem is something else.
Did you find the actual reason why this issue does not work??
Thanks,
Alex

Read other 3 answers

Tags:

Related Articles

  • Problems in creating Materialized View from 10g R2  to 11gOctober 11

    Hi, We have two databases in 10g Release 2. These databases are on two different servers and we use Materialized views to replicate data between these servers. We are currently using 64 bit version of oracle 10g release 2 for LINUX. Oracle Database 1

  • Problem in creating MATERIALIZED VIEWNovember 30

    Hello All, I am trying to create the following materialized view and getting this error SQL> CREATE MATERIALIZED VIEW system_active_loss_mv 2 BUILD IMMEDIATE 3 REFRESH FAST ON COMMIT 4 ENABLE QUERY REWRITE 5 AS 6 SELECT zone_name, 7 EXTRACT(YEAR FROM

  • Problems while facing Materialized ViewsNovember 30

    Hello All, I am facing some problems while creating Materialized Views. Senario. SCOTT USER: sql> Grant select on emp to Public; sql> Create materialized view log on emp; Connected to LDB User sql> CREATE MATERIALIZED VIEW mt_emp Refresh fast as

  • Problem with Materialized Views in 10gOctober 11

    Hi All, I am creating a Materialized View like CREATE MATERIALIZED VIEW mvname TABLESPACE SYSTEM BUILD IMMEDIATE REFRESH WITH ROWID FOR UPDATE AS ( SELECT a,b,c FROM table_a,table_b,table_c WHERE cond ); I am getting an error SQL Error: ORA-12013: up

  • Problem creating materialized viewNovember 30

    OK, I'm totally new in using materialized views. I'm trying to create one and to make it refreshed on commit CREATE MATERIALIZED VIEW VK_CLIENT  build immediate refresh fast on commit AS   select c1.code, c1.fiche fiche_1, decode( c1.sfam, '04',  dec

  • ORA-04052 creating Materialized ViewNovember 30

    ORA-04052 creating Materialized View Hi All !! I'm trying to create a Materialized view and his query definition uses an active public database link. When I ran the script for creating the view I get the following message: ERROR in line 1: ORA-04052:

  • Create Materialized View hangsNovember 30

    Hi, I have a query, which contains about 10 tables and returns about 40000 rows. When I execute the query itself it runs about 1minute. I want to create a Materialized View for that query, but I had to stop it after some hours. There is no error mess

  • ORA-25175 when trying to create materialized view, organization index SOLVNovember 30

    Hi, I'm trying to create a materialized view to compensate for non-normalized table. I have table anormal, and mv is to be on distinct agency, company in that table. Here goes: SQL> select * from v$version; BANNER Oracle Database 10g Enterprise Editi

  • Bug: Can not create materialized view log on 11G XENovember 30

    Hi, I log in as HR user and try create materialized view log CREATE MATERIALIZED VIEW LOG ON HR.EMPLOYEES;I get error > Error starting at line 1 in command: CREATE MATERIALIZED VIEW LOG ON HR.EMPLOYEES Error at Command Line:1 Column:0 Error report: S

  • Creating Materialized View in ToadOctober 11

    As with every new endeavor - come new questions. I am trying to create a materialized view via toad (first time doing this). Now - toad is great, since it provides a kind-of wizard interface. Hence, one doesn't have to completely code in the create s

Copyright (C) 2019 wisumpire.com, All Rights Reserved. webmaster#wisumpire.com 14 q. 0.645 s.