Bulk delete via insert - full undo space

Question

i have a table with 100 million of records.
I want delete 60% of it. Now i read that i should copy the table and insert the valid values.(because its faster)

Example: INSERT INTO Person_New SELECT * FROM Person_old where p.name is not null.

Problem: After a few minutes my undo- space is full AND i can't! resize it because i had no permissions for it.

Question: Should i split the insert in many parts for example with id range? Or is there a better way?

Edit for first comment Error-Output:
ORA-30036: unable to extend segment by 8 in undo tablespace ...

Answer 1

The best option is to avoid the UNDO generation using a DDL statement:

CREATE TABLE PERSON_NEW NOLOGGING AS
   SELECT * FROM Person_old where p.name is not null

The NOLOGGING is to avoid the REDO generation and perform the creation faster.

However, if you must perform an INSERT, consider a direct-path insert using the /*+APPEND*/ hint:

INSERT /*+APPEND*/ INTO PERSON_NEW
  SELECT * FROM Person_old where p.name is not null

Creating the table PERSON_NEW with the NOLOGGING attribute it will be useful (and also dangerous), but using the NOLOGGING attribute inside the DML operation take no effect.