Re: Oracle Snapshot column size

On Jan 27, 9:30 am, "Vsevolod Afanassiev" <vafanass..._at_yahoo.com> wrote:
> Hi,
> Oracle 9.2.0.6.0 on Linux
>
> I neded to replicate a few tables from one database to another
> database. This is simple on-way replication with refresh on demand. So
> I created snapshot logs in the source (master) database and snapshots
> in the targer (slave) database, for example:
>
> SQL> create snapshot log on lnp_app_owner.lnp_array
> 2 with rowid including new values;
>
> SQL> create materialized view lnp_app_owner.lnp_array
> 2 refresh with rowid
> 3 as
> 4 select *
> 5 from lnp_array_at_lnp1p;
>
> Materialized view created.
>
> where database link points from target to the source database.
>
> However, column sizes in the snapshot are 3 times column sizes in the
> source database:
>
> In the source database:
>
> SQL> desc lnp_app_owner.lnp_array
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> ID NOT NULL NUMBER(38)
> BPI_ID NUMBER(38)
> STARTNUMBER VARCHAR2(10)
> ENDNUMBER VARCHAR2(10)
> CENTREX NUMBER(38)
> PNVCODE CHAR(3)
> CNACODE CHAR(3)
> COMMENT CLOB
> STATUS CHAR(4)
> TYPE CHAR(6)
> ACCOUNTNUMBER VARCHAR2(35)
>
> SQL>
>
> In the snapshot:
>
> SQL> desc lnp_app_owner.lnp_array
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> ID NOT NULL NUMBER(38)
> BPI_ID NUMBER(38)
> STARTNUMBER VARCHAR2(30)
> ENDNUMBER VARCHAR2(30)
> CENTREX NUMBER(38)
> PNVCODE CHAR(9)
> CNACODE CHAR(9)
> COMMENT CLOB
> STATUS CHAR(12)
> TYPE CHAR(18)
> ACCOUNTNUMBER VARCHAR2(105)
>
> SQL>
>
> Is it normal? Why would Oracle need to make them 3 timer wider?

FYI they aren't called "snapshots" any more ;) Received on Mon Jan 29 2007 - 06:19:00 CST