Oracle-L: RE: Space Remaining in Current Extent

From: Kevin Lange <klange_at_ppoone.com>
Date: Wed, 28 May 2003 06:40:11 -0800
Message-ID: <F001.005A42BA.20030528064011@fatcity.com>

Thanks for everyone for responding. I think I have enough to get as close as I can to what was requested.

Kevin

-----Original Message-----
Sent: Tuesday, May 27, 2003 5:40 PM
To: Multiple recipients of list ORACLE-L

I'll beg/borrow & steal from John Beresniewicz's book on Oracle Built-in Packages by O'Reilly & RevealNet to give you an answer. Here is the script that John provides with the book. I use it in a somewhat different form, but it appears to tell the truth.

DECLARE
free_blocks NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE(RPAD('TABLE NAME',30)||' FREELIST BLOCKS'); FOR user_tables_rec IN

      (SELECT table_name 
         FROM user_tables)
   LOOP
      DBMS_SPACE.FREE_BLOCKS
         (segment_owner => USER
         ,segment_name  => user_tables_rec.table_name
         ,segment_type  => 'TABLE'
         ,freelist_group_id  => 0
         ,free_blks => free_blocks
         ,scan_limit => NULL);
      
      DBMS_OUTPUT.PUT_LINE(RPAD(user_tables_rec.table_name,30)||' '||
                  TO_CHAR(free_blocks));

END LOOP;
END;
/

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-----Original Message-----
Sent: Tuesday, May 27, 2003 4:10 PM
To: Multiple recipients of list ORACLE-L

As to what this is for .....

I have a request to come up with a way to guage space remaining on an on going basis .... i.e. If I look at the space available between 2 different user transactions I will see a difference. Most of the methods I have seen so far either rely on Analyze or show simply the amount of space that has been allocated to the table at this time (not the actual "This is what you have allocated" and "This is what you have free").

I have tried using dbms_space but it again shows (at least I interpret it that way) the amount of space allocated , not neccessarily exactly what is in use.

If there is an obvious v$ or x$ view out there someplace where I can get this info, it would be great.

-----Original Message-----
Sent: Tuesday, May 27, 2003 2:40 PM
To: Multiple recipients of list 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
---------------------------------------------------------------------

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Goulet, Dick
INET: DGoulet_at_vicr.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

RE: Space Remaining in Current Extent - Done