Re: ORA-01658. Puzzling =(

From: Oracleguru <oracleguru_at_mailcity.com>
Date: Tue, 11 Aug 1998 14:13:01 GMT
Message-ID: <01bdc541$cfeffe80$a504fa80@mndnet>

Other suggestions in this thread are correct. But you may want to try this script, to see the fragmentation and availability of contiguous disk space for your initial extent and next extent for the tablespace in question.

set pause off termout off verify off wrap on set newpage 0 pagesize 58 linesize 80

column today            new_value     today     noprint
column time             new_value     time      noprint
 
select   to_char(sysdate, 'dd-MON-yyyy') today,
         to_char(sysdate, 'HH:MI:SS AM') time

  from dual
/

column object           format a26              heading 'OBJECT'
column file_id          format 9990             heading 'FILE|ID '
column block_id         format 999990           heading 'BLOCK|ID '
column blocks           format 999990           heading 'BLOCKS'
column bytes            format 9,999,999,999    heading 'BYTES'
 

spool x.x

ttitle today center 'EFM_HIST TABLESPACE FRAGMENTATION' -

   right 'Page ' format 990 sql.pno skip 1 -    time -
   skip 2 -
   analyze_efm_hist_tablespace_usage.sql skip 2

select   'freespace' owner, '       ' object,
         file_id, block_id, blocks, bytes         bytes

  from sys.dba_free_space
 where tablespace_name = 'T1'
union
select substr(owner, 1, 15),

         substr(segment_name, 1, 31),
         file_id, block_id, blocks, bytes         bytes

  from sys.dba_extents
 where tablespace_name = 'T1'
order by 3, 4
/
spool off
exit

---

After you run this COALESECE the tablespace by entering:

alter tablespace T1 coalesce;

This should COALESCE the disk space into several contiguous spaces depending on how the fragmentation has taken place.

---

Run the script again to see if there is a difference.

---

If you want to COALESCE tablspace automatically then,

Set pctncrease to 1 for all the tablespaces ( not necessarily tables) except Oracle created ones such as RBS, SYSTEM, TOOLS etc. If you do this then SMON will COALESCE contiguous fragmented disk space every couple of munutes or so.

alter tablespace T1 default storage ( pctincrease 1);

Good luck !!!

Oracleguru
www.oracleguru.net
oracleguru_at_mailcity.com

Mio Marquez <mio.marquez_at_mailcity.com.nospam.please> wrote in article <35D03D2D.1EA67A95_at_mailcity.com.nospam.please>...
> Hi.
> When we try to create a new table, we get an "ORA-01658: Unable to
> create INITIAL extent for segment in tablespace SYSTEM" error.
> The doc says: Failed to find sufficient contiguous space to allocate
> INITIAL extent for segment being created.
>
> As the recommended action says, we've tried adding a new datafile into
> the SYSTEM tablespace and when we try to create the table again, it
> still produces the same error. Curiously, does the word "CONTIGUOUS"
> play a very important role here?
>
> A SELECT sum(bytes) FROM dba_free_space WHERE tablespace_name =
> 'SYSTEM'; returns 110,536,704.
> Out of desperation, we've also tried changing the values of INITIAL
> (original value is 102,400K) and NEXT (original value is 102,400K)
> extents but still the same.
> None of us are very well versed on internal workings of Oracle =(. We're
> using Oracle Workgroup Server 7.3.2.3.1 for Windows NT...
>
> Is there anything that we might be missing here?
> Could somebody please help us by providing some detailed steps on how to
> deal with this problem....
> Any help given is sincerely appreciated.
>
> Thank you and God Bless.
>
> Mio-Nino P. Marquez
> Delphi Developer
>
>
Received on Tue Aug 11 1998 - 09:13:01 CDT