RE: Actual space used by tables indexes etc?

From: Steve Orr <sorr_at_arzoo.com>
Date: Wed, 14 Jun 2000 16:00:30 -0700
Message-Id: <10528.109333@fatcity.com>

We just had a discussion on this two weeks ago... I'm a big fan of the dbms_space package. You really need to see what's going on inside the extents and you need more info than what's in dba_tables. Besides the allocated size of a table, you need to know how much of that allocated storage is actually being used for data. While you can derive how much storage is being consumed using num_rows and avg_row_len, it's better to get block/byte usage using the DBMS_SPACE.UNUSED_SPACE procedure. Below is a snippet of a weekly report I produce. This shows that I have several tables with 1 extent of 128MB which are mostly empty. Trend analysis can be used to predict how long it will be until a table needs a new extent.

Here's the report...

TABLE USAGE REPORT: DATABASE ######. Reported on 29-MAY-00
(Storage numbers in database blocks of 16384 bytes.)

TABLES ON SCHEMA ######

Table                          Total  Unused  Used  Total   Init   Next
Name                           Blocks Blocks Blocks Extent Extent Extent
------------------------------ ------ ------ ------ ------ ------ ------
TBFRM_FORM_FIELD_DETAILS         8192   8191      1      1   8192   8192
TBPIM_USERS                      8192   8177     15      1   8192   8192
TBPIM_CC_DETAILS                 8192   8191      1      1   8192   8192
TBPRE_RATING_HISTORIES           8192   8173     19      1   8192   8192
TBPRE_REVIEWERS                  8192   8188      4      1   8192   8192
TBPRE_REVIEWS                    8192   6857   1335      1   8192   8192
TBPRE_SEARCH_RESULTS             8192   2201   5991      1   8192   8192
TBPRE_URLS                       8192   7122   1070      1   8192   8192
TBPRE_PRODUCT_SEARCHES            512    196    316      2    256    256
-----------------------------------------------------------------------

You can get the same kind of info on indexes. For sample code see the following: http://www.oracle.com/oramag/code/cod04139.html

HTH,
Steve Orr
Fremont, CA

-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Smith, Ron L.
Sent: Wednesday, June 14, 2000 4:30 PM
To: Multiple recipients of list ORACLE-L Subject: Actual space used by tables indexes etc?

Is there a way to know how much space is actually being used by tables, indexes, etc..? Not allocated but actually used?

Ron Smith
Database Administration
rlsmith_at_kmg.com

--
Author: Smith, Ron L.
  INET: rlsmith_at_kmg.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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 Wed Jun 14 2000 - 18:00:30 CDT