Re: How to resize a tempfile?

From: Richard Foote <richard.foote_at_bigpond.nospam.com>
Date: Mon, 07 Mar 2005 09:40:50 GMT
Message-ID: <CkVWd.187275$K7.105741@news-server.bigpond.net.au>

<vafanassiev_at_aapt.com.au> wrote in message news:1110180378.483025.226380_at_z14g2000cwz.googlegroups.com...
> In Oracle 8i/9i/9.2/10g - how to resize a tempfile if
> a sort segment has grown too big?
> If I try to use
>
> alter database tempfile '/u01/oradata/PROD/temp01.dbf' resize 1000m;
>
> it produces ORA-03297: file contains used data beyond requested RESIZE
> value
>
> So is the any way to drop/resize/"re-initialize" sort segment?
>

Usually, if a temp tablespace reaches a certain size, there's a good chance it'll get to that size again. Therefore, unless storage is particularly scarce, I wouldn't be too concerned. That said, if you really need to resize a temp file, you can either wait until your next downtime when the temp segment will be reduced in size again upon restart (meaning your resize command will work), or simply create a new temp tablespace and reassign your users to it, thereby allowing you to drop the previous one (the technique to do this can differ depending on your version).

Cheers

Richard Received on Mon Mar 07 2005 - 03:40:50 CST