Re: Monitor Index creation?

Hi Gene,
Yes, there is, though it is a bit tricky and it may not work if you have a 'real' temporary tablespace (ie designated as temporary in a create or alter tablespace statement).
What you will see happen is
- a temporary segment will be created in your temporary tablespace. Here the sorting will take place. The size of the segment will grow until roughly 1.5 or 2 times the sum of the length of all keys - a second temporary segment will be created in the tablespace the index is going to. Actually this is the index, which will get it's proper segment name just before completion.

Tips:
- you may need to make sure your sort segment will actually fit in the temporary tablespace and is avoiding unnecessary segment creation. Make sure the default storage clause of the temporary tablespace is appropriate. You can't specify explicit storage parameters - As of Oracle 7.2 (?! or 3) there is an UNRECOVERABLE clause in the create index command. This will mean all changes due to the index creation are not reflected in the redo log file. You may be better of by recreating if it fails anyway.
- If you do use redo log make sure no frequent logfile switches occur. Check this out in the v$loghist table.

Hth
A (former) fellow victim of long running create index statements.

Sybrand Bakker, Oracle DBA
(postbus_at_sybrandb.demon.nl)

gplagge_at_hotmail.com wrote:

> Hi,
>
> Is there any way to monitor the progress of an index creation
> for a large index?
>
> We have these indexes that are taking hours and hours, and we'd
> sure like to know if they are making progress, or just stuck!
>
> Any thoughts are appreciated!
>
> Gene Plagge
> gplagge_at_hotmail.com
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
Received on Fri Aug 07 1998 - 13:33:53 CDT