Puzzled by datatypes

From: William Wagman <wjwagman_at_ucdavis.edu>
Date: Fri, 2 Mar 2007 10:48:27 -0800
Message-ID: <FE043305B38A0F448F3924429D650C2A0153FA93@VEXBE2.ex.ad3.ucdavis.edu>

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

Received on Fri Mar 02 2007 - 12:48:27 CST