rbs1orcl.ora file

[Sjoker]

I've got a file called rbs1orcl.ora which is 5 Gb. I can't delete it, how can I reduce the size of this file?

 

[karluk]

Based on the file name this is probably a datafile for your rollback segment tablespace. In general datafiles can be resized using an alter database command such as

ALTER DATABASE DATAFILE '<full_path_name>' RESIZE 1000M;

If you reduce the size of the datafile, as you are planning, there can't be any objects that have space allocated in the portion of the file that is to be deleted. If you have this problem you can try to shrink your rollback segments to release the extra space.

I can't recall whether the tablespace needs to be offline in order to do the file resize. I don't think so, but I'm not sure.

I would also caution you not to reduce the size of the rollback segment tablespace too far. Large transactions can generate lots of rollback info, and you may find yourself needing the extra space. In fact I would guess that's the reason the file is so large to begin with - someone expanded the rollback segment tablespace in order to enable a very large transaction to complete.

 

[DBAwhosaysNIE]

TS doesn't have to be offline to resize

if resize bombs query dba_extents to see which segment(s) have extents beyond the desired resize value. if necessary you can drop the rollback segments, resize datafile and recreate them w/identical storage parameters.

finally, I personally don't use datafiles > 2G. yes, Oracle has supported them for some time now but many Unix commands (compress, scp, etc.) still don't (at least on Solaris).