export dump

when we take a export dump with compress =y option, how does one get to know that the actual compression has taken place?

When you export to dump file with compress=y, EXPORT utility modifies the INITIAL value of the table to be the sum of the current extents. So that means if the table has 50 * 10K extents filled, then the INITIAL extent when you IMPORT the data will be 500K.

thanks , i very well know that this is what happens, but what i was wondering is that how do one come to know that such a option is used,, any query , DD views to tell us that compress =y has taken effect

.

check dba_extents.bytes and compare with the older value.

By default compress=y
if u want to retain the old storage values say compress=no

compress=y is a no no option

Quote:

---

Originally posted by sharmila
... but what i was wondering is that how do one come to know that such a option is used,, any query , DD views to tell us that compress =y has taken effect

---

AFAIK there is no DD views for this.

Quote:

---

Originally posted by pando
compress=y is a no no option

---

pando, please explain - tx

Quote:

---

Originally posted by DaPi
pando, please explain - tx

---

Oooh oooh! I know this one!

compress=y leads to tablespace fragmentation by having different sized extents created on import.

if you understand well delete, truncate, segments and extents you will see why

if I have 100 extents with 1MB each but there are only 1MB real data what happens if I export with compress=y? I will get one 100MB extent 99% empty, if I use compress=n what happens? I get 1extent of 1MB, I saved 99MB

I really wonder why oracle give this parameter default value of Y.
Since most expert say (which i also agree) this does not give any good in most case.

it defaults to Y maybe because in old Oracle versions EXP/IMP were used to reduce number of extents (in Oracle 7 number of extents were limited if I remember right) so it was a must use compress = y

Thanks pando,
I was coming from a world with very few deletes (I have to keep history, so I set end-dates). I guess compress=Y would be OK for cases where there was a bad guess on the initial extent size and most of the 100 extents are "full"? (There's always DEALLOCATE UNUSED for when I get it wrong!)

well with 8i why exp/imp to change initial size when you can do alter table x move?

deallocated unused wont get you below highwatemark

Thanks pando, I cut my teeth on 7.3.4 - looks like I need to RTF8iM.

Sorry! One last . . .

Quote:

---

Originally posted by pando
deallocated unused wont get you below highwatemark

---

are you saying the imp doesn't reset the h.w.m. to the top of "real" data (if the table has been truncated or dropped before imp)?

import wont reset your HWM if you use compress=y, it will simply create the new table with an initial size of your

number_of_extents_in_original_database_of_the_table

OOOOH! I see, thanks pando.

BTW It better to use Locally Managed Tablespaces and there isn't more concerns about COMPRESS. If you do export from DMT (COMPRESS=Y) to LMT - the INITIAL just ignored.