RE: Space Remaining in Current Extent

Kevin,

Also look at AVG_SPACE_FREELIST_BLOCKS which is the average freespace of all blocks on a freelist, and NUM_FREELIST_BLOCKS which is the number of blocks on the freelist. Of course, this is available after an ANALYZE... This is available for INSERTs.

John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

I don't know what the future holds for me, but I do know who holds my future!

• The opinions and statements above are entirely my own and not those of my employer or clients **

> -----Original Message-----
> From: Thomas Day [mailto:tday6_at_csc.com]
> Sent: Tuesday, May 27, 2003 1:40 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Space Remaining in Current Extent
>
>
>
> I'm not sure if this is any help:
> --block_e_byte.sql
> /*
> The Oracle blocks used by a given table and the corresponding bytes.
> */
> --Inspired by a posting by Rick_Cale_at_teamhealth.com on ORACLE-L
> --Requires one parameter, the table name.
> SELECT COUNT (DISTINCT
> DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||
> DBMS_ROWID.ROWID_RELATIVE_FNO(rowid))
> "Blocks Used" FROM &&1;
> SELECT COUNT (DISTINCT
> DBMS_ROWID.ROWID_BLOCK_NUMBER(a.rowid)||
> DBMS_ROWID.ROWID_RELATIVE_FNO(a.rowid))
> * b.value
> "Bytes Used" FROM &&1 a, v$parameter b where name = 'db_block_size'
> group by b.value;
>
> There should be some way to decode rowid to tell you which
> blocks are used
> in a given extent --- I just don't have the time or resources to do it
> right now.
>
>
>
>
>
>
> DENNIS WILLIAMS
>
>
> <DWILLIAMS To:
> Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>
> @LIFETOUCH.COM> cc:
>
>
> Sent by: root Subject: RE:
> Space Remaining in Current Extent
>
>
>
>
>
>
>
> 05/27/2003 03:39
>
>
> PM
>
>
> Please respond
>
>
> to ORACLE-L
>
>
>
>
>
>
>
>
>
>
>
>
> Kevin - Since you haven't received any replies, here goes.
> Within an extent, Oracle uses blocks. I haven't seen a way to
> find the used
> space within a block. There are methods to find the number of
> empty blocks
> underneath the high water mark. Analyze does that, but you've
> ruled that
> out. It might help if you could explain what you are trying
> to accomplish.
> Other ideas are:
> - Write a program that will scan the table, read each row
> and count the
> bytes as it reads it. This would be very accurate, but time-consuming.
> - An approximate answer could be arrived at by doing an analyze and
> getting average row length. This shouldn't change much unless some
> operation
> is performed that would alter that. Then by getting the
> number of rows in
> the table you could get a very close estimate of the table size at any
> time.
>
> Dennis Williams
> DBA, 80%OCP, 100% DBA
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com
>
>
> -----Original Message-----
> Sent: Tuesday, May 27, 2003 11:15 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Hey guys;
> Does anyone know where I can look to find the space remaining in an
> individual extent ?? I know that you can get the freespace from
> dba_free_space but that seems to be based only on unallocated
> extents. I
> have been asked to find out, down to the byte, how much free space is
> available ..... on the fly (which means not only after every analyze)
>
> Any suggestions ??? I am sure Oracle has a table with the information
> somewhere .
>
> Thanks
>
> Kevin
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Kevin Lange
> INET: klange_at_ppoone.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.COM
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Thomas Day
> INET: tday6_at_csc.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Kanagaraj
  INET: john.kanagaraj_at_hds.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).