Hello,

I have two same tables in Oracle 8.1.5.0.0, but the command

create snapshot curr
on prebuilt table
refresh complete
as
select *
from curr@eb_link;

returns

ERROR at line 8:
ORA-12060: shape of prebuilt table does not match definition query

I try to make a snapshot over DB-Link eb_link. This snapshot worked
till I added a new column orun_id_real. To make so, I dropped
snapshot, change source and target table definition. After this
I can not get the snapshot to work.

If I try

create snapshot curr
on prebuilt table
refresh complete
as
select
id
,orun_id
,cd
,mnemonic
,exchange_factor
,iso_code
,decimal_places
,euro
,pt_valid
,uc
,dc
,um
,dm
,orun_id_real
from curr@eb_link

(so without *) Oracle tells me:

,cd
*
ERROR at line 8:
ORA-12060: shape of prebuilt table does not match definition query

but I see no difference in the column type "cd".

Could you please help me?

Thanks a lot
Branislav

PS:
desc curr
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(38)
ORUN_ID NOT NULL NUMBER(5)
CD NOT NULL VARCHAR2(20)
MNEMONIC NOT NULL VARCHAR2(20)
EXCHANGE_FACTOR NOT NULL NUMBER(5,2)
ISO_CODE NOT NULL VARCHAR2(3)
DECIMAL_PLACES NOT NULL NUMBER(2)
EURO NOT NULL VARCHAR2(1)
PT_VALID NOT NULL VARCHAR2(1)
UC NOT NULL VARCHAR2(120)
DC NOT NULL DATE
UM VARCHAR2(120)
DM DATE
ORUN_ID_REAL NOT NULL NUMBER


desc curr@eb_link
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(38)
ORUN_ID NOT NULL NUMBER(5)
CD NOT NULL VARCHAR2(20)
MNEMONIC NOT NULL VARCHAR2(20)
EXCHANGE_FACTOR NOT NULL NUMBER(5,2)
ISO_CODE NOT NULL VARCHAR2(3)
DECIMAL_PLACES NOT NULL NUMBER(2)
EURO NOT NULL VARCHAR2(1)
PT_VALID NOT NULL VARCHAR2(1)
UC NOT NULL VARCHAR2(120)
DC NOT NULL DATE
UM VARCHAR2(120)
DM DATE
ORUN_ID_REAL NOT NULL NUMBER

Try specifying precision for the NUMBER data type. There is a bug in
8.1.5 with using the "float" data type in a mterilaized view. This is
probably due to the same bug. BTW, try to move off 8.1.5 as soon as
you can. This is a much maligned release, and for good reasons.


- Jusung Yang


siarsky@usa.com (siarsky) wrote in message news:<cf3f8310.0307180517.16b54e19@posting.google. com>...
> Hello,
>
> I have two same tables in Oracle 8.1.5.0.0, but the command
>
> create snapshot curr
> on prebuilt table
> refresh complete
> as
> select *
> from curr@eb_link;
>
> returns
>
> ERROR at line 8:
> ORA-12060: shape of prebuilt table does not match definition query
>
> I try to make a snapshot over DB-Link eb_link. This snapshot worked
> till I added a new column orun_id_real. To make so, I dropped
> snapshot, change source and target table definition. After this
> I can not get the snapshot to work.
>
> If I try
>
> create snapshot curr
> on prebuilt table
> refresh complete
> as
> select
> id
> ,orun_id
> ,cd
> ,mnemonic
> ,exchange_factor
> ,iso_code
> ,decimal_places
> ,euro
> ,pt_valid
> ,uc
> ,dc
> ,um
> ,dm
> ,orun_id_real
> from curr@eb_link
>
> (so without *) Oracle tells me:
>
> ,cd
> *
> ERROR at line 8:
> ORA-12060: shape of prebuilt table does not match definition query
>
> but I see no difference in the column type "cd".
>
> Could you please help me?
>
> Thanks a lot
> Branislav
>
> PS:
> desc curr
> Name Null? Type
> ----------------------------------------- -------- ----------------------------
> ID NOT NULL NUMBER(38)
> ORUN_ID NOT NULL NUMBER(5)
> CD NOT NULL VARCHAR2(20)
> MNEMONIC NOT NULL VARCHAR2(20)
> EXCHANGE_FACTOR NOT NULL NUMBER(5,2)
> ISO_CODE NOT NULL VARCHAR2(3)
> DECIMAL_PLACES NOT NULL NUMBER(2)
> EURO NOT NULL VARCHAR2(1)
> PT_VALID NOT NULL VARCHAR2(1)
> UC NOT NULL VARCHAR2(120)
> DC NOT NULL DATE
> UM VARCHAR2(120)
> DM DATE
> ORUN_ID_REAL NOT NULL NUMBER
>
>
> desc curr@eb_link
> Name Null? Type
> ----------------------------------------- -------- ----------------------------
> ID NOT NULL NUMBER(38)
> ORUN_ID NOT NULL NUMBER(5)
> CD NOT NULL VARCHAR2(20)
> MNEMONIC NOT NULL VARCHAR2(20)
> EXCHANGE_FACTOR NOT NULL NUMBER(5,2)
> ISO_CODE NOT NULL VARCHAR2(3)
> DECIMAL_PLACES NOT NULL NUMBER(2)
> EURO NOT NULL VARCHAR2(1)
> PT_VALID NOT NULL VARCHAR2(1)
> UC NOT NULL VARCHAR2(120)
> DC NOT NULL DATE
> UM VARCHAR2(120)
> DM DATE
> ORUN_ID_REAL NOT NULL NUMBER

Thanks Jusung,
I read your older post about it and this was not the case.

I realised (after the post to the newsgroup), that we
do not have the same DBs on both sides:

8.1.5.0.0 ----DB-Link------> 8.1.7

The solution was in:
drop table curr (on the 8.1.5.0.0) DB

and

create table curr
as select * from curr@eb_link;

and then

create snapshot curr

After this recreate of the table from 8.1.7, everything
is working for me.

I think the problem could be in:
1. the DB do not have the same coding => 8.1.7 has UTF8, 8.1.5 has ISO???
2. I have a feeling, that there is a bug in Oracle, if you have
a column varchar2(80) and you have some special characters in it (as
german ä, ö, ü), and if you have 80 chars in a record => the size of the entire
string will be somehow bigger for Oracle....

Branislav