Re: Oracle dbf problem

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 ...
3. Analyze these tables with either the DBMS.STATS package (preferred) or the ANALYZE command.
4. 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.
5. 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 Mon Jul 22 2002 - 21:42:18 CDT