Re: Can't create Initial Extent in Create Table statement

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 04 Jul 1998 20:31:08 GMT
Message-ID: <35a490b1.26436703_at_192.86.155.100>


A copy of this was sent to "Mark Cudmore" <mcudmore_at_kamloops.env.gov.bc.ca> (if that email address didn't require changing) On Fri, 3 Jul 1998 16:17:42 -0700, you wrote:

>Hi all, I am having problems with creating an initial extent for a table
>with 20 plus columns.
>
>I am trying to create a 100M Initial Extent on my table in a tablespace with
>at least 200M free.
>
>When I try to create my table I get the following error:
>
>ORA-01658: unable to create INITIAL extent for segment in tablespace
>KAMWHSE_TABLES
>
>Does anyone have any idea why I can't do this?
>
>Please reply by email also! Thank you in advance

You might have 200m free but what is the largest contigous piece of free space? extents must be allocated from free, contigous blocks. I use the following script to report how much space is allocated, used, free and the largest set of free blocks. The column 'Largest' tells you the biggest extent you can create:


  • free.sql --
  • This SQL Plus script lists freespace by tablespace

column dummy noprint

column  pct_used format 999.9       heading "%|Used"
column  name    format a16      heading "Tablespace Name"
column Kbytes format 999,999,999 heading "KBytes" column used format 999,999,999 heading "Used" column free format 999,999,999 heading "Free" column largest format 999,999,999 heading "Largest" break on report
compute sum of kbytes on report
compute sum of free on report
compute sum of used on report

select nvl(b.tablespace_name,nvl(a.tablespace_name,'UNKOWN')) name,

       kbytes_alloc kbytes,
       kbytes_alloc-nvl(kbytes_free,0) used,
       nvl(kbytes_free,0) free,
       ((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100 pct_used,
       nvl(largest,0) largest
from ( select sum(bytes)/1024 Kbytes_free,
              max(bytes)/1024 largest,
              tablespace_name
       from  sys.dba_free_space
       group by tablespace_name ) a,
     ( select sum(bytes)/1024 Kbytes_alloc,
              tablespace_name
       from sys.dba_data_files
       group by tablespace_name ) b

where a.tablespace_name (+) = b.tablespace_name order by &1
/  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  



Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Jul 04 1998 - 22:31:08 CEST

Original text of this message