For most databases, a single database file is sufficient. 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. All database objects and all data are placed, by default, 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 can only be 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 228 (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 (228*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 many commonly-employed file systems restrict file size to a maximum of 2 GB, some, such as the Windows file system 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.
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.
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:
|Send feedback about this page via email or DocCommentXchange||Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0|