| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Finding the actual size of a table
I'm with Joe on this because I'm a big fan of the dbms_space package. You
really need to see what's going on inside the extents. 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 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...
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.
Steve Orr
-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Wasserman,
Sara
Sent: Thursday, June 01, 2000 9:40 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Finding the actual size of a table
I find that NUM_ROWS * AVG_ROW_LEN is reasonably accurate, but BLOCKS is the number of blocks EVER used, not the number currently in use.
HTH,
Sara Wasserman
> -----Original Message-----
> From: ddorr.cs_at_clearstream.com [SMTP:ddorr.cs_at_clearstream.com]
> Sent: Wednesday, May 31, 2000 5:05 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Finding the actual size of a table
>
> I've always thought you could get valuable information from DBA_TABLES,
> assuming tables have been ANALYZEd.
>
> NUM_ROWS * AVG_ROW_LEN gives the space used by a table,
> BLOCKS gives the number of blocks used,
>
> Am I missing something ???
>
> > ----------
> > From: Joseph S. Testa[SMTP:teci_at_oracle-dba.com]
> > Reply To: ORACLE-L_at_fatcity.com
> > Sent: Tuesday, May 30, 2000 23:10
> > To: Multiple recipients of list ORACLE-L
> > Subject: Re: Finding the actual size of a table
> >
> > it does not exist in a view, look at $ORACLE_HOME/rdbms/admin
> >
> > look for dbmsutil.sql, in there is dbms_space package, thats what you
> want
> > to
> > use.
> >
> > joe
> >
> >
> > Steven Monaghan wrote:
> >
> > > Which data dictionary view can I use to determine the actual size of a
> > > table, not just the extents allocated to it?
> > >
> > > I will be moving a database to a new machine in a few weeks, and I
> want
> > to
> > > start planning out the initial extent sizes for my tables. I don't
> want
> > to
> > > just use exp/imp with compress=Y because I want to manually size some
> of
> > my
> > > smaller reference tables with a large enough initial extent so they
> > don't
> > > need to grow to reduce potential fragmentation.
> > >
> > > I also have TOAD but couldn't find the answer there either.
> > >
> > > TIA,
> > > Steve Monaghan
> > > WorldCom
> > >
> > > --
> > > Author: Steven Monaghan
> > > INET: Steve.Monaghan_at_wcom.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).
> >
> > --
> > Author: Joseph S. Testa
> > INET: teci_at_oracle-dba.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).
> >
> --
> Author:
> INET: ddorr.cs_at_clearstream.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).
-- Author: Wasserman, Sara INET: sjwasserman_at_pscnet.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-LReceived on Thu Jun 01 2000 - 11:56:10 CDT
 |
 |