copy 25 million records from one table to another table in oracle [duplicate]

This question already has answers here: Closed 11 years ago.

Possible Duplicate:

What is the fastest way to insert data into an Oracle table?

Table has 25 million records. I need to add new column datatype is date in this table and copy data into this new column from old column under same table but old column has timestamp datatype. I am doing following steps, Can you please let me know any other way i can do it. When i run follwing queries it running 6 or 7 hours and then i have to kill it. Database is oracle.

alter table  ofr_ft rename to  ofr_ft_bkup;

CREATE TABLE ofr_ft ( 
all old columns,
        age      DATE NOT NULL,
CONSTRAINT ofr_ft_pk
PR开发者_开发知识库IMARY KEY (ofr_ft_id)
);

INSERT INTO ofr_ft
            (old coumns,
             age)
   (values from old columns,
           cast(date_last_chng as date)
      FROM ofr_ft_bkup);

COMMIT;

---

Why do you want to create a new table?

alter table mytable add (newcolumn date);
update mytable set newcolumn = oldcolumn;
alter table mytable drop (oldcolumn);

if the update doesn't work because the rollback segment is too small, something like that should do the trick:

alter table mytable add (newcolumn date);
begin
  loop
    update mytable set newcolumn = oldcolumn
      where oldcolumn is not null
        and newcolumn is null
        and rownum<=10000;
    exit when sql%rowcount=0;
    commit;
  end loop;
end;
/
alter table mytable drop (oldcolumn);

---

It's usually faster to disable the keys first, do the inserts, then enable the keys after.

Also, investigate if it's faster when not in a transaction.