ALTER DATABASE
Go Up to Statement and Function Reference (Language Reference Guide)
Changes the characteristics of the current database. Available in gpre
, DSQL, and isql
, but not in the trigger or stored procedure language.
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>'}
In SQL statements passed to DSQL, omit the terminating semicolon. In embedded applications written in C and C++, and in
isql
, the semicolon is a terminating symbol for the statement, so it must be included.Argument | Description |
---|---|
|
Alternative keyword for |
|
Adds one or more secondary files to receive database pages after the primary file is filled; for a remote database, associate secondary files with the same node. |
|
Specifies the range of pages for a secondary file by providing the number of pages in each file. |
|
Specifies a range of pages for a secondary file by providing the starting page number. |
|
Enables embedded user authentication. |
|
Disables embedded user authentication. |
|
Uses the named encryption key to encrypt the database. Encrypting a database causes all pages to be encrypted. Only the database owner can encrypt a database. |
|
Uses the named encryption key to decrypt the database. Decrypting a database causes all pages to be decrypted and rewritten in plaintext. Only the database owner can decrypt a database. |
|
Enables database flush. The interval <number> is interpreted in units of seconds. |
|
Disables database flush. |
|
Allows transactions to be committed by a background cache writer thread. |
|
Disables group commit. |
|
Allows a database to remain in memory after the last user detaches. Interval is in seconds. |
|
Disables database linger. |
|
Determines how often the garbage collector thread will run to release memory from unused procedures, triggers, and internal system queries back to InterBase memory heap. Interval is in seconds. Default is 300 seconds when the database is created. |
|
Disables memory reclamation. |
|
Necessary to create encryption keys and perform encryption. InterBase uses a System Encryption Password (SEP) to protect the encryption keys that are used to encrypt the database and/or database columns. For more information about using InterBase encryption, see “Encrypting Your Data” in the Data Definition Guide.
|
|
Deletes the password if there are no existing encryption keys.
|
|
Sets database page buffer cache limit. Also, tries to expand cache to that limit. |
|
Sets the password hash function. The default value is |
Description: ALTER DATABASE
adds secondary files to an existing database. Secondary files permit databases to spread across storage devices, but they must remain on the same node as the primary database file. 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.
InterBase dynamically expands the last file in a database as needed. The maximum size of the last file is system-dependent. You should be aware that specifying a LENGTH for such files has no effect.
You cannot use ALTER DATABASE
to split an existing database file. For example, if your existing database is 80,000 pages long and you add a secondary file STARTING AT 50000
, InterBase starts the new database file at page 80,001.
To split an existing database file into smaller files, back it up and restore it. When you restore a database, you are free to specify secondary file sizes at will, without reference to the number and size of the original files.
Example: The following isql
statement adds two secondary files to an existing database. The command creates a secondary database file called employee2.ib
that is 10,000 pages long and another called employee3.ib
. InterBase starts using employee2.ib
only when the primary file reaches 10,000 pages.
ALTER DATABASE
ADD FILE 'employee2.ib'
STARTING AT PAGE 10001 LENGTH 10000
ADD FILE 'employee3.ib';
See Also
- CREATE DATABASE
- DROP DATABASE
- Encrypting Data
- Decrypting Data
- Data Definition Guide
- Operations Guide
- Implementing Stronger Password Protection