Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle dbf problem

Re: Oracle dbf problem

From: Pauline Gu <>
Date: 23 Jul 2002 09:58:04 -0700
Message-ID: <>

Thank you so much Richard and Anurag.

Richard, I am still developing this database, so, I do keep track of the data that I am inserting into. And I am using Toad in which I can find the used space. Thank you so much for taking your time and giving me the information. Last night, I did find out that actually my table size is way too big, even without any data in it. So, I guess I will recreate the table and specify the size.

"Richard Foote" <> wrote in message news:<9h3%8.41655$>...
> Hi Pauline,
> Oh my goodness, you do sound stressed :(
> Couple of questions for you. How have you determined that the total data
> size is 7Mb ? How have you determined that the used space is 5000 Mb ? We
> need to be sure you're assumptions here are correct.
> In terms how much space a barebone table uses, it depends on the storage
> parameters used to create the table. The INITIAL, NEXT, PCTINCREASE and
> MINEXTENTS all govern how much storage a table actually takes up. I can
> create a table that contains 1 row but with an INITIAL set to 500M, 500M is
> the space the table will use. Note if you don't specify a storage clause,
> then it's uses the tablespace's default storage clause (look in
> My recommendation is to do the following (after you confirm the answer to my
> previous questions).
> 1) Check out DBA_SEGMENTS and determine what are the various objects that
> live in this tablespace (they may not all belong to your schema)
> 2) If you know the DBMS.SPACE package, use it to check out the free/used
> space of your objects , else ...
> 2) Analyze these tables with either the DBMS.STATS package (preferred) or
> the ANALYZE command.
> 3) Look in DBA_TABLES and (or USER_TABLES) and look at the following stats:
> BLOCKS (which are the number of blocks in the table which have been 'used')
> and EMPTY_BLOCKS (which are the number of blocks in the table which are
> being not being used). AVG_SPACE will tell you how space Oracle is using on
> average per block. This will give you an idea if the tables have been
> created way too big. Also check out the storage parameters I mentioned
> earlier to see how they've been set.
> 4) Check out your indexes and follow a similar process. They could be
> gobbling up space inappropriately.
> If you find some tables or indexes have been way oversized, then you could
> execute this command:
> This will return the unused space back to the tablespace.
> A bit to go on I hope that should keep you busy for a while.
> Good Luck
> Richard
> "Pauline Gu" <> wrote in message
> > hello,
> >
> > Please help!!!!!!
> >
> > I have a schema in which my total data size is about 7Mb. But when I
> > looked at my tablespace, I saw that the used space is 4994.44Mb. How
> > is that possible? How much space does a barebone table take? I have
> > about 26 tables, with average of about 7 columns in each table, and
> > all the columns are of the type of varchar2 (400) and number (32). I
> > do not have blobs or clobs. I only have sequence and primary key,
> > foreign key constraint on the tables, nothing else (not even index).
> > Can anybody help me with this?
> >
> > In this database, we have about 4 schemas. And we are running out of
> > the space from a 16G hard drive. I could not insert any more data.
> > Please help.
> >
> > thanks.
> >
> > Pauline
Received on Tue Jul 23 2002 - 11:58:04 CDT

Original text of this message