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.
hunchbackAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

meowshCommented:
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

Meowsh

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hakangCommented:
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.
hakang
0
meowshCommented:

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.
0
mshaikhCommented:
Do you have another disk on this system with space?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.