RE: ORA-1658 even though there is enough contig free

But it was said that it failed even with noparallel

-----Original Message-----
Sent: Wednesday, July 31, 2002 1:56 PM
To: Multiple recipients of list ORACLE-L

I have not been able to duplicate the scenario, so I'm only guessing here... (consume with 1 large grain of salt).

When you specify parallel, it attempts to create 2 extents of initial_extent size. If it cannot, it will fail (hence the first problem). I recall in 7.3 (again, can't reproduce in 8i on NT), there are 2 (or the parallel + 1 if parallel is set) segments created for all index creations. that the index is actually created in a 'TEMP SEGMENT' in the specified (i.e. index) tablespace. Once the index creation is complete, the temp segment is renamed to the index name and the old index segment is dropped. This requires that there is must be 2x the amount of space that you specify for the index for the index creation to succeed. If the index creation fails, the TEMP SEGMENT is left in the index tablespace and will eventually be cleaned up by SMON.

Sorry for the shot in the dark, but perhaps it will lead you down the path to a solution.

Dan Fink

-----Original Message-----
Sent: Wednesday, July 31, 2002 11:26 AM
To: Multiple recipients of list ORACLE-L

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
Rich.Jesse_at_qtiworld.com              Quad/Tech International, Sussex, WI USA

> -----Original Message-----
> From: Mohammad Rafiq [mailto:rafiq9857_at_hotmail.com]
> 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: ORACLE-L_at_fatcity.com
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> 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: Rich.Jesse_at_qtiworld.com

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: ListGuru_at_fatcity.com (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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Fink, Dan
  INET: Dan.Fink_at_mdx.com

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: ListGuru_at_fatcity.com (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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.COM

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: ListGuru_at_fatcity.com (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).