Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: sufficient next extent space

Re: sufficient next extent space

From: Jeffrey Beckstrom <JBECKSTROM_at_gcrta.org>
Date: Mon, 07 Feb 2005 08:07:35 -0500
Message-Id: <s20721e2.099@gcrta.org>


That's basically what I had been doing but now need to combine the adjacent extents.
>>> <Tony.Adolph_at_o2.com> 2/7/05 8:03:28 AM >>>


--

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

To

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

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:10:45 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US