Re: tablespace extents, i think

Tim Maggio writes:

NNGET As Bob Swisshelm says, the create tablespace statement does not make any sense considering the objects you want to store in it. But, according to the error message you have, objects are not created using the default storage so they use their own storage clause.

I am convinced you have a fragmentation problem in your tablespace. That means that although you have lots of free space you don't have it within big extents, so ORACLE can not allocate an extent of the adecuate size.

To see the real status of your tablespaces use this queries:

        prompt - Space used in tablespaces
        select 'Tablespace ' || tablespace_name ||
               ' has a total of ' ||
               to_char(sum(bytes),'999,999,990') || ' bytes in use.'
        from dba_ts_quotas
        group by tablespace_name;

        prompt - Free space in tablespaces
        set heading on
        select tablespace_name,
               to_char(count(*),   '99,990')      Extents,
               to_char(min(bytes), '999,999,990') Minimum_size,
               to_char(avg(bytes), '999,999,990') Average_size,
               to_char(max(bytes), '999,999,990') Maximum_size,
               to_char(sum(bytes), '999,999,990') Total_free
        from dba_free_space
        group by tablespace_name;

        prompt - Tablespace fragmentation
        clear columns
        clear breaks
        clear computes
        break on report on tablespace_name skip 1
        col tablespace_name      format a13           head 'Tablespace'
        col owner                format a13           head 'Owner'
        col object               format a23           head 'Object'
        col file_id              format 9,990         head 'File'
        col block_id             format 9,999,990     head 'Block ID'
        col blocks               format 9,999,990     head 'Blocks'

        select tablespace_name, '  FREE SPACE' owner,
               ' ' object, file_id, block_id, blocks
          from dba_free_space
        union select tablespace_name, owner,
                     segment_name object, file_id, block_id, blocks
          from dba_extents
        order by 4,5;

Then you can decide the action to correct the problem:

• Recreate the whole tablespace
• Recreate the objects using appropiate storage clauses

Regards,

Rafael Zarza

European Space Agency Operations Centre     Tel:   (49) 6151 902836
64293 Darmstadt, Germany                    Email: RZARZA_at_ESOC.BITNET