Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


How to delete/remove rollback files?

Posted on 1999-07-26
Medium Priority
Last Modified: 2012-08-13
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.
Question by:hunchback
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Accepted Solution

meowsh earned 120 total points
ID: 1087760
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



Expert Comment

ID: 1087761
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.

Expert Comment

ID: 1087762

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.

Expert Comment

ID: 1087763
Do you have another disk on this system with space?

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question