RE: RE: Create Table..As Select: Number formats

I'm perhaps a little late replying, but have you looked at using

"alter table xxx modify (column number(1,2));" for example to modify the number format after creating the partition. I'm expecting there to be limits but it sounds like you are only trying to increase the number type size not decrease it. Also, you might wear some table scans while it inspects data depending on the change but might be acceptable depending on volumes and timeframes.

                                                                                                                                      
                      Simon.Anderson_at_sc                                                                                               
                      isys.co.uk               To:       Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>                  
                      Sent by:                 cc:                                                                                    
                      ml-errors_at_fatcity        Subject:  RE: RE: Create Table..As Select: Number formats                              
                      .com                                                                                                            
                                                                                                                                      
                                                                                                                                      
                      02/10/2003 03:09                                                                                                
                      Please respond to                                                                                               
                      ORACLE-L                                                                                                        
                                                                                                                                      
                                                                                                                                      

Thanks to everyone who responded to this thread -

Option A is now to persuade the designers to remove the Number formatting from the parttioned table,
Option B is to pre-create the working table and populate it with Truncate and Insert /* Append */

Option B will be slower, I think, due to the extra redo/undo generated despite my best efforts to persuade it otherwise. I've done enough tests with changing the format of the number columns to convince myself that that the calculated Number columns are the only remaining issue.

Cheers
Simon Anderson

>>I'm trying to create a table using 'Create
>>Table...As Select...'

...
> Are you sure that it comes from the NUMBER() columns? Reminds me of the
problem when you have a NULL in a UNION,
> which must be explicitly cast with a to_number(), to_date() or
to_char(). Might it come from some NULL in your CREATE > TABLE AS SELECT ... ?
>
> Regards,
>
> Stephane Faroult
> Oriole

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
  INET: Simon.Anderson_at_scisys.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
   Privileged/Confidential information may be contained in this message.
          If you are not the addressee indicated in this message
       (or responsible for delivery of the message to such person),
            you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
           by reply e-mail or by telephone on (61 3) 9612-6999.
   Please advise immediately if you or your employer does not consent to
                Internet e-mail for messages of this kind.
        Opinions, conclusions and other information in this message
              that do not relate to the official business of
                         Transurban City Link Ltd
         shall be understood as neither given nor endorsed by it.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>




<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone.
In such a case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (03) 9612-6999 or (61) 3 9612-6999.
Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message that do not relate to the official business of Transurban Infrastructure Developments Limited and CityLink Melbourne Limited shall be understood as neither given nor endorsed by them.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mark Richard
  INET: mrichard_at_transurban.com.au

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).