Re: How to truncate the data file in a temporary tablespace?

"Ian Ledzion" <ian.ledzion_at_lgxbow.com> wrote in message news:9hv59o$9u5$1_at_rex.ip-plus.net...
> From what I've read & tested, you can't put data into a temporary
> tablespace, as you'd have to create a permanent object (a table) to put
 the
> data into.
>
> If I've missed something, could you let me know?

That's what Niall said... temporary tablespaces cannot contain real or permanent objects, so there's no need to back them up in first place. They do of course contain 'temporary segments' -ie, the stuff that gets created when you do a sort that can't fit into the sort_area_size. But those get cleared of data at the end of the initiating sort, so it remains true that proper temporary tablespace cannot contain permanent objects.

Regards
HJR
>
> Thanks in advance
>
> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message
> news:3b4312c9$0$3757$ed9e5944_at_reading.news.pipex.net...
> > "Dino Hsu" <dino1_nospam_at_ms1.hinet.net> wrote in message
> > news:v1l5kt41beqmoknksl96eugeltb3f0le8n_at_4ax.com...
> > > Dear all,
> > >
> > > One of the data file in a temporary tablespace is extrordinarily big
> > > (almost half the database size). I am going to do a physical off-line
> > > backup, I need to truncate this data file to a small size. Can anyone
> > > tell me how? Thanks in advance.
> > >
> > > Dino
> >
> > 2 comments
> >
> > 1. If this really is a temporary tablespace i.e. one created with
> >
> > CREATE TEMPORARY TABLESPACE blah ....
> >
> > then you do not need to backup the datafile anyway since it cannot
 contain
> > permanent objects.
> >
> > 2. I would consider resizing the datafile using the procedure suggested
 by
> > others and set autoextend OFF. You should also consider upping the sort
> > area size for your database and or look at what operations are causing
> > sorts.
> >
> > regards
> >
> >
> > --
> > Niall Litchfield
> > Oracle DBA
> > Audit Commission UK
> >
> >
> >
> >
>
>
Received on Sat Jul 21 2001 - 16:28:34 CDT