Re: Unable to insert records (ORA-1653)

You are trying to add another extent of 9360 blocks of 8192 blocks to your table (9360 blocks * 8192 bytes / block = 76,677,120 bytes, which is the next extent size you specified).

This message means that there is no 'contiguous' free space of 9360 blocks available in your tablespace.

There may be free spaces that total more than 9360 blocks. However, I can guarantee there is no single free space => 9360 blocks nor are there 'contiguous' free spaces that total => 9360 blocks.

The most expedient fix is to add another file to the tablespace. Make it big enough to last another six months of inserts (or more).

Sanjay Hans wrote:

> Would appreciate your help on the following problem which I am facing:
>
> I am trying to insert around 80000 records in a table having around 1521058
> records, after
> inserting 65914 records, the process hangs and alertDEV.log shows the
> following error message:-
>
> ORA-1653: unable to extend table/cluster CGO.REVENUES
> by 9360 in tablespace REL_TAB
>
> The table REVENUES was created with following parameters:-
> CREATE TABLE REVENUES ( )
> PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 230031360
> NEXT 76677120 MINEXTENTS 1 MAXEXTENTS 249
> PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "REL_TAB"
>
> The tablespace REL_TAB is having lot of space as shown by the result of the
> following query:
>
> select sum(bytes),tablespace_name from dba_free_space group by
> tablespace_name;
>
> SUM(BYTES) TABLESPACE_NAME
> ---------- ------------------------------
> 312926208 RBS
> 2943057920 REL_IND
> 832356352 REL_TAB
> 187834368 SYSTEM
> 629129216 TEMP
>
> The other useful info you may need is :-
>
> The result of the following query:-
> select segment_name, pct_increase, sum(bytes), initial_extent,next_extent,
> max_extents, count(*) from dba_extents a, dba_tables b where
> segment_name = b.table_name and segment_type = 'TABLE' and a.owner='CGO'
> and
> segment_name in ('REVENUES') group by
> segment_name, pct_increase, max_extents, initial_extent, next_extent
> order by segment_name, max_extents
>
> is
>
> SEGMENT_NAME
> ---------------------------------------------------------------------------
> -----
> PCT_INCREASE SUM(BYTES) INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS COUNT(*)
> ------------ ---------- -------------- ----------- ----------- ----------
> REVENUES
> 0 230031360 76668928 76677120 249 3
>
> Thanks in advance.
Received on Fri Oct 30 1998 - 22:36:24 CST