When you initialize a database, it contains one database file. This first database file is called the main file or the system dbspace. By default, all database objects and all data are placed in the main file.

A dbspace is an additional database file that creates more space for data. A database can be held in up to 13 separate files (the main file and 12 dbspaces). Each table, together with its indexes, must be contained in a single database file. The SQL statement CREATE DBSPACE adds a new file to the database.

Temporary tables are only created in the temporary dbspace.

There are several ways to specify the dbspace where a base table or other database object is created. In the following lists, the location specified by methods occurring earlier in the list take precedence over those occurring later in the list.

If a dbspace name contains a period and is not quoted, the database server generates an error for the name.

Each database file has a maximum allowable size of 2²⁸ (approximately 268 million) database pages. For example, a database file created with a database page size of 4 KB can grow to a maximum size of one terabyte (2²⁸*4 KB). However, in practice, the maximum file size allowed by the physical file system in which the file is created affects the maximum allowable size significantly.

While some older file systems restrict file size to a maximum of 2 GB, many file systems, such as Windows using the NTFS file system, allow you to exploit the full database file size. In scenarios where the amount of data placed in the database exceeds the maximum file size, it is necessary to divide the data into more than one database file. As well, you may want to create multiple dbspaces for reasons other than size limitations, for example, to cluster related objects.

You can use the sa_disk_free system procedure to obtain information about space available for a dbspace.

The SYSDBSPACE system view contains information about all the dbspaces for a database.

 Splitting existing databases

 See also