Re: Rebuild implicit index for primary key

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 7 Jan 2007 11:39:11 -0800
Message-ID: <1168198751.005615.198770@i15g2000cwa.googlegroups.com>

sybrandb wrote:
> 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

I get the impression the OP will need this to be a repeatable process for different objects so we are not talking about typing this in one time. I am suggesting creating a generic task that can generate the necessary SQL, run it, and make a record of the results for future review if necessary. Spool files from tasks such as this are normally directed to a specific output location which can be automatically cleaned up by a cron or other scheduled script.

We have such a directory structure for our production C, pro*C, shell scripts, and SQLPlus scripts to write log files into just have we have defined locations for incoming and outgoing ftp files to be written. I would think most every site has such areas defined.

Then in the US under SOX pretty much all production database structure changes have to be logged or audited in some fashion.

HTH -- Mark D Powell -- Received on Sun Jan 07 2007 - 13:39:11 CST