Datafile size is increasing !!!!

[ShrutiM]

Hi fellaws,

I am facing problem of not being able to check the size of the database file. The file has growing invariably to 1.4 GB :eek: while the total data in the file is 50 MB only :confused:. The size of the file cannot be reduced as there are used extents in the later part of the file, which doesn't allow to reduce this size.

One thing; I am doing lots of data import in this file.

Thanks!!

[moff]

Hi,

How many tablespaces/tables are currently stored in the datafile?

If you want to reduce the size of the datafile One option would be to
export all tables that are stored in the datafile.
drop the associate tablespaces/tables
delete the datafile
create the tablespaces again with the correct datafile size and autoextend off
import the tables.

Rgds

Moff

[ShrutiM]

Thanks for your Reply!!

The datafile has one tablespace and about 180 tables.

I've already tried dropping the data file and again recreating but did it with autoextent on, because it gives error of not being able to create extents while importing the database, if the autoextent is off.

Shruti

[magskn]

I would try rebuilding the tablespace again but this time build your tables with the correct sizing first. Then import your data. That way you shouldn't run into any extent issues. Just be sure to have ignore=y in your import.

:-)

[moff]

Shruti,

It sounds like the next extent size for some of your tables may be set to high.
Find what tables the import fails on and review the next value for those
tables. Oracle will be trying to allocate contiguous space equal to the
value of next. If none is available then it will increase the datafile untill it can
get enough contiguous space to create the next extent.
Have you tried isssuing an alter tablespace coalesce command to manually
coalesce adjacent extents before you try the import? This should
reduce your fragmentation?

Cheers

[ShrutiM]

Thanks for the support given to me in such a shot time. I've been able to check the data file problem successfully. I reduced the Next Extent size of the culprit tables and then did an export. After this I removed the tablespace and the data file and recreated it allover again. After the import the size of the datafile remained very much within the limits.

Thanks all of you

Shruti