Thomas Klinger wrote in message <36f7b376.26787321@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
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.
Another option is to run statistics on the table, then issue:
SELECT EMPTY_BLOCKS FROM USER_TABLES WHERE TABLE_NAME='table_name_here';
to find the total blocks:
SELECT BLOCKS FROM USER_TABLES WHERE TABLE_NAME = 'table_name_here';
The only drawback is that you may not wish to use the cost-based optimizer and hence not want to generate statistics.
Best regards,
-Ari Kaplan
Independent Oracle DBA Consultant
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 275+ Oracle tips, visit my Web Page: <->
<-> <->
<-> www.arikaplan.com <->
<-> <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
Back to Ari Kaplan's Home Page