Search code examples
oracle-databaseperformancesql-deletebulk-operations

delete large data from a large table oracle


I have a table with 400 million records, with one clob column. Size of table is around 30 gb.

I want to delete 250 million records from it.

I have tried

  • traditional delete in small chunks.
  • create table as select ...
  • insert into select...

All efforts failed, as redo logs were filled, or something happened and session got stuck for over one hour so I killed session.

Any suggestions or method to delete such huge data?


Solution

  • I would do the following:

    • CREATED TABLE new_table as select * from old_table where condition
    • add indexes on new_table
    • add grants on new_table
    • add constraints on new_table
    • the rest
    • DROP TABLE old_table
    • rename new_table to old_table