Monday, February 23, 2015

How to solve ORA-02180 when specifying COMPRESSION type

You get
ORA-02180: invalid option for CREATE TABLESPACE
when executing a create tablespace statement like this one:
CREATE TABLESPACE test DATAFILE 
  '/u02/oradata/mydb/test.ora' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED
COMPRESS FOR OLTP 
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;

Solution:
Add the DEFAULT keyword to specify the default parameters for the database:

CREATE TABLESPACE test DATAFILE 
  '/u02/oradata/mydb/test.ora' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED
DEFAULT
COMPRESS FOR OLTP 
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;

No comments:

Post a Comment