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

madhu2_at_ix.netcom.com (Vir Madhu ) 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.

The simplest way is to create a new table based on a query of the initial table, drop the old table, then rename to the new one. For example, suppose you have the follwing table.

mytable

id		number(5)
first_name	varchar2(15)
last_name	varchar2(15)
addr		varchar2(30)
phone		varchar2(12)

If you want the new table to have all but the phone column you could create it with the following SQL. CREATE TABLE newtable AS SELECT id, first_name, last_name, addr FROM mytable; DROP TABLE mytable;
RENAME newtable TO mytable;

Then you'd have to recreate any constraints (such as the primary key).

ALTER TABLE mytable ADD CONSTRAINT mytable_pk PRIMARY KEY (id);

HTH. Received on Tue Jul 25 1995 - 00:00:00 CEST