Re: can you help me understand this?

One thing I know is the analyze will use temporary tablespace. My guess is that particular index became so screwed up, there was some memory problem in analyzing it, and this error was untrapped, and it looked like the process wouldn't simply return any more.
To check for 'efficiency' of indexes
(particular knowing the number of deleted keys ) you should do the following:
analyze <index_name> validate structure;

select * from index_stats;
This will tell you how much wasted space there is in the b-tree,. how many deleted keys there are and so on.

Hth,

Sybrand Bakker, Oracle DBA

<gdas_at_my-deja.com> wrote in message news:8pn59g$jna$1_at_nnrp1.deja.com...
> Hi,
>
> I was running a tuning script against the database. The script is a
> collection of 'ANALYZE TABLE...' AND 'ANALYZE INDEX COMMANDS...'
>
> The script ran for awhile executing the commands successfully. Finally
> it got to one particular 'analyze table...' statement and then
> immediately, Oracle killed the session with the infamous error:
>
> ORA-03113: end-of-file on communication channel
>
> I initiated a new session in sql plus and manually re-ran the last
> statement that caused the problem before, and the same problem
> occurred...ORA-03113 end-of-file on communication channel.
>
> I really don't know why I decided to do this, but after some fruitless
> investigating, I decided to try rebuilding the indexes on that
> particular table.
>
> I then reran the problematic 'analyze table' statement and it completed
> successfully, I then reran the entire script and all was well...so I'm
> in good shape, but I am thoroughly perplexed as to what happened.
>
> Does anyone have any idea what might have happened and why rebuilding
> the indexes solved the problem? Did I just get lucky or could there be
> a more logical explanation to this?
>
> Thanks in advance,
> Gavin
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Sep 13 2000 - 01:23:26 CDT