Re: Puzzled by datatypes

On 3/2/07, William Wagman <wjwagman_at_ucdavis.edu> wrote:
>
> Greetings, I hope someone can explain this to me. I'm trying to move a
> tble from 9i to 10gR2 using import/export. First I am seeing differences
> in the description of a timestamp data type (the DATETIME column) when
> describing a table as opposed to gathering information from the
> dba_tab_cols view. The original table in Oracle 9.2.0.8.0 SE on 32-bit
> RHEL3...
>
> SQL> desc edrs.activityhistory
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> ACTIVITYHISTORYID NOT NULL NUMBER(16)
> EDRSID NUMBER(16)
> CERTIFICATEID NUMBER(16)
> AMENDMENTID NUMBER(16)
> DISPOSITIONID NUMBER(16)
> USERPROFILEID NUMBER(16)
> DATETIME TIMESTAMP(6)
> RECORDSTATUS VARCHAR2(10)
> ACTIONDONE VARCHAR2(50)
> SUBSYSTEM VARCHAR2(15)
> EXECUTIONTIME NUMBER(16)
> USERID VARCHAR2(25)
> PASSWORD VARCHAR2(50)
> REMOTEIP VARCHAR2(15)
> USERDISPLAYNAME VARCHAR2(98)
>
> Whereas the following query...
>
> SQL> l
> 1 select substr(data_type,1,15) data_type,
> 2 data_length,
> 3 data_precision,
> 4 data_scale,column_id,
> 5 substr(column_name,1,15) column_name
> 6 from dba_tab_cols
> 7 where owner = 'EDRS' and table_name = 'ACTIVITYHISTORY'
> 8* order by column_id
>
> Returns...
>
> DATA_TYPE DATA_LENGTH DATA_PRECISION DATA_SCALE COLUMN_ID
> COLUMN_NAME
> --------------- ----------- -------------- ---------- ----------
> ---------------
> NUMBER 22 16 0 1
> ACTIVITYHISTORY
> NUMBER 22 16 0 2 EDRSID
>
> NUMBER 22 16 0 3
> CERTIFICATEID
> NUMBER 22 16 0 4
> AMENDMENTID
> NUMBER 22 16 0 5
> DISPOSITIONID
> NUMBER 22 16 0 6
> USERPROFILEID
> TIMESTAMP(0) 11 0 7
> DATETIME
> VARCHAR2 10 8
> RECORDSTATUS
> VARCHAR2 50 9
> ACTIONDONE
> VARCHAR2 15 10
> SUBSYSTEM
> NUMBER 22 16 0 11
> EXECUTIONTIME
> VARCHAR2 25 12 USERID
>
> VARCHAR2 50 13
> PASSWORD
> VARCHAR2 15 14
> REMOTEIP
> VARCHAR2 98 15
> USERDISPLAYNAME
> DATE 7
> SYS_NC00016$
> VARCHAR2 50
> SYS_NC00017$
> VARCHAR2 25
> SYS_NC00018$
>
> The problem this appears to be causing is that I am trying to move the
> table using export/import to a 10.2.0.3.0 SE database on 64-bit RHEL4
> and upon import I am seeing the error IMP-00020: long column too large
> for column buffer size (7). The import attempts to create the table with
> a datatype of TIMESTAMP(0) for the datetime column. I have tried
> exporting the table with DIRECT=N, precreating the table with
> TIMESTAMP(0) & TIMESTAMP(6), all to no avail. I have found some
> information on Metalink but so far nothing has helped.
>
> Any thoughts? Thanks.
>
> Bill Wagman
> Univ. of California at Davis
> IET Campus Data Center
> wjwagman_at_ucdavis.edu
> (530) 754-6208
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

Bill,

I checked metalink and your issue seems awfully similar to whats described in Note: *286597.1* . The issue was probably caused by bug 2417643

Did you try following the steps listed in that Note?

Anurag

-- 
Anurag Varma

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