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

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?

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:26:50 CDT