Oracle-L: Re: ORA-1658 even though there is enough contig free

From: Mohammad Rafiq <rafiq9857_at_hotmail.com>
Date: Tue, 30 Jul 2002 14:19:49 -0800
Message-ID: <F001.004A6A7E.20020730141949@fatcity.com>

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

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




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

Received on Tue Jul 30 2002 - 17:19:49 CDT