Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: ORA-1658 even though there is enough contig free (SOLVED)

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

From: Mohammad Rafiq <rafiq9857_at_hotmail.com>
Date: Thu, 01 Aug 2002 06:18:20 -0800
Message-ID: <F001.004A8B20.20020801061820@fatcity.com> 





Ron,


Thanks for info...We are aware of it but management doesn't want such change 
and want to stay as it is.



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 14:41:24 -0800




FYI, you do have the choice of running server partitioned.  I'm running 10.7 
character with 8.1.7.4


with no problems.



Ron Thomas


Hypercom, Inc


rthomas_at_hypercom.com


"The problem with some people is that when they aren't drunk, they're 
sober."  --William Butler


Yeats.




                       rafiq9857_at_hotmail
                       .com                     To:       
ORACLE-L_at_fatcity.com
                       Sent by:                 cc:
                       root_at_fatcity.com         Subject:  RE: ORA-1658 even 


though there is enough contig free (SOLVED)



                       07/31/02 03:03 PM
                       Please respond to
                       ORACLE-L










Waleed,


We are running different versions from 7.3.4.5 to 9i. Ver 7.3.4.5 is for
Oracle Financials 10.7 Char and company not yet decided to go for
Application 11i so we have to support it for a while and face all such
limitations ver 7.3.4.



Sorry no choice but to live with it.



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 11:39:50 -0800



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




_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.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).




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


Received on Thu Aug 01 2002 - 09:18:20 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US