Oracle data file size

Question

My client’s tablespaces are autoextend off and i add monthly or bimonthly 3 or 6 gb space for tablespaces. Can you advise me if there is a recommended datafile size for Oracle 11gR2 on Windows 64 Bits? We agree that datafile max size shouldn't exceed 32 GB but i would like to know if there is a recommended size and if it is it depend on which parameters.

Answer 1

From the comments I see there are some misconceptions about tablespaces and datafile sizes. Here is some Information from the manuals. There are two types of tablespaces in Oracle: bigfile tablespaces and smallfile tablespaces.

In the description of the CREATE TABLESPACE statement in the SQL Language Reference we read:

BIGFILE | SMALLFILE

Use this clause to determine whether the tablespace is a bigfile or smallfile tablespace. This clause overrides any default tablespace type setting for the database.

• A bigfile tablespace contains only one data file or temp file, which can contain up to approximately 4 billion (2³²) blocks. The minimum size of the single data file or temp file is 12 megabytes (MB) for a tablespace with 32K blocks and 7MB for a tablespace with 8K blocks. The maximum size of the single data file or temp file is 128 terabytes (TB) for a tablespace with 32K blocks and 32TB for a tablespace with 8K blocks.

• A smallfile tablespace is a traditional Oracle tablespace, which can contain 10²²data files or temp files, each of which can contain up to approximately 4 million (2²²) blocks.

If you omit this clause, then Oracle Database uses the current default tablespace type of permanent or temporary tablespace that is set for the database. If you specify BIGFILE for a permanent tablespace, then the database by default creates a locally managed tablespace with automatic segment-space management.

Restriction on Bigfile Tablespaces

You can specify only one data file in the DATAFILE clause or one temp file in the TEMPFILE clause.

More information can be found in the Oracle Database Administrator's Guide:

About Bigfile Tablespaces

A bigfile tablespace is a tablespace with a single, but potentially very large (up to 4G blocks) data file. Traditional smallfile tablespaces, in contrast, can contain multiple data files, but the files cannot be as large.

The benefits of bigfile tablespaces are the following:

• A bigfile tablespace with 8K blocks can contain a 32 terabyte data file. A bigfile tablespace with 32K blocks can contain a 128 terabyte data file. The maximum number of data files in an Oracle Database is limited (usually to 64K files). Therefore, bigfile tablespaces can significantly enhance the storage capacity of an Oracle Database.

• Bigfile tablespaces can reduce the number of data files needed for a database. An additional benefit is that the DB_FILES initialization parameter and MAXDATAFILES parameter of the CREATE DATABASE and CREATE CONTROLFILE statements can be adjusted to reduce the amount of SGA space required for data file information and the size of the control file.

• Bigfile tablespaces simplify database management by providing data file transparency. SQL syntax for the ALTER TABLESPACE statement lets you perform operations on tablespaces, rather than the underlying individual data files.

Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment space management, with three exceptions: locally managed undo tablespaces, temporary tablespaces, and the SYSTEM tablespace.

Note:

• Bigfile tablespaces are intended to be used with Automatic Storage Management (Oracle ASM) or other logical volume managers that supports striping or RAID, and dynamically extensible logical volumes.

• Avoid creating bigfile tablespaces on a system that does not support striping because of negative implications for parallel query execution and RMAN backup parallelization.

• Using bigfile tablespaces on platforms that do not support large file sizes is not recommended and can limit tablespace capacity. See your operating system specific documentation for information about maximum supported file sizes.

The DB_BLOCK_SIZE is usually 8K, so the datafiles of a smallfile tablespace is limited by 32G. The datafile of a largefile tablespace is limited by about 32T (or by the maximum filesize allowed by the OS). But a bigfile tablespace van only have one datafile. So if you have 8K DB_BLOCK_SIZE the tablespace size is limitedto 32M in both cases.

Yous shouldn't use bigfile tablespaces if you do not use a volume manager that allows the arbitrary extention of the size of a volume. This must be also true for the systems where you may run copies of the database, e,g, systems where you do restore tests or test systems.

The advantage of a bigfile tablespace is that you do not have to add data files if you want to extend the tablespac because Oracle can extend the file automatically.

But except for very large and fast growing tablespaces I prefer smallfile tablespaces. Thirty 32G files are easier to manage than one 1T file. For example assume you have to copy the datafiles of a tablespace. If you have a single 1T file, you can start only a single copy command. If you have 32 files of size 32G you can start 32 copy commands in parallel and if your storage is fast enough it will take only a thirty-second of the time to copy the tablespace. If you want to zip this copy you have the same advantage if you have many small files instead of one large.

With RMAN you have the possibility to to a copy of a file in parallel, but not before 12c.

So all in all I prefer smallfile tablespaces in most cases. If the system tablespace is a bigfile tablespace then all tablespaces must be bigfile tablespaces. So I suggest to create the system tablespace as smallfile tablespace except you want to force all tablespaces to be created as bigfile tablespaces.

When I use smallfile tablespaces one may restrict the size of a datafile to less than 32G if one fears that the tablespace may be blown up by a defect application. But finally I allow datafiles to grow up to 32G.

Answer 2

There is no recommended max size that I am aware of. For me it is a matter of convenience. I keep my datafiles to 10GB each to make copying easier, a 10GB file is less likely to have issues copying than a 32 GB size file.

Answer 3

You don't tell, how you add the space, via autoextend or new datafiles!

There is no clear recommendation, that suits to every environment and every personal opinion. Nowadays on 64-bit machines and filesystems with very large file support, you don't need to care about filesizes. On Win10, you have a file limit of 16EIB (2^60), which you will probably never reach ever. The time is over, that filesystems have relevant hard limits.

On the other side, Oracle has internal limits, which cannot be exceeded. If you have sufficient diskspace, increase tablespaces by AUTOEXTEND their datafiles (if not at maximum) to the maximum allowed in relation to Tablespace blocksize. You can limit AUTOEXTEND yourself per datafile, if you like. Don't think too much about recommended datafile size. You will find many people with different opinions and pro's and con's for each. It's nowadays a philosophic question.

My personal advice: You should limit datafiles to maximum allowed size directly (8k block=32GB, 16k=64GB, 32k=128GB), that can be managed within acceptable time in case of trouble like data-loss. If you have very fast HW, then files might get bigger without problems.