FreeLists / oracle-l / Puzzled by datatypes

["William Wagman" <wjwagman@xxxxxxxxxxx>]

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
--
http://www.freelists.org/webpage/oracle-l