RE: Puzzled by datatypes

  • From: "William Wagman" <wjwagman@xxxxxxxxxxx>
  • To: "Anurag Varma" <avoracle@xxxxxxxxx>
  • Date: Fri, 2 Mar 2007 16:29:15 -0800

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 

Other related posts: