Re: 8.1.7.4 - Linux - Tablespace Issue

Tom wrote:
> >
> > I have a tablespace that apparently does not have sufficient space to
> > extend any further - No issue as i added a datafile to it.
> >
> > Still getting the same error so i checked the freespace in the
> > tablespace and this is the report for that tablespace
> >
> > Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
> > JServer Release 8.1.7.4.0 - Production
> >
> >
> > Tablespace free space
> > TSNAME TOTAL(M) FREE(M) FREE % LARG(M) FRAGMTS MGMT
> > ALLO
> > ------------------------- -------- -------- ------ -------- ------- ----
> > ----
> > xxxxxBLOB 10,900 5,563 51 1,498 7 PERM
> > USER
> >
> > however i'm getting this in the alert log
> >
> > ORA-1691: unable to extend lobsegment xxxxx.SYS_LOB0000071706C00002$$ by
> > 311072 in tablespace xxxxxBLOB
>
> OK this is really confusing me now as i'm only trying to insert a 3meg
> image and i first created a file with inital size of 100meg and next
> extent 10meg so there should be plenty of continous space. Any ideas as
> i'm not getting anywhere fast
>
> thanks!

Why are you not looking in DBA_FREE_SPACE? This is a dictionary-managed tabllespace, so fragmentation is likely your culprit, as you will see when you look at the DBA_FREE_SPACE view and find you do not have a free segment large enough to contain what you're attempting to store. You will also note you have numerous smaller, contiguous blocks of free space that have yet to be coalesced, providing plenty of free space with none of it usable for your BLOB. One solution to this is:

SQL> alter tablespace xxxxxxBLOB coalesce;

And then make the attempt again to insert your BLOB data. You may find you finally have large enough free segments to actually store something.

David Fitzjarrell Received on Thu Dec 08 2005 - 08:18:10 CST