-
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???
-
alter index index_name storage (maxextents 121);
-
too many extents for your index
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.
-
Well, for one, it's good to rebuild indexes regularly, especially if the associated table undergoes deletes.
-
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;
--------------------------------------------------------------------
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|