[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

How to delete/remove rollback files?

i cannot delete a very large table because when doing so - it actually needed more space to write records to the rollback file. So now the HardDisk is filled to the brink..

How can i delete the rollback file - i tried to substitute it with a zero byte file of the same name but Oracle won't be fooled.
  • 2
1 Solution
I dont think you can delete a rollback file.

I think you need to establish whether or not the transaction you are carrying out needs to be completed as one whole transaction or whether you can split it down into several sub-transactions which can be commited as you go along (when you commit you 'clear' the rollback segment).

If you need one whole transaction then you need to increase the size of the rollback file.  Try ....

Alter tablespace <rollback>
add datafile '<LOCATION OF FILE>' size <LARGE VALUE>;

You could also examine the dba datadictionary view dba_data_files to see what datafiles you have at the moment in your rollback segment.

If you dont need one whole transaction then break it down in to logical chunks and issue 'commit' statements as you go thus freeing up the rollback segment.

Best Wishes


if you want to delete a table without any where clause,
you can simply truncate table.
(e.g. truncate table my_table)
i hope it solves your current problem.

Perhaps it is worth adding that if you issue a truncate statement that it will

1. Delete all records
2. Happen immediately
3. It is 'un-rollbackable'.  i.e. you cannot then rollback the transaction should you want to.  Truncate bypasses a large part of the Oracle dataserver and hence you cannot rollback should you realise you have made an error.

Use it with caution.

If you dont want to delete the whole table and hence want to delete certain rows then consider my original answer of either breaking the delete down into logical steps or increasing the rollback segment by adding a new datafile.
Do you have another disk on this system with space?

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now