Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: changing data definition

Re: changing data definition

From: Ray Wang <z2156808_at_student.unsw.edu.au>
Date: Fri, 09 Apr 1999 21:03:55 +1000
Message-ID: <370DDE9B.3E767D59@student.unsw.edu.au>


yeah, i've written up pl/sql to create skeleton of the table and do direct insert with nologgin option ... turns out to be faster than create table as select *

only drawback is that redo is not written but thats not a big deal in this situation.

thanks anyway

--ray

alanlee88_at_my-dejanews.com wrote:
>
> The only way that I can think of is to create a temporary table with exactly
> the same definition and populate it with all the data. Empty that column and
> change the precision (alter table modify..blah..) and do a insert into
> original_table (select * from tmp_table where key1=key2..).
>
> Hope it helps.
>
> Alan Lee
> BSI Consulting
> Houston, Texas
>
> In article <3709DC81.93C858EB_at_student.unsw.edu.au>,
> Ray Wang <z2156808_at_student.unsw.edu.au> wrote:
> > how can i alter a columns type from number to number(15,2)
> >
> > of course the column is populated .... i need a way to make this
> > conversion without emptying the columns.
> >
> > say
> >
> > alter table tablename ( id number(15,2));
> >
> > id used to be number
> >
> > oracle would spit out cannot decrease precision ... blah blah.
> > i understand the inconsistency involved but is there any way around this
> > problem?
> >
> > i know i can spool to a flat file and do a direct load .
> > or
> > create a skeleton and insert
> >
> > both of which will have a time penalty as opposing to
> > just alter.
> >
> > is there anyway to do a create table that can do the data type
> > conversion implicitly?
> >
> > bear in mind i am altering thecolumn type here not data type ...
> >
> > any help will greatly appreciated.
> >
> > thanks
> >
> > --ray
> >
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Fri Apr 09 1999 - 06:03:55 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US