Examples Using

Examples Using `CREATE DATABASE`

Creating a database in Windows, located on disk D with a page size of 4,096.The owner of the database will be the user wizard.The database will be in Dialect 1, and will use WIN1251 as its default character set.
```
SET SQL DIALECT 1;
CREATE DATABASE 'D:\test.fdb'
USER 'wizard' PASSWORD 'player'
PAGE_SIZE = 4096 DEFAULT CHARACTER SET WIN1251;
```
Creating a database in the Linux operating system with a page size of 8,192 (default).The owner of the database will be the user wizard.The database will be in Dialect 3 and will use UTF8 as its default character set, with UNICODE_CI_AI as the default collation.
```
CREATE DATABASE '/home/firebird/test.fdb'
USER 'wizard' PASSWORD 'player'
DEFAULT CHARACTER SET UTF8 COLLATION UNICODE_CI_AI;
```
Creating a database on the remote server “baseserver” with the path specified in the alias “test” that has been defined previously in the file databases.conf.The TCP/IP protocol is used.The owner of the database will be the user wizard.The database will be in Dialect 3 and will use UTF8 as its default character set.
```
CREATE DATABASE 'baseserver:test'
USER 'wizard' PASSWORD 'player'
DEFAULT CHARACTER SET UTF8;
```
Creating a database in Dialect 3 with UTF8 as its default character set.The primary file will contain up to 10,000 pages with a page size of 8,192.As soon as the primary file has reached the maximum number of pages, Firebird will start allocating pages to the secondary file test.fdb2.If that file is filled up to its maximum as well, test.fdb3 becomes the recipient of all new page allocations.As the last file, it has no page limit imposed on it by Firebird.New allocations will continue for as long as the file system allows it or until the storage device runs out of free space.If a LENGTH parameter were supplied for this last file, it would be ignored.
```
SET SQL DIALECT 3;
CREATE DATABASE 'baseserver:D:\test.fdb'
USER 'wizard' PASSWORD 'player'
PAGE_SIZE = 8192
DEFAULT CHARACTER SET UTF8
FILE 'D:\test.fdb2'
STARTING AT PAGE 10001
FILE 'D:\test.fdb3'
STARTING AT PAGE 20001;
```
Creating a database in Dialect 3 with UTF8 as its default character set.The primary file will contain up to 10,000 pages with a page size of 8,192.As far as file size and the use of secondary files are concerned, this database will behave exactly like the one in the previous example.
```
SET SQL DIALECT 3;
CREATE DATABASE 'baseserver:D:\test.fdb'
USER 'wizard' PASSWORD 'player'
PAGE_SIZE = 8192
LENGTH 10000 PAGES
DEFAULT CHARACTER SET UTF8
FILE 'D:\test.fdb2'
FILE 'D:\test.fdb3'
STARTING AT PAGE 20001;
```

`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`

Examples Using

Examples Using CREATE DATABASE

ALTER DATABASE

Examples Using `CREATE DATABASE`

`ALTER DATABASE`