Space increase while Export

[dba_akram]

Hi All,
I have a serious space issue with my 8i server running on Sun E3500, Ultra sparc. The problem is related to export utility. I exported a user from ptroduction to my staging area with compress = n, and ater with compress = y. The amount of space occupied by the tablespace dedicated for this schema shoot up 3 imes than the actual one in prod. I later had to export the same schema on staging to test/development region. I again tried to export with compress = y and n. The space shoot up to 12times actual one. Is there a way I can get rid of such space increase. Actual size in prod was 205Mb and in staging it is around 700Mb and now in testing area it is 3.2Gb. Can anyone of you let me know the reason as well as remedy for this. Anywork around available?

[roobaron]

Hi,

compress =y will compress all extents into one initial extent.
compress =n will leave the table as it appears in the db.

example:
table a has initial and next extent of 100M and has 20 extents
export the table a with compress=y,
import table a into dev database,
table a in dev database will have the following storage
initial extent 2000M next 100M.

I would not use compress=y and having a minimum number of extents does NOT effect performance (have a look at ASK TOM oracle website) there is a potential problem with deletions and truncate, due to a cluster key in dictionary tables but a 100-200 extents is not bad.

Have Fun

[dba_akram]

Hi roobaron,
I tired to export with compress=n and I did not find much of a difference for it. It was almost the shoot up in percentage of size except diff of 5%. If I do alter table deallocate unused on my target database, will i have my space relased. Iam pretty confident that the space is related to this export's initial storage parm reading. Is there any workaround for this.
akram