increasing Max extents on index

[jlakhani]

Hello how do i increase max entents on index which has reached max entents of 50 to 500.????
do i have to be in restrict mode because i am getting the resource wait or busy (ora - 54)

thanks
alter database restrict mode???

[Halo]

alter index index_name storage (maxextents 121);

[mary]

I am not sure if I am wright but I was told a dba rule: there shall not be more than 2 extents in the index. If there are more than 2 extents per index it needs to be re-created or rebuilt with the appropriate storage parametrs(i.e. give it a lage initial and large next values)
Senior dba's please correct me if I am wrong.

[Halo]

Well, for one, it's good to rebuild indexes regularly, especially if the associated table undergoes deletes.

[steeve123]

Here's a script i made to rebuild index.
Just save the following in a .sql file and run it in sql plus.
sorry about the comment, there are in french.


--------------------------------------------------------------------

Prompt Ce script va maintenant vous montrer les indexes fragmentés.
Prompt Appuyer sur [ENTER] pour continuer...

pause;

CLEAR BREAK
CLEAR BUFFER
CLEAR COMPUTE
CLEAR COLUMN
CLEAR SQL

select segment_name, segment_type, extents, bytes
from dba_segments
where extents > 5
and segment_type = 'INDEX'
and owner = user
order by extents desc;

SET FEEDBACK OFF
SET TERMOUT OFF
SET LINESIZE 80
SET PAGESIZE 60
set heading off

SPOOL C:\REBUILD.SQL
select 'ALTER INDEX ' || rtrim(substr(segment_name,1,length(segment_name))) || ' REBUILD STORAGE (INITIAL ',
trunc(bytes / 1024 / 1024) + 1 || 'M NEXT 1M PCTINCREASE 0);'
from dba_segments
where extents > 5
and segment_type = 'INDEX'
and owner = user;
SPOOL OFF;

SET FEEDBACK ON
SET TERMOUT ON

Prompt Voici les modification qui vont être éffectués.
Prompt Appuyer sur [ENTER] pour continuer...

pause;

select 'ALTER INDEX ' || rtrim(substr(segment_name,1,length(segment_name))) || ' REBUILD STORAGE (INITIAL ',
trunc(bytes / 1024 / 1024) + 1 || 'M NEXT 1M PCTINCREASE 0);'
from dba_segments
where extents > 5
and segment_type = 'INDEX'
and owner = user;

Prompt Ce script va maintenant recréer les indexes fragmentés.
Prompt Appuyer sur [ENTER] pour continuer...

pause;

@C:\REBUILD.SQL

SET LINESIZE 1000
SET PAGESIZE 60

Prompt Terminer.
pause;

--------------------------------------------------------------------