Re: Unable to insert records (ORA-1653)

We encounter this once a while with our data warehouse. The tablespace which your table resides on is fragmented. Simple solution is to:

• export your table with 'COMPRESS=Y' option. (it's better if you could export all tables resides on that tablespace)
• drop your table(s).
• coalesce your tablespace 'alter tablespace <tablespace name> coalesce'.
• import your exported table(s).

Now you can go ahead with your inserts.

Minh Giang

John P. Higgins wrote:

> 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 Thu Nov 05 1998 - 20:26:25 CST