Re: Unable to extend temp on non temp tablespace

"Rick Denoire" <100.17706_at_germanynet.de> wrote in message news:qvch1v82ipjne63dpbv6dl7p6hlv54rua1_at_4ax.com...
> Hello
>
> In the past weeks I am getting following message in the alert.log:
>
> ORA-1652: unable to extend temp segment by 131072 in tablespace
> ALL_INDEX
>
> I don't understand why a temp segment won't be created in the TEMP
> tablespace, since this tablespace has been set als default temp
> tablespace for all users. And what are the size units used here?
> Should I read 131072 MB or KB or what?

Blocks so it should be read as 131072*block_size.

>Anyway, I am surprised since
> there are several GB free in the ALL_INDEX tablespace. I am wondering
> if there is no chunk of continuous free space big enough to allocate
> 131072 whatever-Bytes.

Yes that is exactly what is meant.

>
> The ALL_INDEX tablespace consists of 5 files, four of them are 5 GB in
> size, the fifth is 28 GB. Although of these 28 GB only about 16 are
> shown as "used" by the OEM, the file cannot be shrinked down to a
> smaller size. Is there a way to "free" a db file in order to make it
> smaller or to get rid of it?

Yes, the problem is that the extents that are in that file are scattered around the file. You will need to move the extents out of that file and then either shrink or drop recreate. The best way to do this if you have the disk space is to move the objects out of this tablespace temporarily (or assuming they are indexes drop them) resize the datafile, and then move (or recreate) the objects back. This will almost certainly require downtime.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************