ORA-02494
There're several error patterns of ORA-02494 in this post.
A. Missing Size Unit
Let's see the example.
SQL> create tablespace erptbs datafile '/u01/app/oracle/oradata/ORCL/erptbs_01.dbf' size 10m autoextend on next 10m maxsize 31;
create tablespace erptbs datafile '/u01/app/oracle/oradata/ORCL/erptbs_01.dbf' size 10m autoextend on next 10m maxsize 31
*
ERROR at line 1:
ORA-02494: invalid or missing maximum file size in MAXSIZE clause
In the above, we used 31 for the size clause of MAXSIZE of the data file without specifying the size unit G for gigabyte, so SQL parser interpreted 31 as bytes and pass it into SQL engine. 31 bytes is way too small and less than a data block, not to mention the initial size 10M in this case.
To fix it, you should use a proper size unit for the value.
B. Incorrect Keyword
Let's see the example.
SQL> create tablespace erptbs datafile '/u01/app/oracle/oradata/ORCL/erptbs_01.dbf' size 10m autoextend on next 10m maxsize unlimit;
create tablespace erptbs datafile '/u01/app/oracle/oradata/ORCL/erptbs_01.dbf' size 10m autoextend on next 10m maxsize unlimit
*
ERROR at line 1:
ORA-02494: invalid or missing maximum file size in MAXSIZE clause
If you want the data file to grow without specifying a ceiling, UNLIMIT is not the keyword, UNLIMITED is the correct keyword for specifying MAXSIZE.
In fact, the data files do have a max size, it's around 32GB for a small tablespace.
C. Irrational DDL
Generated DDL from tools may not be executable sometimes. I got ORA-02494 when I execute DDL scripts that were generated from other database by TOAD, a very popular administration tool used by DBA.
SQL> CREATE TABLESPACE PERSON DATAFILE
2 '/u02/app/oracle/oradata/datastore/.ACFS/snaps/orcl/ORCL/datafile/user01.dbf' SIZE 5G AUTOEXTEND ON NEXT 20M MAXSIZE 4G
3 LOGGING
4 ONLINE
5 EXTENT MANAGEMENT LOCAL AUTOALLOCATE
6 BLOCKSIZE 8K
7 SEGMENT SPACE MANAGEMENT MANUAL
8 FLASHBACK ON;
LOGGING
*
ERROR at line 3:
ORA-02494: invalid or missing maximum file size in MAXSIZE clause
As you can see, we tried to create a tablespace with a 5GB datafile, but it's limited within MAXSIZE 4GB. This is the biggest problem that throw ORA-02494.
Corrected DDL
Now let's make it rational, we change MAXSIZE 4GB to 10GB.
SQL> CREATE TABLESPACE PERSON DATAFILE
2 '/u02/app/oracle/oradata/datastore/.ACFS/snaps/orcl/ORCL/datafile/user01.dbf' SIZE 5G AUTOEXTEND ON NEXT 20M MAXSIZE 10G
3 LOGGING
4 ONLINE
5 EXTENT MANAGEMENT LOCAL AUTOALLOCATE
6 BLOCKSIZE 8K
7 SEGMENT SPACE MANAGEMENT MANUAL
8 FLASHBACK ON;
Tablespace created.
A lesson learned from this issue would be that we should inspect all DDL scripts generated from tools before actually executing them, especially creating tablespace DDL.
But my question is: how can this problem happen? Any comment will be appreciated.