COMPRESS a table

[pbopbo]

Hello,

I want to compress a table which contains blobs.
I tried

ALTER TABLE MYTABLE COMPRESS

but I get this error :

ORA-00439: feature not enabled : Heap segment compression

How can I enable this feature?
Is it the only way to compress BLOB data ?

Thanx,

Philippe

 

[Scunningham99]

have you tried imp/exp.



Sy UK

 

[pbopbo]

What do you mean ?
I don't want to export the table, I just want it to be smaller in Oracle...

 

[Scunningham99]

when you import it back in with compress option it will be only using one segment.

Sy UK

 

[sem]

What version do you use? Is it 9.2 EE?

Regards, Dima

 

[pbopbo]

OK, I understand now...
I'll try this. But as I want to keep this table always as small as possible I will be forced to program this procedure in an automatic job. Is there no way to let Oracle do this job ?

 

[pbopbo]

Yes 9.2 EE...

 

[SantaMufasa]

I just want to clarify a concept to which SCunningham99 alluded 4 replies ago: "...when you import it back in with compress option it will be only using one segment."

When you do a exp "COMPRESS=Y", this directs the utility to evaluate the entire size of a table (perhaps 100MB, spread across dozens of extents) and create a storage parameter for table that reads, "STORAGE (INITIAL 100M...)" so that when IMP processes the table, it attempts to place the entire table into one EXTENT (Notice, "EXTENT" not "SEGMENT" as Sy suggested; a SEGEMENT is, for example, a table or an index; an EXTENT is a contiguous allocation of blocks within a SEGMENT.) And when EXP/IMP follow the COMPRESS=Y directive, it is a misnomer...there is no "compression" taking place at all; only "consolidation". Oracle would have been much more accurate to specify the EXP parameter as "CONSOLIDATE=Y", but Oracle's software developers have never been known for their literary skills anyway.

Cheers,

Dave
Sandy, Utah, USA @ 17:12 GMT, 10:12 Mountain Time

 

[sem]

I suppose that Philippe talks not about compressing table while importing, but rather about a new Oracle 9.2 feature to compress the whole table in "natural" sence: keep it compress on disk and uncompress by need.

Philippe, documentation says that LOB segments are not compressed. I'm not very sure in my own advice, but try to specify separate storage for your LOB column(s) to not store them inline.

Regards, Dima

 

[pbopbo]

You have understood me, Dima. As I wrote in my firt message, there is a

COMPRESS

command that can be applied to a table through an

ALTER TABLE

command. But it seems that I need a specific feature (

Heap segment compression

). And I'm not sure that this feature is adequate for my purpose. Any complementary informations about this feature ?

 

[sem]

Ok, I got it. You should install Data Compression feature (part of Business Intelligence). It's not an option, thus should be installed with EE by default. Probably somebody unchecked it during install.

Regards, Dima

 

[pbopbo]

Thank you, I'll try to add this feature to the server.

Cheers,

Philippe