“Invalid option” while creating a tablespace

oracle

Can someone tell me what is wrong with this syntax:

CREATE TABLESPACE ACME_DATA
DATAFILE '/oracle/d02/acme/ACME_DATA_01.dbf'
SIZE 40M
EXTENT MANAGEMENT LOCAL
AUTOEXTEND ON NEXT 2M
MAXSIZE 100M
UNIFORM SIZE 128KB
SEGMENT SPACE MANAGEMENT AUTO;

Error report:

SQL Error: ORA-02180: invalid option for CREATE TABLESPACE
02180. 00000 -  "invalid option for CREATE TABLESPACE"

Best Answer

The autoextend option belongs to the datafile specification (one per datafile), not to the tablespace itself, and the uniform option is part of the extent management clause, so you've got a mixup there.

Try:

CREATE TABLESPACE ACME_DATA
DATAFILE
  '/oracle/d02/acme/ACME_DATA_01.dbf' SIZE 40M AUTOEXTEND ON NEXT 2M MAXSIZE 100M
EXTENT MANAGEMENT
  LOCAL UNIFORM SIZE 128K
SEGMENT SPACE MANAGEMENT
  AUTO;

(Note: size units are K, M, G, not KB or similar.)

Related Question