Re: temp file useage on 10.2..0.4 enterprise database / windows 2003
Date: Wed, 15 Sep 2010 06:20:32 -0700 (PDT)
Message-ID: <4c755411-da0c-4a9c-87a3-74bf8e31566d_at_n7g2000vbo.googlegroups.com>
On Sep 14, 12:04 pm, ddf <orat..._at_msn.com> wrote:
> On Sep 14, 6:13 am, dba cjb <chris.br..._at_providentinsurance.co.uk>
> wrote:
>
> > We have a database using temp space with approx 3 * 60 gig files
> > / I woud like to drop a temp file but am not sure on getting
> > max_used_blocks from v$sort_segment out of AWR
>
> > ie what's the max space used in temp from awr history rather than when
> > instance was bounced
>
> > regards
> > Chris B
>
> AFAIK that metric is not recorded in the AWR repository; it's reported
> from the v$tempseg_usage view.
>
> David Fitzjarrell
The information in dba_temp_files may also be of interest. The traditional way to resize the temporary tablespace is to drop and recreate the temporary tablespace. You might also try shrinking the temp files. Shrinking is most likely to be effective if attempted immediately after re-starting the instance in restricted mode, which is also the easiest time to drop and re-create the temporay tablespace.
If you need to resize the temporary tablespace while the sytem is in use then creating a new temporary tablespace, making it the default and/or switching all the users to default to it, then waiting for existing tasks to complete, finally followed by a drop of the old temporary tablespace is another approach.
HTH -- Mark D Powell -- Received on Wed Sep 15 2010 - 08:20:32 CDT