SQL Script Rebuild Unusable Indexes

Updated: 24-Nov-2004
Version: 1.0

Description

This script rebuilds unusable indexes.

Parameters

&1 - index owner

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

set verify off
set pause off
set doc off
set heading off

spool rebuildunusable.lst

define iowner = UPPER('&1')

rem accept iowner prompt 'Enter Index Owner : '

prompt
prompt Rebuilding Unusable Indexes for &iowner
prompt

set serverout on

DECLARE	

sql_stmt varchar2(1024);	

l_index_owner all_indexes.owner%TYPE := &iowner;

cursor get_ind is
   select owner,index_name from dba_indexes
   where owner = UPPER(l_index_owner)
   and (index_type not like 'IOT%' 
   AND index_type not like 'LOB%')  /* this operation is not supported on IOT/LOB indexes */
   and status = 'UNUSABLE' ;
   /* and dropped='NO' ;   (10g) exclude objects in the recyclebin */

BEGIN

   FOR ind_rec in get_ind LOOP

      sql_stmt := 'alter index '||ind_rec.owner||'.'||ind_rec.index_name
               ||' rebuild online ';

      dbms_output.put_line(sql_stmt);
  
      EXECUTE IMMEDIATE sql_stmt;

   END LOOP;

END;
/

REM 10g recycle bin
REM PROMPT
REM PROMPT Purging Recycle Bin
REM PROMPT
REM
purge dba_recyclebin ;

set heading on pages 2000
col owner form a20
col status form a10

select owner,status, count(*)
from dba_indexes
where owner  = &iowner
group by owner,status
/

spool off

Return to Index of SQL Scripts

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links