Using additional dbspaces

Typically needed for large databases

For most databases, a single database file is enough. However, for users of large databases, additional database files are sometimes necessary. Additional database files are also convenient tools for clustering related information in separate files.

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 command 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.

IN DBSPACE clause (if specified)
default_dbspace option (if set)
system dbspace

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.

For information about the maximum file size allowed on the supported operating systems, see SQL Anywhere size and number limitations.

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

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

Splitting existing databases

If you want to split existing database objects among multiple dbspaces, you must unload your database and modify the generated command file (named reload.sql by default) for rebuilding the database. In the reload.sql file, add IN clauses to the CREATE TABLE statements to specify the dbspace for each table you do not want to place in the main file.

Permissions on dbspaces

SQL Anywhere supports permissions on dbspaces. Only the CREATE permission is supported. The CREATE permission allows a user to create database objects in the specified dbspace. You can grant CREATE permission for a dbspace by executing a GRANT CREATE statement. See GRANT statement.

Dbspace permissions behave as follows:

A user trying to create a new object with underlying data must have CREATE permission on the dbspace where the data is being placed.
Even if a GRANT CREATE ON statement was issued, the user (grantee) must have RESOURCE authority to create new database objects.
The current list of objects that can be placed in specific dbspaces, and that require the CREATE permission, includes tables, indexes, text indexes, and materialized views. Note that objects such as normal views and procedures do not have any underlying data and do not require the CREATE permission.
A user can be granted the CREATE permission directly, or they can inherit the permission through membership in a group that has been granted the permission.
It is possible to grant PUBLIC the CREATE permission on a specific dbspace, in which case any user who also has RESOURCE authority can create objects on the dbspace.
A newly-created dbspace automatically grants CREATE permission on itself to PUBLIC.
It is possible to revoke permissions, for example when trying to secure a dbspace. Permissions on the internal dbspaces system and temporary can also be managed to control access.
Creating local temporary tables does not require any permissions; dbspace permissions do not affect the creation of local temporary tables. However, the creation of global temporary tables requires RESOURCE authority and CREATE permission on the temporary dbspace.