Wednesday, August 18, 2010

ORA-12060: During Mview Creation

Well I had racked my brains a lot to create an mview on a large table across geographies, and it failed a few times.
Plus to increase my problem the character set was also different between the two DBs.:

Master Site:
SQL> select * from nls_database_parameters@link1 where PARAMETER='NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET WE8MSWIN1252

Mview Site:
SQL> select * from nls_database_parameters where PARAMETER='NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET AL32UTF8


I started by creating the mview directly:

create materialized view user.test1
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "users"
PARALLEL 4
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
WITH PRIMARY KEY
START WITH sysdate+0 NEXT SYSDATE+6/24
USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE
AS select * from user.test1@link1;


This ran for ever and finally failed with ORA-01555, as expected, as it ran for almost a day.
To give a background the size of the table is 1G.

Then I finally decided to use the PREBUILT Table option:
So I took an export backup of that table in the master site and imported it in mview site.
And then tried to recreate the mview with PREBUILT TABLE option:

create materialized view user.test1 ON PREBUILT TABLE
refresh fast with primary key for update
as
select col1,col2 from user.test1@link1;

Which failed with:
Col1,
*
ERROR at line 6:
ORA-12060: shape of prebuilt table does not match definition query


Well I checked the table structure and realized that the col size for char data type had become 2 folds as during import, due to character set conversion this happens.

Then I searched metalink, etc, and found an options called "WITH REDUCED PRECISION", to be used when there is col mis-match.

create materialized view user.test1 ON PREBUILT TABLE WITH REDUCED PRECISION
refresh fast with primary key for update
as
select col1,col2 from user.test1@link1;

But it still failed:

create materialized view user.test1 ON PREBUILT TABLE WITH REDUCED PRECISION
*
ERROR at line 1:
ORA-12060: shape of prebuilt table does not match definition query

What resolved the problem is removing "with primary key for update"

create materialized view user.test1 ON PREBUILT TABLE WITH REDUCED PRECISION
refresh fast on demand
as
select col1,col2 from user.test1@link1;

Materialized view created.

1 Comments:

Ritzy said...

Very interesting and useful indeed. Thanks for sharing your findings.