RE: Rebuild Indexes
- From: "Juan Miranda" <j.miranda@xxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 13 Feb 2004 10:14:14 +0100
Hi
Try this.
This do not use a cursor so you can stop it when you what.
Take care in production because VALIDATE STRUCTURE do some tipe of lock.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++=
+++
-- Juan Miranda Serm=E1tica 06/AGO/2002
-- Utiliza VALIDATE STRUCTURE -> OJO en producci=F3n. Puede generar =
errores
ORA-00054.
-- OJO nolog -> NO usar con stand by
set serveroutput on size 1000000
set pagesize 0
set feedback off
set echo off
set trimspool on
spool c:\reb_index1.sql
DECLARE
dbname varchar2(20);
wday varchar2(11);
BEGIN
dbms_output.put_line('set echo off');
dbms_output.put_line('set feedback off');
dbms_output.put_line('set head off');
dbms_output.put_line('spool c:\reb_index2.sql');
dbms_output.put_line('prompt set feedback on');
dbms_output.put_line('prompt set echo on');
dbms_output.put_line('prompt spool c:\reb_index2.log');
FOR t IN (select owner, index_name from dba_indexes where owner not =
in
('SYS','SYSTEM') order by owner,index_name) LOOP
dbms_output.put_line('prompt --Analizando
'||t.owner||'.'||t.index_name);
dbms_output.put_line('Analyze index '||t.owner||'.'||t.index_name||'
validate structure;');
dbms_output.put_line('select ' || '''' || 'Alter index ' || t.owner =
||
'.' || t.index_name || ' rebuild online;' || ''''|| ' from index_stats =
where
(height > 2) or
(10<=3Ddecode(lf_rows_len,0,NULL,((del_lf_rows_len/lf_rows_len)*100)));')=
;
END LOOP;
dbms_output.put_line('prompt spool off');
dbms_output.put_line('spool off');
dbms_output.put_line('@c:\reb_index2.sql');
END;
/
spool off
@c:\reb_index1.sql
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++=
+++
-----Mensaje original-----
De: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] =
En
nombre de vidya kalyanaraman
Enviado el: viernes, 13 de febrero de 2004 9:46
Para: oracle-l@xxxxxxxxxxxxx
Asunto: Rebuild Indexes=20
Hi
It may be a silly thing to ask, but I am stuck right now.=20
I have been given a task to find out the indexes which need to be=20
rebuilt. There are around 3000 Indexes. I know I can run the following =
command
"analyze index <Index> VALIDATE STRUCTURE " =20
for a single index and then find the rows from index_state based on=20
del_lf_rows_len/lf_rows_len > 20%. =20
Does anyone have a script for dynamically finding out the indexes that=20
are the candidates for rebuilding? How do you normally handle=20
situations like this?
TIA
Vidya
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
