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

[areeb]

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

[PAVB]

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.

[areeb]

Why is that dblink works not materialized view?

[PAVB]

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.

[areeb]

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.