Strange - datafile size & export .dmp file size

[gajananpkini]

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

[OracleDoc]

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.

[engiri]

[/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?.

[pando]

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

[gajananpkini]

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.

[jmodic]

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.

[gajananpkini]

jurij has answerd my question.

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