Altering a Database
Go Up to Creating Databases (Data Definition Guide)
Use ALTER DATABASE to add one or more secondary files to an existing database. Secondary files are useful for controlling the growth and location of a database. They permit database files to be spread across storage devices, but must remain on the same node as the primary database file. For more information on secondary files, see Creating a Multifile Database.
A database can be altered by its creator, the SYSDBA user, and any users with operating system root privileges.
ALTER DATABASE requires exclusive access to the database. For more information about exclusive database access, see “Shutting down and restarting databases” in the “Database Configuration and Maintenance” chapter of the Operations Guide.
The syntax for ALTER DATABASE is:
ALTER {DATABASE | SCHEMA}
{ADD <add_clause> | DROP <drop_clause> | ENCRYPT <key_name> | DECRYPT <key_name> | SET <set_clause>};
<add_clause> = FILE 'filespec' [fileinfo] [add_clause] | ADMIN OPTION
fileinfo = LENGTH [=] int [PAGE[S]]
| STARTING [AT [PAGE]] int [fileinfo]
<drop_clause> = ADMIN OPTION
<key_name> = ENCRYPT <|> DECRYPT
<set_clause> = {FLUSH INTERVAL <number> | NO FLUSH INTERVAL | GROUP COMMIT | NO GROUP COMMIT |
LINGER INTERVAL <number> | NO LINGER INTERVAL | PAGE CACHE <number> | RECLAIM INTERVAL <number> | NO RECLAIM INTERVAL | SYSTEM ENCRYPTION PASSWORD <255-character_string> | NO SYSTEM ENCRYPTION PASSWORD} | PASSWORD DIGEST '<digest_name>'}
You must specify a range of pages for each file either by providing the number of pages in each file, or by providing the starting page number for the file. For more details about the ALTER DATABASE syntax, see ALTER DATABASE.
It is never necessary to specify a length for the last – or only – file, because InterBase always dynamically sizes the last file and will increase the file size as necessary until all the available space is used.
The first example adds two secondary files to the currently connected database by specifying the starting page numbers:
ALTER DATABASE
ADD FILE 'employee2.ib' STARTING AT PAGE 10001 LENGTH 10000
ADD FILE 'employee3.ib' STARTING AT PAGE 20001
The next example does nearly the same thing as the previous example, but it specifies the secondary file length rather than the starting page number. The difference is that in the previous example, the original file will grow until it reaches 10000 pages. In the second example, InterBase starts the secondary file at the next available page and begins using it immediately.
ALTER DATABASE
ADD FILE 'employee2.ib' LENGTH 10000
ADD FILE 'employee3.ib'