Re: Oracle dbf problem

Hi Pauline,

That's great, glad I could help :)

Note as I mentioned previously that de-allocating the unwanted storage with the deallocate unused option might be the way to go.

Cheers

Richard
"Pauline Gu" <peironggu_at_yahoo.com> wrote in message news:f40bbecb.0207230858.2864161a_at_posting.google.com...
> 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.
>
> Pauline
> "Richard Foote" <richard.foote_at_bigpond.com> wrote in message
news:<9h3%8.41655$Hj3.124899_at_newsfeeds.bigpond.com>...
> > 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
> > DBA_TABLESPACE)
> >
> > 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:
> >
> > ALTER TABLE too_big DEALLOCATE UNUSED;
> >
> > 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" <peironggu_at_yahoo.com> wrote in message
> > news:f40bbecb.0207221711.50cff407_at_posting.google.com...
> > > 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 Wed Jul 24 2002 - 03:47:56 CDT