ALTER DATABASE

Use the ALTER DATABASE command to change properties of a database; for example, the default character set, the name, the default schema, the owner of the database, or whether history data is to be collected for the database.

Syntax

Syntax for altering a database:
ALTER DATABASE <db_name> [ OWNER TO <user> ] [ RENAME TO <new_db_name> ] 
  [ SET DEFAULT SCHEMA <schema_name> ] [ SET DEFAULT CHARACTER SET LATIN9 ] ]
  [ COLLECT HISTORY {ON|OFF|DEFAULT} ] [ DATA_VERSION_RETENTION_TIME <number-of-days> NOCASCADE ]
  [GROOM BACKUPSET {<backupset>|DEFAULT}]

Inputs

The ALTER DATABASE command has the following inputs:
Table 1. ALTER DATABASE inputs
Input Description
<db_name> The name of the database.
OWNER TO The name of the new database owner.
RENAME TO The new database name.
SET DEFAULT SCHEMA The default schema for users who connect to the database. If you change the default schema, users who inherited access to the previous default schema lose access to that schema and inherit access to the new schema. There users no longer have access to the previous default schema unless they were explicitly granted access to it.
SET DEFAULT CHARACTER SET Set the default character set to LATIN9 to be able to compare, join, or cast char or nchar class data.
COLLECT HISTORY Whether history data is to be collected for sessions attached to this database.
ON
History is collected only if the user is a member of at least one group for which COLLECT HISTORY is set to ON.
OFF
History is not collected for the database.
DEFAULT
History is collected for the database only if the user is a member of at least one group for which COLLECT HISTORY is set to ON and if one of the following criteria apply:
  • The user is not a member of any user group.
  • All the user groups of which the user is a member have COLLECT HISTORY set to DEFAULT.
  • The user is a member of at least one user group that has COLLECT HISTORY set to ON.
DATA_VERSION_RETENTION_TIME

Updates the value of DATA_VERSION_RETENTION_TIME for the specified database. The new value has no impact on existing schemas and tables in the database. But, the value determines the default value that is inherited by a subsequent CREATE SCHEMA command in the database that does not explicitly specify this property.

The maximum allowed value is 92 days, which is the maximum number of days in a calendar quarter.
GROOM BACKUPSET

If set to DEFAULT, the GROOM TABLE command automatically determines the default backup set for the table's database.

If set to a specific backupsetid string, GROOM TABLE uses the specified backup set as the default backup set for the table's database.

Outputs

The ALTER DATABASE command has the following outputs:
Table 2. ALTER DATABASE outputs
Output Description
ALTER DATABASE The command completed successfully.
ERROR: ALTER DATABASE: database "db1" is being accessed by other users The database is being used by other users.

Privileges

You must be the admin user, the database owner, or your account must have the Alter privilege for the database or for the Database object class. To change the database owner or the default schema, you must have List privilege for that user or schema.

Usage

The following provide sample usage:
  • Change the default character set for the emp database: 
    MYDB.SCH1(USER)=> ALTER DATABASE emp SET DEFAULT 
CHARACTER SET LATIN9;
  • Change the owner of the emp database: 
    MYDB.SCH1(USER)=> ALTER DATABASE emp OWNER TO admin3;
  • Rename the emp database: 
    MYDB.SCH1(USER)=> ALTER DATABASE emp RENAME TO employees;