Re: Help!! Max extents exceeded - Urgent

Joanna Lee <joanna_at_scotts.com.sg> wrote in article <345F0114.BDE24305_at_scotts.com.sg>...
> Hi
>
> My company is using oracle version 6. My colleague encountered
> an error ora-01556 which says : max number of 40 extents exceeded. Can
> anybody tell me how big is the 40? is it 40M or 400M? I've increased the
> tablespace to 140M but to no avail. Can somebody help me? Thanks.
>
>
> Joanna
>
>

Unfortunately, there is only one thing you can do to fix this, which is re-create the
table with more appropiate storage parameters.

The number 40 refers to the number of extents, not the amount of space the object
takes up.

You can find out the number of bytes by querying dba_extents.

ie select sum(bytes)

      from dba_extents
      where segment_name = 'MY_TABLE_NAME';

When you re-create your table, you'll want to change the storage parameters,
particularily INITIAL_EXTENT, which you should probably increase a fair bit,
(you'll have to work out how much) and PCT_INCREASE.

Your PCT_INCREASE will probably be o.k., around 50% if its using the default.

You can find out your current values from dba_tables.

I read some where that as a rule of thumb, any table that exceeds 5 extents is over extended, and you should start thinking about changing the storage parameters to avoid this kind of problem.

Hope this helps, (probably not what you wanted to hear!)

Have fun.

Jason. Received on Wed Nov 05 1997 - 00:00:00 CST