Anurag, I saw that note but I did not try it as I was assured by the developers that the table was not created using CTAS via a database link. I guess I should just go ahead and try it anyway. Thanks. Bill Wagman Univ. of California at Davis IET Campus Data Center wjwagman@xxxxxxxxxxx (530) 754-6208 ________________________________ From: Anurag Varma [mailto:avoracle@xxxxxxxxx] Sent: Friday, March 02, 2007 4:08 PM To: William Wagman Cc: oracle-l@xxxxxxxxxxxxx Subject: Re: Puzzled by datatypes On 3/2/07, William Wagman <wjwagman@xxxxxxxxxxx> 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@xxxxxxxxxxx (530) 754-6208 -- //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