Datafile size is increasing !!!!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Datafile size is increasing !!!!

  1. #1
    Join Date
    Nov 2000
    Location
    New Delhi, India
    Posts
    81

    Angry

    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!!

  2. #2
    Join Date
    Feb 2000
    Posts
    175
    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

  3. #3
    Join Date
    Nov 2000
    Location
    New Delhi, India
    Posts
    81

    Datafile size is increasing !!!!

    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

  4. #4
    Join Date
    Jan 2000
    Location
    *
    Posts
    37
    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.

    :-)

  5. #5
    Join Date
    Feb 2000
    Posts
    175
    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

  6. #6
    Join Date
    Nov 2000
    Location
    New Delhi, India
    Posts
    81

    Thumbs up

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width