ORA-12028: materialized view type is not supported by master site

Hi ,
I am trying to create a materialized view to replicate data from database A to database B on a table(exist on database A) whose characterset is AL24UTFFSS(DB A 8174)) which is different from characterset of db B(AL32UTF8 10g).

Series of steps on :
I)On db A

1)Created Materialized view log on master table(UWS_LOT )


II)On db B

connect test/test
1)SQL> create database link aa connect xyz to identified by XXXX using 'aa.xx.com';

Database link created.

2)SQL> create materialized view test.mv_UWS_LOT refresh fast as select * from xyz.UWS_LOT@aa;
create materialized view test.mv_UWS_LOT refresh fast as select * from xyz.UWS_LOT@aa
*
ERROR at line 1:
ORA-12028: materialized view type is not supported by master site
@AA.XX.COM

then created MV with rowid,

create materialized view test.mv_UWS_LOT refresh fast with rowid as select * from xyz.UWS_LOT@aa;

ERROR at line 1:
ORA-12703: this character set conversion is not supported
ORA-06512: at line 1


Please let me know what needs to be done to fix it.

Regards

Your main issue here is that AL24UTFFSS is an invalid character set on Ora9i and upper then, when you are trying to replicate that data into a Ora10g database it doesn't work.

You may want to check Oracle Note234381.1 which is about how to migrate from AL24UTFFSS to AL32UTF8.

Why is that dblink works not materialized view?

Quote:

---

Originally Posted by areeb

Why is that dblink works not materialized view?

---

DBLink is just giving you connectivity, it touches nothing there.
Think of a DBLink just like a client session connecting to the target database.

Quote:

---

Your main issue here is that AL24UTFFSS is an invalid character set on Ora9i and upper then, when you are trying to replicate that data into a Ora10g database it doesn't work.

---

Was able to create MV with UTF8(10g) but not with AL32UTF8 why is that?

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> connect TEST/TEST

SQL> select value from v$nls_parameters where parameter='NLS_CHARACTERSET';

VALUE
----------------------------------------------------------------
UTF8

SQL> create database link aa connect to xyz identified by test using 'xxxx.xx.xxx.xxx';

Database link created.

SQL> create materialized view test.mv_abc refresh fast with rowid as select * from xyz.abc@aa;

Materialized view created.