At 02:34 AM 7/31/2003 -0800, you wrote:
If this is true:
The rebuild creates a new temporary segment that is the same size as the required extents in the old index. If there is insufficient space to create this temporary segment you get this error.
It doesn't reuse the existing space the index occupies but builds a second identical index then renames and drops the old one.
Then rebuild will take longer as volume of data increases and more space will be required. Where is the temp.. segment created? In the old tablespace, the new tablespace (if you are moving it) or in memory or .....in memory then ...??
I did the following in Oracle RDBMS 9i:
_________________________________________________________________________________
SQL> alter index xsc_uk rebuild tablespace ax_le_small storage (initial 128K 2 next 128K); SQL> select initial_extent,next_extent,index_name from dba_indexes 2 where index_name like 'XSC%';
65536 131072 XSC_CLNT_FK_I
65536 131072 XSC_PK
131072 131072 XSC_UK
_________________________________________________________________________________
As I wish to use uniform extent sizing and I was given an import that does have that. I am a little concerned about the initial extent changing - what if there is data in the index? - how it could possibly deallocate space if you wish to have a smaller extent size. It was very quick. Did I really end up with new extents for XSC_UK each 128K????
Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).