Re: Failure to Extend Rollback

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 30 Aug 1999 13:33:09 GMT
Message-ID: <37cd84fe.3160124@newshost.us.oracle.com>

A copy of this was sent to g.renfrew_at_acca.org.uk (if that email address didn't require changing) On Mon, 30 Aug 1999 08:09:47 GMT, you wrote:

>Hi, quick question about rollback extents.
>
>I'm getting an ORA-1650 (rdbms 7.3.4.4/ solaris 2.7) on the RBS
>tablespace. Next extent size is 1048567. However, the database is
>unable to allocate this extent due to lack of contiguous free space.
>However, it looks to me like there is enough space (see query below).
>Why does an extent of 1048567 bytes not fit into the free space I've
>got?
>
>SQL> select bytes, count(bytes)
> 2 from dba_free_space
> 3 where tablespace_name = 'RBS'
> 4 group by bytes;
>
> BYTES COUNT(BYTES)
>---------- ------------
> 940032 1
> 1054720 63
>
>Many thanks.
>
>Graeme Renfrew
>DBA
>ACCA, Glasgow
>
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

here is a script that shows freespace by tablespace. It shows TOTAL free space and the largest contigous (largest allocatable extent size) in the tablespace.

If you have 8.0 and below, you'll need to either remove the reference to dba_temp_files or

create view dba_temp_files as select * from dba_data_files where 1=0;

to let this work.

You might have 100meg free in a tablespace but the largest free extent size might be much much smaller. this would be due to fragmentation, having >1 datafile in the tablespace both with free space (an extent is contigous and cannot span files) and so on.

---

• free.sql --
• This SQL Plus script lists freespace by tablespace

  ---

column dummy noprint

column  pct_used format 999.9       heading "%|Used"
column  name    format a16      heading "Tablespace Name"

column Kbytes format 999,999,999 heading "KBytes" column used format 999,999,999 heading "Used" column free format 999,999,999 heading "Free" column largest format 999,999,999 heading "Largest" break on report
compute sum of kbytes on report
compute sum of free on report
compute sum of used on report

select nvl(b.tablespace_name,nvl(a.tablespace_name,'UNKOWN')) name,

       kbytes_alloc kbytes,
       kbytes_alloc-nvl(kbytes_free,0) used,
       nvl(kbytes_free,0) free,
       ((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100 pct_used,
       nvl(largest,0) largest
from ( select sum(bytes)/1024 Kbytes_free,
              max(bytes)/1024 largest,
              tablespace_name
       from  sys.dba_free_space
       group by tablespace_name ) a,
     ( select sum(bytes)/1024 Kbytes_alloc,
              tablespace_name
       from sys.dba_data_files
       group by tablespace_name
       union all
       select sum(bytes)/1024 Kbytes_alloc,
              tablespace_name
       from sys.dba_temp_files
       group by tablespace_name ) b

where a.tablespace_name (+) = b.tablespace_name order by &1
/

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Aug 30 1999 - 08:33:09 CDT