Skip to content

How to Resolve ORA-02494: invalid or missing maximum file size in MAXSIZE clause

  • by

ORA-02494

There're several error patterns of ORA-02494 in this post.

  1. Missing Size Unit
  2. Incorrect Keyword
  3. Irrational DDL

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 tablespace DDL.

But my question is: how can this problem happen? Any comment will be appreciated.

Leave a Reply

Your email address will not be published. Required fields are marked *