Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.4K Intelligent Advisor
- 75 Insurance
- 537.7K On-Premises Infrastructure
- 138.7K Analytics Software
- 38.6K Application Development Software
- 6.1K Cloud Platform
- 109.6K Database Software
- 17.6K Enterprise Manager
- 8.8K Hardware
- 71.3K Infrastructure Software
- 105.4K Integration
- 41.6K Security Software
Help to enable Advanced Compression

818314
Member Posts: 5
Hi,
I'm trying to create a table using COMPRESS FOR OLTP option but when I run "alter table myTable compress for oltp;" an "invalid ALTER TABLE option" occurs.
I'm on 11gR2, my compatible value in gv$parameter is 11.2.0.0.0.
Any ideas?
Thank you.
I'm trying to create a table using COMPRESS FOR OLTP option but when I run "alter table myTable compress for oltp;" an "invalid ALTER TABLE option" occurs.
I'm on 11gR2, my compatible value in gv$parameter is 11.2.0.0.0.
Any ideas?
Thank you.
Answers
-
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. -
Hi Seph,
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.
Peter. -
My understanding is that you can turn on compression for a non-compressed table and that only data inserted afterwards will get compressed, existing non-compressed data will remain uncompressed.
-
Hi Dan, how about here:
http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/tablecls.htm#CNCPT89136
Compression Declaration
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.
This discussion has been closed.