Strange - datafile size & export .dmp file size
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Strange - datafile size & export .dmp file size

  1. #1
    Join Date
    Jan 2004
    Location
    Washington , USA
    Posts
    132

    Strange - datafile size & export .dmp file size

    Folks

    Oracle 8.1.7
    linux

    I am not able to understand this equation

    see i have a datafile which is 7.5 GB now. I have added so far 6 datafiles to exitsing tablespace with alter tablespace add datafile command.

    Now the export backup .dmp FILE SIZE of this schema( only this schema ) comes to almost 3 Gb.

    I query this command from view dba_free_space
    select sum(bytes)/1024/1024,tablespace_name from dba_free_space group by tablespace_name;
    The tablespace FREE space now is 450mb it means that when it reaches almost 50mb i have to add another datafile ' ie 7th datafile'

    what u guys suggest.




    now my question is will tablespace AUTOEXTEND option help in this scenario.
    or
    manually adding the datafile will help me - because u see it helps me to monitor the space growth

  2. #2
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995

    Re: Strange - datafile size & export .dmp file size

    Kinda looks like babble fish translated this but I'll try and give it a go.

    see i have a datafile which is 7.5 GB now. I have added so far 6 datafiles to exitsing tablespace with alter tablespace add datafile command.

    I don't think this matters but.. if one is 7.5gigs how big are the others to the corresponding tablespace?

    Now the export backup .dmp FILE SIZE of this schema( only this schema ) comes to almost 3 Gb.
    If you already have one datafile out there that's 7.5 gigs and you're export size for the whole schema is only 3 gigs you're either very lucky (and thank yourself for it) or you're not exporting rows, in which case I hope this is not your primary means of backup.

    I query this command from view dba_free_space
    select sum(bytes)/1024/1024,tablespace_name from dba_free_space group by tablespace_name;
    The tablespace FREE space now is 450mb it means that when it reaches almost 50mb i have to add another datafile ' ie 7th datafile'
    If you have only 450 megs of space left on that tablespace and you're in a production env, you should of made the tablespace bigger a long time ago. The thing I would ask is, how fast is this tablespace growing? Is there data being loaded on there that is not necessary and can be either deleted or archived off.
    But to answer your question without knowing the whole story I would add another datafile to the tablespace and make it big enough where you're not going to have to worry about space for awhile




    now my question is will tablespace AUTOEXTEND option help in this scenario.
    or
    manually adding the datafile will help me - because u see it helps me to monitor the space growth
    Using AUTOEXTEND in my opinon is a matter of the DBA's choice. Personally I hate it. I much rather keep tabs on the tablespace myself to watch growth rate and to keep tabs on the developers when they decide to make some load.
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  3. #3
    Join Date
    Apr 2003
    Posts
    353

    Re: Re: Strange - datafile size & export .dmp file size

    [/B] If you already have one datafile out there that's 7.5 gigs and you're export size for the whole schema is only 3 gigs you're either very lucky (and thank yourself for it) or you're not exporting rows, in which case I hope this is not your primary means of backup.


    no.. the possibilities are

    a. export does not include some other users belong to that database
    b. fragment

    if rows are not exported the file size at any cost will not exceed
    10m.. isnt it?.

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447

    Re: Strange - datafile size & export .dmp file size

    Originally posted by gajananpkini

    Now the export backup .dmp FILE SIZE of this schema( only this schema ) comes to almost 3 Gb.
    try compress=n when export

  5. #5
    Join Date
    Jan 2004
    Location
    Washington , USA
    Posts
    132
    Answer to ur Question

    First datafile was created with 2gb and when the tablespace was getting full i added 6 additional datafiles with 1 GB each everytime whenever i had to .

    everytime when 1 GB space was added i didnt worry for a while.
    Till 2 months it is ok. Then next 3rd month i have to add additional 1 GB space.

    The database is in archive log mode and Hot backup is the primary mode.

    Export backup is 3 Gb size and it includes all rows & data for that schema.

    i personally like to add datafiles when the tablespace gets full, because when i add i know and i can track it by querying dba_free_space.
    so now i feel is whenever i have to add a datafile instead of 1 GB i have to give 3 or 4gb and see it.

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    Re: Re: Re: Strange - datafile size & export .dmp file size

    Originally posted by engiri
    If you already have one datafile out there that's 7.5 gigs and you're export size for the whole schema is only 3 gigs you're either very lucky (and thank yourself for it) or you're not exporting rows, in which case I hope this is not your primary means of backup.


    no.. the possibilities are

    a. export does not include some other users belong to that database
    b. fragment

    if rows are not exported the file size at any cost will not exceed
    10m.. isnt it?. [/B]
    Add the most probable answer why export is much smaller than (apparently full) tablespace: because this tablespace also contains indexes, and index content is not included in the export dump file.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Jan 2004
    Location
    Washington , USA
    Posts
    132
    jurij has answerd my question.

    I was not knowing tablespace also contains indexes, and index content is not included in the export dump file.

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