Re: 8.1.7.4 - Linux - Tablespace Issue

> 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.

thanks -

SQL> select tablespace_name, sum(bytes)/1024/1024 as total_free_mb, count(*) as fragments, max(bytes)/1024/1024 as biggest_bit from dba_free_space group by tablespace_name;

gives

TABLESPACE_NAME                TOTAL_FREE_MB  FRAGMENTS BIGGEST_BIT
------------------------------ ------------- ---------- -----------
xxxxxBLOB                         7362.71094          8  1799.99219

so

SQL> alter tablespace xxxxxBLOB coalesce;

and again

select tablespace_name, sum(bytes)/1024/1024 as total_free_mb, count(*) as fragments, max(bytes)/1024/1024 as biggest_bit from dba_free_space group by tablespace_name;

TABLESPACE_NAME                TOTAL_FREE_MB  FRAGMENTS BIGGEST_BIT
------------------------------ ------------- ---------- -----------
DECCABLOB                         7362.71094          8  1799.99219

so no change and i can't insert this data

any other tips?

thanks Received on Thu Dec 08 2005 - 08:21:13 CST