RE: Snapshots on prebuilt tables with reduced precision

"With Reduced Precision" simply allows you to place a materialized view = over a prebuilt table that the definition of the prebuilt table does not = match the the precision of columns in the defining query of the = materialized view. If you have data in the master that is larger than = the precision of the prebuilt table, it will fail on refresh. This would best be used if the prebuilt table had the larger precision = definition than the master since smaller data size data would be = replicated to the MV and this would allow any previous data in the = prebuilt table that is larger than the master site to still maintain = it's size. If you had a prebuilt table with smaller column widths than = the master, then I would alter those columns on the prebuilt table to = the larger size, instead of having the materialized view possibly fail = in the future for the ORA-01401.

create table yo (col1 varchar2(10));

create table yoyo (col1 varchar2(8));

alter table yo add constraint pk_yo primary key (col1);

alter table yoyo add constraint pk_yoyo primary key (col1);

create materialized view yoyo on prebuilt table with reduced precision = refresh force as select * from yo;

insert into yo values ('0123456789');

commit;

exec dbms_mview.refresh('YOYO','C');
BEGIN dbms_mview.refresh('YOYO','C'); END;

*
ERROR at line 1:

ORA-12008: error in materialized view refresh path
ORA-01401: inserted value too large for column
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 841
ORA-06512: at line 1

Drop materialized view yoyo;

alter table yoyo modify (col1 varchar2(10));

create materialized view yoyo on prebuilt table with reduced precision = refresh force as select * from yo;

exec dbms_mview.refresh('YOYO','C');

PL/SQL procedure successfully completed.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kaing, Leng Sent: Tuesday, October 12, 2004 12:45 AM To: oracle-l_at_freelists.org
Subject: Snapshots on prebuilt tables with reduced precision

Greetings all,

Initially I thought the option "with reduced precision" was used to get =
=3D around the problem of replication tables where column orders or =
number =3D of columns do not match. eg. master table has 4 columns but = we only want =3D to replicate 3 of the columns. And/or the order of the = columns on the =3D master and slave tables do not match.

However, today I've just discovered this definition in the Oracle =3D manuals: "Specify WITH REDUCED PRECISION to authorize the loss of =3D = precision that will result if the precision of the table or materialized =
=3D view columns do not exactly match the precision returned by =
subquery"=3D20 I'm now confused. What does "authorise the loss of = precision" mean? =3D Master column can be varchar(10) and slave column = can be varchar(8) and =3D
2 characters dropped off in the process? Am I right to assume that "with reduced precision" is used with the =3D = number of columns and/or order in the master and slave do not match?

TIA, Leng.

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l