Re: changing data definition

Ray,

Did you try using create table as select * ..with the nologging or unrecoverable option as the comparison to your pl/sql skeleton ?

Chris

Ray Wang wrote:
>
> 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 Sun Apr 11 1999 - 17:01:52 CDT