Re: sufficient next extent space

From: <Tony.Adolph_at_o2.com>
Date: Mon, 7 Feb 2005 14:03:28 +0100
Message-ID: <OF4E0EC8FB.44365539-ONC1256FA1.004790B7-C1256FA1.00479C10@viaginterkom.de>

fs.sql
--
Gives tablespace usage stats
--
History:
--
V1.0 Initial - TonyA - 21 Dec 01
--

compute sum of total_MB on report compute sum of used_MB on report compute sum of free_MB on report break on report

column ts format a15 heading "Tablespace|* = auto extend" column TOTAL_MB format 9,999,999 heading "Size|Mbytes" column pct_free format 999.99 heading "% Free" column pct_used format 999.99 heading "% Used" column used_MB format 9,999,999.9 heading "Used|Mbytes" column free_MB format 9,999,999.9 heading "Free|Mbytes" column max_extent_size format 99,999.99 heading "Largest|free|extent|Mbytes"

select

        df.tablespace_name||decode(autoextensible,'YES','*',' ') ts,
        total/1024/1024 Total_MB,
        (total - free)/1024/1024 Used_MB,
        ((total - free)/ total) * 100 pct_used,
        free/1024/1024 free_MB,
        (free / total ) * 100 pct_free,
        max_extent/1024/1024 max_extent_size
from
        (select
                tablespace_name,
                sum(bytes) free,
                max(bytes) max_extent
        from
                dba_free_space
        group
                by tablespace_name) fs
,
        (select
                tablespace_name,
                autoextensible,
                sum(bytes) total
        from
                dba_data_files
        group by tablespace_name, autoextensible
        union all
        select
                tablespace_name,
                autoextensible,
                sum(bytes) total
        from
                dba_temp_files
        group by tablespace_name,autoextensible) df

where

df.tablespace_name = fs.tablespace_name (+) /

clear compute
clear break
clear column

"Jeffrey Beckstrom" <JBECKSTROM_at_gcrta.org> Sent by: oracle-l-bounce_at_freelists.org
02/07/2005 01:49 PM
Please respond to
JBECKSTROM_at_gcrta.org

<oracle-l_at_freelists.org>, <oracle-db-l_at_Groups.ITtoolbox.com>, 
<ORACLE-L_at_IC.SUNYSB.EDU>, <oracledba_at_LazyDBA.com>, 
<oracle-rdbms_at_yahoogroups.com>

Subject
sufficient next extent space

I have an SQL script that looks for sufficient space for the next extent. I am having a problem though when Oracle is breaking the datafile into multiple free extents, i.e.

26  107,758    153,816 ***FREE***
 426  126,985    507,904 ***FREE***
 426  190,473    421,824 ***FREE***
 427        9    507,904 ***FREE***
 427   63,497    507,904 ***FREE***
 427  126,985    507,904 ***FREE***
 427  190,473    421,824 ***FREE***

File 427 is a brand new 1900M datafile in the tablespace. Right now I am seeing the largest free as 507,904 bytes and not 1900 M. Any suggestions on how to combine the extents in the sql script.

Jeffrey Beckstrom
Database Administrator
Greater Cleveland Regional Transit Authority 1240 W. 6th Street
Cleveland, Ohio 44113

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Mon Feb 07 2005 - 08:03:25 CST