This content has been marked as final. Show 5 replies
Are you using 11gR2 Enterprise Edition? Table compression is only available in EE, any other edition might tell you that the statement is invalid. The flavour you want (for OLTP) is an extra option on top of EE, which you'll have to be willing to pay extra for, if you want to use it for real.
See the [URL http://download.oracle.com/docs/cd/E11882_01/license.112/e10594/editions.htm#CJACGHEB]Oracle Database Licensing Information docs for details.
Hi Dan, how about here:
You can declare compression at the tablespace, table, partition, or subpartition level. If specified at the tablespace level, then all tables created in this tablespace are compressed by default.
The following statement applies OLTP compression to the orders table:
ALTER TABLE oe.orders COMPRESS FOR OLTP;
Whoops, I'll have to update my username, Andy Rivenes
Edited by: user12090872 on Apr 27, 2011 8:31 AM
damorgan wrote:What you want to do is create the table with compression enabled then insert your existing data into it using DIRECT LOAD.
Where did you see DDL indicating you could use ALTER TABLE to turn a non-compressed table into a compressed table?
Please provide the link.
What you want to do is create the table with compression enabled then insert your existing data into it using DIRECT LOAD.
This is 1 option to compress a table, but you don't have to do it like this. I have compressed tables as follows:
- First, alter the table to enable compression on it
ALTER TABLE "SCHEMA"."TABLE" COMPRESS FOR OLTP;
- Second, a reorg is required because existing table data is not compressed
ALTER TABLE "SCHEMA"."TABLE" MOVE;
I usually reorg large tables in parallel to speed up this process.