ora-1659

[default]

Hi,

I want to move an index from tablespace TS1 to tablespace TS2. Tablespace TS2 has 700mb free. The index size is 100mb.

I tried to do this with alter index 'index_name' rebuild online storage(initial 5m next 5m minextents 2 pctincrease 0) tablespace ts02;

It is giving me the ora-01659: unable to allocate MINEXTENTS beyond 8 in tablespace TS2.

Does any one know why?

[Shestakov]

U have fragmentation of free extents.
Before create (or rebuild) index execute:
alter tablespace TS2 coalesce;

[default]

Hi,

Coalesceing the tablespace did not help.

Any Idea Why?

[Shestakov]

Check size of largest free extent.
Then try to decrease inital/next extent size little bit less then max free extent.
U can use this script for check:
koef of free extents fragmentation
max size of free extent.

COLUMN tablespace FORMAT A20 TRUNCATE heading 'Tablespace|name' justify left
COLUMN Total FORMAT 9999990 heading 'Size|(MB)' justify right
COLUMN used FORMAT 9999990 heading 'Used|(MB)' justify right
COLUMN free FORMAT 9999990 heading 'Free|(MB)' justify right
COLUMN pct_free FORMAT 9999.99 heading '% Free' justify right
COLUMN largest FORMAT 9999990 heading 'Largest|Free|Extent' justify right
COLUMN fragment FORMAT 9999990 heading '# free|exetents' justify right
COLUMN k_frag FORMAT 9999.99 heading 'Fragment|Ratio' justify right

select substr(a.tablespace_name,1,13) tablespace,
round(sum(a.total1)/1024/1024, 1) Total,
round(sum(a.total1)/1024/1024, 1)-round(sum(a.sum1)/1024/1024, 1) used,
round(sum(a.sum1)/1024/1024, 1) free,
round(sum(a.sum1)/1024/1024, 1)*100/round(sum(a.total1)/1024/1024, 1) pct_free,
round(sum(a.maxb)/1024/1024, 1) largest,
max(a.cnt) fragment,
100 - max(a.k_frag) k_frag
from
(select tablespace_name,
0 total1,
sum(bytes) sum1,
max(bytes) MAXB,
count(bytes) cnt,
sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)))) k_frag
from dba_free_space
group by tablespace_name
union
select tablespace_name,
sum(bytes) total1,
0,
0,
0,
0
from dba_data_files
group by tablespace_name) a
group by a.tablespace_name
order by 8 desc
/

COLUMN tablespace clear
COLUMN Total clear
COLUMN used clear
COLUMN free clear
COLUMN pct_free clear
COLUMN largest clear
COLUMN fragment clear
COLUMN k_frag clear