FirebirdSQL logo
 Data Definition (DDL) StatementsSHADOW 

ALTER DATABASE

Alters the file organisation of a database, toggles its “copy-safe” state, manages encryption, and other database-wide configuration

Available in

DSQL, ESQL — limited feature set

Syntax
ALTER {DATABASE | SCHEMA} <alter_db_option> [<alter_db_option> ...]

<alter_db_option> :==
    <add_sec_clause>
  | {ADD DIFFERENCE FILE 'diff_file' | DROP DIFFERENCE FILE}
  | {BEGIN | END} BACKUP
  | SET DEFAULT CHARACTER SET charset
  | {ENCRYPT WITH plugin_name [KEY key_name] | DECRYPT}
  | SET LINGER TO linger_duration
  | DROP LINGER
  | SET DEFAULT SQL SECURITY {INVOKER | DEFINER}
  | {ENABLE | DISABLE} PUBLICATION
  | INCLUDE <pub_table_filter> TO PUBLICATION
  | EXCLUDE <pub_table_filter> FROM PUBLICATION

<add_sec_clause> ::= ADD <sec_file> [<sec_file> ...]

<sec_file> ::=
  FILE 'filepath'
  [STARTING [AT [PAGE]] pagenum]
  [LENGTH [=] num [PAGE[S]]

<pub_table_filter> ::=
    ALL
  | TABLE table_name [, table_name ...]
Note

Multiple files can be added in one ADD clause:

ALTER DATABASE
  ADD FILE x LENGTH 8000
    FILE y LENGTH 8000
    FILE z

Multiple occurrences of add_sec_clause (ADD FILE clauses) are allowed;an ADD FILE clause that adds multiple files (as in the example above) can be mixed with others that add only one file.
Table 1. ALTER DATABASE Statement Parameters
Parameter Description

add_sec_clause

Adding a secondary database file

sec_file

File specification for secondary file

filepath

Full path and file name of the delta file or secondary database file

pagenum

Page number from which the secondary database file is to start

num

Maximum size of the secondary file in pages

diff_file

File path and name of the .delta file (difference file)

charset

New default character set of the database

linger_duration

Duration of linger delay in seconds;must be greater than or equal to 0 (zero)

plugin_name

The name of the encryption plugin

key_name

The name of the encryption key

pub_table_filter

Filter of tables to include to or exclude from publication

table_name

Name (identifier) of a table

The ALTER DATABASE statement can:

  • add secondary files to a database

  • switch a single-file database into and out of the “copy-safe” mode (DSQL only)

  • set or unset the path and name of the delta file for physical backups (DSQL only)

Note

SCHEMA is currently a synonym for DATABASE;this may change in a future version, so we recommend to always use DATABASE

Who Can Alter the Database

The ALTER DATABASE statement can be executed by: