RE: Puzzled by datatypes

From: William Wagman <wjwagman_at_ucdavis.edu>
Date: Fri, 2 Mar 2007 16:29:15 -0800
Message-ID: <FE043305B38A0F448F3924429D650C2A0153FC5E@VEXBE2.ex.ad3.ucdavis.edu>

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_at_ucdavis.edu
(530) 754-6208  

---

From: Anurag Varma [mailto:avoracle_at_gmail.com] Sent: Friday, March 02, 2007 4:08 PM
To: William Wagman
Cc: oracle-l_at_freelists.org
Subject: 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

Received on Fri Mar 02 2007 - 18:29:15 CST