RE: Puzzled by datatypes

From: William Wagman <wjwagman_at_ucdavis.edu>
Date: Wed, 7 Mar 2007 10:43:30 -0800
Message-ID: <FE043305B38A0F448F3924429D650C2A0163162D@VEXBE2.ex.ad3.ucdavis.edu>

Anurag,  

Harking back to the discussion of last week, the statement  

alter table activityhistory modify (datetime timestamp(6));  

resolved the issue. The discouraging thing is that I opened an SR with Oracle a week ago and they still haven't figured it out. Shhesh.  

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:55 PM
To: William Wagman
Cc: oracle-l_at_freelists.org
Subject: Re: Puzzled by datatypes

Bill,

Its possible that developers forgot how it was created .. or they might have created it
from a table which had the problem in the first place. The key point is that the
problem exists and the resolution is noted in the metalink Note...

For example:

ORA92> create table x1 as select * from x1_at_remotedb;

Table created.

ORA92> desc x1

 Name                                                              Null?

Type

---

• -------------------------------------------- A NUMBER B TIMESTAMP(6)

ORA92> select column_name, data_type, data_length, data_scale from user_tab_columns where table_name = 'X1';

COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_SCALE

-------------------- -------------------- ----------- ---------- 
A                    NUMBER                        22
B                    TIMESTAMP(0)                  11          0

ORA92> create table x2 as select * from x1;

Table created.

ORA92> desc x2

 Name                                                              Null?

Type

---

• -------------------------------------------- A NUMBER B TIMESTAMP(6)

ORA92> select column_name, data_type, data_length, data_scale from user_tab_columns where table_name = 'X2';

COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_SCALE

-------------------- -------------------- ----------- ----------
A                    NUMBER                        22 
B                    TIMESTAMP(0)                  11          0

Now I can say that X2 was not created via CTAS from remote db link :)

Let us know if the fix in that note solved your issue ... I can see this bug to potentially
cause issues for folks who are using exp/imp to upgrade dbs...

Anurag

On 3/2/07, William Wagman < wjwagman_at_ucdavis.edu <mailto:wjwagman_at_ucdavis.edu> > wrote:

        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 

	 

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Mar 07 2007 - 12:43:30 CST