Problem solve Get help with specific problems with your technologies, process and projects.

Identifying the Oracle datafile MAXSIZE

A simple way of identifying the maximum size to which a datafile can grow before switching to the next datafile.

There is always some ambiguity regarding the datafile size limit on a given platform and version of the database....

Here is a simple way of identifying the maximum size to which a datafile can grow before switching to the next datafile. To do this let's create a tablespace with some default values and set AUTOEXTEND on without the MAXSIZE specified:

SVRMGR>Create tablespace WORK_SPACE_DATA 
       datafile '/keg4/oradata/fpdev1/work_space_data01.dbf' 
       size 400M AUTOEXTEND ON 

Now let's get the file id of the datafile created for the WORK_SPACE_DATA tablespace:

SVRMGR>select file_id,file_name,autoextensible 
       from dba_data_files 
       where tablespace_name like 'WORK_SPACE_DATA'; 

----------    ---      ------- 
31            YES      /keg4/oradata/fpdev1/work_space_data01.dbf 

Querying the filext$ table, we can get the value of the maximum size in database blocks to which the datafile can grow before switching to the next file. Technically this must be the maximum filesize that Oracle can understand for a database datafile.

SVRMGR> select * from filext$ 
                     where file# = 31; 

FILE#              MAXEXTEND             INC 
----------        ----------           ---------- 
        31         4194302                1 

Setting the default would give you a maximum value of 4194302 blocks with increments of 1 block when the datafile extends. On a database of 8K block size this would work out to be:

SVRMGR> select (4194302*8192)/1024 from dual; 

So, the database datafile can have a maximum size of 33.55Gb on a 8k database block size and Sun Solaris8 platform. The above shows that the dependency is on the database block size for a given platform. Also if we do not set the MAXEXTEND for the datafile, then the first datafile would grow to the MAXEXTEND value and only then would shift to the next datafile for a given tablespace with multiple datafiles. So it is imperative to set the MAXEXTEND value when turning on the AUTOEXTEND option.

For More Information

  • What do you think about this tip? E-mail the Editor at [email protected] with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle and SQL