ora-1652

[smacattack]

Trying to create a index in the INDEX tablespace and receiving ora-1652 error.
index initial size 103m next extent 5m.
plenty of space in datafiles to create this index.
default temp tablespace size 1024m 50% free.
sort_area_size = 10240000

any ideas why i cannot create index and keep receiving
error
ORA-1652: unable to extend temp segment by 8666 in tablespace INDEX_TS

thanks

 

[Turkbear]

Hi,
The space allocated for INDEX_TS tablespace is filled..Add a data file or increase the max size, if autoextend is on..


A very quick look at the error messages documentation would have found this...





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[smacattack]

As i said in the original message the index_ts tablesapce has plenty of free space to accomodate the size of the new index.
Why does it still error with space allocation problem?

I am creating file not rebuilding index therefore the space required in tablesapce datafile should only equal the initial size plus next extent surely. The datafiles have at least 300m available in some files.

Any other useful advice please

thanks

 

[KenCunningham]

Perhaps the tablespace is so fragmented it can't find the necessary contiguous space for the next extent.

 

[smacattack]

This is a more reasonable explanation ken thanks.
But how do i fix this, any scripts available to anlayze what is fragmenting this area.
We have had a few performance issues and this is why i am trying to rebuild and re-create certain indexes.
thanks

 

[KenCunningham]

I don't know of any scripts off the top of my head, but Dave (SantaMufasa) or any of the other regulars here may do so - certainly I've seen reference to 'Swiss-cheese' in several posts (meaning that the tablespace has many data-free 'holes', none of which is large enough to create a next extent). I believe one method of setting this right is to export and then re-import, so that the data comes in nice and sequentially. I'll get back to you when I've done some more digging.

 

[KenCunningham]

I just did a keyword search in this forum for 'swiss'. There are quite a few hits, such as Thread186-670254 which will hopefully be what you require.

 

[stefanhei]

Smacattack,
you probably still use dictionary managed tablespaces?
Then you may try:

alter tablespace INDEX_TS coalesce

which will reduce free space fragmentation a bit.

Stefan