Hi Rafiq, Right, PARALLEL does do that, which is why I had stated in the original post that I had tried the CREATE without PARALLEL after coalescing the TS.
In the meantime, I have figured out what the problem is. My problem is on a test system. This test system's "MYCOOLTBL" happens to be partitioned and the index was being created "LOCAL". So, with 4 partitions, the CREATE needed 4 extents of 140MB each. Thus, a single 140MB free extent wasn't enough. Once I "fixed" the partitioned table, the index created fine in 140MB. Your query, however, still only returns a max value of 36MB, which is not true for me. After quickly looking thru your query, I'd say it has to do with going after "max(block_id)" in the subquery. The maximum block_id for freespace in this TS probably points to a 36MB extent, but it is not the largest free extent. Use DBA_FREE_SPACE. :) Thanks! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA > -----Original Message----- > From: Mohammad Rafiq [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, July 31, 2002 12:56 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: ORA-1658 even though there is enough contig free > > > That query give u perfect size in shape of FAT_MB and that's > the maximun > size of intitial extent... I never relied on DBA_FREE_SPACE > Have you coalesed yr tablespace before and after running this > query? Besides > using parallel 2 , it requires 2 extents of 140M instead of > one extent of > 140M so u may reduce yr initial extent size to start with 20M > for initial > and next... > > If u have any specific question, please let me know > > Regards > Rafiq > > > > Reply-To: [EMAIL PROTECTED] > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Date: Wed, 31 Jul 2002 09:26:08 -0800 > > Hmmm...I'm confused. Your query returns the second biggest chunk from > DBA_FREE_SPACE on that TS. So, what happened to the 147MB > chunk I had? And > why is my query against DBA_FREE_SPACE invalid for testing > the max size of > an initial extent? > > BTW, this TS only has one datafile. > > Rich Jesse System/Database Administrator > [EMAIL PROTECTED] Quad/Tech International, > Sussex, WI USA > > > -----Original Message----- > > From: Mohammad Rafiq [mailto:[EMAIL PROTECTED]] > > Sent: Tuesday, July 30, 2002 5:20 PM > > To: Multiple recipients of list ORACLE-L > > Subject: Re: ORA-1658 even though there is enough contig free > > > > > > Rich, > > 1)Run following script to check fat size which will indicate > > how big your > > initial extent may be as you are not finding contigous > space in your > > tablespace. when it ask for value give your tablespace name.. > > > > undefine table_space > > set verify off > > prompt This script provides a report useful for resizing datafiles > > prompt You should perform a 'ALTER TABLESPACE tsname > COALESCE' before > > running > > prompt this script to ensure you are getting all free space > > at end of the > > file > > accept table_space prompt "Enter a tablespace_name or all: " > > select ddf.file_name, > > dfs.file_id, > > ddf.blocks, > > (ddf.blocks*value)/1024/1024 file_size_mb, > > dfs.block_id block_hwm, > > ddf.blocks-dfs.block_id fat_blocks, > > floor(((ddf.blocks-dfs.block_id)*value)/1024/1024) > > fat_mb, > > ceil(((ddf.blocks*value)/1024/1024 - > > ((ddf.blocks-dfs.block_id)*value)/1024/1024)) resize_to > > from dba_free_space dfs, > > dba_data_files ddf, > > v$parameter > > where v$parameter.name = 'db_block_size' > > and (ddf.tablespace_name = UPPER('&&table_space') > > or 'ALL' = UPPER('&&table_space')) > > and dfs.tablespace_name = ddf.tablespace_name > > and dfs.file_id = ddf.file_id > > and dfs.block_id = (select max(block_id) > > from dba_free_space > > where file_id = dfs.file_id) > > order by fat_blocks desc; > > / > > > > 2) Coalesce your tablespace and run this script again.. > > 3) or reduce yr initial extent size and don't use parallel clause > > > > HTH, > > Regards > > Rafiq > > > > > > > > > > Reply-To: [EMAIL PROTECTED] > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > > Date: Tue, 30 Jul 2002 13:18:28 -0800 > > > > Hi all, > > > > On 8.1.7.2.0 on HP/UX 11.0, I need to create an index online. > > So, I see how > > much space I have: > > > > select max(bytes/1024/1024) > > from dba_free_space > > where tablespace_name = 'MY_IDX_TS'; > > > > ...and it returns "147.3475". So I create my index: > > > > CREATE INDEX myschema.mycoolidx > > ON myschema.mycooltbl(mycoolcol) PCTFREE 15 > > STORAGE( > > INITIAL 140 M > > NEXT 30 M > > ) > > NOLOGGING ONLINE TABLESPACE my_idx_ts PARALLEL 2; > > > > But I keep getting "ORA-01658 unable to create initial extent > > in MY_IDX_TS", > > even though there is ample room. I thought it was because I > > had originally > > tried it with "PARALLEL 2" and there was some overhead needed, so I > > coalesced the TS (DICTIONARY, obviously) and tried it without > > the PARALLEL > > to no avail. I've even dropped the INITIAL down to 130M > > without luck (again > > after coalescing). Oh, to be LOCAL... > > > > So, how big can I make the initial extent? I don't remember > > running into > > this before and I can't find anything on MetaLink. And of > > course, I killed > > the session when it was creating it so now I can't drop the > > index without a > > ORA-8104 error. > > > > Anyone? > > > > TIA! > > Rich Jesse System/Database Administrator -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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).
