Snapshots on prebuilt tables with reduced precision 2004-10-12 - By Parker, Matthew
"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 (See ORA-01401.ora-code.com).
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 (See ORA-12008.ora-code.com): error in materialized view refresh path
ORA-01401 (See ORA-01401.ora-code.com): inserted value too large for column
ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_SNAPSHOT ", line 803
ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_SNAPSHOT ", line 860
ORA-06512 (See ORA-06512.ora-code.com): at "SYS.DBMS_SNAPSHOT ", line 841
ORA-06512 (See ORA-06512.ora-code.com): 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@(protected) =
[mailto:oracle-l-bounce@(protected)] On Behalf Of Kaing, Leng
Sent: Tuesday, October 12, 2004 12:45 AM
To: oracle-l@(protected)
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
|
|
