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

Any plans for Oracle upgrade? :)

Waleed

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

Rich,

Thanks for reply and your explanation. For ver 7.3.4 dba_free_space specify large extent available but when using initial extent of around that size , it never creates initial extent of that size and generate error so I am not relying it and instead using this query. However I am not using any partitioning as 7.3.4 don't have this option....

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 10:50:59 -0800

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

> -----Original Message-----
> From: Mohammad Rafiq [mailto:rafiq9857_at_hotmail.com]
> 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: 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).




_________________________________________________________________
MSN Photos is the easiest way to share and print your photos: 
http://photos.msn.com/support/worldwide.aspx

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