Re: Relocating undo tablespace

EdStevens wrote:
> Platform: Oracle 9.2.0.6 on Solaris 5.9
>
> Hit a snag today on a box that holds two instances - one for dev, one
> for QA for the same app. Started getting alerts that two of the file
> systems had filled up. The SA had recently added a LUN to another disk
> group, so we created a couple of 'overflow' file systems there. One of
> the full FS's had normal data files, and I was able to relocate some of
> them to these new file systems on the other disk group. The other
> tablespace has only one of the control file mirrors, and the undo
> tablespace. I'm a little antsy about relocating that one. The
> procedure I used for the normal data files was:
> 1) offline the TS,
> 2) move the data file,
> 3) alter database rename file,
> 4) online the TS
>
> Doesn't seem like this would work for the one and only undo tablespace.
> Am thinking of treating it like the system TS ..
>

> 1) shutdown
> 2) move the data file
> 3) startup mount
> 4) alter database rename file
> 5) alter database open
>
> Am I on track or getting ready to drive over a cliff?

It's nice to be able to sleep on a problem instead of grasping the first solution that comes to mind.

Last night it struck me there was a simpler, and in my case better, solution. The only files on this particular FS were the UNDO and a control file copy from each of the two instances on the box. If I moved the UNDO, the FS would be nearly empty and unused. The UNDO isn't *that* much different from other table spaces .... why not just resize the file in place?

ALTER DATABASE DATAFILE ... RESIZE ... Poof! My FS usage went from 100% to 70%. The SA is happy, I'm happy, my files remain in their 'standard' locations ... Received on Wed Mar 08 2006 - 09:34:39 CST