ORA-2180 or ORA-933 on CREATE or ALTER of a Tablespace with COMPRESS FOR ALL OPERATIONS; (Doc ID 1419493.1)

Last updated on MARCH 27, 2014

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.3 - Release: 11.1 to 11.2
Information in this document applies to any platform.

Symptoms

Using the keywords 'COMPRESS FOR ALL OPERATIONS' (OLTP data compression) fails when trying to create or alter a tablespace in an 11g database.

CREATE TABLESPACE comp_test DATAFILE '/share_ic_1/compressed.dbf' SIZE 10G default compress for all operations;
--> SQL Error: ORA-02180: invalid option for CREATE TABLESPACE

ALTER TABLESPACE comp_test DEFAULT COMPRESS FOR ALL OPERATIONS;
--> SQL Error: ORA-00933: SQL command not properly ended


Note: OLTP compression was first introduced in 11g.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms