Re: Help - Index drop and rebuild script
From: Kevin Gillins <kevin_gillins_at_compuserve.com>
Date: Wed, 15 Sep 1999 21:56:09 -0600
Message-ID: <7rpj01$58r$3_at_ssauraaa-i-1.production.compuserve.com>
column pos noprint
select
Date: Wed, 15 Sep 1999 21:56:09 -0600
Message-ID: <7rpj01$58r$3_at_ssauraaa-i-1.production.compuserve.com>
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Here is a script I have that will generate the index ddl. See if this can be adjusted to help you out.
set pagesize 0
set pause off
set verify off
set feedback off
column owner noprint new_value _own
column tbl noprint new_value _tbl column idx noprint new_value _idx column unq noprint new_value _unq column tbs noprint new_value _tbs
column pos noprint
select
owner,
table_name tbl,
index_name idx,
decode(substr(uniqueness,1,1),'U','unique') unq,
tablespace_name tbs
from dba_indexes
where index_name = upper('&index');
spool fix_idx.sql
select 'conn &_own/&_own' from dual;
select 'lock table &_tbl in exclusive mode;' from dual;
select 'drop index &_idx;' from dual;
select 'create &_unq index &_idx on &_tbl (' from dual;
select decode(rownum,1,column_name,','||column_name) from dba_ind_columns where index_owner = '&_own' and index_name = '&_idx' order by column_position;
select ') tablespace &_tbs' from dual;
select 'storage (initial ?' from dual;
select 'next ?' from dual;
select ');' from dual;
select 'conn system' from dual;
spool off
ed fix_idx
set verify on
set feedback on
-- Kevin Gillins Management Information Consulting Boston Office 978-887-3300 noone <no_at_nospam.com> wrote in message news:37E0432E.80BEFE36_at_nospam.com... I am looking for a generic drop and rebuild PL/SQL script to drop and recreate all the indexes for a specific table. I want to integrate into a script that might have different at each client site but the same table structure. Does anyone have something pre-built for this or a similar task? It will save me a lot of nights.... Thanks Brad ------=_NextPart_000_0193_01BEFFC5.1E0C10E0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printableReceived on Thu Sep 16 1999 - 05:56:09 CEST
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Diso-8859-1" =
http-equiv=3DContent-Type>
<META content=3D"MSHTML 5.00.2314.1000" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV>Here is a script I have that will generate the index ddl. See =
if this=20 can be adjusted to help you out.</DIV>
<DIV> </DIV>
<DIV>
<HR>
<BR><FONT face=3D"Courier New" size=3D2>set pagesize 0<BR>set pause =
off<BR>set=20 verify off<BR>set feedback off</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>column owner noprint new_value=20
_own<BR>column tbl noprint new_value _tbl<BR>column idx noprint = new_value=20 _idx<BR>column unq noprint new_value _unq<BR>column tbs noprint = new_value=20 _tbs<BR>column pos noprint<BR>select<BR> = owner,<BR> =20 table_name tbl,<BR> index_name idx,<BR> =20 decode(substr(uniqueness,1,1),'U','unique') unq,<BR> = tablespace_name=20 tbs<BR>from dba_indexes<BR>where index_name =3D = upper('&index');</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>spool fix_idx.sql</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>select 'conn =
&_own/&_own' from=20 dual;</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>select 'lock table &_tbl in =
exclusive=20 mode;' from dual;</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>select 'drop index &_idx;' =
from=20 dual;</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>select 'create &_unq index =
&_idx on=20 &_tbl (' from dual;</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>select=20
decode(rownum,1,column_name,','||column_name)<BR>from dba_ind_columns = where=20 index_owner =3D '&_own' and<BR>index_name =3D '&_idx' order by=20 column_position;</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>select ') tablespace &_tbs' =
from=20 dual;</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>select 'storage (initial ?' =
from=20 dual;</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>select 'next ?' from =
dual;</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>select ');' from =
dual;<BR>select 'conn=20 system' from dual;</FONT></DIV>
<DIV> </DIV>
<DIV><BR><FONT face=3D"Courier New" size=3D2>spool off</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>ed fix_idx</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>set verify on<BR>set feedback=20
on</FONT></DIV>
<DIV> </DIV>
<DIV>
<HR>
</DIV>
<DIV><BR>-- <BR><BR>Kevin Gillins<BR>Management Information =
Consulting<BR>Boston=20 Office<BR>978-887-3300</DIV>
<DIV> </DIV>
<DIV> </DIV>
<BLOCKQUOTE=20
style=3D"BORDER-LEFT: #000000 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: = 0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px"> <DIV>noone <<A href=3D"mailto:no_at_nospam.com">no_at_nospam.com</A>> = wrote in=20 message <A=20 = href=3D"news:37E0432E.80BEFE36_at_nospam.com">news:37E0432E.80BEFE36_at_nospam.= com</A>...</DIV>I=20 am looking for a generic drop and rebuild PL/SQL <BR>script to drop = and=20 recreate all the indexes for a specific table.<BR><BR>I want to = integrate into=20 a script that might have different<BR>at each client site but the same = table=20 structure.<BR><BR>Does anyone have something pre-built for this or a = similar=20 task?<BR><BR>It will save me a lot of=20 nights....<BR><BR>Thanks<BR><BR>Brad</BLOCKQUOTE></BODY></HTML> ------=_NextPart_000_0193_01BEFFC5.1E0C10E0--