Unable to extend temp on non temp tablespace

[Sybrand Bakker]

On Sun, 05 Jan 2003 23:52:39 +0100, Rick Denoire
<100.17706@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

[Niall Litchfield]

"Rick Denoire" <100.17706@germanynet.de> wrote in message
news:qvch1v82ipjne63dpbv6dl7p6hlv54rua1@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

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

[Rick Denoire]

"Jonathan Lewis" <jonathan@jlcomp.demon.co.uk> wrote:

>
>Following on from Niall's comment -
>it isn't really very convenient to have a
>single 48GB tablespace for your indexes,
>so you may take this opportunity to
>create several small tablespaces and
>distribute the indexes sensibly between them.

I am a little bit surprised by this statement since I always thought
that a tablespace is more a logical concept in order to ease
management as long as all files belonging to it share suitable common
properties. I really can't understand why having several tablespaces
of the same "type" would be better in any way. But I am very willing
to learn.

>Do a google groups (and the FAQ) search for
>locally managed tablespaces for futher ideas
>of improving the ease of management at the
>tablespace level.

I already know the essential advantages of locally managed tablespaces
and still can't figure out why one tablespace should be broken down
into a number of similar tablespaces. I don't know about any physical
constraint applicable to a tablespace as a whole, files can be
distributed on different devices while still belonging to the same
tablespace.

I would appreciate your comments.

Thanks
Rick Denoire

[Rick Denoire]

Sybrand Bakker <gooiditweg@nospam.demon.nl> wrote:

>4 Your file layout should be amended, 4 x 5G + 1 x 28G is *bad*

The only disadvantage that I can see is the so called inode contention
for a file that is too large in size (compared to several files
instead). If you know any other issue due to asymmetric space
distribution between files, please let me know.

In my specific case, a file of 28 GB tends to be a problem at times,
for example, when the file has to be moved to a different device: It
takes longer. That is the reason I asked about shrinking it. Dropping
all indexes / resizing / rebuilding is not a feasible alternative
since that would last at least one day.

Thanks
Rick Denoire

[Jonathan Lewis]

Note the word "convenient". I wouldn't insist
that it was a mistake to have such a large
tablespace - your comment about ease of
management is the critical one, and that's
a feature which should always be given serious
consideration.

A couple of points in the discussion though are:

Although the tablespace is, as you say, a "more
logical" concept, there are still a couple of physical
activities that you might want to associate with a
tablespace. In particular, the tablespace is often the
'sensible' minimum unit of backup - (e.g. for a subset
restore) even though the file is the 'actual' minimum unit
of backup.


Many sites now use rman, but if you don't then
you still need to do 'alter tablespace begin/end backup'
to take a hot backup - and for the duration of the backup
you tend to increase the rate of redo. From a purely theoretical
basis, for randomly scattered block changes, the excess redo
is likely to grow at an n-squared rate compared to the size of
the file.


--
Regards

Jonathan Lewis
[url]http://www.jlcomp.demon.co.uk[/url]

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see [url]http://www.jlcomp.demon.co.uk/tutorial.html[/url] )

Next Seminar dates:
(see [url]http://www.jlcomp.demon.co.uk/seminar.html[/url] )

____England______January 21/23
____USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
[url]http://www.jlcomp.demon.co.uk/faq/ind_faq.html[/url]





Rick Denoire <100.17706@germanynet.de> wrote in message
<8p0k1v4q6e8plpngdab122sgagarule0sj@4ax.com>...

>"Jonathan Lewis" <jonathan@jlcomp.demon.co.uk> wrote:
>

>>
>>Following on from Niall's comment -
>>it isn't really very convenient to have a
>>single 48GB tablespace for your indexes,
>>so you may take this opportunity to
>>create several small tablespaces and
>>distribute the indexes sensibly between them.

>
>I am a little bit surprised by this statement since I always thought
>that a tablespace is more a logical concept in order to ease
>management as long as all files belonging to it share suitable common
>properties. I really can't understand why having several tablespaces
>of the same "type" would be better in any way. But I am very willing
>to learn.
>

.... cut ....

>

files can be

>distributed on different devices while still belonging to the same
>tablespace.
>

[Norman Dunbar]

Hi Rick,

glad you got there in the end !

<RANT>
I loath and detest people who insist on using (or defaulting)
COMPRESS=YES on exports - they cause so many problems later on.
</RANT>

:o)

Cheers,
Norman.

-------------------------------------
Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar@LFS.co.uk
Tel: 0113 289 6265
Fax: 0113 289 3146
URL: [url]http://www.Lynx-FS.com[/url]
-------------------------------------


-----Original Message-----
From: Rick Denoire [mailto:100.17706@germanynet.de]
Posted At: Monday, January 06, 2003 10:58 PM
Posted To: server
Conversation: Unable to extend temp on non temp tablespace
Subject: Re: Unable to extend temp on non temp tablespace


Norman Dunbar <Norman.Dunbar@lfs.co.uk> wrote:

>Hi Rick,
>
>one other thing - do any of the indexes in question have PCTINCREASE

set

>on them ?

No. PCTINCREASE is set to zero. But you did not miss the point too
far. In fact, the problem was identified as being caused by unsuitable
storage parameters. The initial extent was set to high, so while
rebuilding the index, at least as much space in a chunk would be
needed. Probably, the index was rebuild in the past using the compress
option.

Thanks
Rick Denoire

[Paul Brewer]

"Norman Dunbar" <Norman.Dunbar@lfs.co.uk> wrote in message
news:E2F6A70FE45242488C865C3BC1245DA7032172D0@lnew ton.leeds.lfs.co.uk...

> I loath and detest people who insist on using (or defaulting)
> COMPRESS=YES on exports - they cause so many problems later on.

Bingo! (as the teletubbies' vacuum cleaner would say).
Use LMT, though. Then these daft parameters will be ignored.

Regrads ;-)
Paul