Delete large chunk of the rows in one table - help
635106Aug 26 2009 — edited Aug 27 2009I need help for the database is Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
In one table, i want to delete all the records more than 1 year old.
If i run select, it will return 2.2 million records, but when i tried to delete all those 2 million records using "delete" statement, it returned below error.
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
But if i try to delete records less than 1 month old which contains only 105380 rows, it deleted successfully.
My question is , i dont think there is enough undo tablespace, but how can i remove those 2 millions records without manually run" delete from table where rownum < 5000" couples of times. Is there any easy way to delete those big chunk of the records.?