Re: Rebuild implicit index for primary key

From: sybrandb <sybrandb_at_gmail.com>
Date: 6 Jan 2007 11:11:46 -0800
Message-ID: <1168110705.898854.158590@11g2000cwr.googlegroups.com>

Mark D Powell wrote:
> Franz Kruse wrote:
> > Jim Smith:
> >
> > > ...
> > > Or you could use an anonymous PL/SQL block
> > >
> > > declare
> > > sqlstmt varchar2(1000);
> > > begin
> > > SELECT
> > > 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD;'
> > > into sqlstmt
> > > FROM
> > > DBA_INDEXES
> > > WHERE
> > > STATUS='INVALID'
> > > AND OWNER='owner_name_here'
> > > AND TABLE_NAME='table_name_here';
> > >
> > > execute immediate sqlstmt;
> > >
> > > end;
> > >
> > > (with appropriate error checking of course)
> >
> > Perfect. That's exactly what I was looking for.
> > Thank you very much.
> >
> > Franz

>

> Franz, both Jim and Charles offered the same solution only Jim used
> pl/sql where Charles was telling you to use SQL to generate and run SQL
> but it is a lot easier to spool out a file with the actual SQL being
> ran and the results of the operation using an SQLPlus script than using
> execute immediate.

>
> HTH -- Mark D Powell --

One of the main advantages of the spool 'solution' you always hage to remember
set pagesize 0 newpage 0 feedback off.
The other advantage is when you don't pay attention it will definitely clutter the O/S with spool junk.
I don't see why generating a spool file would be 'easier'. Actually such a script takes more time to type than the anonymous block solution.

-- 
Sybrand Bakker
Senior Oracle DBA

Received on Sat Jan 06 2007 - 13:11:46 CST