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: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> 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
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).




_________________________________________________________________
Send and receive Hotmail on your mobile device: http://mobile.msn.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mohammad Rafiq
  INET: rafiq9857_at_hotmail.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).