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).
