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: Space Remaining in Current Extent

RE: Space Remaining in Current Extent

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Tue, 27 May 2003 14:14:42 -0800
Message-ID: <F001.005A3A5F.20030527141442@fatcity.com>


Kevin - The following may be useful in your quest as well:

Diego Cutrone posted this 1/18/2002

   This query will count how many blocks have data (at least one row) and are below the HWM:
select count(distinct substr(rowid,15,4)||substr(rowid,1,8)) from TABLE;

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----

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

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:

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: 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). Received on Tue May 27 2003 - 17:14:42 CDT

Original text of this message

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