How to delete large number of rows from a table.?

Rao_S
Rao_S used Ask the Experts™
on
I have to delete about 200000 old rows and when i run a script with a commit point at 100, i get a tablespace error: ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS_01'.
when i run the script at 10 or 50 commit point, i get long runing script error:
ORA-01555: snapshot too old: rollback segment number  with name "" too small
What should i do next?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
How many rows are in the table?  Can you create a new table with the 'saved' rows, truncate the old table then insert the rows back in?

Give more space to your undo tablespace and retry with the 100 commit point (or larger).
Commented:
How big your table? You can delete 200K at one time if your table is not huge.  How much space do you have on UNDOTBS_01? What is the size of the table?

or, you can consider creating new table using CTAS instead of deleting rows.

Create new_table from oldtable (minus 200K rows)

and rename your new_table, drop the old table.
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
I would also look into what is causing the snapshot to old.  If you are commiting every few rows, I'm not sure what would be causing this.  That error is typically caused by the need of the undo for read consistent transactions.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
the table has 1581823 rows, and now the number of rows to delete is down to 44500.
how do i check, how much free space is available? the table data has 2 clob fields. i thought the inconsistant read/transaction could be due to an app writing to it constantly, but for the last 5 mnts, there has been no change to the total count.

Author

Commented:
i tried this:
select * from dba_free_space where tablespace_name = 'SEH_ER_LT' order by bytes desc;
but i get nothing, could be access issues, is something wrong with my select?
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
dba_free_space is actually 'free' unallocated space.  Deleting rows doesn't release the space to 'free'.  The space remains allocated to the table.  If frees up space in the blocks allocated.

After running stats check out the empty_blocks column from user_tables.
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>> i thought the inconsistant read/transaction could be due to an app writing to it constantly,

I guess it's possible but I wouldn't expect deleting 10 rows at a time would cause an ora-1555 when 100 rows doesn't.

I would look at your loop/code that is doing the delete to see if you are selecting more than you really need.

Can you create a temporary table that contains the key fields you will be deleting.  Then you can loop through the temp table and delete from the primary table.  That should help free up the need to query the primary table.
awking00Information Technology Specialist
Commented:
See attached.
tp.sql
Senior Oracle DBA
Commented:
The ORA-01555 in this case is most likely caused by itself.  You probably have a loop that is selecting from the table, then inside the loop you are doing a delete and a commit.  This creates a fetch across commit situation and will definitely cause an ORA-01555.

The easiest way I have found to get around this, is to create a temporary table that has the ROWIDs of the rows that you want to delete.  Then you select the ROWIDs from the temp table and do the delete and commit in the loop.  Since you are not selecting from the table you are deleting, no ORA-01555.

There are better ways to get around a snapshot too old in these situations, but for a one time delete like this, I think the temp table is the easiest way to go.

Author

Commented:
thank you so much for the temp table solution! that is a great solution!
for now, i changed the commit to 100 and i got in touch with my dba, he ran stats and increased the space on the undo tablespace and reran the script. it worked fine!
on the qa database i had to delete 1080499 rows out of a table of 2714962. this one failed on the same 01555 error, he increases the space on redo log and will submitt the job several more times.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>This creates a fetch across commit situation and will definitely cause an ORA-01555.

Excellent point!  I keep forgetting about this one.  I shouldn't, you post it often enough!  :)

Author

Commented:
successfully deleted large number of rows from both the tables.
the query to get tablespace data is very good! as well as the idea to create temp table.
johnsoneSenior Oracle DBA

Commented:
Unfortunately, I run into the fetch across commits so many times, I cannot forget about it.  I wish that developers (and vendors) would learn how to write code for massive updates and deletes against very large (multi-terabyte) databases.  I end up rewriting so many of these I lost track a very long time ago.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial