RE: sufficient next extent space

From: Mercadante, Thomas F <thomas.mercadante_at_labor.state.ny.us>
Date: Mon, 7 Feb 2005 09:33:59 -0500
Message-ID: <C9995D8C5E0DDA4A8FF9D68EE666CE0702A97309@exchsen0a1ma>

All,

This is exactly why we like Locally Managed Disks and Uniform extent sizes. All of this "management" effort goes away. Just not needed any more.

Tom

-----Original Message-----
From: Jeffrey Beckstrom [mailto:JBECKSTROM_at_gcrta.org] Sent: Monday, February 07, 2005 9:20 AM
To: oracle-l_at_freelists.org; oracle-db-l_at_Groups.ITtoolbox.com; ORACLE-L_at_IC.SUNYSB.EDU; oracledba_at_LazyDBA.com; Tony.Adolph_at_o2.com; oracle-rdbms_at_yahoogroups.com
Subject: Re: sufficient next extent space

That's the problem the new datafile is showing as multiple free extents so max won't work.
>>> <Tony.Adolph_at_o2.com> 2/7/05 9:20:05 AM >>>

Hi Jeffrey,

My point is that IF they are ajacent they will be counted by max(bytes) as one lump (extent). I.e. max(bytes) gives the largest continuous lump of freespace. I think you have an error in your calculations / assumptions. continuous lump of freespace = free extents if they are adjacent

I don't know why your new datafile isn't showing up as 99% free, i.e. max(bytes) = 99% of total size of new datafile, but that's a different problem....

Tony

"Jeffrey Beckstrom" <JBECKSTROM_at_gcrta.org> 02/07/2005 02:36 PM To
<Tony.Adolph_at_o2.com> cc
Subject
Re: sufficient next extent space

That is what I currently do. That won't combine if adjacent. Only want to combine the free extents if they are adjacent to each other.

>>> <Tony.Adolph_at_o2.com> 2/7/05 8:36:31 AM >>>

Hi Jeff,

Run this query. I have grouped by file_id instead of TS:

select

              file_id, 
              sum(bytes) free, 
              max(bytes) max_extent 
      from 
              dba_free_space 
      group by file_id 

I think you will see that do not have 3 (or how ever many) large contiguous extents... max(bytes) gets the largest for you.... If it doesn't come up in the column, then the big extents you extpected is already split somehow.

Tony

"Jeffrey Beckstrom" <JBECKSTROM_at_gcrta.org> 02/07/2005 02:19 PM
To
<Tony.Adolph_at_o2.com> cc
Subject
Re: sufficient next extent space

      (select 
             tablespace_name, 
             sum(bytes) free, 
             max(bytes) max_extent 
     from 
             dba_free_space 
     group 
             by tablespace_name) fs 
 

But this is grouping solely by tablespace_name getting max(bytes). In my example:

427        9    507,904 ***FREE***
427   63,497    507,904 ***FREE***
427  126,985    507,904 ***FREE***
427  190,473    421,824 ***FREE*** 

I would want all 3 free space entries combined since they are adjacent.  This is a brand new datafile and notice all Oracle setup the free extents.

>>> <Tony.Adolph_at_o2.com> 2/7/05 8:17:17 AM >>>

The adjacent extents are already summed... look at column max_extent in dba_free_space

Tony

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

To

<Tony.Adolph_at_o2.com> cc
<oracle-l_at_freelists.org>, <oracle-l-bounce_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

Re: sufficient next extent space

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

Received on Mon Feb 07 2005 - 09:38:25 CST