ALTER DATABASE

From InterBase
Jump to: navigation, search

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>'}
Important: 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

SCHEMA

Alternative keyword for DATABASE

ADD FILE '<filespec>'

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.

LENGTH [=] <int> [ PAGE [ S ]]

Specifies the range of pages for a secondary file by providing the number of pages in each file.

STARTING [ AT [ PAGE ]] <int>

Specifies a range of pages for a secondary file by providing the starting page number.

ADD ADMIN OPTION

Enables embedded user authentication.

DROP ADMIN OPTION

Disables embedded user authentication.

ENCRYPT <key_name>

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.

DECRYPT <key_name>

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.

SET FLUSH INTERVAL <number>

Enables database flush. The interval <number> is interpreted in units of seconds.

SET NO FLUSH INTERVAL

Disables database flush.

SET GROUP COMMIT

Allows transactions to be committed by a background cache writer thread.

SET NO GROUP COMMIT

Disables group commit.

SET LINGER INTERVAL

Allows a database to remain in memory after the last user detaches. Interval is seconds.

SET NO LINGER INTERBAL

Disables database linger.

SET RECLAIM INTERVAL

Reclaims the interval is in seconds. 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.

SET NO RECLAIM INTERVAL

Disables memory reclamation.

SET SYSTEM ENCRYPTION PASSWORD

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.

Note: Only the SYSDSO (Data Security Owner) can create this password.

SET NO SYSTEM ENCRYPTION PASSWORD

Deletes the password if there are no existing encryption keys.

Note: Only SYSDSO can delete a password.

SET PAGE CACHE

Sets database page buffer cache limit. Also, tries to expand cache to that limit.

SET PASSWORD DIGEST '<digest_name>'

Sets the password hash function. The default value is 'DES-CRYPT'. See Implementing Stronger Password Protection for more information.

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.

Note: 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.

Tip: 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

Advance To: