Is it okay to delete a datafile???

[AQWQ]

Is it okay to delete a datafile??My rollback segment goes on increasing and when i try to reset it its not getting back to the normal size.AM using oracle8 on a Unix machine. So for the time being am adding datafiles to solve this issue!!But i don have enough disk space to add more datafiles.So is there any cponsequence if i delete the old datafiles?? someone please help me how to reset the rollback segment.

Thanx in advance.. Santhosh Ravindran

 

[raygg]

It is probably the same on unix as NT - I have only done this on NT. First create a 5 or 10 new rollbacks with their own new datafile and alter the datafile and the tablespace and the rbs online. Set up each one with 8 to 20 segments. The example below will shrink the rollback segment back to 4M if it gets larger than that. If it keeps shrinking back all the time then you need to make a bigger optimal size.

CREATE PUBLIC ROLLBACK SEGMENT "RBS1"
TABLESPACE "RBS"
STORAGE ( INITIAL 512K NEXT 512K OPTIMAL 4096K MINEXTENTS 8
MAXEXTENTS 4096)

To activate a rollback you must add it to the init.ora file with a line like this. The default init.ora file has a commented out line like this.

rollback_segments = ( RBS0, RBS1, RBS2, RBS3, RBS4, RBS5 )

Add them to the init.ora file and eliminate the old rollbacks from the init.ora file list. Restart the database
and now you can set the old rollbacks offline, delete them and delete the old rollback datafiles. Check the Oracle doc and set the optimal size on the rollbacks so they will shrink back to a size you specify.

 

[carp]

I would probably NOT delete a datafile if I could avoid it. If your rollback segment won't shrink, it's probably because it has too many transactions using it so it can't drop any extents. You might try taking it offline, and, once the transactions have completed, either try shrinking it again or dropping/recreating it.

The phrase "my rollback segment" implies you only have one rollback segment. You might want to create multiple rollback segments to decrease the number of transactions per rollback segment. Not only will your rollback segments be more manageable, but you will also have more efficient transaction processing as a result.

 

[raygg]

Identify which sql statements are creating the large rollbacks - you may want to check with either the developers or the production maintenance staff to see if they are running sql statements that should have more frequent commits (such as after every 1000 or so updates or deletes). Adds to tables do not have much impact on rollbacks - just deletes and updates. If the rollbacks are growing because of large table deletes, in some cases, truncates can be used instead of deletes. Truncates do not fill the rollbacks, but dropping large tables before truncating or fully deleting all the rows will.

 

[carp]

At the risk of taking off on a tangent, dropping tables has no more effect on the rollback segments than truncating the table; they're both DDL statements and only affect the system rollback segment.

 

[raygg]

My error. Thanks for pointing this out.