0

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?

APC
  • 144,005
  • 19
  • 170
  • 281
Vineesh Choudhary
  • 23
  • 8
  • "40 crore" can't understand what this means, can you clarify please? – Jorge Campos Dec 13 '17 at 16:36
  • Ok, it is from indian numbering systems. It may be better to use common terms in your question and future ones. – Jorge Campos Dec 13 '17 at 16:38
  • For Non-Indian users :- 40 crore = 400 million. – Kaushik Nayak Dec 13 '17 at 16:42
  • 1
    Maybe if you ask it in http://dba.stackexchange.com you will get a better specific attention? – Jorge Campos Dec 13 '17 at 16:44
  • see here maybe? https://asktom.oracle.com/pls/apex/asktom.search?tag=deleting-many-rows-from-a-big-table – OldProgrammer Dec 13 '17 at 16:45
  • Are there any triggers associated with those tables? – arunb2w Dec 13 '17 at 18:18
  • If your redo logs are filling up, run the CTAS with the nologging option. – Sam M Dec 14 '17 at 02:56
  • It's a bit hard to give advice without knowing the precise reasons why each of the approaches you listed failed. For instance, what do you mean that the session was stuck for an hour? What is "stuck"? Which approach was that? Please bear in mind that you are dealing with a large amount of data. Wrangling that takes a lot of crunch and/or a lot of time. What strategies are open to you depends on how powerful your server is, what your elapsed time expectations are and what else your system has to do in that time window. – APC Dec 21 '17 at 16:26

1 Answers1

0

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
Jucan
  • 421
  • 4
  • 8