ALTER TABLESPACE ADD DATAFILE

How to Resolve ORA-01641: tablespace is not online - cannot add data file

by Ed Chen
August 5, 2022December 15, 2022

ORA-01641 Tried to add a data file to a tablespace, but it failed with ORA-01641. SQL> alter tablespace example add datafile '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example01.dbf' size 10m autoextend… Read More »

How to Resolve ORA-00959: tablespace does not exist

by Ed Chen
May 25, 2022March 20, 2023

ORA-00959 ORA-00959 means that the tablespace you specified in the statement cannot be found in the database, you should check the name you provide or… Read More »

How to Resolve ORA-02140: invalid tablespace name

by Ed Chen
May 23, 2022October 19, 2022

ORA-02140 There're 2 types of ORA-02140 when we tried to add a data file to a tablespace. 1. Use a Keyword as Tablespace Name We… Read More »

How to Resolve ORA-27040: file create error, unable to create file

by Ed Chen
May 18, 2022December 17, 2022

ORA-27040 Tried to add a data file to a tablespace, but it failed with ORA-01119 and ORA-27040. SQL> ALTER TABLESPACE EXAMPLE ADD DATAFILE '/u01/app/oracle/oradata/ORCLCDB/ORCLPDBX/example02.dbf' SIZE… Read More »

How to Resolve ORA-27038: created file already exists

by Ed Chen
December 29, 2021May 18, 2022

ORA-27038 Tried to add a data file to a tablespace, but it failed with ORA-27038. SQL> alter tablespace example add datafile '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB/example03.dbf' size 10m autoextend… Read More »

How to Resolve ORA-01537: cannot add file

by Ed Chen
December 27, 2021June 27, 2022

ORA-01537 Tried to add a data file to a permanent tablespace, but it failed with ORA-01537. SQL> alter tablespace example add datafile '/u01/app/oracle/oradata/ORCL/example01.dbf' size 10m… Read More »

How to Resolve ORA-00905: missing keyword

by Ed Chen
December 24, 2021March 22, 2023

ORA-00905 ORA-00905 is a very broadly used error message, any expected keyword missing from its statement will result in ORA-00905. These are only cases we… Read More »

How to Resolve ORA-02142: missing or invalid ALTER TABLESPACE option

by Ed Chen
December 22, 2021April 7, 2022

ORA-02142 Tried to add a new data file to a tablespace, but it failed with ORA-02142. SQL> ALTER TABLESPACE EXAMPLE DATAFILE '/u01/app/oracle/oradata/ORCLCDB/ORCLPDB1/example02.dbf' SIZE 10M AUTOEXTEND… Read More »

How to Resolve ORA-00933: SQL command not properly ended

by Ed Chen
September 22, 2021February 20, 2023

ORA-00933 ORA-00933 means that you use an unexpected word or phrase at the position where SQL parser knows what qualified keyword should be. Use cases… Read More »

How to Resolve ORA-03206: maximum file size of (4194304) blocks in AUTOEXTEND clause is out of range

by Ed Chen
November 25, 2020December 9, 2022

ORA-03206 Theoretically, we can add a data file with maximum size of 32 GB to a tablespace with 8 KB block size. Actually, we can't.… Read More »