Re: Unable to extend temp on non temp tablespace

On Sun, 05 Jan 2003 23:52:39 +0100, Rick Denoire <100.17706_at_germanynet.de> wrote:

>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? 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.
>
>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?
>
>Using Oracle 8.1.7 on Solaris 7/Sun E450.
>
>Thanks a lot
>Rick Denoire

1 When an index is rebuilt, it will initially be treated as a temporary segment in your index tablespace 2
The unit is in *blocks* as you could have gathered from the docs. Hence, without knowing the blocksize it is hard to say how many space you need
3 You can free a db file in your case, by dropping all the indexes in the tablespace and recreating them.
4 Your file layout should be amended, 4 x 5G + 1 x 28G is *bad*

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sun Jan 05 2003 - 17:04:31 CST