Re: Getting current space of a table ...

From: Ari Kaplan <akaplan_at_interaccess.com>
Date: Tue, 23 Mar 1999 10:55:04 -0600
Message-ID: <922208007.942.78@news.remarQ.com>

Thomas,

In Oracle7, to find out the total blocks of a table that contain rows, enter the following SQL

SELECT COUNT(DISTINCT(SUBSTR(ROWID,1,8))) FROM table_name;

In Oracle8, you can find the total blocks using DBMS_ROWID:

SELECT COUNT(DISTINCT(SUBSTR(DBMS_ROWID.ROWID_TO_RESTRICTED(ROWID),1,8))) FROM table_name;

You will get the total number of blocks that contain records of the table.

To determine the overall size, find the block size:

SELECT value FROM V$PARAMETER WHERE NAME = 'db_block_size';

The total space of the data is the multiplication of "Total Blocks" * "Block Size". This will be in bytes.

To find the free space in the table, subtract the size calculated above (used size) from the total size of the most recent extent.

Best regards,

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 275+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             http://homepage.interaccess.com/~akaplan          <->
<->                                                               <->
<->             email: akaplan_at_interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

Thomas Klinger wrote in message <36f7b376.26787321_at_194.48.138.240>...
>Hi there!
>
>Sounds like an easy question.
>I want to get the used space of a table in percentage digits.
>
>I.e. I have a table which takes at its init size ~174MB. And it is
>still at extent 0. Max_Extents=6.
>But how much is the table really filled up? How to do this?
>
>USED_SPACE_OF_CURRENT_TAKEN_BLOCKS=?
>FREE_SPACE_IN_THIS_EXTENT=100%-USED_SPACE_OF_CURRENT_TAKEN_BLOCKS
>
>Is it also possible to get the value of available rows which can be
>inserted into FREE_SPACE_IN_THIS_EXTENT and how much rows can be
>inserted at the max?
>
>Can anyone help me?
>
>
>Kind regards
>
> Thomas Klinger
> Systemspecialist
>=======================================
>t.klinger_at_mobilkom.at
>http://www.mobilkom.at
>=======================================
>
>
>
Received on Tue Mar 23 1999 - 10:55:04 CST