Extent Management

[shailendra]

Hi friends

I have Locally Managed Tablespace I have exported one table from that tablespace with compress=y option and try to import that table into another tablespace but it is not creating one extent.

Is there any use of parameter compress if you have locally managed tablespace?

Thanks

Shailendra

[jovery]

If extent management is automatic Oracle will determine the extent sizes no matter what extent parameters you determine at creation.

Regards

[shailendra]

I have tried with both the option Manual as well as Auto but it is still not working it is creating no of extents instead of making one extends.

Please suggest me
Shailendra

[gandolf989]

If you knew that your biggest table was 20mb, and you imported into a tablespace with a 20mb uniform extent size. Then you would only get one extent for each table. But it doesn't matter if you have more than one extent. My preference would be to have different sized tablespaces all uniform extent, with extent sizes of 1mb, 10mb, 100mb, depending on the actual size of your tables as well as how fast they will grow. If you use a uniform extent size, extents will always be the same size just like Jovery said.

[grjohnson]

Why would anyone want to use the COMPRESS=Y option?

[jmodic]

Originally posted by grjohnson
Why would anyone want to use the COMPRESS=Y option?

Perhaps an even more interesting question:

Why does Oracle still insist with the default of COMPRESS=Y after all those new releases since Oracle5 when this default was set?

[grjohnson]

Originally posted by jmodic
Why does Oracle still insist with the default of COMPRESS=Y after all those new releases since Oracle5 when this default was set?[/B] [/B]

It has me mystified...

[mrchrispy]

whats wrong with compress=Y?
Are there any drawback with having a table in one extent?

[jovery]

An example of the problem with compress=y

If you have a table with 200 2Mb extents but it only contains 10Mb of data when you export the table with compress=y Oracle to take a sum of the total extents and use that for the intial value.

Hence when the object is recreated with the import it will be 400Mb in size to store 10Mb of data. If however, you use compress=N the object will be recreated with 5 extents occuppting just 10Mb.

Regards

[jmodic]

Another, much more severe (a show stopper!) example of the potential problem when using COMPRESS=Y:

You have a table with 5 extents of 500MB each that was exported with COMPRESS=Y. Now you want to import this table. You have plenty of space in your destination tablespace, say 10GB, but this tablespace is composed of 5 datafiles, each 2GB in size. You won't be able to import it, since the initial extent will require 2.5 GB of space and an extent can not span the datafiles!