Re: Reduce the No. of Fields in a Oracle Table

mcdonaldr_at_logica.com (Russell McDonald) wrote:

>lorenzen_at_winternet.com (James Lorenzen) wrote:
 

>>Vir Madhu (madhu2_at_ix.netcom.com) wrote:
>>: We are re_writing a Oracle application and as a part of that, I have
>>: to reduce no. of fields in the table( consisting of 85 fields ) by
>>: eliminating 5 fields. I am writing a program in Pro*C that reads all
>>: but those 5 fields and insert into a new table i.e., the new table will
>>: have 80 fields. There are 1 million records in this table. I am new to
>>: oracle application development and wondering if there is a Standard
>>: Oracle Tool that does this. Any pointers from the knowledgeable netters
>>: will be highly appreciated.
>>: Thank you
>>: Madhu.
 

>>It would be much simpler to either create the new table, selecting all
>>except the the five dropped columns.
>> CREATE TABLE tablename AS SELECT [the 80 columns] FROM [source table]
>>-- or -
>> create the table and then insert into it:
>> INSERT INTO tablename SELECT [the 80 columns] FROM [source table]
 

>>HTH
>> James
 

>It would be easier to write as James suggests but running it would be
>a problem. You would have a lot of problems with rollback segment size
>trying to insert 1 million rows as one transaction. Unless you can set
>aside a large amount of space for a huge rollback segment I think you
>will need commit points every n records during the insert.
 

>Russell

How does Oracle handle snapshotting if large tables then? Say you're trying to create a snapshot on a 3 million row table. I've got to do this in a few months. I don't think there's even disk space to hold double the space of that table which unless I'm mistaken in how big the rollback segment will have to be. Do I have to write a custom snapshot procedure to commit every 1000 or so rows? Should I modify the system snapshot package to do this?

--
Chuck Hamilton
chuckh_at_ix.netcom.com

If at first you don't succeed, sky-diving isn't for you.