when we take a export dump with compress =y option, how does one get to know that the actual compression has taken place?
Printable View
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
AFAIK there is no DD views for this.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
pando, please explain - txQuote:
Originally posted by pando
compress=y is a no no option
Oooh oooh! I know this one!Quote:
Originally posted by DaPi
pando, please explain - tx
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 . . .
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)?Quote:
Originally posted by pando
deallocated unused wont get you below highwatemark
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.