DDLTablespace

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

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.

Irrational DDL

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 *