oracle database, error in adding datafiles to an existing tablespace

Question

I want to add datafiles to an existing tablespace. The command I am using is:

ALTER TABLESPACE "HIGH_VOLUME"
ADD DATAFILE '/data2/oracle/oradata/CMGPERF02/hv05.dbf'
SIZE 30G AUTOEXTEND ON NEXT 100M LOGGING DEFAULT NOCOMPRESS ONLINE EXTENT 
MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;

error:

Error starting at line : 28 in command - ALTER TABLESPACE "HIGH_VOLUME" ADD DATAFILE '/data2/oracle/oradata/CMGPERF02/hv05.dbf' SIZE 30G AUTOEXTEND ON NEXT 100M LOGGING DEFAULT NOCOMPRESS ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO Error report - ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended"

I have sysdba access , so it might not be an access related issue i guess. Even I searched in oracle docs site and writen answer is.

ALTER TABLESPACE lmtbsb
ADD DATAFILE '/u02/oracle/data/lmtbsb02.dbf' SIZE 1M;

there might be syntax error from my end. kindly help me to rectify it. Oracle version is 12.1.0.2.

Thanks

kfinity kfinity · Accepted Answer · 2018-10-02T17:50:39

The docs are pretty confusing to read, but basically you're combining tablespace-level options (like logging and compression) with an ADD DATAFILE clause, which doesn't work. The only options which can follow ADD DATAFILE are SIZE, REUSE, and AUTOEXTEND. If you want to alter tablespace options, use a separate ALTER TABLESPACE command.

ALTER TABLESPACE "HIGH_VOLUME" 
ADD DATAFILE '/data2/oracle/oradata/CMGPERF02/hv05.dbf' 
SIZE 30G 
AUTOEXTEND ON NEXT 100M
-- These are tablespace options which can't be applied to single datafiles: 
--LOGGING 
--DEFAULT 
--NOCOMPRESS 
--ONLINE 
--EXTENT MANAGEMENT LOCAL AUTOALLOCATE 
--SEGMENT SPACE MANAGEMENT AUTO

oracle database, error in adding datafiles to an existing tablespace

1 Answers