SQL References (A~B)

ALTER AUDIT POLICY

Function

It adds an auditing target to an audit policy object or drops an auditing target from an audit policy object.

Syntax

<alter audit policy statement> ::= 
    ALTER AUDIT POLICY policy_name
    { <add_audit_option> | <drop_audit_option> }
    ;

<add_audit_option> ::=
    ADD { <privilege_audit_clause> | <role_audit_clause> | <action_audit_clause> } [, ...]

<drop_audit_option> ::=
    DROP { <privilege_audit_clause> | <role_audit_clause> | <action_audit_clause> } [, ...]


<privilege_audit_clause> ::=
    PRIVILEGES <database_privilege> [, ...]

<role_audit_clause> ::=
    ROLES <role_name> [, ...]

<action_audit_clause> ::=
    ACTIONS { <object_action_audit> | <system_action_audit> } [, ...]

<object_action_audit> ::=
      ALL ON [schema_name.]object_name
    | <object_action> ON [schema_name.]object_name

<system_action_audit> ::=
      ALL
    | <system_action>

Invocation and Access Rules

The AUDIT SYSTEM ON DATABASE privilege is required to execute the <alter audit policy statement>.

Syntax Rules and Parameters

policy_name

It is the name of the audit policy object to be altered.

<add_audit_option>

It adds an auditing target to an audit policy.

<drop_audit_option>

It drops an auditing target from an audit policy.

<privilege_audit_clause>

For more information, refer to CREATE AUDIT POLICY.

<role_audit_clause>

For more information, refer to CREATE AUDIT POLICY.

<action_audit_clause>

For more information, refer to CREATE AUDIT POLICY.

Description

It can alter an already activated audit policy, but it does not affect existing sessions; it only affects newly created sessions.

When dropping the ALL option as follows, not all actions are dropped, but only the corresponding ALL option is dropped.

CREATE AUDIT POLICY p1
       ACTIONS ALL ON u1.t1,
               SELECT ON u1.t1;

ALTER AUDIT POLICY p1 DROP
      ACTIONS ALL ON u1.t1;

Examples

The following is an example of adding a new audit option to an audit policy.

ALTER AUDIT POLICY policy_dml
      ADD ACTIONS SELECT ON u1.t1;

The following is an example of dropping an audit option from an audit policy.

ALTER AUDIT POLICY policy_dml
      DROP ACTIONS SELECT ON u1.t1;

Compatibility

The SQL standard does not include an audit policy.

For More Information

Refer to the following.

ALTER CLUSTER GROUP name ADD MEMBER

Function

It adds a cluster member to the cluster group.

Syntax

<alter cluster group add member statement> ::=
    ALTER CLUSTER GROUP group_name ADD
        <cluster member definition> [, ...]
    ;

<cluster member definition> ::=
    CLUSTER MEMBER member_name <connection attribute> [<member position>]

<connection attribute> ::=
    HOST 'address' PORT port_no

<member position> ::=
    POSITION DEFAULT
  | POSITION MAX
  | POSITION number

Invocation and Access Rules

It can be performed in a cluster system.
The ADMINISTRATION ON DATABASE privilege is required to execute the <alter cluster group add member statement>.

Syntax Rules and Parameters

group_name

It is the name of the cluster group.

<cluster member definition>

It defines a cluster member to be included in a cluster group.
A cluster group can include a maximum of 32 cluster members.

member_name

It is the name of a cluster member.
The name of the cluster member must match the name defined when the database for that member was created. 
There must be no duplicate cluster group or cluster member names. 
The length of the name must be less than 128 bytes.

The start-up phase for the cluster member should be GLOBAL OPEN.

<connection attribute>

It defines the connection information for communication between the cluster members.
The <connection attribute> must match the HOST and PORT defined when the database for that cluster member was created.
The combination of HOST and PORT must be unique within the cluster system.

<member position>

It assigns the position number to the cluster member.

The member_position information of a cluster member can be retrieved through the DBA_CLUSTER view.

SELECT member_name, member_id, member_position FROM dba_cluster;

If the following position numbers are in use,

The following values are assigned based on each option.

Description

The <alter cluster group add member statement> statement does not rebalance shards in the tables.
The following statement should be executed to rebalance the shards on the added cluster member.

Examples

The following is an example of adding two cluster members to a cluster group.

gSQL>
ALTER CLUSTER GROUP g1 ADD
    CLUSTER MEMBER g1n3 HOST '192.168.0.13' PORT 10130,
    CLUSTER MEMBER g1n4 HOST '192.168.0.14' PORT 10140
;

Cluster Group altered.

The following is an example of designating the empty member position as a cluster member position.

ALTER CLUSTER GROUP g2 ADD
    CLUSTER MEMBER g2n1 HOST '192.168.0.21' PORT 10210 POSITION 4
;

Compatibility

The SQL standard does not define the concept of a cluster.

For More Information

Refer to the following.

ALTER CLUSTER GROUP name OFFLINE MEMBER

Function

It sets a cluster member within the cluster group to offline.

Syntax

<alter cluster group offline member statement> ::=
    ALTER CLUSTER GROUP group_name OFFLINE CLUSTER MEMBER member_name
    ;

Invocation and Access Rules

It can be performed in a cluster system.
The ADMINISTRATION ON DATABASE privilege is required to execute the <alter cluster group offline member statement>.

Syntax Rules and Parameters

group_name

It is the name of a cluster group.

member_name

It is the name of a cluster member.
The cluster member must be included in the cluster group of group_name.
The cluster member must be inactive.

Description

It sets the inactive cluster member to offline.

The <alter cluster group offline member statement> does not rebalance shards in the tables.

Examples

If an attempt is made to set a cluster member that is not inactive to offline, the following error will occur.

gSQL>

ALTER CLUSTER GROUP g1 OFFLINE CLUSTER MEMBER g1n2;

ERR-42000(16417): active member 'G1N2' cannot be offlined

The following is an example of changing a specific cluster member to an offline state.

gSQL>

ALTER CLUSTER GROUP g1 OFFLINE
    CLUSTER MEMBER g1n3
;
Cluster Group altered.

Compatibility

The SQL standard does not define the concept of a cluster.

For More Information

Refer to the following.

ALTER CLUSTER LOCATION

Function

It alters the cluster location information.

Syntax

<alter cluster location statement> ::=
    ALTER CLUSTER LOCATION member_name 
    <cluster connection attribute>
    ;

<cluster connection attribute> ::
       HOST 'address' PORT port_no

Invocation and Access Rules

It can be performed in a cluster system.
The ADMINISTRATION ON DATABASE privilege is required to execute the <alter cluster location statement>.

Syntax Rules and Parameters

member_name

It is the name of a cluster member.
The same cluster member name must exist in the registered cluster location information.
The length of the name must be shorter than 128 bytes.

<cluster connection attribute>

It defines the connection information for communication between cluster members.
The combination of HOST and PORT must be unique within the cluster system.

Description

If the connection information of the cluster location is altered, the cluster member does not need to be dropped or recreated, but the connection information can be altered using the ALTER CLUSTER LOCATION.

Examples

gSQL> 
ALTER CLUSTER LOCATION g1n2
    HOST '192.168.0.12' PORT 10120
;

Location altered.

Compatibility

The SQL standard does not define the concept of a cluster.

For More Information

Refer to the following.

ALTER DATABASE ADD LOGFILE

Function

It adds log file groups or log file members to the database.

Syntax

<alter database add logfile statement> ::=
      <add logfile member statement> 
    | <add logfile group statement>
    ;

<add logfile member statement> ::=
    ALTER DATABASE ADD LOGFILE MEMBER <add logfile clause> [, ...] TO 
        <group clause>

<add logfile group statement> ::=
    ALTER DATABASE ADD LOGFILE <group clause> ( 'logfile_name' [, ...] ) 
        <size clause> [ REUSE ]

<group clause> ::=
    GROUP integer

<add logfile clause> ::=
    'logfile_name' [ REUSE ]

<size clause> ::=
    integer [ M | G ]

Invocation and Access Rules

The ALTER DATABASE ON DATABASE privilege is required to execute the <alter database add logfile statement>.

Syntax Rules and Parameters

<alter database add logfile statement>

The database must be in the MOUNT phase.

<add logfile member statement>

A log member is added to an existing log file group.

<add logfile group statement>

It adds a new log file group.

Description

It is recommended to back up the control file to prepare for potential control file damage, as new log file groups and log members are stored in the control file.

Examples

The following is an example of adding two log file members to the existing log file group 3.

ALTER DATABASE ADD LOGFILE MEMBER 'logfile1.log', 'logfile2.log' TO GROUP 3;

The following is an example of adding a new log file group 4 to the database. The size of log file group 4 is 100 M, and the log file name is 'logfile1.log'.

ALTER DATABASE ADD LOGFILE GROUP 4 ( 'logfile1.log' ) SIZE 100M;

When adding a log group, a single log file must be used. However, multiple log files can be added as members to an existing group.

Compatibility

The SQL standard does not define the ALTER DATABASE statement.

For More Information

Refer to the following.

ALTER DATABASE ARCHIVELOG

Function

It alters the archive setting of the online log file in the database.

Syntax

<alter database archivelog statement> ::=
    ALTER DATABASE { ARCHIVELOG | NOARCHIVELOG }
    ;

Invocation and Access Rules

The ALTER DATABASE ON DATABASE privilege is required to execute the <alter database archivelog statement>.

Syntax Rules and Parameters

<alter database archivelog statement>

Description

For database backup and media recovery using the backup, the system must operate in ARCHIVELOG mode.

Example

The following is an example of setting the database to archive mode.

ALTER DATABASE ARCHIVELOG;

Compatibility

The SQL standard does not define the ALTER DATABASE statement.

For More Information

Refer to the following.

ALTER DATABASE BACKUP

Function

It creates a backup of the entire database.

The backup targets are the datafile and the control file, and the datafile can be backed up using both full and incremental backups.

Syntax

<alter database backup statement> ::=
      <database begin backup statement>
    | <database end backup statement>
    | <database incremental backup statement>
    | <database controlfile backup statement>
    ;

<database begin backup statement> ::=
    ALTER DATABASE BEGIN BACKUP [ AT <domain name> ]
    ;

<database end backup statement> ::=
    ALTER DATABASE END BACKUP [ AT <domain name> ]
    ;


<database incremental backup statement> ::=
    ALTER DATABASE BACKUP INCREMENTAL
        <incremental backup option> [ FORMAT 'format string' ] 
        [ PIECE integer ] [ <parallel clause> ] [ AT <domain name> ];

<incremental backup option> ::=
      LEVEL integer [ CUMULATIVE | DIFFERENTIAL ]

<database controlfile backup statement> ::=
    ALTER DATABASE BACKUP CONTROLFILE TO 'target_name'
        [ AT <domain name> ]    ;


<parallel clause> ::=
      NOPARALLEL
    | PARALLEL [ integer ]

Invocation and Access Rules

The ALTER DATABASE ON DATABASE privilege is required to execute the <alter database backup statement>.

Syntax Rules and Parameters

<database begin backup clause>

The database is set to a state where a full backup is available.

<database end backup clause>

The database is set to a state where a full backup is not available.

<database incremental backup statement>

<incremental backup option>

FORMAT 'format string'

PIECE integer

<parallel clause>

It specifies the number of threads to be used during backup.

<database controlfile backup statement>

The maximum length of 'target_name' managed by GOLDILOCKS is 1024 bytes. However, since the maximum file name length varies depending on the OS, the actual length of 'target_name' that can be created may be less than 1024 bytes.

<domain name>

It is the name of the member or group on which the statement is performed.
If not specified, the statement is performed on all groups.

Description

It backs up the data files and control files in the database. A full backup of the database begins with BEGIN BACKUP, copies the datafiles using an OS file copy, and ends with END BACKUP. The incremental backup file is created in the path set by the BACKUP_DIR 1 property using a single statement.

Examples

The following is an example of setting the entire backup state to ACTIVE.

ALTER SYSTEM BEGIN BACkUP;

The following is an example of setting the entire backup state to INACTIVE.

ALTER SYSTEM END BACkUP;

The following is an example of creating an incremental backup at LEVEL 1 using DIFFERENTIAL.

ALTER DATABASE BACKUP INCREMENTAL LEVEL 1 DIFFERENTIAL;

The following is an example of creating the 'controlfile.bak' backup file for the control file. If an absolute path is not specified, the backup file is created in the directory set by the LOG_DIR property.

ALTER DATABASE BACKUP CONTROLFILE TO 'controlfile.bak';

The following is an example of performing an incremental backup with four threads, creating four backup files named with dates and piece numbers.

ALTER DATABASE BACKUP INCREMENTAL LEVEL 0 FORMAT 'backup_%T_%p' PIECE 4 PARALLEL 4;

Compatibility

The SQL standard does not define the ALTER DATABASE statement.

For More Information

Refer to the following.

ALTER DATABASE CLEAR AUDIT TRAIL

Function

It purges audit records accumulated due to the application of the audit policy.

Syntax

<clear audit trail statement> ::= 
    ALTER DATABASE CLEAR AUDIT TRAIL
        [ AT <domain name> ]
    ;

Invocation and Access Rules

The AUDIT SYSTEM ON DATABASE privilege is required to execute the <clear audit trail statement>.

Syntax Rules and Parameters

<domain name>

It is the name of the member or group on which the statement is performed.
If not specified, the statement is performed on all groups.

Description

If an audit policy is activated, an audit trails is getting longer as time goes by.
Tables configuring an audit trail are stored in MEM_AUX_TBS tablespace, and a user should be cautious not to let the audit trail keep increasing.

Storing Audit Trail

To store the audit trail when necessary, it should be stored according to the following procedure and then purged.

CREATE TABLE backup_audit_trail AS SELECT * FROM AUDIT_TRAIL;
COMMIT;
INSERT INTO backup_audit_trail SELECT * FROM AUDIT_TRAIL;
COMMIT;
ALTER DATABASE CLEAR AUDIT TRAIL;

Example

Purge the audit trail using the following statement.

ALTER DATABASE CLEAR AUDIT TRAIL;

Compatibility

The SQL standard does not include the audit policy.

For More Information

Refer to the following.

ALTER DATABASE CLEAR PASSWORD HISTORY

Function

It deletes the user's password change history accumulated due to the application of the profile.

Syntax

<clear password history statement> ::= 
    ALTER DATABASE CLEAR PASSWORD HISTORY
        [ AT <domain name> ]
    ;

Invocation and Access Rules

The ALTER DATABASE ON DATABASE privilege is required to execute the <clear password history statement>.

Syntax Rules and Parameters

<domain name>

It is the name of the member or group on which the statement is performed.
If not specified, the statement is performed on all groups.

Description

When a profile is applied to a user, the user's password change history is accumulated based on the PASSWORD_REUSE_MAX and PASSWORD_REUSE_TIME policies.
Managing the change history

PASSWORD_REUSE_MAX

PASSWORD_REUSE_TIME

Managing the change history

value

value

It manages only the change history within the specified value range, and any change history outside this range is automatically deleted.

value

UNLIMITED

Since all change history must be reviewed, it only accumulates the change history and does not remove any records.

UNLIMITED

value

Since all change history must be reviewed, it only accumulates the change history and does not remove any records.

UNLIMITED

UNLIMITED

Since the change history is not reviewed, it is also not managed.

<Clear password history statement> deletes the accumulated user password change history.

Examples

The following is an example of executing the <clear password history statement>.
gSQL> ALTER DATABASE CLEAR PASSWORD HISTORY;

Database altered.

gSQL> COMMIT;

Commit complete.

Compatibility

The SQL standard does not define the ALTER DATABASE statement.

For More Information

Refer to the following.

ALTER DATABASE DATAFILE AUTOEXTEND

Function

It alters the property to automatically extend the disk tablespace data file. If the property is ON, the size to be extended and the maximum size of the data file can also be altered.

Syntax

<alter database datafile autoextend statement> ::= 
    ALTER DATABASE DATAFILE datafile_name <autoextend clause>
        [ AT <domain name> ]
    ;

<autoextend clause>
    AUTOEXTEND { ON [ <next size clause> ] [ <max size clause> ] | OFF }

<next size clause>
    NEXT <size clause>

<max size clause>
    MAXSIZE { <size clause> | UNLIMITED }

Invocation and Access Rules

The ALTER DATABASE ON DATABASE privilege is required to execute the <alter database datafile autoextend statement>.

The datafile automatic expand property can only alter the property of disk tablespace.

datafile_name

It specifies the name of the data file to be altered.

<autoextend clause>

It sets the automatic expand property to ON or OFF. If set to ON, the automatic expansion size and the maximum size of the data file can be specified.

<next size clause>

It specifies the size to be extended when the data file in use runs out of available space.

<max size clause>

It specifies the maximum size to which the data file can be extended.

Description

Refer to the syntax rules for each statement.

Examples

The following is an example of altering the automatic expand property of the datafile, the automatic expansion size, and the datafile size.

gSQL> ALTER DATABASE DATAFILE 'DISK_TBS.dbf' AUTOEXTEND OFF;

Database altered.

gSQL> ALTER DATABASE DATAFILE 'DISK_TBS.dbf' AUTOEXTEND ON;

Database altered.

gSQL> ALTER DATABASE DATAFILE 'DISK_TBS.dbf' AUTOEXTEND ON NEXT 20M;

Database altered.

gSQL> ALTER DATABASE DATAFILE 'DISK_TBS.dbf' AUTOEXTEND ON MAXSIZE 1G;

Database altered.

gSQL> ALTER DATABASE DATAFILE 'DISK_TBS.dbf' AUTOEXTEND ON 20M MAXSIZE 1G;

Database altered.

Compatibility

The SQL standard does not define the concept of the datafile.

For More Information

Refer to CREATE DISK DATA TABLESPACE.

ALTER DATABASE DELETE BACKUP

Function

It deletes the backup file and the backup information of the incremental backup. It can delete all incremental backups of the database or obsolete backups that are no longer usable.

Syntax

<alter database delete backup statement> ::=
    ALTER DATABASE DELETE <delete backup list option> 
        BACKUP LIST [ <including backup file option> ]
        [ AT <domain name> ]
    ;

<delete backup list option> ::=
      OBSOLETE
    | ALL

<including backup file option> ::=
    INCLUDING BACKUP FILES

Invocation and Access Rules

The ALTER DATABASE ON DATABASE privilege is required to execute the <alter database delete backup statement>.

Syntax Rules and Parameters

<alter database delete backup statement>

The database must be in MOUNT or OPEN phase.

<delete backup list option>

It selects the targets to be deleted from the existing incremental backups.

<including backup file option>

<domain name>

It is the name of the member or group on which the statement is performed.
If not specified, the statement is performed on all groups.

Description

Deletion of the OBSOLETE incremental backup deletes backups taken before the most recent LEVEL 0 database backup. When a non-LEVEL 0 incremental backup is performed, it is not deleted, even if it includes previously performed incremental backups. This is because the backup may be needed for incomplete recovery using incremental backups.

Be cautious when deleting the backup file along with an incremental backup. It can not be recovered, even using the control file that contains the incremental backup information.

Example

The following is an example of how to delete the backup information and backup files of all existing incremental backups.

ALTER DATABASE DELETE ALL BACKUP LIST INCLUING BACKUP FILES;

Compatibility

The SQL standard does not define the ALTER DATABASE statement.

For More Information

Refer to the following.

ALTER DATABASE DISABLE CHANGE TRACKING

Function

It disables the data change tracking feature for the disk tablespace.

Syntax

<alter database disable change tracking statement> ::=
    ALTER DATABASE DISABLE CHANGE TRACKING
        [ AT <domain name> ]
    ;

Invocation and Access Rules

The ALTER DATABASE ON DATABASE privilege is required to execute the <alter database disable change tracking statement>.

Syntax Rules and Parameters

The database must be in the MOUNT or OPEN phase.

<domain name>

It is the name of the member or group on which the statement is performed.
If not specified, the statement is performed on all groups.

Description

It disables the management of backup target pages when performing an incremental backup for the disk tablespace.

If CHANGE TRACKING is disabled, the system must scan all pages to determine the backup target during an incremental backup of the disk tablespace. This will increase the backup duration and, as a result, could impact service availability, so caution is advised.

Example

The following is an example of disabling the CHANGE TRACKING.

ALTER DATABASE DISABLE CHANGE TRACKING;

Compatibility

The SQL standard does not define the ALTER DATABASE statement.

For More Information

Refer to the following.

ALTER DATABASE DROP INACTIVE CLUSTER MEMBERS

Function

It drops the entire inactive cluster member.

Syntax

<alter database drop inactive members statement> ::=
    ALTER DATABASE DROP [ FORCE | NO FORCE ] INACTIVE CLUSTER MEMBERS
    ;

Invocation and Access Rules

It can be performed in a cluster system.
The ADMINISTRATION ON DATABASE privilege is required to execute the <alter database drop inactive cluster members statement>.

Syntax Rules and Parameters

[ FORCE | NO FORCE ]

Description

It drops the entire inactive cluster member.
The inactive state of a cluster member means it is not connected to the cluster system, which occurs in the following cases.

However, if the table shard is lost while dropping the cluster member, the inactive cluster member can not be dropped.

Additionally, an inactive cluster member cannot be removed if there is a risk of data loss. Data loss can be prevented only if it is guaranteed that the replica of the table or shard belonging to the inactive cluster member does not contain more recent data than the other members of the cluster group. Therefore, the removal of an inactive cluster member is allowed if at least one online member exists in the same cluster group for a sharded table, or in the entire cluster for a cloned table.

However, if no online cluster member exists in the cluster group and the service is unavailable due to an inactive cluster member, the inactive cluster member can be dropped using the FORCE option, despite the risk of data loss.

It is recommended to use the <alter database drop inactive members statement> when an inactive cluster member can no longer be included in the cluster system.

Examples

The following is an example of executing the <alter database drop inactive members statement>.

gSQL> ALTER DATABASE DROP INACTIVE CLUSTER MEMBERS;

Database altered.

Compatibility

The SQL standard does not define the concept of a cluster.

For More Information

Refer to ALTER SYSTEM JOIN DATABASE.

ALTER DATABASE DROP LOGFILE

Function

It drops a log file group or a member that exists in the database.

Syntax

<alter database drop logfile statement> ::=
      <drop logfile group statement>
    | <drop logfile member statement>
    ;

<drop logfile group statement> ::=
    ALTER DATABASE DROP LOGFILE <group clause>

<group clause> ::=
    GROUP integer

<drop logfile member statement> ::=
    ALTER DATABASE DROP LOGFILE MEMBER <logfile_list>

<logfile_list> ::=
      'logfile_name'
    | <logfile_list> , 'logfile_name'

Invocation and Access Rules

The ALTER DATABASE ON DATABASE privilege is required to execute the <alter database drop logfile statement>.

Syntax Rules and Parameters

<alter database drop logfile statement>

The database must be in MOUNT phase.
An error occurs if the log file to be deleted is in the CURRENT or ACTIVE stage.
At least four log file groups must remain after dropping.

<drop logfile group statement>

It drops the existing log file group.

<drop logfile member statement>

It drops the existing log file members.

Description

For more information, refer to the syntax rules for each statement.

Examples

The following is an example of dropping the existing log file GROUP 3.

ALTER DATABASE DROP LOGFILE GROUP 3;

The following is an example of dropping logfile1.log and logfile2.log from the existing logfile GROUP 3.

ALTER DATABASE DROP LOGFILE MEMBER 'logfile1.log', 'logfile2.log';

Compatibility

The SQL standard does not define the ALTER DATABASE statement.

For More Information

Refer to the respective syntax rules and the following.

ALTER DATABASE DROP OFFLINE SEGMENTS

Function

It drops the segments of offline shards for all tables.

Syntax

<alter database drop offline segments statement> ::=
    ALTER DATABASE DROP OFFLINE SEGMENTS 
    ;

Invocation and Access Rules

It can be performed within the cluster system.

The ALTER DATABASE ON DATABASE privilege is required to execute the <alter database drop offline segments statement>.

Description

It drops the segments of offline shards for all tables and can be performed even when an inactive cluster member exists.

The inactive state of a cluster member means it is not connected to the cluster system, and it occurs in the following situations.

The <alter database drop offline segments statement> performs the <alter table drop offline segments statement> for each table, and it is equivalent to the sum of the following queries.

ALTER TABLE t1 DROP OFFLINE SEGMENTS;
COMMIT;
ALTER TABLE t2 DROP OFFLINE SEGMENTS;
COMMIT;
ALTER TABLE t3 DROP OFFLINE SEGMENTS;
COMMIT;

...

ALTER TABLE tn DROP OFFLINE SEGMENTS;
COMMIT;

The <alter database drop offline segments statement> does not terminate even if an error occurs in a specific table; it continues with the next table and succeeds with the following warning.

gSQL> ALTER DATABASE DROP OFFLINE SEGMENTS;

ERR-42000(16553): of the total '5' tables, '1' tables failed to drop offline segments
Database altered.

The error message above indicates that one of the five tables failed.

If the <alter database drop offline segments statement> is executed again after taking appropriate action for the error, it will be applied only to the table that previously failed.

For more information about the error, refer to the system trace log (system.trc) of the member that executed the statement.

Example

The following is an example of executing the <alter database drop offline segments statement>.

gSQL> ALTER DATABASE DROP OFFLINE SEGMENTS;

Database altered.

Compatibility

The SQL standard does not define the concept of a cluster.

For More Information

Refer to ALTER TABLE name DROP OFFLINE SEGMENTS.

ALTER DATABASE DROP UNUSABLE SEGMENTS

Function

The unusable segments of offline replicas among all tables in the database are dropped.

Syntax

<alter database drop unusable segments statement> ::=
    ALTER DATABASE DROP UNUSABLE SEGMENTS 
    ;

Invocation and Access Rules

It can be performed within the cluster system.

The ALTER DATABASE ON DATABASE privilege is required to execute the <alter database drop unusable segments statement>.

Description

The <alter database drop unusable segments statement> performs <alter table drop unusable segments statement> on each table, and has the same effect as executing all of the following queries.

ALTER TABLE t1 DROP UNUSABLE SEGMENTS;
COMMIT;
ALTER TABLE t2 DROP UNUSABLE SEGMENTS;
COMMIT;
ALTER TABLE t3 DROP UNUSABLE SEGMENTS;
COMMIT;

...

ALTER TABLE tn DROP UNUSABLE SEGMENTS;
COMMIT;

The <alter database drop unusable segments statement> does not terminate even if an error occurs in a specific table; it continues with the next table and succeeds with the following warning.

gSQL> ALTER DATABASE DROP UNUSABLE SEGMENTS;

ERR-42000(16553): of the total '5' tables, '1' tables failed to drop offline segments
Database altered.

The error message above indicates that one of the five tables failed.

If the <alter database drop unusable segments statement> is executed again after taking appropriate action for the error, it will be applied only to the table that previously failed.

For more information about the error, refer to the system trace log (system.trc) of the member that executed the statement.

This statement can be executed even if there are inactive members.

Example

The following is an example of executing the <alter database drop unusable segments statement>.

gSQL> ALTER DATABASE DROP UNUSABLE SEGMENTS;

Database altered.

Compatibility

The SQL standard does not define the concept of an unusable segment.

For More Information

Refer to ALTER TABLE name DROP UNUSABLE SEGMENTS.

ALTER DATABASE ENABLE CHANGE TRACKING

Function

It enables the data change tracking feature for the disk tablespace.

Syntax

<alter database enable change tracking statement> ::=
    ALTER DATABASE ENABLE CHANGE TRACKING
        [ USING FILE 'file_name' REUSE ]
        [ AT <domain name> ]
    ;

Invocation and Access Rules

The ALTER DATABASE ON DATABASE privilege is required to execute the <alter database enable change tracking statement>.

Syntax Rules and Parameters

<alter database enable change tracking statement>

The database must be in the MOUNT or OPEN phase.
The CHANGE TRACKING feature must be disabled.

<domain name>

It is the name of the member or group on which the statement is performed.
If not specified, the statement is performed on all groups.

Description

It enables the management of backup target pages when performing an incremental backup for the disk tablespace.

Example

The following is an example of enabling the CHANGE TRACKING.

ALTER DATABASE ENABLE CHANGE TRACKING;

Compatibility

The SQL standard does not define the ALTER DATABASE statement.

For More Information

Refer to the following.

ALTER DATABASE MOVE SHARD

Function

It rebalances the shards of all tables in a specific cluster group to another cluster group.

Syntax

<alter database move shard statement> ::=
    ALTER DATABASE MOVE SHARD FROM CLUSTER GROUP src_cluster_group
        TO CLUSTER GROUP dest_cluster_group 
       [ ONLINE | OFFLINE ] 
       [ LOGGING | NOLOGGING ] 
       [ <scan partition> ] 
       [ <parallel clause> ]
    ;

<scan partition> ::= 
    SCAN PARTITION integer

<parallel clause> ::=
    NOPARALLEL
  | PARALLEL [ integer ]

Invocation and Access Rules

It can be performed in a cluster system.
The ALTER DATABASE ON DATABASE privilege is required to execute the <alter database move shard statement>.

Syntax Rules and Parameters

src_cluster_group

It is the cluster group to which the table shard is moved.

dest_cluster_group

It is the target cluster group to which the table shard is moved.

[ ONLINE | OFFLINE ]

It determines whether DML operations are allowed while rebalancing the table shard.

[ LOGGING | NOLOGGING ]

It specifies the amount of logging performed during table synchronization when rebalancing a table shard.

When the NOLOGGING option is used, redo logs are not generated. Therefore, if the server terminates unexpectedly after executing move shard, the table becomes unusable. To prevent this, execute the CHECKPOINT statement after completing move shard.

<scan partition>

It specifies the number of partitions for the shard.

<parallel clause>

It specifies the number of threads to be used when rebalancing the table.

Description

When adding a cluster member and a cluster group using the following statements, the table shard will not be rebalanced.

Execute the <alter database rebalance statement> to rebalance the shards of the entire table that were not rebalanced when adding a cluster group and a cluster member.

Executing the <alter database move shard statement> for tables that did not have their shards rebalanced means the following.

ALTER TABLE t1 MOVE SHARD FROM CLUSTER GROUP src_group TO CLUSTER GROUP dest_group;
COMMIT;
ALTER TABLE t2 MOVE SHARD FROM CLUSTER GROUP src_group TO CLUSTER GROUP dest_group;
COMMIT;
ALTER TABLE t3 MOVE SHARD FROM CLUSTER GROUP src_group TO CLUSTER GROUP dest_group;
COMMIT;
...
...
ALTER TABLE t_n MOVE SHARD FROM CLUSTER GROUP src_group TO CLUSTER GROUP dest_group;
COMMIT;

It is performed as described above for all tables except for CLONED and CLUSTER WIDE tables. The <alter database move shard statement> proceeds even if rebalancing the shard of a specific table fails. It does not roll back the tables that successfully rebalanced their shards.

Therefore, when executing the <alter database move shard statement> again after appropriately addressing an error, it will rebalance only the shard for the table that requires rebalancing. In this case, the table that successfully rebalanced its shard will not be included as a target for rebalancing.

Examples

The following is an example of executing the <alter database move shard statement>.

gSQL> ALTER DATABASE MOVE SHARD FROM CLUSTER GROUP G1 TO CLUSTER GROUP G2;

Database altered.

Compatibility

The SQL standard does not define the concept of a cluster.

For More Information

Refer to the following.

ALTER DATABASE OFFLINE INACTIVE CLUSTER MEMBERS

Function

It sets the entire inactive cluster member to offline. In other words, it sets the shard map for the cluster member to offline.

Syntax

<alter database offline inactive cluster members statement> ::=
    ALTER DATABASE OFFLINE INACTIVE CLUSTER MEMBERS
    ;

Invocation and Access Rules

This action can be performed in a cluster system.
The ADMINISTRATION ON DATABASE privilege is required to execute the <alter database offline inactive cluster members statement>.

Syntax Rules and Parameters

It sets the entire inactive cluster member to offline.
An inactive cluster member refers to one that is not connected to the cluster system, and this state occurs in the following cases.

Description

The <alter database offline inactive members> statement should be used when all inactive cluster members can no longer be included in the cluster system.

If an inactive cluster member can participate in the cluster system, use the ALTER SYSTEM JOIN DATABASE statement to include it in the system.

A cluster member that has been set to offline can be brought online again using the following statements after the join.

Examples

gSQL> ALTER DATABASE OFFLINE INACTIVE CLUSTER MEMBERS;

Compatibility

The SQL standard does not define the concept of a cluster.

For More Information

Refer to the following.

ALTER DATABASE REBALANCE

Function

It rebalances the shards of all tables.

Syntax

<alter database rebalance statement> ::=
    ALTER DATABASE REBALANCE
       [ ONLINE | OFFLINE ] 
       [ LOGGING | NOLOGGING ]
       [ <scan partition> ] 
       [ <parallel clause> ]
    ;

<scan partition> ::= 
    SCAN PARTITION integer

<parallel clause> ::=
    NOPARALLEL
  | PARALLEL [ integer ]

Invocation and Access Rules

It can be performed in a cluster system.
The ALTER DATABASE ON DATABASE privilege is required to execute the <alter database rebalance statement>.

Syntax Rules and Parameters

[ ONLINE | OFFLINE ]

It determines whether DML operations are allowed while rebalancing the table shard.

[ LOGGING | NOLOGGING ]

It specifies the amount of logging performed during table synchronization when rebalancing a table shard.

When the NOLOGGING option is used, redo logs are not generated. Therefore, if the server terminates unexpectedly after the rebalance operation, the table becomes unusable. To prevent this, execute the CHECKPOINT statement after the rebalance operation completes.

<scan partition>

It specifies the number of partitions for the shard.

<parallel clause>

It specifies the number of threads to be used when rebalancing the table.

Description

When adding a cluster member and a cluster group using the following statements, the table shards are not rebalanced.

Execute the <alter database rebalance statement> to rebalance the shards of the entire table that were not rebalanced when adding a cluster group and a cluster member.

The <alter database rebalance statement> is performed with the following concepts for tables whose shards were not rebalanced.

ALTER TABLE t1 REBALANCE;
COMMIT;
ALTER TABLE t2 REBALANCE;
COMMIT;
ALTER TABLE t3 REBALANCE;
COMMIT;
...
...
ALTER TABLE t_n REBALANCE;
COMMIT;
The <alter database rebalance statement> proceeds even if rebalancing the shard of a specific table fails. It does not roll back the table whose shard rebalancing was successful.
Therefore, when the <alter database rebalance statement> is executed again after appropriately handling an error, it will rebalance only the shard for the table that requires rebalancing. In this case, the table whose shard rebalancing was successful will not be included in the rebalancing target.

Examples

The following is an example of executing the <alter database rebalance statement>.

gSQL> ALTER DATABASE REBALANCE;

Database altered.

Compatibility

The SQL standard does not define the concept of a cluster.

For More Information

Refer to ALTER TABLE name REBALANCE.

ALTER DATABASE REBALANCE EXCLUDE CLUSTER GROUP

Function

It rebalances the shards of all tables, excluding the shards of a specific cluster group.

Syntax

<alter database rebalance exclude cluster group statement> ::=
    ALTER DATABASE REBALANCE EXCLUDE CLUSTER GROUP cluster_group_name        
       [ ONLINE | OFFLINE ]        
       [ LOGGING | NOLOGGING ]        
       [ <scan partition> ]        
       [ <parallel clause> ]
    ;

<scan partition> ::= 
    SCAN PARTITION integer

<parallel clause> ::=
    NOPARALLEL
  | PARALLEL [ integer ]

Invocation and Access Rules

It can be performed in a cluster system.
The ALTER DATABASE ON DATABASE privilege is required to execute the <alter database rebalance exclude cluster group statement>.

Syntax Rules and Parameters

cluster_group_name

It is the name of the cluster group that does not include the shards of the tables.
If the specified cluster group is the only cluster group, the statement can not be executed.

[ ONLINE | OFFLINE ]

It determines whether DML operations are allowed during the rebalancing of the table's shard.

[ LOGGING | NOLOGGING ]

It specifies the amount of logging performed during table synchronization when rebalancing a table shard.

When the NOLOGGING option is used, redo logs are not generated. Therefore, if the server terminates unexpectedly after the rebalance operation, the table becomes unusable. To prevent this, execute the CHECKPOINT statement after the rebalance operation completes.

<scan partition>

It specifies the number of partitions for the shard.

<parallel clause>

It specifies the number of threads to be used when rebalancing the table.

Description

To drop a cluster group using the DROP CLUSTER GROUP statement, there must be no shards in the cluster group.

Execute the <alter database rebalance exclude cluster group statement> to ensure that the specified cluster group does not include any shards. Executing the <alter database rebalance exclude cluster group statement> for tables that include a shard from the specified cluster group has the following meaning.

ALTER TABLE t1 REBALANCE EXCLUDE CLUSTER GROUP g3;
COMMIT;
ALTER TABLE t2 REBALANCE EXCLUDE CLUSTER GROUP g3;
COMMIT;
ALTER TABLE t3 REBALANCE EXCLUDE CLUSTER GROUP g3;
COMMIT;
...
...
ALTER TABLE t_n REBALANCE EXCLUDE CLUSTER GROUP g3;
COMMIT;
If  the <alter database rebalance exclude cluster group statement> fails due to a lack of storage space, it does not roll back the tables that successfully excluded a shard.
Therefore, when executing the <alter database rebalance exclude cluster group statement> again after appropriately handling an error, it will exclude and rebalance only the shard for the table that requires rebalancing. In this case, the table that successfully excluded the shard will not be included in the rebalancing target.

Examples

The following is an example of executing the <alter database rebalance exclude cluster group statement>.

gSQL> ALTER DATABASE REBALANCE EXCLUDE CLUSTER GROUP g3;

Database altered.

Compatibility

The SQL standard does not define the concept of a cluster.

For More Information

Refer to the following.

ALTER DATABASE RECOVER

Function

It recovers the entire data file or part of the data files in the database using the online and archive log files.

Syntax

<alter database recover statement> ::=
      <complete database recover statement>
    | <datafile recover statement>
    | <complete tablespace recover statement>
    | <incomplete database recover statement>
    ;

<complete database recover statement> ::=
    ALTER DATABASE RECOVER [<recovery slaves clause>]

<datafile recover statement> ::=
    ALTER DATABASE RECOVER DATAFILE
        <datafile recovery clause>
        [ <recovery slaves clause> ]
        [ AT <domain name> ]

<datafile recovery clause> ::=
    <datafile recovery object> [, ...]

<datafile recovery object> ::=
    'datafile_name' [<recovery using backup option>] [recovery corruption option>]

<recovery using backup option> ::=
    USING BACKUP 'backup_datafile_name'

<recovery corruption option> ::=
    CORRUPTION

<complete tablespace recover statement> ::=
    ALTER DATABASE RECOVER TABLESPACE tablespace_name
        [ <recovery slaves clause> ]
        [ AT <domain name> ]

<incomplete database recover statement> ::=
      <batch incomplete recovery statement>
    | <interactive incomplete recovery statement>
    ;

<batch incomplete recovery statement> ::=
    ALTER DATABASE RECOVER <until clause> [<recovery slaves clause>]
    
<until clause> ::=
      UNTIL CHANGE integer
    | UNTIL CHANGE SCN scn_format
    | UNTIL TIME datetime_format

<using backup controlfile option> ::=
    USING BACKUP CONTROLFILE

<interactive incomplete recovery statement> ::=
    ALTER DATABASE <incomplete recovery option>

<incomplete recovery option> ::=
      BEGIN INCOMPLETE RECOVERY [<recovery slaves clause>]
    | END INCOMPLETE RECOVERY
    | RECOVER 'logfile name'
    | RECOVER AUTOMATICALLY
    | RECOVER SUGGESTION
    ;
<datafile recovery clause> ::=
    <datafile recovery object> [, ...]

<recovery slaves clause> ::=
      NOPARALLEL
    | PARALLEL [integer]

Invocation and Access Rules

The ALTER DATABASE ON DATABASE privilege is required to execute the <alter database recover statement>.

Syntax Rules and Parameters

<complete database recover statement>

It recovers the database's data files to the latest state using the online and archive log files.

<datafile recover statement>

It recovers to the latest state the datafile of a tablespace that was set offline using the immediate option, the backed-up datafile, or the datafile of a tablespace that requires recovery using the archive log files due to an error during backup.

<complete tablespace recover statement>

The data files of the tablespace are recovered to the latest state.

<incomplete database recover statement>

<batch incomplete database recover statement>

The datafiles in the database are recovered in batches to a specific point in time using the online and archive logfiles.

<interactive incomplete database recover statement>

The data files in the database are interactively recovered with the user up to a specific point in time using online and archive log files.

<recovery slaves clause>

It sets the number of slaves participating in the parallel recovery. If the <recovery slaves clause> is omitted, the value set in the RECOVERY_SLAVES property will be used.

If the <recovery slaves clause> is specified when performing BEGIN INCOMPLETE RECOVERY of an incomplete recovery, the database will be recovered in parallel using the value specified during BEGIN INCOMPLETE RECOVERY in the subsequent RECOVER statement.

<domain name>

It is the name of the member or group on which the statement is performed.
If not specified, the statement is performed on all groups.

Description

Since it is difficult to pinpoint the exact recovery completion point in a single attempt, incomplete recovery must be performed multiple times to find the desired recovery point. However, if the database is started with the RESETLOGS option after incomplete recovery, it will result in a new database. Therefore, incomplete recovery should be performed multiple times after making copies of the archive log files and online redo log files.

Examples

The following is an example of a complete recovery for the entire database.

ALTER DATABASE RECOVER;

The following is an example of datafile recovery.

ALTER DATABASE RECOVER DATAFILE 'test.dbf';

The following is an example of tablespace recovery.

ALTER DATABASE RECOVER TABLESPACE test_tbs;

The following is an example of incomplete recovery for the entire database until LSN 11123.

ALTER DATABASE RECOVER UNTIL CHANGE 11123;

The following is an example of incomplete recovery for the entire database until SCNs '100.10.1000', '100.10.*', and '100.*'.

ALTER DATABASE RECOVER UNTIL SCN '100.10.1000';
'*' represents infinite.
If dcn is set to '*', all logs less than or equal to the specified gcn are recovered.
If lcn is set to '*', all logs less than or equal to the specified gcn and dcn are recovered.
ALTER DATABASE RECOVER UNTIL SCN '100.10.1000';
ALTER DATABASE RECOVER UNTIL SCN '100.10.*';
ALTER DATABASE RECOVER UNTIL SCN '100.*';

The following is an example of incomplete recovery for the entire database until datetime '2026-03-09 16:38:35.148078'.

ALTER DATABASE RECOVER UNTIL TIME '2026-03-09 16:38:35.148078';

The following is an example of interactive incomplete recovery until the recoverable archive log files.

ALTER DATABASE BEGIN INCOMPLETE RECOVERY;
ALTER DATABASE RECOVER AUTOMATICALLY;
ALTER DATABASE END INCOMPLETE RECOVERY;

Compatibility

The SQL standard does not define the ALTER DATABASE statement.

For More Information

Refer to the following.

ALTER DATABASE REGISTER

Function

It registers unrecoverable segments in the database.

Syntax

<alter database register statement> ::=
    ALTER DATABASE REGISTER IRRECOVERALBE SEGMENT 
        <segment physical identifier list>
    ;

<segment physical identifier list> ::=
      integer
    | <segment physical identifier list> , integer

Invocation and Access Rules

The ALTER DATABASE ON DATABASE privilege is required to execute the <alter database register statement>.

Syntax Rules and Parameters

<alter database register statement>

It registers the unrecoverable segments in the database. This statement can be used under the assumption that the segment is no longer in use, when the database is unrecoverable, and no backup exists.

<segment physical identifier list>

The list of unrecoverable segment identifiers
• Integer: 8-byte integer segment identifier

Description

When a server restarts after an abnormal termination, the database performs the recovery process. During this process, it re-executes pages using the REDO log to recover pages that were not written to disk in the previous service stage.

If an unexpected failure occurs during the execution of the REDO operation, this statement can be used to ignore the failure and continue the recovery process.

Example

The following is an example of abandoning the recovery of the segment with identifier 4028679323648.

ALTER DATABASE REGISTER IRRECOVERABLE SEGMENT 4028679323648;

Compatibility

The SQL standard does not define the ALTER DATABASE statement.

For More Information

Refer to the following.

ALTER DATABASE RENAME CHANGE TRACKING FILE

Function

It renames the change tracking file.

Syntax

<rename change tracking file statement> ::=
    ALTER DATABASE RENAME CHANGE TRACKING FILE 'file_name'
    ;

Invocation and Access Rules

The ALTER DATABASE ON DATABASE privilege is required to execute the <rename change tracking file statement>.

Syntax Rules and Parameters

<rename change tracking file statement>

'file_name'

Description

For more information, refer to the syntax rules for each statement.

Example

The following is an example of renaming the change tracking file.

gSQL> ALTER DATABASE RENAME CHANGE TRACKING FILE 'new_change_tracking.ctf';

Database altered.

Compatibility

The SQL standard does not define the ALTER DATABASE statement.

For More Information

Refer to the following.

ALTER DATABASE RENAME GLOBAL TRANSACTION LOGFILE

Function

It renames the global transaction logfile in the database.

Syntax

<alter database rename global transaction logfile statement> ::=
    ALTER DATABASE RENAME GLOBAL TRANSACTION LOGFILE <source_clause>
       TO <target_clause>
    ;

<source_clause> ::= <logfile_list>

<target_clause> ::= <logfile_list>

<logfile_list> ::=
      'logfile_name'
    | <logfile_list>, 'logfile_name'

Invocation and Access Rules

The ALTER DATABASE ON DATABASE privilege is required to execute the <alter database rename global transaction logfile statement>.

Syntax Rules and Parameters

<alter database rename global transaction logfile statement>

Description

For more information, refer to the syntax rules for each statement.

Example

The following is an example of modifying the global transaction logfile.

ALTER DATABASE RENAME GLOBAL TRANSACTION LOGFILE
 'org_commit_0.log', 'org_commit_1.log' TO 'new_commit_0.log', 'new_commit_1.log';

Compatibility

The SQL standard does not define the ALTER DATABASE statement.

For More Information

Refer to ALTER DATABASE RENAME LOGFILE.

ALTER DATABASE RENAME LOGFILE

Function

It renames the logfile in the database.

Syntax

<alter database rename logfile statement> ::=
    ALTER DATABASE RENAME LOGFILE <logfile_list> TO <logfile_list>
    ;

<logfile_list> ::=
      'logfile_name'
    | <logfile_list> , 'logfile_name'

Invocation and Access Rules

The ALTER DATABASE ON DATABASE privilege is required to execute the <alter database rename logfile statement>.

Syntax Rules and Parameters

<alter database rename logfile statement>

Description

For more information, refer to the syntax rules for each statement.

Example

The following is an example of modifying the existing 'logfile.log' to 'newlogfile.log'.

ALTER DATABASE RENAME LOGFILE 'logfile.log' TO 'newlogfile.log';

Compatibility

The SQL standard does not define the ALTER DATABASE statement.

For More Information

Refer to the following.

ALTER DATABASE RESET LOCAL CLUSTER MEMBER

Function

It resets the local cluster member, excluding the tablespace object, to the state at the time of database creation.

Syntax

<alter database reset local cluster member statement> ::=
    ALTER DATABASE RESET LOCAL CLUSTER MEMBER
    ;

Invocation and Access Rules

It can be performed in a cluster system.
The start-up phase must be LOCAL OPEN.
The ADMINISTRATION ON DATABASE privilege is required to execute the <alter database reset local cluster member statement>.

Description

It resets the local cluster member, excluding the tablespace object, to the state at the time of database creation. It drops all user-created objects, except for the tablespace object.
The <alter database reset local cluster member statement> resets an inactive cluster member and allows the new cluster member to participate in the cluster system.
An inactive cluster member that is disconnected from the cluster system is processed as follows.

At this point, the device corresponding to the cluster member excluded from the cluster system can be reused using the following two methods.

Method 2 reduces the cost of recreating the tablespace compared to Method 1.

Examples

The following is an example of a reset using the <alter database reset local cluster member statement> after bringing the local cluster member, which has been excluded from the cluster system, up to the LOCAL OPEN phase.

gSQL> \startup nomount

Startup success


gSQL> ALTER SYSTEM MOUNT DATABASE;

System altered.


gSQL> ALTER SYSTEM OPEN LOCAL DATABASE;

System altered.

gSQL> ALTER DATABASE RESET LOCAL CLUSTER MEMBER;

Database altered.

Compatibility

The SQL standard does not define the concept of a cluster.

For More Information

Refer to the following.

ALTER DATABASE RESTORE

Function

It restores the data files in the database or tablespace using an incremental backup.

Syntax

<alter database restore statement> ::=
      <database restore statement>
    | <tablespace restore statement>
    | <controlfile restore statement>
    ;

<database restore statement> ::=
    ALTER DATABASE RESTORE [ <until clause> ] [ <parallel clause> ]

<until clause> ::=
    UNTIL CHANGE integer

<tablespace restore statement> ::=
    ALTER DATABASE RESTORE TABLESPACE tablespace_name 
        [ <parallel clause> ] [ AT <domain name> ]


<parallel clause> ::=
      NOPARALLEL
    | PARALLEL [ integer ]

<controlfile restore statement> ::=
    ALTER DATABASE RESTORE CONTROLFILE FROM 'file_name'

Invocation and Access Rules

The ALTER DATABASE ON DATABASE privilege is required to execute the <alter database restore statement>.

Syntax Rules and Parameters

<database restore statement>

It restores the data files in the database using an incremental backup. 
The database must be in the MOUNT phase.

<tablespace restore statement>

It restores the data files in the tablespace using an incremental backup.

<parallel clause>

It specifies the number of threads to be used during the backup.

<domain name>

It is the name of the member or group on which the statement is performed.
If not specified, the statement is performed on all groups.

<controlfile restore statement>

The control file is recovered using 'file_name'.

Description

Data recovery using a full backup employs the OS copy command to directly copy the backup file to the data file path. Data recovery using incremental backup restores only the deleted or outdated data files.

Examples

The following is an example of database recovery using an incremental backup.

ALTER DATABASE RESTORE;

The following is an example of tablespace recovery using an incremental backup.

ALTER DATABASE RESTORE TABLESPACE test_tbs;

The following is an example of database recovery using only the incremental backup with an LSN smaller than 11123.

ALTER DATABASE RESTORE UNTIL CHANGE 11123;

The following is an example of control file recovery using the controlfile.bak.

ALTER DATABASE RESTORE CONTROLFILE FROM 'controlfile.bak'

The following is an example of database recovery using four threads.

ALTER DATABASE RESTORE PARALLEL 4;

Compatibility

The SQL standard does not define the ALTER DATABASE statement.

For More Information

Refer to the following.

ALTER DATABASE SYNCHRONIZE

Function

It remotely synchronizes the shards and sequences across all tables.

Syntax

<alter database synchronize statement> ::=
    ALTER DATABASE SYNCHRONIZE 
       [ <synchronize target> ] 
       [ ONLINE | OFFLINE ] 
       [ LOGGING | NOLOGGING ] 
       [ <scan partition> ] 
       [ <parallel clause> ]
    ;

<synchronize target> ::= 
    TABLE
  | SEQUENCE
  | TABLE AND SEQUENCE
  | SEQUENCE AND TABLE

<scan partition> ::= 
    SCAN PARTITION integer

<parallel clause> ::=
    NOPARALLEL
  | PARALLEL [ integer ]

Invocation and Access Rules

It can be performed in the cluster system.

The ALTER DATABASE ON DATABASE privilege is required to execute the <alter database synchronize statement>.

Syntax Rules and Parameters

<synchronize target>

It specifies the synchronization target object.

[ ONLINE | OFFLINE ]

It determines whether DML operations are allowed during the synchronization process.

[ LOGGING | NOLOGGING ]

It specifies the amount of logging performed during table synchronization.

When the NOLOGGING option is used, redo logs are not generated. Therefore, if the server terminates unexpectedly after executing move shard, the table becomes unusable. To prevent this, execute the CHECKPOINT statement after synchronization is complete.

<scan partition>

It specifies the number of partitions for the shard.

If the <synchronize target> specifies only SEQUENCE, it will be ignored.

<parallel clause>

It specifies the number of threads to be used for table synchronization.

If the <synchronize target> specifies only SEQUENCE, it will be ignored.

Description

It synchronizes all existing offline shards and sequences, then switches them to online. Unlike ALTER DATABASE REBALANCE, it can be performed even if an inactive cluster member exists.

The inactive state of a cluster member means that it is not connected to the cluster system and occurs in the following situations.

The <alter database synchronize statement> performs the <alter table synchronize statement> for each table and is equivalent to the sum of the following queries.

ALTER TABLE t1 SYNCHRONIZE;
COMMIT;
ALTER TABLE t2 SYNCHRONIZE;
COMMIT;
ALTER TABLE t3 SYNCHRONIZE;
COMMIT;

...

ALTER TABLE tn SYNCHRONIZE;
COMMIT;

The <alter database synchronize statement> does not terminate even if an error occurs while synchronizing a specific table. It continues to synchronize the next table and succeeds, issuing the following warning.

gSQL> ALTER DATABASE SYNCHRONIZE;

ERR-42000(16555): of the total '5' tables, '1' tables failed to synchronize
Database altered.

The error message above indicates that one of the five tables has failed.

If the <alter database synchronize statement> is executed again after taking the appropriate action for the error, it will only operate on the failed table.

For more information about the error, refer to the system trace log (system.trc) of the member that executed the statement.

Example

The following is an example of executing the <alter database synchronize statement>.

gSQL> ALTER DATABASE SYNCHRONIZE;

Database altered.

Compatibility

The SQL standard does not define the concept of a cluster.

For More Information

Refer to the following.

ALTER INDEX

Function

It alters the index definition.

Syntax

<alter index statement> ::=
      <alter index physical attribute statement>
    | <rename index statement>
    | <aging index statement>
    | <rebuild index statement>
    | <index coalesce statement>
    | <alter index enforcement>
    ;

Invocation and Access Rules

One of the following privileges is required to execute <alter index statement>.

Syntax Rules and Parameters

<alter index physical attribute statement>

It alters the physical attributes of the index.
For more information, refer to ALTER INDEX name STORAGE.

<rename index statement>

It renames the index.
For more information, refer to ALTER INDEX name RENAME TO.

<aging index statement>

It deletes the empty pages of the index.
For more information, refer to ALTER INDEX name AGING.

<rebuild index statement>

It rebuilds the index.
For more information, refer to ALTER INDEX name REBUILD.

<index coalesce statement>

It removes index fragmentation.
For more information, refer to ALTER INDEX name COALESCE.

<alter index enforcement>

It enables or disables an index.
For more information, refer to ALTER INDEX name ENABLE/DISABLE.

Description

Refer to the descriptions of each detailed statement.

Examples

Refer to the examples of each detailed statement.

Compatibility

The SQL standard does not define the concept of the index.

ALTER INDEX name AGING

Function

It deletes an empty page from the index. It can be performed concurrently with DML.

Syntax

<aging index statement> ::=
    ALTER INDEX index_name AGING
        [ AT <domain name> ]
    ;

Invocation and Access Rules

One of the following privileges is required to execute the <aging index statement>.

Syntax Rules and Parameters

index_name

It is the name of the target index.

<domain name>

It is the name of the member or group on which the statement is performed.
If not specified, the statement is performed on all groups.

Description

This syntax returns pages, where all keys have been deleted, from the index pages to a segment. Aging is processed in two steps: logical deletion and physical deletion. Logical deletion involves disconnecting the reference to the page in the index, and it is performed when the SCN at the time of deleting the last key of a page is smaller than the agable SCN of the system. After that, physical deletion is performed when the SCN of the logical deletion is smaller than the agable SCN of the system.

If the agable SCN of the system does not increase, the empty page may not be deleted, even if the index AGING statement succeeded.

Examples

The following is an example of index aging.

gSQL> select index_name, empty_blocks from user_indexes where index_name = 'T1X';

INDEX_NAME EMPTY_BLOCKS
---------- ------------
T1X                   2

1 row selected.

gSQL> alter index t1x aging;

Index altered.

gSQL> select index_name, empty_blocks from user_indexes where index_name = 'T1X';

INDEX_NAME EMPTY_BLOCKS
---------- ------------
T1X                   0

1 row selected.

Compatibility

The SQL standard does not define the concept of the index.

For More Information

Refer to the following.

ALTER INDEX name COALESCE

Function

It coalesces adjacent leaf pages of the index, reducing the index space in use. This operation can be performed concurrently with DML.

Syntax

<index coalesce statement> ::=
    ALTER INDEX index_name COALESCE
        [ AT <domain name> ]
    ;

Invocation and Access Rules

The user must meet the following conditions to execute the <index coalesce statement>.

At least one of the following privileges for the tablespace in which the index is to be created is required.

Syntax Rules and Parameters

index_name

It is the name of the target index. 
The schema name can be specified, and if omitted, the user's default schema name will be used.

<domain name>

It is the name of the member or group on which the statement is performed.
If not specified, the statement is performed on all groups.

Description

Index coalesce

Index coalesce

Comparison with INDEX REBUILD


INDEX REBUILD

INDEX COALESCE

Altering index attributes

Possible

Impossible

Moving tablespace

Possible

Impossible

Locking table

Required

Not required

Additional space for execution

Required

Not required

Decreasing tree height

Possible

Impossible

Examples

gsql> ALTER INDEX T1X COALESCE;

Index altered.

Compatibility

The SQL standard does not define the concept of the index.

For More Information

Refer to the following.

ALTER INDEX name ENABLE/DISABLE

Function

It enables or disables an index.

Syntax

<alter index enforcement> ::=
    ALTER INDEX index_name <index enforcement>
    ;

<index enforcement> ::=
      { ENABLE | ENFORCED }
    | { DISABLE | NOT ENFORCED }

Invocation and Access Rules

One of the following privileges is required to execute the <alter index enforcement> .

Syntax Rules and Parameters

index_name

It is the name of the target index.
The schema name can be specified, and if omitted, the user's default schema name will be used.

<index enforcement>

ENABLE and ENFORCED have the same meaning.
DISABLE and NOT ENFORCED have the same meaning.

Description

An index created to support a key constraint must be managed using the ALTER CONSTRAINT statement.

CREATE TABLE t1 ( pk INTEGER PRIMARY KEY );
COMMIT;

gSQL> \desc t1

COLUMN_NAME TYPE         IS_NULLABLE
----------- ------------ -----------
PK          NUMBER(10,0) FALSE      

INDEX_NAME           TABLESPACE_NAME INDEX_TYPE IS_UNIQUE COLUMNS
-------------------- --------------- ---------- --------- -------
T1_PRIMARY_KEY_INDEX MEM_TEMP_TBS    BTREE      TRUE      PK     

CONSTRAINT_NAME CONSTRAINT_TYPE ASSOCIATED_INDEX     COLUMNS
--------------- --------------- -------------------- -------
T1_PRIMARY_KEY  PRIMARY KEY     T1_PRIMARY_KEY_INDEX PK

gSQL> ALTER INDEX T1_PRIMARY_KEY_INDEX DISABLE;
ERR-42000(16050): cannot modify index used for enforcement of unique/primary/foreign key : 
ALTER INDEX T1_PRIMARY_KEY_INDEX DISABLE
            *
ERROR at line 1:

gSQL> ALTER TABLE t1 ALTER CONSTRAINT t1_primary_key NOT ENFORCED;
Table altered.

Examples

Disable the index.

CREATE TABLE t1 ( c1 INTEGER );
CREATE INDEX idx1 ON t1(c1);

gSQL> ALTER INDEX idx1 DISABLE;
Index altered.

Compatibility

The SQL standard does not define the concept of the index.

For More Information

Refer to the following.

ALTER INDEX name REBUILD

Function

It rebuilds the index.

Syntax

<rebuild index statement> ::=
    ALTER INDEX index_name REBUILD
        [ ONLINE | OFFLINE ]
        [ <index attributes> [...] ]
        [ TABLESPACE tablespace_name ]
        [ AT <domain name> ]
    ;

<index attributes> ::=
      <physical attribute clause>
    | STORAGE ( <segment attr clause> [...] )
    | <parallel clause> 

<physical attribute clause> ::=
      PCTFREE integer
    | INITRANS integer
    | MAXTRANS integer

<segment attr clause> ::=
      INITIAL <size_clause>
    | NEXT <size_clause>

<size clause> ::=
      integer [ K | M | G | T ]

<parallel clause> ::=
      NOPARALLEL
    | PARALLEL [ integer ]

Invocation and Access Rules

The user must satisfy the following conditions to execute the <rebuild index statement>.

At least one of the following privileges on the tablespace where the index is to be created is required.

Syntax Rules and Parameters

index_name

It is the name of the target index.
The schema name can be specified; if omitted, the user's default schema name is used.

[ ONLINE | OFFLINE ]

It determines whether DML operations are allowed on the table during the index rebuild process.

<physical attribute clause>

It defines the physical attribute information of the index.

<segment attr clause>

It specifies the information about the storage space where the index will be stored.

<size clause>

It specifies the file size in bytes. (If omitted, bytes are used by default.)

NOPARALLEL | PARALLEL [ integer ]

It specifies the number of threads to be used when rebuilding the index.

TABLESPACE tablespace_name

It specifies the name of the tablespace where the index will be rebuilt.

<domain name>

It is the name of the member or group on which the statement is performed.
If not specified, the statement is performed on all groups.

Description

Examples

The following is an example of altering the index's logging setting and tablespace.

gsql> SELECT INDEX_NAME, TABLESPACE_NAME FROM INDEXES AS IDX, TABLESPACES AS TBS WHERE IDX.TABLESPACE_ID = TBS.TABLESPACE_ID AND IDX.INDEX_NAME = 'T1X';

INDEX_NAME TABLESPACE_NAME
---------- ---------------
T1X        MEM_TEMP_TBS   

1 row selected.

gsql> ALTER INDEX T1X REBUILD TABLESPACE MEM_DATA_TBS;

SELECT INDEX_NAME, TABLESPACE_NAME FROM INDEXES AS IDX, TABLESPACES AS TBS WHERE IDX.TABLESPACE_ID = TBS.TABLESPACE_ID AND IDX.INDEX_NAME = 'T1X';

INDEX_NAME TABLESPACE_NAME
---------- ---------------
T1X        MEM_DATA_TBS   

1 row selected.

Compatibility

The SQL standard does not define the concept of the index.

For More Information

Refer to the following.

ALTER INDEX name RENAME TO

Function

It alters the index name.

Syntax

<rename index statement> ::=
    ALTER INDEX index_name
        RENAME TO new_index_name
    ;

Invocation and Access Rules

One of the following privileges is required to execute the <rename index statement>.

Syntax Rules and Parameters

index_name

It is the name of the target index.
The schema name cannot be specified, and it must be the same as the schema name of the existing index.

new_index_name

It is the name of the new index, and it must be unique within the schema.

Description

Refer to the syntax rules for each statement.

Example

The following is an example of altering an index name.

gSQL> ALTER INDEX t1_idx1 RENAME TO idx_t1_id;

Index altered.

Compatibility

The SQL standard does not define the concept of the index.

For More Information

Refer to the following.

ALTER INDEX name STORAGE

Function

It alters the physical attributes of the index.

Syntax

<alter index physical attribute statement> ::=
    ALTER INDEX index_name
    | <physical attribute clause>
    | [ STORAGE ( <segment attr clause> [...] ) ]
    ;

<physical attribute clause> ::=
      PCTFREE integer
    | INITRANS integer
    | MAXTRANS integer

<segment attr clause> ::=
      INITIAL <size_clause>
    | NEXT <size_clause>

<size clause> ::=
      integer [ K | M | G | T ]

Invocation and Access Rules

One of the following privileges is required to execute the <alter index physical attribute statement>.

Syntax Rules and Parameters

index_name

It is the name of the target index.

<physical attribute clause>

It defines the physical attributes of the index.

<segment attr clause>

It specifies the information for the index storage space.

<size clause>

It specifies the file size in bytes. (If omitted, bytes are used by default.)

Description

Refer to the syntax rules for each statement.

Examples

The following is an example of altering the physical attributes of an index.

gSQL> ALTER INDEX idx_t1_id PCTFREE 10 INITRANS 4 MAXTRANS 8;

Index altered.

Compatibility

The SQL standard does not define the concept of the index.

For More Information

Refer to the following.

ALTER PROFILE

Function

It alters the password management method.

Syntax

<alter profile statement> ::= 
    ALTER PROFILE profile_name LIMIT 
    { <password_parameters>, ...}
    ; 

<password parameters> ::= 
      FAILED_LOGIN_ATTEMPTS { integer | UNLIMITED | DEFAULT }
    | PASSWORD_LOCK_TIME  { password_parameter_number_interval | UNLIMITED | DEFAULT }
    | PASSWORD_LIFE_TIME  { password_parameter_number_interval | UNLIMITED | DEFAULT }
    | PASSWORD_GRACE_TIME { password_parameter_number_interval | UNLIMITED | DEFAULT }
    | PASSWORD_REUSE_MAX  { integer | UNLIMITED | DEFAULT }
    | PASSWORD_REUSE_TIME { password_parameter_number_interval | UNLIMITED | DEFAULT }
    | PASSWORD_VERIFY_FUNCTION { <verify_policy> | NULL | DEFAULT }

<verify_policy> ::= 
      KISA_VERIFY_FUNCTION
    | ORA12C_VERIFY_FUNCTION
    | ORA12C_STRONG_VERIFY_FUNCTION
    | VERIFY_FUNCTION_11G 
    | VERIFY_FUNCTION

<password_parameter_number_interval> ::=
   integer 
 | integer / integer

Invocation and Access Rules

The ALTER PROFILE ON DATABASE privilege is required to execute the <alter profile statement>.

Syntax Rules and Parameters

profile_name

It is the name of the profile to be altered.

FAILED_LOGIN_ATTEMPTS

It sets the number of consecutive failed login attempts allowed.
For more information, refer to CREATE PROFILE.

PASSWORD_LOCK_TIME

It sets the account lockout duration (in days) after consecutive login failures.
For more information, refer to CREATE PROFILE.

PASSWORD_LIFE_TIME

It sets the password lifetime (in days).
For more information, refer to CREATE PROFILE.

PASSWORD_GRACE_TIME

It sets the grace period for password expiration when logging in after the PASSWORD_LIFE_TIME.
For more information, refer to CREATE PROFILE.

PASSWORD_REUSE_MAX

It specifies the number of recent passwords that can not be reused when a user attempts to reuse an old password.
For more information, refer to CREATE PROFILE.

PASSWORD_REUSE_TIME

It specifies the elapsed time required before an old password can be reused.
For more information, refer to CREATE PROFILE.

PASSWORD_VERIFY_FUNCTION

It sets the password complexity verification method.
For more information, refer to CREATE PROFILE.

Examples

The following is an example of modifying the profile to control account lockout.

gSQL> ALTER PROFILE prof1 LIMIT
        FAILED_LOGIN_ATTEMPTS 3
        PASSWORD_LOCK_TIME 3;

Profile altered.

gSQL> COMMIT;

Commit complete.

The following is an example of modifying the profile to control the password lifetime.

gSQL> ALTER PROFILE prof1 LIMIT
        PASSWORD_LIFE_TIME 90 
        PASSWORD_GRACE_TIME 7;

Profile altered.

gSQL> COMMIT;

Commit complete.

The following is an example of modifying the profile to control password reusability.

gSQL> ALTER PROFILE prof1 LIMIT
        PASSWORD_REUSE_MAX  DEFAULT
        PASSWORD_REUSE_TIME DEFAULT;

Profile altered.

gSQL> COMMIT;

Commit complete.

The following is an example of modifying the profile to control password complexity verification.

gSQL> ALTER PROFILE prof1 LIMIT
        PASSWORD_VERIFY_FUNCTION KISA_VERIFY_FUNCTION;

Profile altered.

gSQL> COMMIT;

Commit complete.

Compatibility

The SQL standard does not define the concept of the profile.

For More Information

Refer to DROP PROFILE.

ALTER SEQUENCE

Function

It alters the sequence.

Syntax

<alter sequence generator statement> ::=
    ALTER SEQUENCE sequence_name <alter sequence generator options>
    ;

<alter sequence generator options> ::=
    <alter sequence generator option> [, ...]

<alter sequence generator option> ::=
      <alter sequence generator restart option>
    | <basic sequence generator option>

<alter sequence generator restart option> ::=
    RESTART [ WITH integer ]

<basic sequence generator option> ::=
      <sequence generator increment by option>
    | <sequence generator maxvalue option>
    | <sequence generator minvalue option>
    | <sequence generator cycle option>
    | <sequence generator cache option>


<sequence generator increment by option> ::=
    INCREMENT BY integer

<sequence generator maxvalue option> ::=
      MAXVALUE integer
    | (NO MAXVALUE | NOMAXVALUE)

<sequence generator minvalue option> ::=
      MINVALUE integer
    | (NO MINVALUE | NOMINVALUE)

<sequence generator cycle option> ::=
      CYCLE 
    | (NO CYCLE | NOCYCLE)

<sequence generator cache option> ::=
      CACHE integer
    | (NO CACHE | NOCACHE)

Invocation and Access Rules

One of the following privileges is required to execute the <alter sequence generator statement>.

Syntax Rules and Parameters

sequence_name

It is the name of the sequence to be altered.
The schema to which the sequence belongs can be defined, such as schema_name.sequence_name. If the schema_name is omitted, the default schema name of the user executing the statement will be used.

<alter sequence generator restart option>

It sets the NEXT VALUE of the sequence.
However, it does not change the START WITH value defined in the CREATE SEQUENCE statement.

If the <alter sequence generator restart option> clause is not specified, the sequence attributes are changed based on the current sequence value.

<sequence generator increment by option>

It changes the interval of the sequence numbers.
The following are the constraints and characteristics.

<sequence generator maxvalue option>

It changes the maximum value that the sequence can generate.
However, the MAXVALUE must not be smaller than the current sequence value.

<sequence generator minvalue option>

It changes the minimum value that the sequence can generate.
However, the MINVALUE must not be greater than the current sequence value.

<sequence generator cycle option>

It changes whether the sequence continues generating values when it reaches the maximum or minimum value.

<sequence generator cache option>

It defines the number of sequence values to be pre-loaded into memory for quick access.

When the database is restarted, the sequence values stored in memory are lost, and it starts from the value after reloading.

Description

It is not possible to change the START WITH attribute, which is one of the sequence attributes defined in the CREATE SEQUENCE statement. To change the START WITH attribute, the sequence must be re-created by executing the CREATE SEQUENCE statement after performing a DROP SEQUENCE.

Examples

The following is an example of restarting the sequence value using the RESTART option and assigning a new ID based on it.

gSQL> SELECT id, name FROM t1 ORDER BY 1;

 ID NAME  
--- ------
 10 leekmo
 42 mkkim 
 51 jhkim 
172 ehpark

4 rows selected.


gSQL> ALTER SEQUENCE seq1 RESTART;

Sequence altered.


gSQL> UPDATE t1 SET id = seq1.NEXTVAL;

4 rows updated.


gSQL> SELECT id, name FROM t1 ORDER BY 1;

ID NAME  
-- ------
 1 leekmo
 2 mkkim 
 3 jhkim 
 4 ehpark

4 rows selected.

Compatibility

The SQL standard does not define the CACHE/ NO CACHE clause.

SQL standard compatibility

Feature ID

Description

Compatibility

T176

Sequence generator support

O

T177

Sequence generator support: simple restart option

O

For More Information

Refer to the following.

ALTER SESSION CLEANUP GLOBAL TEMPORARY SEGMENT POOL;

Function

It returns all segments that were caught for reuse in a session to the tablespaces.

Syntax

<alter session cleanup global temporary segment pool statement> ::=
    ALTER SESSION CLEANUP GLOBAL TEMPORARY SEGMENT POOL
    ;

Description

It cleans up only the segments from the segment cache in the performed session.

Examples

The following is an example of cleaning up the segment cache in the session.

gSQL> ALTER SESSION CLEANUP GLOBAL TEMPORARY SEGMENT POOL;

Session altered.

Compatibility

The SQL standard does not define the concept of the segment cache for global temporary tables and a global temporary indexes.

For More Information

Refer to Global Temporary Table.

ALTER SESSION SET property_name

Function

It sets the property value for the session.

Syntax

<alter session set statement> ::=
    ALTER SESSION SET <property name> { = <property value> | TO DEFAULT }
    ;

Syntax Rules and Parameters

<property name>

It is the property name to be set.
For more information, refer to the Server Property section in the database administration manual.

<property value>

It is the property value to be set.

TO DEFAULT

It sets the session property value to the system property value.

Description

For more information on each property, refer to the Server Property section in the database administration manual.

Examples

The following is an example of an error that occurs when setting the ERROR HINT property, causing an error in the hint clause.

gSQL> ALTER SESSION SET HINT_ERROR = ON;

Session altered.

gSQL> SELECT /*+ INDEX( t1, invalid_index ) */ name FROM t1 WHERE id = 1;

ERR-42000(16058): not applicable hint : 
SELECT /*+ INDEX( t1, invalid_index ) */ name FROM t1 WHERE id = 1
           *
ERROR at line 1:

The following is an example of setting the session property value to the system property value.

gSQL> ALTER SESSION SET HINT_ERROR TO DEFAULT;

Session altered.

Compatibility

The SQL standard does not define the concept of the session property.

For More Information

Refer to ALTER SESSION SET property_name.

ALTER SYSTEM CANCEL SESSION

Function

It cancels the operation currently being executed in the session.

Syntax

<alter system cancel session statement> ::=
      ALTER SYSTEM CANCEL SESSION [<member_position>,] <session_id>,
          <serial#> [AT <domain_name>]
    ;

Invocation and Access Rules

The ALTER SYSTEM ON DATABASE privilege is required to execute the <alter system cancel session statement>.

Syntax Rules and Parameters

<member_position>

This syntax is valid only in a cluster database.
t is the member position of the session that is the target for cancellation.

<session_id>

It is the session ID.

<serial#>

It is a SERIAL NUMBER for the session.

<domain name>

This syntax is valid only in a cluster database.
It is the name of the member or group on which the syntax is performed. 
If not specified, the statement is performed on all groups.

Description

CANCEL SESSION applies only to a driver session, but it does not apply to a system session or a cluster session.
If this statement is executed in a system session or cluster session, the following error will occur.
gSQL> ALTER SYSTEM CANCEL SESSION 1,1;

ERR-42000(16603): system session cannot be canceled

Example

The following is an example of executing the <alter system cancel session statement>.
gSQL> SELECT USER_NAME, SESSION_ID, SERIAL_NO, SESSION_STATUS, PROGRAM_NAME FROM V$SESSION WHERE USER_NAME = 'TEST';

USER_NAME SESSION_ID SERIAL_NO SESSION_STATUS PROGRAM_NAME
--------- ---------- --------- -------------- ------------
TEST              28        10 CONNECTED      gsql        
TEST              29         1 CONNECTED      gsqlnet     

2 rows selected.

gSQL> ALTER SYSTEM CANCEL SESSION 28, 10;

System altered.

Compatibility

The SQL standard does not define the ALTER SYSTEM CANCEL SESSION statement.

ALTER SYSTEM CHECKPOINT

Function

It performs a CHECKPOINT.

Syntax

<alter system checkpoint statement> ::=
    ALTER SYSTEM CHECKPOINT
    [ AT <domain name> ]
    ;

Invocation and Access Rules

The ALTER SYSTEM ON DATABASE privilege is required to execute the <alter system checkpoint statement>.

Syntax Rules and Parameters

<alter system checkpoint statement>

CHECKPOINT is an operation that ensures all data altered by committed transactions is written to disk.

<domain name>

It is the name of the member or group on which the statement is performed.
If not specified, the statement is performed on all groups.

Description

The checkpoint operation records all changes made by committed transactions to disk, enabling rapid recovery in the event of a system error.

Example

The following is an example of how to perform a CHECKPOINT.

ALTER SYSTEM CHECKPOINT;

Compatibility

The SQL standard does not define the concept of a CHECKPOINT.

ALTER SYSTEM CLEANUP BUFFER_CACHE

Function

It clears all buffer pages that can be freed from the buffer cache.

Syntax

<alter system cleanup buffer_cache statement> ::=
    ALTER SYSTEM CLEANUP BUFFER_CACHE
    [ AT <domain name> ]
    ;

Invocation and Access Rules

The ALTER SYSTEM ON DATABASE privilege is required to execute the <alter system cleanup buffer_cache statement>.

Syntax Rules and Parameters

<alter system cleanup buffer_cache statement>

Syntax rules and parameters do not exist for the <alter system cleanup buffer_cache statement>.

<domain name>

It is the name of the member or group on which the statement is performed.
If not specified, the statement is performed on all groups.

Description

It flushes and frees all buffer pages that can be freed, cached in the buffer.

It must be used to clear the buffer cache before performance measurement.

If used on a live server, it could have a detrimental effect on performance.

Example

The following is an example of how to perform a CLEANUP BUFFER_CACHE.

ALTER SYSTEM CLEANUP BUFFER_CACHE;

Compatibility

The SQL standard does not define the concept of CLEANUP BUFFER_CACHE.

ALTER SYSTEM CLEANUP PLAN

Function

It performs a CLEANUP of all SQL plans.

Syntax

<alter system cleanup plan statement> ::=
    ALTER SYSTEM CLEANUP PLAN
    [ AT <domain name> ]
    ;

Invocation and Access Rules

The ALTER SYSTEM ON DATABASE privilege is required to execute the <alter system cleanup plan statement>.

Syntax Rules and Parameters

<alter system cleanup plan statement>

There are no syntax rules or parameters for the <alter system cleanup plan statement>.

<domain name>

It is the name of the member or group on which the statement is performed.
If not specified, the statement is performed on all groups.

Description

It cleans up all cached SQL plans.

Examples

The following is an example of executing the CLEANUP PLAN.

ALTER SYSTEM CLEANUP PLAN;

Compatibility

The SQL standard does not define the concept of a CLEANUP PLAN.

ALTER SYSTEM FLUSH LOGS

Function

Requests that redo log records stored in the database log buffer be flushed to the log file.

Syntax

<alter system flush flush logs statement> ::=
    ALTER SYSTEM FLUSH LOGS [ AT domain_name ]
    ;

Invocation and Access Rules

The ALTER SYSTEM ON DATABASE privilege is required to execute the <alter system flush logs statement>.

Syntax Rules and Parameters

<alter system flush logs statement>

Requests that logs generated by transactions be flushed to the log file.

<domain name>

It is the name of the member or group on which the statement is performed. 
If not specified, the statement is performed on all groups.

Description

Requests that redo log records stored in the log buffer be flushed to the online log file and waits until the operation is complete. 
Use this statement when log flushing must be completed.

Example

The following is an example of executing FLUSH LOGS.

ALTER SYSTEM FLUSH LOGS;

Compatibility

The SQL standard does not define FLUSH LOGS.

ALTER SYSTEM IRRECOVERABLE CLUSTER MEMBER

Function

It specifies an irrecoverable cluster member.

Syntax

<alter system irrecoverable cluster member statement> ::=
    ALTER SYSTEM IRRECOVERABLE CLUSTER MEMBER <domain name>
    ;

Invocation and Access Rules

The ALTER SYSTEM ON DATABASE privilege is required to execute the <alter system irrecoverable cluster member statement>.

Syntax Rules and Parameters

<alter system irrecoverable cluster member statement>

The database must be in the MOUNT phase.

<domain name>

It is the name of an irrecoverable member. 
Specifying all members in a group as irrecoverable members is not allowed.

Description

It is used to restart the system, excluding the corresponding member, if the cluster fails to restart due to an irrecoverable member. The corresponding member must be dropped using the ALTER DATABASE DROP INACTIVE CLUSTER MEMBERS after the system successfully restarts.

This statement is used when a global transaction in the PREPARE state (in-doubt transaction) requests status information from a remote member to determine whether to COMMIT, but the COMMIT decision cannot be made due to the member being in an unrecoverable state. In such cases, the COMMIT decision is made excluding that member.

Examples

The following is an example where one of six nodes is in an unrecoverable state, and the transaction state of the other five nodes is PREPARE. When the local startup phase is brought up, an error occurs.

gSQL> ALTER SYSTEM OPEN LOCAL DATABASE;

ERR-HY000(56013): cannot resolve in-doubt transaction '0.1.45613060' because '1' members of the total '5' remote cluster members were disconnected - connection map was '011110'

To perform the startup while excluding the unrecoverable member in this state, follow the steps below.

gSQL> ALTER SYSTEM IRRECOVERABLE CLUSTER MEMBER G3N2;

System altered.

gSQL> ALTER SYSTEM OPEN LOCAL DATABASE;

System altered.

Compatibility

The SQL standard does not define the concept of an IRRECOVERABLE CLUSTER MEMBER.

ALTER SYSTEM JOIN DATABASE

Function

It includes a specific inactive cluster member back into the cluster system.

Syntax

<alter system join database statement> ::=
    ALTER SYSTEM JOIN DATABASE 
    ;

Invocation and Access Rules

It can be performed in a cluster system.
The ADMINISTRATION ON DATABASE privilege is required to execute the <alter system join database statement>.

Description

The inactive state of a cluster member means that it is not connected to the cluster system, and it occurs in the following situations.

If a specific cluster member is inactive, it can be included back into the cluster system using the following procedure.

$ gsql sys gliese --as sysdba --dsn=G3N2
gSQL> \startup
$ gsql sys gliese --as sysdba --dsn=G3N2
gSQL> ALTER SYSTEM JOIN DATABASE;

Use the <alter system join database statement> to make an inactive cluster member, which has been started up to the local open phase, participate in the cluster system without shutting it down.

To have the inactive cluster member rejoin the cluster system, the database state of the cluster system and that of the inactive cluster member must be the same.

The inactive cluster member cannot rejoin the cluster system after the transaction that alters the database in the cluster system has completed.

To operate the cluster system normally, inactive cluster members should be dropped according to the following procedure when multiple inactive cluster members exist.

  1. JOIN the inactive cluster members that can participate in the cluster system.

gSQL> ALTER SYSTEM JOIN DATABASE;

  1. DROP the inactive cluster members that cannot participate in the cluster system.

gSQL> ALTER DATABASE DROP INACTIVE CLUSTER MEMBERS;

Since all inactive cluster members are removed from the cluster system when executing the <alter database drop inactive cluster member statement>, all inactive cluster members that can participate should be included in the cluster system before dropping.

The <alter system join database statement> command changes user tables to the online state one by one. However, if it fails to change all tables to the online state, it outputs the following warning messages.
gSQL> ALTER SYSTEM JOIN DATABASE;

ERR-42000(16405): of the total '5' tables in the database, '2' tables need to be rebalanced : 
  concurrent execution : 0 
  inactive member      : 0 
  replica usablility   : 0 
  offline tablespace   : 0 
  low table scn        : 2 
  others               : 0

The meanings of each warning message are as follows:

Examples

gSQL> ALTER SYSTEM JOIN DATABASE;

Compatibility

The SQL standard does not define the concept of a cluster.

For More Information

Refer to ALTER DATABASE DROP INACTIVE CLUSTER MEMBERS.

ALTER SYSTEM [KILL | DISCONNECT] SESSION

Function

It terminates the session.

Syntax

<alter system end session statement> ::=
      ALTER SYSTEM DISCONNECT SESSION [<member_position>,] <session_id>,
           <serial#> [<disconnect_option>] [AT <domain name>]
    |  ALTER SYSTEM KILL SESSION [<member_position>,]
           <session_id>, <serial#> [AT <domain name>]
    ;
<disconnect_option> ::=
      POST_TRANSACTION
    | IMMEDIATE

Invocation and Access Rules

The ALTER SYSTEM ON DATABASE privilege is required to execute the <alter system end session statement>.

Syntax Rules and Parameters

<member_position>

It is the member position of a session that is a disconnect/kill target in a cluster environment.

<session_id>

It is the session ID.

<serial#>

It is the SERIAL NUMBER of the session.

<disconnect_option>

If the <disconnect_option> is not used, it operates in IMMEDIATE.

<domain name>

It is the name of the member or group on which the statement is performed.
If not specified, the statement is performed on all groups.

Description

DISCONNECT SESSION can specify the POST_TRANSACTION and IMMEDIATE options. POST_TRANSACTION terminates the session after the currently running transaction is completed. IMMEDIATE terminates the session after immediately cleaning up the currently running transaction
KILL SESSION terminates an abnormal session that remains in the system without its associated process.

Example

gSQL> SELECT USER_NAME, SESSION_ID, SERIAL_NO, SESSION_STATUS, PROGRAM_NAME FROM V$SESSION WHERE USER_NAME = 'TEST';

USER_NAME SESSION_ID SERIAL_NO SESSION_STATUS PROGRAM_NAME
--------- ---------- --------- -------------- ------------
TEST              62        49 CONNECTED      gsql        
TEST              65       109 CONNECTED      gsqlnet     
TEST              66       130 CONNECTED      gsql        

3 rows selected.

gSQL> ALTER SYSTEM DISCONNECT SESSION 65, 109;

System altered.

Compatibility

The SQL standard does not define this.

ALTER SYSTEM {MOUNT | OPEN} DATABASE

Function

It mounts the database on the system or alters the database to a state that is available for service.

Syntax

<alter system database statement> ::=
    ALTER SYSTEM <alter system database clause>
    ;

<alter system database clause> ::= 
      MOUNT DATABASE
    | OPEN [ <database_scope> ] DATABASE [ <open_database_option> ]

<open_database_option> ::=
      NORESETLOGS
    | RESETLOGS

<database_scope> ::=
      LOCAL
    | GLOBAL

Invocation and Access Rules

The ADMINISTRATION ON DATABASE privilege is required to execute the <alter system database statement>.

Syntax Rules and Parameters

<alter system database clause>

<open database option>

<database_scope>

Examples

The following is an example of initializing the online redo logs.

ALTER SYSTEM OPEN DATABASE RESETLOGS;

Compatibility

The SQL standard does not define the concept of MOUNT or OPEN in the database.

For More Information

Refer to ALTER DATABASE RECOVER.

ALTER SYSTEM RECONNECT GLOBAL CONNECTION

Function

It determines whether to reconnect to the session that is connected in GLOBAL CONNECTION form.

Syntax

<alter system reconnect global connection statement> ::=
    ALTER SYSTEM RECONNECT GLOBAL CONNECTION
    ;

Invocation and Access Rules

The ALTER SYSTEM ON DATABASE privilege is required to execute the <alter system reconnect global connection statement>.

Description

Whether the GLOBAL CONNECTION client reconnects is determined by comparing the SCN of a system object acquired from the server during the first connection with the SCN of the current server system object. This statement causes the client to reconnect by increasing the SCN of the system object.

The client does not reconnect immediately after executing this statement. The client will reconnect by comparing the SCN when executing a command on the server, and will not attempt to reconnect if the connections to all members from the client are valid.

Examples

The following is an example of executing the statement.

gSQL> ALTER SYSTEM RECONNECT GLOBAL CONNECTION;

System altered.

Compatibility

The SQL standard does not define the concept of a GLOBAL CONNECTION.

ALTER SYSTEM RESET property_name

Function

It removes a property value from the properties file.

Syntax

<alter system reset statement> ::=
    ALTER SYSTEM { RESET | UNSET } <property name>
        [ SCOPE = { FILE | SPFILE } ]
        [ AT <domain name>]
    ;

Invocation and Access Rules

The ALTER SYSTEM ON DATABASE privilege is required to execute the <alter system reset statement>.

Syntax Rules and Parameters

{ RESET | UNSET }

RESET and UNSET are reserved words with the same meaning, so either one can be used.

<property name>

It is the name of the property to be removed.
For more information, refer to the Server Property section in the database administration manual.

[ SCOPE = { FILE | SPFILE } ]

It removes the property from the property file, so only SCOPE=FILE/SPFILE can be used.

If the SCOPE clause is not specified, the default value is SCOPE = FILE.

<domain name>

It is the name of the member or group on which the statement is performed.
If not specified, the statement is performed on all groups.

Description

If a property is altered using SCOPE=FILE/SPFILE, the updated value is saved in the property file and applied when the database is restarted.
When executing RESET, the updated property value is removed from the property file, and the default value is used when the database is restarted.

Examples

The following is an example of altering a property using SCOPE=FILE.

gSQL> ALTER SYSTEM SET PROCESS_MAX_COUNT=128 SCOPE=FILE;

System altered.

The following is an example of removing the property that was altered above.

gSQL> ALTER SYSTEM RESET PROCESS_MAX_COUNT SCOPE=FILE;

System altered.

gSQL> ALTER SYSTEM RESET PROCESS_MAX_COUNT SCOPE=SPFILE;

System altered.

gSQL> ALTER SYSTEM RESET PROCESS_MAX_COUNT;

System altered.

gSQL> ALTER SYSTEM UNSET PROCESS_MAX_COUNT;

System altered.

Compatibility

The SQL standard does not define the concept of a system property.

For More Information

Refer to ALTER SYSTEM SET property_name.

ALTER SYSTEM SET property_name

Function

It sets the value of the system property.

Syntax

<alter system set statement> ::=
    ALTER SYSTEM SET <property name> { = <property value> | TO DEFAULT }
        [ DEFERRED ]
        [ SCOPE = [ MEMORY | { FILE | SPFILE } | BOTH ] ]
    [AT <domain name>]
    ;

Invocation and Access Rules

The ALTER SYSTEM ON DATABASE privilege is required to execute the <alter system set statement>.

Syntax Rules and Parameters

<property name>

It is the name of the property to be set.
For more information, refer to the Server Property section in the database administration manual.

<property value>

It is the value of the property to be set.

TO DEFAULT

It sets the system property value to the initial value at the time the system is started.

[ DEFERRED ]

It defines the point in time when the altered property will be applied.

If the SYS_MODIFIABLE property value is IMMEDIATE, and DEFERRED is not explicitly specified, it is immediately applied to all sessions.

[ SCOPE = [ MEMORY | { FILE | SPFILE } | BOTH ] ]

It specifies the range affected by the system property changes.

If the SCOPE clause is omitted, the default value is SCOPE = MEMORY.
If the SYS_MODIFIABLE property value is FALSE, it must be specified as SCOPE=FILE/SPFILE.

<domain name>

It is the name of the member or group on which the statement is performed.
If not specified, the statement is performed on all groups.

Description

For more information, refer to the Server Property section in the database administration manual.

Examples

The following is an example of changing a property whose SYS_MODIFIABLE value is DEFERRED.

gSQL> ALTER SYSTEM SET HINT_ERROR = ON;

ERR-22000(13019): Invalid property modify mode.(HINT_ERROR)

gSQL> ALTER SYSTEM SET HINT_ERROR = ON DEFERRED;

System altered.

The following is an example of changing a property whose SYS_MODIFIABLE value is FALSE.

gSQL> ALTER SYSTEM SET PROCESS_MAX_COUNT=128;

ERR-22000(13018): Specified property cannot be modified with this SCOPE option.(PROCESS_MAX_COUNT)

gSQL> ALTER SYSTEM SET PROCESS_MAX_COUNT=128 SCOPE=FILE;

System altered.

The following is an example of changing the altered property back to its default value at the time the session was connected.

gSQL> ALTER SYSTEM SET TRANSACTION_COMMIT_WRITE_MODE=0;

System altered.

gSQL> ALTER SYSTEM SET TRANSACTION_COMMIT_WRITE_MODE TO DEFAULT;

System altered.

gSQL> ALTER SYSTEM SET TRANSACTION_COMMIT_WRITE_MODE TO DEFAULT DEFERRED;

System altered.

Compatibility

The SQL standard does not define the concept of a system property.

For More Information

Refer to ALTER SYSTEM RESET property_name.

ALTER SYSTEM SWITCH LOGFILE

Function

It alters the log files from the CURRENT state to the ACTIVE state in the database.

Syntax

<alter system switch logfile statement> ::=
    ALTER SYSTEM SWITCH LOGFILE
    [ AT <domain name> ]
    ;

Invocation and Access Rules

The ALTER SYSTEM ON DATABASE privilege is required to execute the <alter system switch logfile statement>.

Syntax Rules and Parameters

<alter system switch logfile statement>

The database must be in the MOUNT or OPEN phase.

<domain name>

It is the name of the member or group on which the statement is performed.
If not specified, the statement is performed on all groups.

Description

Normally, if the log file in the CURRENT state is filled, a log switch occurs automatically. This statement is used to forcibly execute a log switch under special circumstances.

Example

ALTER SYSTEM SWITCH LOGFILE;

Compatibility

The SQL standard does not define the concept of a LOGFILE.

For More Information

Refer to ALTER SYSTEM {MOUNT | OPEN} DATABASE.

ALTER TABLE

Function

It alters the table definition.

Syntax

<alter table statement> ::=
      <alter table physical attribute statement>
    | <rename table statement>
    | <add column definition>
    | <drop column definition>
    | <alter column definition>
    | <rename column statement>
    | <add table constraint definition>
    | <drop table constraint definition>
    | <alter table constraint definition>
    | <alter table drop offline segments statement>
    | <rename table constraint statement>
    | <add table supplemental log statement>
    | <drop table supplemental log statement>
    | <rebalance statement>
    | <alter table reorganize statement>
    | <move shard statement>
    | <merge shards statement>
    | <split shard statement>
    | <alter table synchronize statement>
    | <rename shard statement>
    | <read { only | write } statement>
    ;

Invocation and Access Rules

One of the following privileges is required to execute the <alter table statement>.

Syntax Rules and Parameters

<alter table physical attribute statement>

It alters the physical attributes of a table.
For more information, refer to ALTER TABLE name STORAGE.

<rename table statement>

It renames the table.
For more information, refer to ALTER TABLE name RENAME TO.

<add column definition>

It adds columns to the table.
For more information, refer to ALTER TABLE name ADD COLUMN.

<drop column definition>

It drops a column from the table.
For more information, refer to ALTER TABLE name SET UNUSED COLUMN.

<alter column definition>

It alters the column definition in the table.
For more information, refer to ALTER TABLE name ALTER COLUMN.

<rename column statement>

It renames the column in the table.
For more information, refer to ALTER TABLE name RENAME COLUMN.

<add table constraint definition>

It adds constraints to the table.
For more information, refer to ALTER TABLE name ADD CONSTRAINT.

<drop table constraint definition>

It drops the constraints of the table.
For more information, refer to ALTER TABLE name DROP CONSTRAINT.

<alter table constraint definition>

It alters the constraints of the table.
For more information, refer to ALTER TABLE name ALTER CONSTRAINT.

<alter table drop offline segments statement>

It drops the offline shards of the table. 
For more information, refer to ALTER TABLE name DROP OFFLINE SEGMENTS.

<rename table constraint statement>

It renames the constraints of the table.
For more information, refer to ALTER TABLE name RENAME CONSTRAINT.

<add table supplemental log statement>

It configures the system to add additional information to the redo log when the data in the table is altered.
For more information, refer to  ALTER TABLE name ADD SUPPLEMENTAL LOG.

<drop table supplemental log statement>

It configures the system not to add information to the redo log when the data in the table is altered.
For more information, refer to ALTER TABLE name DROP SUPPLEMENTAL LOG.

<rebalance statement>

In a cluster environment, it restores consistency by rebalancing the table's shard or synchronizing the corrupted shard.
For more information, refer to ALTER TABLE name REBALANCE.

<alter table reorganize statement>

It reorganizes the table physically.
For more information, refer to ALTER TABLE name REORGANIZE.

<alter table synchronize statement>

It restores consistency by synchronizing the already deployed offline shards in a cluster environment.
For more information, refer to ALTER TABLE name SYNCHRONIZE.

<move shard statement>

It rebalances a specific shard of a table within a particular cluster group in a cluster environment.
For more information, refer to ALTER TABLE name MOVE SHARD .

<merge shards statement>

It merges specific shards of a table in a cluster environment and then rebalances them.
For more information, refer to  ALTER TABLE name MERGE SHARDS.

<split shard statement>

It rebalances a specific shard of a table in a particular cluster group by splitting the shard in a cluster environment.
For more information, refer to ALTER TABLE name SPLIT SHARD.

<rename shard statement>

It renames a specific shard of a table in a cluster environment. 
For more information, refer to ALTER TABLE name RENAME SHARD.

<read { only | write } statement>

It sets the table to READ ( only | write }.
For more information, refer to ALTER TABLE name READ { ONLY | WRITE }.

Description

For more information, refer to the description of each detailed statement.

Example

Refer to the examples of each detailed statement.

Compatibility

The SQL standard does not define the following statements.

ALTER TABLE name ADD COLUMN

Function

It adds a column to the table.

Syntax

<add column definition> ::=
      ALTER TABLE table_name ADD [ COLUMN ] <column definition>
    | ALTER TABLE table_name ADD [ COLUMN ] ( <column definition> [, ...] )
    ;

Invocation and Access Rules

The user must meet the following conditions to execute the <add column definition>.

Syntax Rules and Parameters

table_name

It is the name of the table to be altered.
The schema to which the table belongs can be defined using the format schema_name.table_name. If schema_name is omitted, the default schema name of the user executing the statement is used.

ADD [ COLUMN ]

The reserved word COLUMN can be omitted.

<column definition>

It defines the column to be added. For more information, refer to the <column definition> clause of the CREATE TABLE statement.
Column names in a table must be unique.
When the DEFAULT clause is specified while defining the column, the default value is stored in the added column for all rows.
When  the <identity column specification> clause is specified while defining the column, an automatically generated value is stored in the added column for each row.
When the NOT NULL constraint is specified while defining the column, the table must be empty, or the DEFAULT clause or <identity column specification> clause must be specified along with it.

( <column definition> [, ...] )

It adds multiple columns.
Multiple <column definitions> are listed inside the parentheses.

Description

The added column is positioned at the end of the existing columns.
When specifying the DEFAULT or <identity column specification> clause, the processing time increases in proportion to the number of rows in the table.

Examples

The following is an example of adding a column.

gSQL> ALTER TABLE region ADD COLUMN r_new_comment VARCHAR(152);

Table altered.

The following is an example of adding multiple columns.

gSQL> ALTER TABLE partsupp ADD COLUMN ( 
   ps_retailprice NUMERIC(12,2), 
   ps_acctbal NUMERIC(12,2), ps_mktsegment  CHAR(10) );

Table altered.

The following is an example of adding an identity column and a column with the DEFAULT clause.

gSQL> ALTER TABLE region ADD COLUMN ( 
    r_regionkey INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    r_comment   VARCHAR(152) DEFAULT 'N/A' );

Table altered.

gSQL> SELECT r_regionkey, r_name, r_comment FROM region;

R_REGIONKEY R_NAME                    R_COMMENT
----------- ------------------------- ---------
          1 AFRICA                    N/A      
          2 AMERICA                   N/A      
          3 ASIA                      N/A      
          4 EUROPE                    N/A      
          5 MIDDLE EAST               N/A      

5 rows selected.

The following is an example of adding a column with a deferrable constraint.

gSQL> ALTER TABLE t1 ADD COLUMN ( id INTEGER CONSTRAINT t1_uk UNIQUE DEFERRABLE );

Table altered.

gSQL> COMMIT;

Commit complete.

Compatibility

The SQL standard does not define the addition of multiple column definitions.

For More Information

Refer to the following.

ALTER TABLE name ADD CONSTRAINT

Function

It adds a table constraint.

Syntax

<add table constraint definition> ::=
    ALTER TABLE table_name 
        ADD <table constraint definition>
    ;

Invocation and Access Rules

The user must meet the following conditions to execute the <add table constraint definition> clause.

Constraints for PRIMARY KEY and UNIQUE in a cluster system must include all sharding keys.

Syntax Rules and Parameters

table_name

It is the name of the table to be altered.
The schema to which the table belongs can be defined using the format schema_name.table_name. If schema_name is omitted, the default schema name of the user executing the statement is used.

<table constraint definition>

It defines the constraint to be added.
The NOT NULL constraint can not be added using the ALTER TABLE .. ADD CONSTRAINT statement. Instead, it can be defined using the ALTER TABLE name ALTER COLUMN statement as follows.
ALTER TABLE t1 ALTER COLUMN c1 SET NOT NULL;

For more information, refer to the <table constraint definition> clause of the CREATE TABLE statement.

Description

When adding key constraints, such as a primary key or unique key, an index is automatically created for them.

Examples

The following is an example of adding a primary key constraint to a table.

gSQL> ALTER TABLE t1 ADD PRIMARY KEY ( id );

Table altered.

The following is an example of specifying a constraint name when adding a primary key constraint to a table.

gSQL> ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY ( id );

Table altered.

The following is an example of adding a DEFERRABLE constraint.

gSQL> ALTER TABLE t1 ADD CONSTRAINT t1_uk UNIQUE ( id ) DEFERRABLE INITIALLY DEFERRED;

Table altered.

gSQL> COMMIT;

Commit complete.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

F381

Extended schema manipulation

O

For More Information

Refer to the following.

ALTER TABLE name ADD GLOBAL SECONDARY INDEX

Function

It creates a global secondary index on the table.

Syntax

<alter table add global secondary index definition> ::=
    ALTER TABLE table_name 
        ADD GLOBAL SECONDARY INDEX
        [ <index attributes> [...] ] [ TABLESPACE tablespace_name ]
    ;

<index attributes> ::=
      <physical attribute clause>
    | STORAGE ( <segment attr clause> [...] )
    | <parallel clause> 

<physical attribute clause> ::=
      PCTFREE integer
    | INITRANS integer
    | MAXTRANS integer

<segment attr clause> ::=
      INITIAL <size_clause>
    | NEXT <size_clause>

<size clause> ::=
      integer [ K | M | G | T ]

<parallel clause> ::=
      NOPARALLEL
    | PARALLEL [ integer ]

Invocation and Access Rules

The <alter table add global secondary index definition> can be defined in a cluster system, and the user must satisfy the following conditions.

Syntax Rules and Parameters

table_name

It is the name of a table in which the index is to be created.
The schema to which the table belongs can be defined using the format schema_name.table_name.
If schema_name is omitted, the default schema name of the user executing the statement is used.

<physical attribute clause>

It defines the physical attribute information of the index.

<segment attr clause>

It specifies the information about the storage space where the index will be stored.

<size clause>

It specifies the file size in bytes. (If omitted, bytes are used by default.)

NOPARALLEL | PARALLEL [ integer ]

It specifies the number of threads to be used when building the index.

TABLESPACE tablespace_name

It specifies the name of the tablespace where the index will be stored.

Description

A non-deterministic query requires a global secondary index. The LOGGING and NOLOGGING indexes have the following trade-offs.

Examples

The following is an example of adding a global secondary index to table T1.

gSQL> ALTER TABLE T1 ADD GLOBAL SECONDARY INDEX;

Table altered.

gSQL> COMMIT;

Commit complete.
The following is an example of creating a global secondary index as a logging index on the tablespace USER_DATA_TBS of table T1.
gSQL> ALTER TABLE T1 ADD GLOBAL SECONDARY INDEX LOGGING TABLESPACE USER_DATA_TBS;

Table altered.

gSQL> COMMIT;

Commit complete.

The following is an example of creating a global secondary index as a nologging index on the tablespace USER_TEMP_TBS of table T1.

gSQL> ALTER TABLE T1 ADD GLOBAL SECONDARY INDEX NOLOGGING TABLESPACE USER_TEMP_TBS;

Table altered.

gSQL> COMMIT;

Commit complete.

Compatibility

The SQL standard does not define the concept of a global secondary index.

For More Information

Refer to the following.

ALTER TABLE name ADD SUPPLEMENTAL LOG

Function

When the table data is altered, if a primary key exists in the table, it is configured to add the primary key value to the redo log.

Syntax

<add table supplemental log statement> ::=
    ALTER TABLE table_name 
        ADD SUPPLEMENTAL LOG DATA ( PRIMARY KEY ) COLUMNS
    ;

Invocation and Access Rules

One of the following privileges is required to execute the <add table supplemental log statement>.

Syntax Rules and Parameters

table_name

It is the name of the table to be altered.
The schema to which the table belongs can be defined using the format schema_name.table_name. If schema_name is omitted, the default schema name of the user executing the statement is used.
The statement can be executed even if the primary key does not exist in the table.

Description

It also records a SUPPLEMENTAL LOG when performing UPDATE/DELETE operations on the corresponding TABLE. The recorded SUPPLEMENTAL LOG is used for log analysis or with tools like CDC.
To record the SUPPLEMENTAL LOG for every TABLEs, set SUPPLEMENTAL_LOG_DATA_PRIMARY_KEY = YES.

Example

The following is an example of how to configure the system to add the primary key value to the redo log when modifying data in the table.

gSQL> ALTER TABLE t1 ADD SUPPLEMENTAL LOG DATA ( PRIMARY KEY ) COLUMNS;

Table altered.

Compatibility

The SQL standard does not define the concept of the <add table supplemental log statement>.

For More Information

Refer to ALTER TABLE name DROP SUPPLEMENTAL LOG.

ALTER TABLE name ALTER COLUMN

Function

It alters the column definition.

Syntax

<alter column definition> ::=
    ALTER TABLE table_name 
        ALTER [ COLUMN ] column_name <alter column action>

<alter column action> ::=

      <set column default clause>
    | <drop column default clause>
    | <set column not null clause>
    | <drop column not null clause>
    | <alter column data type clause>
    | <alter identity column specification>
    | <drop identity property clause>    
    ;

<set column default clause> ::=
    SET DEFAULT <default option>

<drop column default clause> ::=
    DROP DEFAULT

<set column not null clause> ::=
    SET [ CONSTRAINT constraint_name ] NOT NULL [ <constraint characteristics> ]


<constraint characteristics> ::=
      [ NOT ] DEFERRABLE [ <constraint check time> ] [ <constraint enforcement> ]
    | <constraint check time> [ [ NOT ] DEFERRABLE ] [ <constraint enforcement> ]
    | [ <constraint enforcement> ]

<constraint check time> ::=
      INITIALLY DEFERRED 
    | INITIALLY IMMEDIATE

<constraint enforcement> ::=
    [NOT] ENFORCED

<drop column not null clause> ::=
    DROP NOT NULL

<alter column data type clause> ::=
    SET DATA TYPE <data type> 

<alter identity column specification> ::=
     <set identity column generation clause> [ <alter identity column option> ... ]
   | <alter identity column option> ...

<set identity column generation clause> ::=
   SET GENERATED { ALWAYS | BY DEFAULT }

<alter identity column option> ::=
     <alter sequence generator restart option>
   | [ SET ] <basic sequence generator option>

<alter sequence generator restart option> ::=
    RESTART [ WITH integer ]

<basic sequence generator option> ::=
      <sequence generator increment by option>
    | <sequence generator maxvalue option>
    | <sequence generator minvalue option>
    | <sequence generator cycle option>
    | <sequence generator cache option>

<drop identity property clause> ::=
    DROP IDENTITY

Invocation and Access Rules

One of the following privileges is required to execute the <alter column definition>.

Syntax Rules and Parameters

table_name

It is the name of the table to be altered. 
The schema to which the table belongs can be defined using the format schema_name.table_name. If schema_name is omitted, the default schema name of the user executing the statement is used.

ALTER [ COLUMN ]

The reserved word COLUMN can be omitted.

column_name

It is the name of the column to be altered.

<set column default clause>

It sets the default value for the column.
The column must not be an identity column
If the DEFAULT clause is used in subsequent INSERT statement, the set default value will be applied.
The data type of the DEFAULT expression must be compatible with the column's data type.
If they are incompatible or the expression is invalid, an error will occur.
For more information, refer to the <default clause> in the CREATE TABLE statement.

<drop column default clause>

It drops the default value of the column.
The column must not be an identity column.
If the default value is dropped, NULL will be set when the DEFAULT clause is used in INSERT statement.

<set column not null clause>

For more information about the DEFERRABLE constraint, refer to the SET CONSTRAINTS.

<drop column not null clause>

<alter column data type clause>

The SET DATA TYPE is a DDL statement that is automatically committed.

Type conversion can be performed within the same family, and it must meet the following conditions.

Conversion of character string types

from \ to

CHAR(n)

VARHCAR(n)

LONG VARCHAR

CHAR(m)

X

X

X

VARCHAR(m)

X

n >= m

X

LONG VARCHAR

X

X

O

The conversion of the char length unit must satisfy the following condition.

Conversion of the character length unit

from \ to

OCTETS

CHARACTERS

OCTETS

O

O

CHARACTERS

X

O

Conversion of binary string type

from \ to

BINARY(n)

VARBINARY(n)

LONG VARBINARY

BINARY(m)

X

X

X

VARBINARY(m)

X

n >= m

X

LONG VARBINARY

X

X

O

Conversion of numeric type

from \ to

SMALLINT

INTEGER

BIGINT

NUMERIC

NUMERIC(q)

NUMERIC(q,t)

NUMBER(q)

NUMBER(q,t)

REAL

DOUBLE PRECISION

FLOAT

FLOAT(q)

NUMBER

SMALLINT

O

O

O

O

q >= 5

q >= 5

t >= 0

(q-t) >= 5

q >= 5

q >= 5

t >= 0

(q-t) >= 5

O

O

O

ddc(q) >= 5

O

INTEGER

X

O

O

O

q >= 10

q >= 10

t >= 0

(q-t) >= 10

q >= 10

q >= 10

t >= 0

(q-t) >= 10

X

O

O

ddc(q) >= 10

O

BIGINT

X

X

O

O

q >= 19

q >= 19

t >= 0

(q-t) >= 19

q >= 19

q >= 19

t >= 0

(q-t) >= 19

X

X

O

ddc(q) >= 19

O

NUMERIC

X

X

X

O

q == 38

q == 38

t == 0

q == 38

q == 38

t == 0

X

X

O

ddc(q) == 38

O

NUMERIC(p)

5 >= p

10 >= p

19 >= p

O

q >= p

q >= p

t >= 0

(q-t) >= p

q >= p

q >= p

t >= 0

(q-t) >= p

8 >= p

16 >= p

O

ddc(q) >= p

O

NUMERIC(p,s)

5 >= p

0 >= s

5 >= (p-s)

10 >= p

0 >= s

10 >= (p-s)

19 >= p

0 >= s 19 >= (p-s)

38 >= p

0 >= s

38 >= (p-s)

q >= p

0 >= s

q >= (p-s)

q >= p

t >= s

(q-t) >= (p-s)

q >= p

0 >= s

q >= (p-s)

q >= p

t >= s

(q-t) >= (p-s)

8 >= p

16 >= p

O

ddc(q) >= p

O

NUMBER(p)

5 >= p

10 >= p

19 >= p

O

q >= p

q >= p

t >= 0

(q-t) >= p

q >= p

q >= p

t >= 0 (q-t) >= p

8 >= p

16 >= p

O

ddc(q) >= p

O

NUMBER(p,s)

5 >= p

0 >= s

5 >= (p-s)

10 >= p 0 >= s 10 >= (p-s)

19 >= p

0 >= s

19 >= (p-s)

38 >= p

0 >= s

3

8 >= (p-s)

q >= p

0 >= s

q >= (p-s)

q >= p

t >= s

(q-t) >= (p-s)

q >= p

0 >= s

q >= (p-s)

q >= p

t >= s (q-t) >= (p-s)

8 >= p

16 >= p

O

ddc(q) >= p

O

REAL

X

X

X

X

X

X

X

X

O

O

O

ddc(q) >= 8

O

DOUBLE PRECISION

X

X

X

X

X

X

X

X

X

O

O

ddc(q) >= 16

O

FLOAT

X

X

X

X

X

X

X

X

X

X

O

ddc(q) == 38

O

FLOAT(p)

X

X

X

X

X

X

X

X

8 >= ddc(p)

16 >= ddc(p)

O

ddc(q) >= ddc(p)

O

NUMBER

X

X

X

X

X

X

X

X

X

-

O

ddc(q) == 38

O

The decimal digit count (ddc) value for FLOAT (p) is as follows.

Decimal digit count (ddc) value

FLOAT(p)

ddc(p)

1 ~ 3

1

4 ~ 6

2

7 ~ 9

3

10 ~ 13

4

14 ~ 16

5

17 ~ 19

6

20 ~ 23

7

24 ~ 26

8

27 ~ 29

9

30 ~ 33

10

34 ~ 36

11

37 ~ 39

12

40 ~ 43

13

44 ~ 46

14

47 ~ 49

15

50 ~ 53

16

54 ~ 56

17

57 ~ 59

18

60 ~ 63

19

64 ~ 66

20

67 ~ 69

21

70 ~ 73

22

74 ~ 76

23

77 ~ 79

24

80 ~ 83

25

84 ~ 86

26

87 ~ 89

27

90 ~ 93

28

94 ~ 96

29

97 ~ 99

30

100 ~ 103

31

104 ~ 106

32

107 ~ 109

33

110 ~ 113

34

114 ~ 116

35

117 ~ 119

36

120 ~ 123

37

124 ~ 126

38

All numeric types are managed using the same structure, and each numeric type is equivalent to the following NUMBER(p, s) expression.

NUMBER expressions of numeric types

Numeric type

NUMBER(p,s) expression

SMALLINT

NUMBER(5,0)

INTEGER

NUMBER(10,0)

BIGINT

NUMBER(19,0)

NUMERIC

NUMBER(38,0)

NUMERIC(p)

NUMBER(p,0)

NUMERIC(p,s)

NUMBER(p,s)

NUMBER(p)

NUMBER(p,0)

NUMBER(p,s)

NUMBER(p,s)

REAL

NUMBER(8,N/A) <= FLOAT(24)

DOUBLE PRECISION

NUMBER(16,N/A) <= FLOAT(53)

FLOAT

NUMBER(38,N/A) <= FLOAT(126)

FLOAT(p)

NUMBER( ddc(p), N/A )

NUMBER

NUMBER(38, N/A )

The native numeric type is the same as the numeric type in C language and cannot be converted to a different type.

Native numeric type conversion

from \ to

NATIVE_SMALLINT

NATIVE_INTEGER

NATIVE_BIGINT

NATIVE_REAL

NATIVE_DOUBLE

NATIVE_SMALLINT

O

X

X

X

X

NATIVE_INTEGER

X

O

X

X

X

NATIVE_BIGINT

X

X

O

X

X

NATIVE_REAL

X

X

X

O

X

NATIVE_DOUBLE

X

X

X

X

O

Boolean type conversion

from \ to

BOOLEAN

BOOLEAN

O

Conversion of date/time type (TZ: WITH TIME ZONE)

from \ to

DATE

TIME

TIME(g)

TIME TZ

TIME(g) TZ

TIMESTAMP

TIMESTAMP(g)

TIMESTAMP TZ

TIMESTAMP(g) TZ

DATE

O

X

X

X

X

X

X

X

X

TIME

X

O

g >= 6

X

X

X

X

X

X

TIME(f)

X

6 >= f

g >= f

X

X

X

X

X

X

TIME TZ

X

X

X

O

g >= 6

X

X

X

X

TIME(f) TZ

X

X

X

6 >= f

g >= f

X

X

X

X

TIMESTAMP

X

X

X

X

X

O

g >= 6

X

X

TIMESTAMP(f)

X

X

X

X

X

6 >= f

g >= f

X

X

TIMESTAMP TZ

X

X

X

X

X

X

X

O

g >= 6

TIMESTAMP(f) TZ

X

X

X

X

X

X

X

6 >= f

g >= f

Type conversion of the INTERVAL YEAR TO MONTH family (If p,q are omitted, the default value is 2.)

from \ to

YEAR(q)

MONTH(q)

YEAR(q) TO MONTH

YEAR(p)

q >= p

X

X

MONTH(p)

X

q >= p

X

YEAR(p) TO MONTH

X

X

q >= p

Type conversion of the INTERVAL DAY TO TIME family (If p,q are omitted, the default value is 2.) (If f,g are omitted, the default value is 6.)

from \ to

DAY(q)

HOUR(q)

MINUTE(q)

SECOND(q,g)

DAY(q) TO HOUR

DAY(q) TO MINUTE

DAY(q) TO SECOND(g)

HOUR(q) TO MINUTE

HOUR(q) TO SECOND(g)

MINUTE(q) TO SECOND(g)

DAY(p)

q >= p

X

X

X

X

X

X

X

X

X

HOUR(p)

X

q >= p

X

X

X

X

X

X

X

X

MINUTE(p)

X

X

q >= p

X

X

X

X

X

X

X

SECOND(p,f)

X

X

X

q >= p

g >= f

X

X

X

X

X

X

DAY(p) TO HOUR

X

X

X

X

q >= p

X

X

X

X

X

DAY(p) TO MINUTE

X

X

X

X

X

q >= p

X

X

X

X

DAY(p) TO SECOND(f)

X

X

X

X

X

X

q >= p

g >= f

X

X

X

HOUR(p) TO MINUTE

X

X

X

X

X

X

X

q >= p

X

X

HOUR(p) TO SECOND(f)

X

X

X

X

X

X

X

X

q >= p

g >= f

X

MINUTE(p) TO SECOND(f)

X

X

X

X

X

X

X

X

X

q >= p

g >= f

ROWID type conversion

from \ to

ROWID

ROWID

O

<alter identity column specification>

It alters the identity property of the column.
The column must be an identity column.

<drop identity property clause>

It drops the identity property of the column.
The column must be an identity column.

Description

The time taken to perform the null check in the SET NOT NULL clause is proportional to the number of rows in the table.
The following columns do not allow NULL values. In other words, even if the DROP NOT NULL clause is executed, NULL values are not permitted in these cases.
Changes to the default value using the SET DEFAULT clause and changes to the identity property using the <alter identity column specification> clause are applied to subsequent INSERT or UPDATE statements.

Examples

The following is an example of setting the DEFAULT property for the column.

gSQL> ALTER TABLE region ALTER COLUMN r_comment SET DEFAULT 'N/A';

Table altered.

The following is an example of dropping the DEFAULT property from the column.

gSQL> ALTER TABLE region ALTER COLUMN r_comment DROP DEFAULT;

Table altered.

The following is an example of setting a NOT NULL constraint on the column.

gSQL> ALTER TABLE region ALTER COLUMN r_regionkey SET NOT NULL;

Table altered.

The following is an example of dropping the NOT NULL constraint from the column.

gSQL> ALTER TABLE region ALTER COLUMN r_regionkey DROP NOT NULL;

Table altered.

The following is an example of extending the size of the column's data type.

gSQL> ALTER TABLE region ALTER COLUMN r_comment SET DATA TYPE VARCHAR(512);

Table altered.

The following is an example of restarting the next value of the identity column.

gSQL> ALTER TABLE region ALTER COLUMN r_regionkey RESTART;

Table altered.

The following is an example of dropping the identity property from the column.

gSQL> ALTER TABLE region ALTER COLUMN r_regionkey DROP IDENTITY;

Table altered.

Compatibility

The SQL satndards compatibility

Feature ID

Description

Compatibility

F381

Extended schema manipulation

X

F382

Alter column data type

O

F383

Set column not null clause

O

F384

Drop identity property value

O

F385

Drop column generation expression clause

X

F386

Set identity column generation clause

O

S043

Enhanced reference types

X

T174

Identity columns

O

T178

Identity columns: simple restart option

O

For More Information

Refer to the following.

ALTER TABLE name ALTER CONSTRAINT

Function

It alters the characteristics of the table constraint.

Syntax

<alter table constraint definition> ::=
    ALTER TABLE table_name 
        ALTER <constraint object> <constraint characteristics>
    ;

<constraint object> ::=
      CONSTRAINT constraint_name
    | PRIMARY KEY 
    | UNIQUE ( column_name [, ...] ) 

<constraint characteristics> ::=
      [ NOT ] DEFERRABLE [ <constraint check time> ] [ <constraint enforcement> ]
    | <constraint check time> [ [ NOT ] DEFERRABLE ] [ <constraint enforcement> ]
    | <constraint enforcement>

<constraint check time> ::=
      INITIALLY DEFERRED 
    | INITIALLY IMMEDIATE

<constraint enforcement> ::=
    [NOT] ENFORCED

Invocation and Access Rules

One of the following privileges is required to execute the <alter table constraint definition>.

Cluster does not support deferrable constraints.

Syntax Rules and Parameters

table_name

It is the name of the table to be altered. 
The schema to which the table belongs can be defined using the format schema_name.table_name. If schema_name is omitted, the default schema name of the user executing the statement is used.

<constraint object>

The constraint to be altered is specified as follows.

DEFERRABLE | NOT DEFERRABLE

It alters whether the constraint state is deferrable.

For more information about deferrable constraints, refer to SET CONSTRAINTS.

INITIALLY IMMEDIATE | INITIALLY DEFERRED

It alters the initial value of the check point for the constraint.

Constraints defined as NOT DEFERRABLE can not be altered to INITIALLY DEFERRED.

[NOT] ENFORCED

It enables or disables the constraint.

Description

For more information about deferrable constraints, refer to the SET CONSTRAINTS statement.

Example

The following is an example where the constraint t1_uk is set as deferrable and its checking time is set to DEFERRED.

gSQL> ALTER TABLE t1 ALTER CONSTRAINT t1_uk DEFERRABLE INITIALLY DEFERRED;

Table altered.

gSQL> COMMIT;

Commit complete.

Compatibility

The SQL standard does not define the following clauses.

SQL standard compatibility

Feature ID

Description

Compatibility

F492

Optional table constraint enforcement

O

ALTER TABLE name ALTER GLOBAL SECONDARY INDEX

Function

It alters the physical attributes of the global secondary index on the table.

Syntax

<alter table alter global secondary index storage statement> ::=
    ALTER TABLE table_name ALTER GLOBAL SECONDARY INDEX
      <physical attribute clause>
    | [ STORAGE ( <segment attr clause> [...] ) ]
    ;

<physical attribute clause> ::=
      PCTFREE integer
    | INITRANS integer
    | MAXTRANS integer

<segment attr clause> ::=
      INITIAL <size_clause>
    | NEXT <size_clause>

<size clause> ::=
      integer [ K | M | G | T ]

Invocation and Access Rules

One of the following privileges is required to execute the <alter table alter global secondary index storage statement>.

Syntax Rules and Parameters

table_name

It is the name of the table where the index is to be created.
The schema to which the table belongs can be defined using the format schema_name.table_name. If schema_name is omitted, the default schema name of the user executing the statement is used.

<physical attribute clause>

It defines the physical attribute information of the index.

<segment attr clause>

It specifies the information for the index storage space.

<size clause>

It specifies the file size in bytes. (If omitted, bytes are used by default.)

Description

A global secondary index is required to query non-deterministic query.

Examples

It alters the INITRANS and MAXTRANS values to 2 and 4, respectively, for use by the global secondary index on table T1.

gSQL> ALTER TABLE T1 ALTER GLOBAL SECONDARY INDEX INITRANS 2 MAXTRANS 4;

Table altered.

gSQL> COMMIT;

Commit complete.

Compatibility

The SQL standard does not define the concept of a global secondary index.

For More Information

Refer to the following.

ALTER TABLE name ALTER GLOBAL SECONDARY INDEX AGING

Function

It deletes empty pages from the global secondary index.
It can be performed concurrently with DML.

Syntax

<global secondary index aging statement> ::=
    ALTER TABLE table_name ALTER GLOBAL SECONDARY INDEX AGING
        [ AT <domain name> ]
    ;

Invocation and Access Rules

One of the following privileges is required to execute the <global secondary index aging statement> .

Syntax Rules and Parameters

table_name

It is the name of the target table.
The schema to which the table belongs can be defined using the format schema_name.table_name. If schema_name is omitted, the default schema name of the user executing the statement is used.

<domain name>

It is the name of the member or group on which the statement is performed.
If not specified, the statement is performed on all groups.

Description

This statement returns index pages whose all keys have been deleted to the segment.
Aging is performed in two phases: logical deletion and physical deletion.

If the system's agable SCN does not increase, empty pages may not be deleted even if the INDEX AGING statement succeeds.

Example

The following is an example of performing aging on a global secondary index.

gSQL> select table_name, empty_blocks from user_global_secondary_indexes where table_name = 'T1';

TABLE_NAME EMPTY_BLOCKS
---------- ------------
T1                    2

1 row selected.

gSQL> alter table t1 alter global secondary index aging;

Table altered.

gSQL> select table_name, empty_blocks from user_global_secondary_indexes where table_name = 'T1';

TABLE_NAME EMPTY_BLOCKS
---------- ------------
T1                    0

1 row selected.

Compatibility

The SQL standard does not define the concept of a global secondary index.

For More Information

Refer to ALTER TABLE name ALTER GLOBAL SECONDARY INDEX REBUILD.

ALTER TABLE name ALTER GLOBAL SECONDARY INDEX COALESCE

Function

It drops the fragmentation of the global secondary index.

Syntax

<global secondary index coalesce statement> ::=
    ALTER TABLE table_name ALTER GLOBAL SECONDARY INDEX COALESCE
        [ AT <domain name> ]
    ;

Invocation and Access Rules

The user must meet the following conditions to execute the <global secondary index coalesce.

Syntax Rules and Parameters

table_name

It is the name of the target table.
The schema to which the table belongs can be defined using the format schema_name.table_name. If schema_name is omitted, the default schema name of the user executing the statement is used.

<domain name>

It is the name of the member or group on which the statement is performed.
If not specified, the statement is performed on all groups.

Description

Comparison with INDEX REBUILD


INDEX REBUILD

INDEX COALESCE

Altering index attributes

Possible

Impossible

Moving tablespace

Possible

Impossible

Locking table

Required

Not required

Additional space for execution

Required

Not required

Decreasing tree height

Possible

Impossible

Examples

It drops the fragmentation of the global secondary index on table T1.

gSQL> ALTER TABLE T1 ALTER GLOBAL SECONDARY INDEX COALESCE;

Table altered.

Compatibility

The SQL standard does not define the concept of a global secondary index.

For More Information

Refer to ALTER TABLE name ALTER GLOBAL SECONDARY INDEX REBUILD.

ALTER TABLE name ALTER GLOBAL SECONDARY INDEX REBUILD

Function

It rebuilds the global secondary index

Syntax

<global secondary index rebuild statement> ::=
    ALTER TABLE table_name ALTER GLOBAL SECONDARY INDEX REBUILD
        [ ONLINE | OFFLINE ]
        [ <index attributes> [...] ]
        [ TABLESPACE tablespace_name ]
        [ AT <domain name> ]
    ;

<index attributes> ::=
      <physical attribute clause>
    | STORAGE ( <segment attr clause> [...] )
    | <parallel clause> 

<physical attribute clause> ::=
      PCTFREE integer
    | INITRANS integer
    | MAXTRANS integer

<segment attr clause> ::=
      INITIAL <size_clause>
    | NEXT <size_clause>

<size clause> ::=
      integer [ K | M | G | T ]

<parallel clause> ::=
      NOPARALLEL
    | PARALLEL [ integer ]

Invocation and Access Rules

The user must meet the following conditions to execute the <global secondary index rebuild statement>.

Syntax Rules and Parameters

table_name

It is the name of the table where the index is to be rebuilt. 
The schema to which the table belongs can be defined using the format schema_name.table_name. If schema_name is omitted, the default schema name of the user executing the statement is used.

[ ONLINE | OFFLINE ]

It determines whether DML operations are allowed on the table while rebuilding the index.

<physical attribute clause>

It defines the physical attribute information of an index.

<segment attr clause>

It specifies the information for the index storage space.

<size clause>

It specifies the file size in bytes. (If omitted, bytes are used by default.)

NOPARALLEL | PARALLEL [ integer ]

It specifies the number of threads to be used for rebuilding the index.

TABLESPACE tablespace_name

It specifies the name of the tablespace where the index is to be rebuilt.

<domain name>

It is the name of the member or group on which the statement is performed.
If not specified, the statement is performed on all groups.
It cannot be used with <index attributes>.

Description

Examples

Rebuild the global secondary index on table T1.

gSQL> ALTER TABLE T1 ALTER GLOBAL SECONDARY INDEX REBUILD;

Alter the tablespace and logging settings of the global secondary index on table T1.

gSQL> ALTER TABLE T1 ALTER GLOBAL SECONDARY INDEX REBUILD TABLESPACE MEM_DATA_TBS;

gSQL> ALTER TABLE T1 ALTER GLOBAL SECONDARY INDEX REBUILD TABLESPACE MEM_TEMP_TBS;

Compatibility

The SQL standard does not define the concept of a global secondary index.

For More Information

Refer to the following.

ALTER TABLE name DROP CONSTRAINT

Function

It drops a table constraint.

Syntax

<drop table constraint definition> ::=
    ALTER TABLE table_name 
        DROP <constraint object>
        [ <drop behavior> ]
    ;

<constraint object> ::=
      CONSTRAINT constraint_name
    | PRIMARY KEY 
    | UNIQUE ( column_name [, ...] ) 

<drop behavior> ::=
      RESTRICT
    | CASCADE
    | CASCADE CONSTRAINTS

Invocation and Access Rules

One of the following privileges is required to execute the <drop table constraint definition>.

Syntax Rules and Parameters

table_name

It is the name of the table to be altered. 
The schema to which the table belongs can be defined using the format schema_name.table_name. If schema_name is omitted, the default schema name of the user executing the statement is used.

CONSTRAINT constraint_name

It is the name of the constraint to be dropped.

PRIMARY KEY

It is the primary key constraint on the table.

UNIQUE( column_name [, ...] )

It is the unique constraint on the columns.

<drop behavior>

When omitted, the default value is RESTRICT.

CASCADE and CASCADE CONSTRAINTS have the same meaning.

If the constraint to be dropped is a PRIMARY KEY or UNIQUE constraint and it is referenced by a FOREIGN KEY, the CASCADE or CASCADE CONSTRAINTS option must be specified.

Description

The <drop column not null clause> of the ALTER TABLE name ALTER COLUMN is used to drop the NOT NULL constraint without specifying the constraint name.

A FOREIGN KEY must be dropped by specifying its constraint name.

CREATE TABLE parent ( pk INTEGER PRIMARY KEY );
CREATE TABLE child ( fk INTEGER REFERENCES parent(pk) );
COMMIT;

gSQL> ALTER TABLE child DROP FOREIGN KEY;
ERR-42000(40000): syntax error: 
ALTER TABLE child DROP FOREIGN KEY
                       ^     ^
Error at line 1

gSQL>
SELECT constraint_name
  FROM information_schema.referential_constraints
 WHERE constraint_table_name = 'CHILD'
;

CONSTRAINT_NAME                          
-----------------------------------------
CHILD_FOREIGN_KEY_FK_REFERENCES_PARENT_PK
1 row selected.

gSQL> ALTER TABLE child DROP CONSTRAINT CHILD_FOREIGN_KEY_FK_REFERENCES_PARENT_PK;
Table altered.

Examples

The following is an example of dropping the primary key constraint from the table.

gSQL> ALTER TABLE t1 DROP PRIMARY KEY;

Table altered.

The following is an example of dropping a table constraint by specifying its constraint name.

gSQL> ALTER TABLE t1 DROP CONSTRAINT t1_pk;

Table altered.

Compatibility

The SQL standard does not define the following clauses.

SQL standard compatibility

Feature ID

Description

Compatibility

F381

Extended schema manipulation

O

For More Information

Refer to the following.

ALTER TABLE name DROP GLOBAL SECONDARY INDEX

Function

It drops a global secondary index from the table.

Syntax

<alter table drop global secondary index definition> ::=
    ALTER TABLE table_name 
        DROP GLOBAL SECONDARY INDEX
    ;

Invocation and Access Rules

The <alter table drop global secondary index definition> statement can be defined in a cluster system, and the user must meet the following conditions.

Syntax Rules and Parameters

table_name

It is the name of the table from which the index is to be dropped.

Description

A global secondary index is required to query a non-deterministic query.

Examples

It drops the global secondary index from table T1.

gSQL> ALTER TABLE T1 DROP GLOBAL SECONDARY INDEX;

Table altered.

gSQL> COMMIT;

Commit complete.

Compatibility

The SQL standard does not define the concept of a global secondary index.

For More Information

Refer to the following.

ALTER TABLE name DROP OFFLINE SEGMENTS

Function

It drops the segments of offline shards.

Syntax

<alter table drop offline segments statement> ::=
    ALTER TABLE table_name 
        DROP OFFLINE SEGMENTS    
;

Invocation and Access Rules

It can be performed in a cluster system.

One of the following privileges is required to execute the <alter table drop offline segments statement>.

Syntax Rules and Parameters

table_name

It is the name of the table.
The schema to which the table belongs can be defined using the format schema_name.table_name. If schema_name is omitted, the default schema name of the user executing the statement is used.

Description

It drops the segments of offline shards.

The <alter table drop offline segments statement> can be performed even if an inactive cluster member exists.

If the following conditions are not met, the operation will fail.

For example, if all replicas in cluster group G3 of the sharded table t1 are offline, the following error will occur.

gSQL> ALTER TABLE t1 DROP OFFLINE SEGMENTS;

ERR-42000(16361): sharded table "PUBLIC"."T1" must have at least one usable replica of group 'G3'

Use the <alter database drop offline segments statement> to perform this operation for all tables.

Example

The following is an example of performing the <alter table drop offline segments statement> on table T1.

gSQL> ALTER TABLE t1 DROP OFFLINE SEGMENTS;

Table altered.

Compatibility

The SQL standard does not define the concept of a cluster.

For More Information

Refer to ALTER DATABASE DROP OFFLINE SEGMENTS.

ALTER TABLE name DROP UNUSABLE SEGMENTS

Function

Unusable segments are dropped from the segments of offline replicas.

Syntax

<alter table drop unusable segments statement> ::=
    ALTER TABLE table_name 
        DROP UNUSABLE SEGMENTS    
;

Invocation and Access Rules

It can be performed within the cluster system.

One of the following privileges is required to execute <alter table drop unusable segments statement>.

Syntax Rules and Parameters

table_name

It is the name of the table. 
The schema to which the table belongs can be defined using the format schema_name.table_name. If schema_name is omitted, the default schema name of the user executing the statement is used.

Description

An unusable segment refers to a segment that can no longer be used, and it can be created in the following situations:

This statement can be executed even if there are inactive cluster members.

Unlike the <alter table drop offline segments statement>, it can be executed even when no replicas are in the online state.

To perform the same operation on all tables, use the <alter database drop offline segments statement>.

Example

The following is an example of performing the <alter table drop unusable segments statement> on table T1.

gSQL> ALTER TABLE t1 DROP UNUSABLE SEGMENTS;

Table altered.

Compatibility

The SQL standard does not define the concept of an unusable segment.

For More Information

Refer to ALTER TABLE name DROP OFFLINE SEGMENTS.

ALTER TABLE name DROP SUPPLEMENTAL LOG

Function

It configures the system to prevent primary key information from being recorded in the redo log when the data in the table is changed.

Syntax

<add table supplemental log statement> ::=
    ALTER TABLE table_name 
        DROP SUPPLEMENTAL LOG DATA ( PRIMARY KEY ) COLUMNS
    ;

Invocation and Access Rules

One of the following privileges is required to execute the <drop table supplemental log statement>.

Syntax Rules and Parameters

table_name

It is the name of the table to be altered.
The schema to which the table belongs can be defined using the format schema_name.table_name. If schema_name is omitted, the default schema name of the user executing the statement is used.
This must be set using the ALTER TABLE name ADD SUPPLEMENTAL LOG statement.

Description

For more information, refer to the syntax rules for each statement.

Example

The following is an example of configuring the system to prevent primary key information from being recorded in the redo log when the data in the table is changed.

gSQL> ALTER TABLE t1 DROP SUPPLEMENTAL LOG DATA ( PRIMARY KEY ) COLUMNS;

Table altered.

Compatibility

The SQL standard does not define the concept of the <drop table supplemental log statement>.

ALTER TABLE name MERGE SHARDS

Function

It merges specific shards in a table within a cluster environment and rebalances them.

Syntax

<alter table merge shards statement> ::=
    ALTER TABLE table_name MERGE SHARDS <source shard list> 
       INTO dest_shard_name [ <dest shard placement> ]
    ;

<source shard list> ::= 
    source_shard_name [, ...]
  | start_shard_name TO end_shard_name

<dest shard placement> ::=
    AT CLUSTER GROUP dest_group_name

Invocation and Access Rules

It can be performed in a cluster system.

One of the following privileges is required to execute the <alter table merge shards statement>.

Syntax Rules and Parameters

table_name

It is the name of the table.
The schema to which the table belongs can be defined using the format schema_name.table_name. If schema_name is omitted, the default schema name of the user executing the statement is used.
The statement can only be performed when the table is cluster-specific and a list shard or range shard.

<source shard list>

It is the list of original shards to be merged.
The shards specified in the list must already exist in the table.

source_shard_name

It is the name of an original shard to be merged.
If the shard does not exist in the table, the statement can not be performed.

start_shard_name

It is the name of the starting shard in the range to be merged.
It is used only with a range shard.

end_shard_name

It is the name of the last shard in the range to be merged.
It is used only with a range shard.

dest_shard_name

It is the name of the target shard.

<dest shard placement>

It is the name of the cluster group where the target shard is to be placed.
If the corresponding clause is omitted, the dest_shard_name must be included in the <source shard list>.

Description

It merges specific shards of a given table and places them in an arbitrary cluster group.

The following is an error that occurs when attempting to merge non-adjacent shards in a range-sharded table using the list method.

CREATE TABLE t1( i1 INTEGER ) 
    SHARDING BY RANGE (i1)
    SHARD shard1 VALUES LESS THAN ( 200 )      AT CLUSTER GROUP G1,
    SHARD shard2 VALUES LESS THAN ( 400 )      AT CLUSTER GROUP G2,
    SHARD shard3 VALUES LESS THAN ( MAXVALUE ) AT CLUSTER GROUP G3
;

Table created.

ALTER TABLE t1 MERGE SHARDS shard1, shard3 INTO shard4 AT CLUSTER GROUP G2;

ERR-42000(16488): shards being merged are not adjacent : 
ALTER TABLE t1 MERGE SHARDS shard1, shard3 INTO shard4 AT CLUSTER GROUP G2
                                    *
ERROR at line 1:

Examples

The following is an example of merging shards using the list method.

gSQL> ALTER TABLE t1 MERGE SHARDS shard1, shard2, shard3 INTO shard4 AT CLUSTER GROUP G2;

Table altered

The following is an example of merging shards using the range method.

gSQL> ALTER TABLE t1 MERGE SHARDS shard1 TO shard3 INTO shard4 AT CLUSTER GROUP G2;

Table altered

Compatibility

The SQL standard does not define the concept of a cluster.

For More Information

Refer to the following.

ALTER TABLE name MOVE SHARD

Function

It rebalances a specific shard of a table or all shards in a specific cluster group to a target cluster group.

Syntax

<alter table move shard statement> ::=
    ALTER TABLE table_name MOVE SHARD
        { shard_name_list | FROM CLUSTER GROUP src_cluster_group }
        TO CLUSTER GROUP dest_cluster_group 
       [ ONLINE | OFFLINE ] 
       [ LOGGING | NOLOGGING ] 
       [ <scan partition> ] 
       [ <parallel clause> ]
    ;

<scan partition> ::= 
    SCAN PARTITION integer

<parallel clause> ::=
    NOPARALLEL
  | PARALLEL [ integer ]

Invocation and Access Rules

It can be performed in a cluster system.

One of the following privileges is required to execute the <alter table move shard statement>.

Syntax Rules and Parameters

table_name

It is the name of the table. 
The schema to which the table belongs can be defined using the format schema_name.table_name. If schema_name is omitted, the default schema name of the user executing the statement is used.
The statement can only be performed when the table is cluster-group-specific.

shard_name_list

It is the list of shard names to be rebalanced.

If any shard does not exist in the table, the statement cannot be performed.

src_cluster_group

It is the name of the specific cluster group to be rebalanced.

dest_cluster_group

It is the name of the target cluster group where the table's shard is to be placed.
If the shard of the table already exists in the specified cluster group, the statement cannot be performed.

[ ONLINE | OFFLINE ]

It determines whether DML operations are allowed while rebalancing the table's shard.

[ LOGGING | NOLOGGING ]

It specifies the amount of logging performed during table synchronization when rebalancing a table shard.

When the NOLOGGING option is used, redo logs are not generated. Therefore, if the server terminates unexpectedly after executing move shard, the table becomes unusable. To prevent this, execute the CHECKPOINT statement after completing move shard.

<scan partition>

It specifies the number of partitions for the shard.

<parallel clause>

It specifies the number of threads to be used during the rebalancing of the table.

Description

It rebalances a specific shard of the table from one cluster group to another.
To drop a specific cluster group, first rebalance the table's shard, then perform the DROP CLUSTER GROUP statement.

To move the shards of all tables from one cluster group to another, perform the ALTER DATABASE MOVE SHARD FROM CLUSTER GROUP TO CLUSTER GROUP statement.

If it is a CLONED table or a CLUSTER WIDE table, an error will occur, and the operation will fail.

Examples

The following is an example of executing the <alter table move shard statement> statement.

gSQL> ALTER TABLE t1 MOVE SHARD shard1, shard2 TO CLUSTER GROUP g3;

Table altered.

gSQL> ALTER TABLE t1 MOVE SHARD FROM CLUSTER GROUP g1 TO CLUSTER GROUP g3;

Table altered.

The following is an example where a CLONED table and a CLUSTER-WIDE table fail to move shards.

gSQL> CREATE TABLE T1 ( C1 INTEGER ) SHARDING BY RANGE (C1)
         AT CLUSTER WIDE
         SHARD s1 VALUES LESS THAN (10),                                      
         SHARD s2 VALUES LESS THAN (MAXVALUE);

Table created.

gSQL> ALTER TABLE T1 MOVE SHARD s1 TO CLUSTER GROUP g2;

ERR-42000(16440): cannot execute on cluster wide sharded tables

gSQL> CREATE TABLE T2 ( C1 INTEGER ) CLONED AT CLUSTER GROUP g1, g2;

Table created.

gSQL> ALTER TABLE T2 MOVE SHARD FROM CLUSTER GROUP g1 TO CLUSTER GROUP g3;

ERR-42000(16437): cannot execute on cloned tables

Compatibility

The SQL standard does not define the concept of a cluster.

For More Information

Refer to ALTER DATABASE MOVE SHARD.

ALTER TABLE name OFFLINE INACTIVE CLUSTER MEMBERS

Function

Changes all inactive cluster members in the table's cluster member information to the offline state. That is, it marks the shard map of the corresponding cluster members as offline.

Syntax

<alter table offline inactive cluster members statement> ::=
    ALTER TABLE table_name OFFLINE INACTIVE CLUSTER MEMBERS
    ;

Invocation and Access Rules

It can be performed in a cluster system.

One of the following privileges is required to execute <alter table offline inactive cluster members statement>.

Syntax Rules and Parameters

table_name

It is the name of the table. 
The schema to which the table belongs can be defined using the format schema_name.table_name. 
If schema_name is omitted, the default schema name of the user executing the statement is used.

Description

Use this statement when all inactive cluster members that contain shards of the table can no longer participate in the cluster.

In addition, for an inactive cluster member to rejoin the cluster system, it must be in the offline state on all members.

To change all tables in the database to the offline state for inactive cluster members, execute the <alter database offline inactive cluster members> statement.

Example

The following is an example of executing the <alter table offline inactive cluster members statement> statement on table t1.

gSQL> ALTER TABLE t1 OFFLINE INACTIVE CLUSTER MEMBERS;

Table altered.

Compatibility

The SQL standard does not define the concept of clusters.

For More Information

Refer to ALTER DATABASE OFFLINE INACTIVE CLUSTER MEMBERS.

ALTER TABLE name READ { ONLY | WRITE }

Function

It sets READ { ONLY | WRITE } on the table.

Syntax

<alter table read { only | write } statement> :==
     ALTER TABLE table_name
         READ { ONLY | WRITE }
     ;

Invocation and Access Rules

One of the following privileges is required to execute the <alter table read { only | write } statement>.

Syntax Rules and Parameters

table_name

It is the name of the table.
The schema to which the table belongs can be defined using the format schema_name.table_name. 
If schema_name is omitted, the default schema name of the user executing the statement is used.

Description

It sets the table property to READ { ONLY | WRITE }.

If set to READ ONLY, neither the SELECT .. FOR UPDATE statement nor any DML/DDL statements that modify table data can be used. However, DDL statements that do not modify the table data are allowed.

Disallowed SQL statements when set to READ ONLY

• INSERT, UPDATE, DELETE

• TRUNCATE

• SELECT .. FOR UPDATE

• ALTER TABLE RENAME/DROP COLUMN

• ALTER TABLE SET COLUMN UNUSED


Allowed SQL statements when set to READ ONLY

• SELECT

• CREATE/ALTER/DROP INDEX

• ALTER TABLE ADD/ALTER COLUMN

• ALTER TABLE ADD/ALTER/RENAME/DROP CONSTRAINT

• ALTER TABLE for physical property changes

• ALTER TABLE DROP UNUSED COLUMNS

• ALTER TABLE RENAME TO

• DROP TABLE

• ALTER TABLE ADD/DROP SUPPLEMENTAL LOG

• LOCK TABLE

Examples

The following is an example of executing the <alter table read { only | write } statement>.

gSQL> ALTER TABLE t1 READ ONLY;

Table altered.

gSQL> ALTER TABLE t1 READ WRITE;

Table altered.

Compatibility

The SQL standard does not define the <alter table read { only | write } statement>.

For More Information

Refer to ALTER TABLE.

ALTER TABLE name REBALANCE

Function

It rebalances the shard within the table.

Syntax

<alter table rebalance statement> ::=
    ALTER TABLE table_name REBALANCE 
       [ ONLINE | OFFLINE ] 
       [ LOGGING | NOLOGGING ]
       [ <scan partition> ] 
       [ <parallel clause> ]
    ;

<scan partition> ::= 
    SCAN PARTITION integer

<parallel clause> ::=
    NOPARALLEL
  | PARALLEL [ integer ]

Invocation and Access Rules

It can be performed in a cluster system.
One of the following privileges is required to execute the <alter table rebalance statement>.

Syntax Rules and Parameters

table_name

It is the name of the table. 
The schema to which the table belongs can be defined using the format schema_name.table_name. If schema_name is omitted, the default schema name of the user executing the statement is used.

[ ONLINE | OFFLINE ]

It determines whether DML operations are allowed while rebalancing the table shard.

[ LOGGING | NOLOGGING ]

It specifies the amount of logging performed during table synchronization when rebalancing a table shard.

When the NOLOGGING option is used, redo logs are not generated. Therefore, if the server terminates unexpectedly after the rebalance operation, the table becomes unusable. To prevent this, execute the CHECKPOINT statement after the rebalance operation completes.

<scan partition>

It specifies the number of partitions for the shard.

<parallel clause>

It specifies the number of threads to be used during the table rebalancing process.

Description

It does not rebalance shards in a table when adding a cluster member or cluster group using the following statements.

To rebalance the shards of a table in the newly added cluster group and cluster member, execute the <alter table rebalance statement> statement. If the shards of the table are already rebalanced, the operation will succeed without requiring additional rebalancing.

To rebalance shards across all tables, execute the ALTER DATABASE REBALANCE statement.

Examples

The following is an example of executing the <alter table rebalance statement> statement.

gSQL> ALTER TABLE t1 REBALANCE;

Table altered.

Compatibility

The SQL standard does not define the concept of a cluster.

ALTER TABLE name REBALANCE EXCLUDE CLUSTER GROUP cluster_group_list

Function

It rebalances the shard of the table to exclude a shard from a specific cluster group.

Syntax

<alter table rebalance exclude cluster group statement> ::=
    ALTER TABLE table_name REBALANCE 
        EXCLUDE CLUSTER GROUP cluster_group_list 
       [ ONLINE | OFFLINE ] 
       [ LOGGING | NOLOGGING ]
       [ <scan partition> ] 
       [ <parallel clause> ]
    ;

<scan partition> ::= 
    SCAN PARTITION integer

<parallel clause> ::=
    NOPARALLEL
  | PARALLEL [ integer ]

Invocation and Access Rules

It can be performed in a cluster system.

One of the following privileges is required to execute the <alter table rebalance exclude cluster group statement>.

Syntax Rules and Parameters

table_name

It is the name of the table. 
The schema to which the table belongs can be defined using the format schema_name.table_name. If schema_name is omitted, the default schema name of the user executing the statement is used.
The statement can only be executed if the table is a cluster-wide table.

cluster_group_list

It is a list of the cluster groups that do not include a shard of the table.
If the cluster group to be excluded from rebalancing is the entire group, the statement can not be executed.

[ ONLINE | OFFLINE ]

It determines whether DML operations are allowed while rebalancing the table shard.

[ LOGGING | NOLOGGING ]

It specifies the amount of logging performed during table synchronization when rebalancing a table shard.

When the NOLOGGING option is used, redo logs are not generated. Therefore, if the server terminates unexpectedly after the rebalance operation, the table becomes unusable. To prevent this, execute the CHECKPOINT statement after the rebalance operation completes.

<scan partition>

It specifies the number of partitions for the shard.

<parallel clause>

It specifies the number of threads to be used during the table rebalancing process.

Description

It excludes a specific cluster group and rebalances the shard of the table.
If the shard of the table does not exist in the specified cluster group, the operation will succeed without requiring a separate rebalancing. 
The shard is rebalanced based on the cluster group where the shard of the table is currently located.
To drop a specific cluster group, rebalance the shard of the table, and execute the DROP CLUSTER GROUP statement.
To rebalance the shards while excluding a cluster group from all tables, execute the  ALTER DATABASE REBALANCE EXCLUDE CLUSTER GROUP statement.

Examples

The following is an example of executing the <alter table rebalance exclude cluster group statement> statement.

gSQL> ALTER TABLE t1 REBALANCE EXCLUDE CLUSTER GROUP g3;

Table altered.

Compatibility

The SQL standard does not define the concept of a cluster.

ALTER TABLE name RENAME COLUMN

Function

It renames the table column.

Syntax

<rename column statement> ::=
    ALTER TABLE table_name 
        RENAME COLUMN old_column_name TO new_column_name
    ;

Invocation and Access Rules

One of the following privileges is required to execute the <rename column statement>.

Syntax Rules and Parameters

table_name

It is the name of the table to be altered. 
The schema to which the table belongs can be defined using the format schema_name.table_name. If schema_name is omitted, the default schema name of the user executing the statement is used.

old_column_name

It is the old name of the column to be altered.

new_column_name

It is the new name for the column to be altered. 
The same column name must not already exist in the table.

Description

Even when the column name is altered, it does not require changes to objects such as indexes or constraints that were created based on the previous column name.

Even if the column name is changed, the meaning of the CHECK constraint remains the same, as shown below.

CREATE TABLE t1 ( c1 INTEGER, c2 INTEGER,
                  CONSTRAINT t1_check CHECK ( c1 > c2 ) );

gSQL>
SELECT constraint_name, check_clause
  FROM information_schema.check_constraints
 WHERE constraint_table = 'T1';

CONSTRAINT_NAME CHECK_CLAUSE        
--------------- --------------------
T1_CHECK        CHECK( "C1" > "C2" )

1 row selected.

gSQL> INSERT INTO t1 VALUES ( 1, 2 );
ERR-23000(16665): check constraint "PUBLIC"."T1_CHECK" violated

gSQL> ALTER TABLE t1 RENAME COLUMN c1 TO tmp;
Table altered.

gSQL> INSERT INTO t1 VALUES ( 1, 2 );
ERR-23000(16665): check constraint "PUBLIC"."T1_CHECK" violated

gSQL> ALTER TABLE t1 RENAME COLUMN c2 TO c1;
Table altered.

gSQL> INSERT INTO t1 VALUES ( 1, 2 );
ERR-23000(16665): check constraint "PUBLIC"."T1_CHECK" violated

gSQL> ALTER TABLE t1 RENAME COLUMN tmp TO c2;
Table altered.

gSQL> INSERT INTO t1 VALUES ( 1, 2 );
ERR-23000(16665): check constraint "PUBLIC"."T1_CHECK" violated

gSQL>
SELECT constraint_name, check_clause
  FROM information_schema.check_constraints
 WHERE constraint_table = 'T1';

CONSTRAINT_NAME CHECK_CLAUSE        
--------------- --------------------
T1_CHECK        CHECK( "C2" > "C1" )

1 row selected.

Example

The following is an example of swapping the names of two columns, col_1 and col_2.

gSQL> ALTER TABLE t1 RENAME COLUMN col_1 TO col_temp;

Table altered.

gSQL> ALTER TABLE t1 RENAME COLUMN col_2 TO col_1;

Table altered.

gSQL> ALTER TABLE t1 RENAME COLUMN col_temp TO col_2;

Table altered.

gSQL> COMMIT;

Commit complete.

Compatibility

The SQL standard does not define the <rename column statement>.

For More Information

Refer to the following.

ALTER TABLE name RENAME CONSTRAINT

Function

It renames the constraints of the table.

Syntax

<rename table constraint statement> ::=
    ALTER TABLE table_name 
        RENAME <constraint object> TO new_constraint_name
    ;

<constraint object> ::=
      CONSTRAINT constraint_name
    | PRIMARY KEY 
    | UNIQUE ( column_name [, ...] )

Invocation and Access Rules

One of the following privileges is required to execute the <rename table constraint statement>.

Syntax Rules and Parameters

table_name

It is the name of the table to be altered.
The schema to which the table belongs can be defined using the format schema_name.table_name. If schema_name is omitted, the default schema name of the user executing the statement is used.

<constraint object>

The existing name of the constraint to be altered can be specified as follows.

new_column_name

It is the new name of the constraint to be altered.

Description

The index name that was automatically created with a key constraint, such as a primary key or unique key, can not be altered. Use the ALTER INDEX name RENAME TO statement to rename the index.

Examples

The following is an example of renaming the primary key constraint of a table.

gSQL> ALTER TABLE t1 RENAME PRIMARY KEY TO pk_t1;

Table altered.

The following is an example of renaming a table constraint by specifying its constraint name.

gSQL> ALTER TABLE t1 RENAME CONSTRAINT pk_t1 TO t1_pk;

Table altered.

Compatibility

The SQL standard does not define the <rename table constraint statement> statement.

For More Information

Refer to the following.

ALTER TABLE name RENAME SHARD

Function

It renames a specific shard of a table in a cluster environment.

Syntax

<alter table rename shard statement> ::=
    ALTER TABLE table_name 
        RENAME SHARD shard_name TO new_shard_name
    ;

Invocation and Access Rules

It can be performed in a cluster system.
One of the following privileges is required to execute the <alter table rename shard statement>.

Syntax Rules and Parameters

table_name

It is the name of the table to be altered. 
The schema to which the table belongs can be defined using the format schema_name.table_name. If schema_name is omitted, the default schema name of the user executing the statement is used.

shard_name

It is the existing name of the shard to be altered.
If the shard does not exist in the table, the statement can not be executed.

new_shard_name

It is the new name of the shard to be altered. 
The same shard name must not exist in the table.

Description

It alters the name of a specific shard in a hash, range, or list table. This statement can not be performed on a cloned table.

Examples

The following is an example of executing the <alter table rename shard statement>.

gSQL> ALTER TABLE t_range RENAME SHARD r_01 TO r_new_01;

Table altered.

gSQL> ALTER TABLE t_list RENAME SHARD l_01 TO l_new_01;

Table altered.

gSQL> ALTER TABLE t_hash RENAME SHARD shard_000000 TO h_new_00;

Table altered.

gSQL> COMMIT;

Commit complete.

Compatibility

The SQL standard does not define the concept of a cluster.

For More Information

Refer to the following.

ALTER TABLE name RENAME TO

Function

It renames the table.

Syntax

<rename table statement> ::=
    ALTER TABLE table_name 
        RENAME TO new_table_name
    ;

Invocation and Access Rules

One of the following privileges is required to execute the <rename table statement>.

Syntax Rules and Parameters

table_name

It is the existing name of the table.
The schema to which the table belongs can be defined using the format schema_name.table_name. If schema_name is omitted, the default schema name of the user executing the statement is used.

new_table_name

It is the new name of the table.
The same table name must not exist in the schema.

Description

Even when the table is renamed, objects referring to the table, such as indexes and constraints do not need to be renamed.

Example

The following is an example of exchanging the names of two tables, t1 and t2.

gSQL> ALTER TABLE t1 RENAME TO t_temp;

Table altered.

gSQL> ALTER TABLE t2 RENAME TO t1;

Table altered.

gSQL> ALTER TABLE t_temp RENAME TO t2;

Table altered.

gSQL> COMMIT;

Commit complete.

Compatibility

The SQL standard does not define the <rename table statement>.

For More Information

Refer to ALTER TABLE.

ALTER TABLE name REORGANIZE

Function

It reorganizes the table physically.

Syntax

<alter table reorganize statement> ::=
    ALTER TABLE table_name REORGANIZE 
       [ LOGGING| NOLOGGING ] 
       [ ONLINE | OFFLINE ] 
       [ <scan partition> ] 
       [ <parallel clause> ]
       [ AT <domain name> ]
    ;

<scan partition> ::=
    SCAN PARTITION integer

<parallel clause> ::=
    NOPARALLEL
  | PARALLEL [ integer ]

Invocation and Access Rules

One of the following privileges is required to execute the <alter table reorganize statement>.

Syntax Rules and Parameters

table_name

It is the name of the table to be altered. 
The schema to which the table belongs can be defined using the format schema_name.table_name. If schema_name is omitted, the default schema name of the user executing the statement is used.

[ LOGGING | NOLOGGING ]

It specifies whether redo logs are recorded when reorganizing a table.

When the NOLOGGING option is used, redo logs are not generated. Therefore, if the server terminates unexpectedly after the reorganization, the table becomes unusable. To prevent this, execute the CHECKPOINT statement after the reorganization completes.

[ ONLINE | OFFLINE ]

It specifies whether DML operations are allowed when reorganizing a table.

<scan partition>

It divides the table into the specified number of parts and synchronizes them with the new table.

<parallel clause>

It specifies the number of threads to be used when reorganizing a table.

<domain name>

It is the name of the member or group on which the statement is performed.
If not specified, the statement is performed on all groups.
This option is for cluster databases only.

Description

Table Reorganization is the process of rearranging the physical structure of a table to improve query performance or optimize storage space.
By eliminating free space within table pages, the physical size of the table can be reduced.

The key features of table reorganization are as follows:

Examples

다음은 <alter table reorganize statement> 구문을 수행하는 예이다.

gSQL> ALTER TABLE t1 REORGANIZE;

Table altered.

Compatibility

SQL 표준에서는 reorganize 에 대한 개념을 정의하지 않고 있다.

For More Information

관련 내용은 ALTER TABLE을 참조한다.

ALTER TABLE name SET TRIGGER ORDER

Function

It alters the execution order of the triggers created on the table.

Syntax

<alter table set trigger order statement> ::=
    ALTER TABLE <table_name> SET TRIGGER ORDER <trigger_name> [, ...]
    ;

Invocation and Access Rules

One of the following privileges is required to execute <alter table set trigger order statement>.

Syntax Rules and Parameters

table_name

It is the name of the table to be altered. 
The schema to which the table belongs can be defined using the format schema_name.table_name. If schema_name is omitted, the default schema name of the user executing the statement is used.

<trigger name> [, ... ]

All listed triggers must meet the following conditions:

Description

For example, if the following AFTER STATEMENT triggers are created on table t1 in the given order:

The execution order of the triggers can be changed using the ALTER TABLE t1 SET TRIGGER ORDER statement, as shown below:

Examples

When the execution order of triggers currently created on the table is as follows:

gSQL>
SELECT action_timing
     , action_orientation
     , action_order
     , trigger_name
  FROM information_schema.triggers
 WHERE event_object_table = 'T1'
 ORDER BY action_order;

ACTION_TIMING ACTION_ORIENTATION ACTION_ORDER TRIGGER_NAME
------------- ------------------ ------------ ------------
AFTER         STATEMENT                     1 TRG1        
AFTER         STATEMENT                     2 TRG2        
AFTER         STATEMENT                     3 TRG3        
AFTER         STATEMENT                     4 TRG4        
AFTER         STATEMENT                     5 TRG5        

5 rows selected.

Executing the <alter table set trigger order statement> alters the execution order of the triggers as shown below.

gSQL> ALTER TABLE t1 SET TRIGGER ORDER trg5, trg4, trg3, trg2, trg1;

Table Altered

gSQL>
SELECT action_timing
     , action_orientation
     , trigger_name
     , action_order
  FROM information_schema.triggers
 WHERE event_object_table = 'T1'
 ORDER BY action_order;

ACTION_TIMING ACTION_ORIENTATION ACTION_ORDER TRIGGER_NAME
------------- ------------------ ------------ ------------
AFTER         STATEMENT                     1 TRG5        
AFTER         STATEMENT                     2 TRG4        
AFTER         STATEMENT                     3 TRG3        
AFTER         STATEMENT                     4 TRG2        
AFTER         STATEMENT                     5 TRG1        

5 rows selected.

Compatibility

The SQL standard does not define any statement for altering the execution order of triggers.

ALTER TABLE name SET UNUSED COLUMN

Function

It drops a table column.

Syntax

<drop column definition> ::=
    ALTER TABLE table_name <drop column clause>
    ;

<drop column clause> ::=
      SET UNUSED [ COLUMN ] <column_name_list> [ <drop behavior> ]

<column name list> ::=
      column_name
    | ( column_name [, ...] )

<drop behavior> ::=
      RESTRICT
    | CASCADE
    | CASCADE CONSTRAINTS

Invocation and Access Rules

One of the following privileges is required to execute the <drop column definition>.

Syntax Rules and Parameters

table_name

It is the name of the table to be altered. 
The schema to which the table belongs can be defined using the format schema_name.table_name. If schema_name is omitted, the default schema name of the user executing the statement is used.

SET UNUSED [ COLUMN ]

It disables the use of the specified columns.

column_name_list

It is the name of one or more columns to be dropped.

column_name

It is the name of the column to be dropped.

Indexes that include the column are also dropped.

CREATE INDEX idx1 ON t1(c1, c2);

--# The index idx1 is also dropped.
ALTER TABLE t1 SET UNUSED COLUMN (c1);

Constraints composed of the columns are also dropped.

CREATE TABLE t1 ( c1 INTEGER PRIMARY KEY
                , c2 INTEGER );

--# The primary key is also dropped.
ALTER TABLE t1 SET UNUSED COLUMN (c1);

Triggers that explicitly specify the column are also dropped.

CREATE TRIGGER trigger1
    AFTER UPDATE OF c1 ON t1
BEGIN
    NULL;
END;
/

--# UPDATE OF triggers are also dropped.
ALTER TABLE t1 SET UNUSED COLUMN (c1);

drop behavior

If omitted, the default value is RESTRICT.

CASCADE and CASCADE CONSTRAINTS have the same meaning.

If there is a constraint that includes the column to be dropped along with other columns, the CASCADE CONSTRAINTS option must be specified.

CREATE TABLE t1 ( c1 INTEGER
                , c2 INTEGER
                , UNIQUE(c1, c2) );

--# error
ALTER TABLE t1 SET UNUSED COLUMN c1;

--# success
ALTER TABLE t1 SET UNUSED COLUMN c1 CASCADE CONSTRAINTS;

If a FOREIGN KEY referencing a constraint that will be dropped together with the column exists, the CASCADE CONSTRAINTS option must be specified.

CREATE TABLE parent ( pk INTEGER PRIMARY KEY
                    , c1 INTEGER );

CREATE TABLE child ( fk INTEGER REFERENCES parent(pk) 
                   , c2 INTEGER );

--# error
ALTER TABLE parent SET UNUSED COLUMN ( pk );

--# success
ALTER TABLE parent SET UNUSED COLUMN ( pk ) CASCADE CONSTRAINTS;

Description

SET UNUSED COLUMN does not physically delete the data, ensuring consistent performance regardless of the number of rows.

Example

The following is an example of setting the column to be unused.

gSQL> ALTER TABLE t1 SET UNUSED COLUMN ( addr );

Table altered.

Compatibility

The SQL standard does not define the following clauses.

SQL standard compatibility

Feature ID

Description

Compatibility

F033

ALTER TABLE statement: DROP COLUMN clause

X

For More Information

Refer to the following.

ALTER TABLE name SPLIT SHARD

Function

It rebalances a specific shard of a table by splitting it in a cluster environment.

Syntax

<alter table split shard statement> ::=
    ALTER TABLE table_name SPLIT SHARD source_shard_name
        INTO ( <split shard placement> [, ...] )
    ;

<split shard placement> ::=
    <split shard bound def> AT CLUSTER GROUP dest_group_name

<split shard bound def> ::=
    <split list shard def>
  | <split range shard def>

<split list shard def> :=
    SHARD dest_shard_name VALUES IN ( <split list value clause> )

<split list value clause> :=
    <split list value> [, ...]

<split list value> :=
    constant
  | NULL

<split range shard def> :=
    SHARD dest_shard_name VALUES LESS THAN ( <split range value clause> )

<split range value clause> :=
    <split range value> [, ...]

<split range value> :=
    constant

Invocation and Access Rules

It can be performed in a cluster system.
One of the following privileges is required to execute the <alter table split shard statement>.

Syntax Rules and Parameters

table_name

It is the name of the table
The schema to which the table belongs can be defined using the format schema_name.table_name. If schema_name is omitted, the default schema name of the user executing the statement is used.
The statement can only be performed when the table is cluster group-specific and either a list shard or a range shard.

source_shard_name

It is the name of the original shard to be split. 
If the shard does not exist in the table, the statement cannot be executed.

<split shard placement>

It defines the target shard to which the original shard is rebalanced after being split.

<split shard bound def>

It defines the bound of the target shard to be split.

It can be defined using one of the following two bound defs.

<split list shard def>

It defines the split shard bound for a list shard.

<split range shard def>

It defines the split shard bound for a range shard.

dest_group_name

It is the name of the cluster group to which the split shard will be rebalanced.

Description

It splits a specific shard of a given table and places it into a random cluster group.
This is used to distribute records and balance the load by splitting shards when there are too many records for a specific shard or when the load is unevenly distributed across group members.

Examples

The following is an example of executing the <alter table split shard statement>.

gSQL> ALTER TABLE t1 SPLIT SHARD shard1 INTO ( SHARD shard11 VALUES IN ( 11 ) AT CLUSTER GROUP G2 );

Table altered.

gSQL> ALTER TABLE t1 SPLIT SHARD shard1 INTO ( SHARD shard11 VALUES LESS THAN ( 11 ) AT CLUSTER GROUP G2 );


Table altered.

Compatibility

The SQL standard does not define the concept of a cluster.

For More Information

Refer to the following.

ALTER TABLE name STORAGE

Function

It alters the physical attributes of a table.

Syntax

<alter table physical attribute statement> ::=
    ALTER TABLE table_name 
      [ <physical attribute clause> ]
    | [ STORAGE ( <segment attr clause> [...] ) ]
    ;

<physical attribute clause> ::=
      PCTFREE integer
    | PCTUSED integer
    | INITRANS integer
    | MAXTRANS integer

<segment attr clause> ::=
    NEXT <size_clause>
    | MAXSIZE <size_clause>

<size clause> ::=
      integer [ K | M | G | T ]

Invocation and Access Rules

One of the following privileges is required to execute the <alter table physical attribute statement>.

Syntax Rules and Parameters

table_name

It is the name of the table to be altered.
The schema to which the table belongs can be defined using the format schema_name.table_name. If schema_name is omitted, the default schema name of the user executing the statement is used.

<physical attribute clause>

It alters the physical attribute of a page that configures the table.
This is not applied to already allocated pages, but is applied to newly allocated pages.
For more information, refer to the <table physical attribute clause> in the CREATE TABLE statement.

<segment attr clause>

It alters the physical attribute of the extent that configures the segment.

This is not applied to the already allocated extent, but is applied to newly allocated extents.

Description

For more information, refer to the syntax rules for each statement.

Example

The following is an example of altering the physical attributes of the table.

gSQL> ALTER TABLE t1 PCTFREE 10 PCTUSED 40 STORAGE ( NEXT 10M  MAXSIZE  100M );

Table altered.

Compatibility

The SQL standard does not define the physical attributes of a table.

For More Information

Refer to ALTER TABLE.

ALTER TABLE name SYNCHRONIZE

Function

It synchronizes the shards of the existing table remotely.

Syntax

<alter table synchronize statement> ::=
    ALTER TABLE table_name SYNCHRONIZE 
       [ ONLINE | OFFLINE ] 
       [ LOGGING | NOLOGGING ]
       [ <scan partition> ] 
       [ <parallel clause> ]
    ;

<scan partition> ::= 
    SCAN PARTITION integer

<parallel clause> ::=
    NOPARALLEL
  | PARALLEL [ integer ]

Invocation and Access Rules

It can be performed in a cluster system.

One of the following privileges is required to execute the <alter table synchronize statement>.

Syntax Rules and Parameters

table_name

It is the name of the table. 
The schema to which the table belongs can be defined using the format schema_name.table_name. If schema_name is omitted, the default schema name of the user executing the statement is used.

[ ONLINE | OFFLINE ]

It determines whether DML operations are allowed while synchronizing the table shard.

[ LOGGING | NOLOGGING ]

It specifies the amount of logging performed during table synchronization.

When the NOLOGGING option is used, redo logs are not generated. Therefore, if the server terminates unexpectedly after synchronization, the table becomes unusable. To prevent this, execute the CHECKPOINT statement after synchronization is complete.

<scan partition>

It specifies the number of partitions for the shard.

<parallel clause>

It specifies the number of threads to be used for synchronizing the table.

Description

Table synchronization synchronizes the existing offline shards to restore consistency. Unlike the <alter table rebalance statement>, it can be performed even when an inactive cluster member exists.

If the following conditions are not met, the operation will fail.

For example, if all replicas of the sharded table t1 in cluster group G3 are offline, the following error occurs.

gSQL> ALTER TABLE t1 SYNCHRONIZE;

ERR-42000(16546): sharded table "PUBLIC"."T1" must have at least one online replica of group 'G3'

Execute the <alter database synchronize statement> to synchronize shards across all tables.

Example

The following is an example of executing the <alter table synchronize statement> for table T1.

gSQL> ALTER TABLE t1 SYNCHRONIZE;

Table altered.

Compatibility

The SQL standard does not define the concept of a cluster.

For More Information

Refer to the following.

ALTER TABLE name USABLE

Function

It changes a table in the unusable state to the usable state.

Syntax

<alter table usable statement> ::=
    ALTER TABLE table_name USABLE 
    ;

Invocation and Access Rules

It can be executed in a cluster system or a standalone system.

One of the following privileges is required to execute the <alter table usable statement> .

Syntax Rules and Parameters

table_name

It is the name of the table. 
The schema to which the table belongs can be defined using the format schema_name.table_name. If schema_name is omitted, the default schema name of the user executing the statement is used.

<domain name>

It is the name of the member or group on which the statement is performed. 
If not specified, the statement is performed on all groups.

Description

It changes a table in the unusable state to the usable state.
If the table is already in the usable state, the operation is treated as successful.
When the table is changed to the usable state, all indexes associated with the table are rebuilt. However, indexes stored in offline tablespaces are not rebuilt.

A table can become unusable in the following cases:

The <alter table usable statement> can be executed even if there are inactive cluster members.
However, if the table contains any logically corrupted pages, the statement fails and the following error is returned.
gSQL> ALTER TABLE t1 USABLE;

ERR-42000(16677): unable to set table to 'usable' state due to remaining corrupted pages.

Example

The following is an example of executing the <alter table usable statement>.

gSQL> ALTER TABLE t1 USABLE;

Table altered.

Compatibility

The SQL standard does not define the concept of usable segment.

ALTER TABLESPACE

Function

It alters the tablespace definition.

Syntax

<alter tablespace statement> ::=
      <rename tablespace statement>
    | <backup tablespace statement>
    | <on/offline tablespace statement>
    | <add file statement>
    | <drop file statement>
    | <rename datafile statement>
    ;

Invocation and Access Rules

The ALTER TABLESPACE privilege is required to execute the <alter tablespace statement>.

Syntax Rules and Parameters

<rename tablespace statement>

It renames the tablespace.
For more information, refer to the ALTER TABLESPACE name RENAME TO statement.

<backup tablespace statement>

It backs up the tablespace.
For more information, refer to the ALTER TABLESPACE name BACKUP statement.

<on-offline tablespace statement>

It changes all files in the tablespace to either the online or offline state.
For more information, refer to the ALTER TABLESPACE name [ONLINE|OFFLINE] statement.

<add file statement>

It adds a file to the tablespace.
For more information, refer to the ALTER TABLESPACE name ADD [DATAFILE|MEMORY] statement.

<drop file statement>

It drops a file from the tablespace.
For more information, refer to the ALTER TABLESPACE name DROP [DATAFILE|MEMORY] statement.

<rename datafile statement>

It renames the datafile in the data tablespace. 
For more information, refer to the ALTER TABLESPACE name RENAME DATAFILE statement.

Description

Unlike other Data Definition Language (DDL) statements, the ALTER TABLESPACE statement can not be rolled back, and the transaction is automatically committed after executing the statement.

Example

Refer to the examples for each detailed statement.

Compatibility

The SQL standard does not define the concept of a tablespace.

For More Information

Refer to the following.

ALTER TABLESPACE name ADD [DATAFILE|MEMORY]

Function

It extends the space of the tablespace.

Syntax

<add space statement> ::=
    ALTER TABLESPACE tablespace_name ADD <space specification>
        [AT <domain name>]
    ;

<space specification> ::=
      MEMORY <memory clause> [, ...]
    | DATAFILE <add datafile clause> [, ...]

<size clause> ::=
    integer [ K | M | G | T ]

<memory clause> 
     'memory_name' { SIZE <size clause> }

<add datafile clause> ::=
     'filename' 
        { SIZE <size clause> | REUSE | SIZE <size clause> REUSE }
        [ <autoextend clause> ]

<autoextend clause>
    AUTOEXTEND { ON [ <next size clause> ] [ <max size clause> ] | OFF }

<next size clause>
    NEXT <size clause>

<max size clause>
    MAXSIZE { <size clause> | UNLIMITED }

Invocation and Access Rules

The ALTER TABLESPACE ON DATABASE privilege is required to execute the <add space statement>.

Syntax Rules and Parameters

<add space statement>

It adds space to the tablespace.

tablespace_name

It is the name of the tablespace to which space will be added.

<file specification>

The following syntax must be used based on the tablespace type.

<add datafile clause>

It defines the memory datafile to be added.

<memory clause>

For more information, refer to the <memory clause> in the CREATE MEMORY TEMPORARY TABLESPACE statement.

<autoextend clause>

It sets the automatic extension property when adding a data file to a disk tablespace. The automatic extension property can be set to ON or OFF. If set to ON, the automatic extension size and the maximum size of the data file can be specified.

<next size clause>

It specifies the size to be extended when there is no more available space in the data file currently in use.

<max size clause>

It specifies the maximum size to which the data file can be extended.

<domain name>

It is the name of the member or group for which the statement is executed.
If not specified, the statement is performed on all groups.

Description

For more information, refer to the syntax rules for each statement.

Example

The following is an example of adding a datafile to the tablespace.

gSQL> ALTER TABLESPACE space1 ADD DATAFILE 'test_file_a2.dbf' SIZE 10M REUSE;

Tablespace altered.

Compatibility

The SQL standard does not define the concept of a tablespace.

For More Information

Refer to the following.

ALTER TABLESPACE name BACKUP

Function

It switches the tablespace to backup enabled state and backup disabled state to perform the backup.

Syntax

<backup tablespace statement> ::=
      <tablespace begin backup statement>
    | <tablespace end backup statement>
    | <tablespace incremental backup statement>    
    ;

<tablespace begin backup statement> ::=
    ALTER TABLESPACE tablespace_name BEGIN BACKUP [AT <domain_name>];

<tablespace end backup statement> ::=
    ALTER TABLESPACE tablespace_name END BACKUP [AT <domain_name>];

<tablesapce incremental backup statement> ::=
    ALTER TABLESPACE tablespace_name 
        BACKUP INCREMENTAL <incremental backup option> 
           [ FORMAT 'format string' ] [ PIECE integer ] 
           [ <parallel clause> ] [AT <domain_name>];

<incremental backup option> ::=
      LEVEL integer [ CUMULATIVE | DIFFERENTIAL ]

<parallel clause> ::=
      NOPARALLEL
    | PARALLEL [ integer ]

Invocation and Access Rules

The ALTER TABLESPACE ON DATABASE privilege is required to execute the <backup space statement>.

Syntax Rules and Parameters

<tablespace begin backup statement>

It sets the tablespace to the backup enabled state.

tablespace_name

It is the name of the tablespace whose backup state is to be switched.

<tablespace end backup statement>

It sets the tablespace to the backup disabled state.

<tablesapce incremental backup statement>

It performs an incremental backup of the tablespace.
The database must be in the OPEN phase and operated in ARCHIVELOG mode.

<incremental backup option>

FORMAT 'format string'

PIECE integer

<parallel clause>

It specifies the number of threads to be used during the backup.

<domain name>

It is the name of the member or group on which the statement is performed.
If not specified, the statement is performed on all groups.

Description

It backs up the datafiles created in the tablespace. A full backup of the tablespace begins with BEGIN BACKUP, copies the datafiles using OS file copy, and ends with END BACKUP. The incremental backup file is created in the path specified by the BACKUP_DIR 1 property using a single statement.

Examples

The following is an example of setting the full backup state to 'ACTIVE' for the tablespace DICTIONARY_TBS.

ALTER TABLESPACE DICTIONARY_TBS BEGIN BACKUP;

The following is an example of setting the full backup state to 'INACTIVE' for the tablespace DICTIONARY_TBS.

ALTER TABLESPACE DICTIONARY_TBS END BACKUP;

The following is an example of generating a LEVEL 0 incremental backup for the tablespace DICTIONARY_TBS.

ALTER TABLESPACE DICTIONARY_TBS BACKUP INCREMENTAL LEVEL 0;

The following is an example of performing a backup with four threads, resulting in four backup files, each consisting of the tablespace name, date and piece number.

ALTER TABLESPACE DICTIONARY_TBS BACKUP INCREMENTAL LEVEL 0 FORMAT 'backup_%N_%T_%p' PIECE 4 PARALLEL 4;

Compatibility

The SQL standard does not define the concept of a tablespace.

For More Information

Refer to the following.

ALTER TABLESPACE name DROP [DATAFILE|MEMORY]

Function

It reduces the space of the tablespace.

Syntax

<drop space statement> ::=
    ALTER TABLESPACE tablespace_name DROP <file specification>
    [ AT <domain name> ]
    ;

<file specification> ::=
      DATAFILE 'filename' 
    | MEMORY 'memory_name'

Invocation and Access Rules

The ALTER TABLESPACE ON DATABASE privilege is required to execute the <drop space statement>.

Syntax Rules and Parameters

tablespace_name

It is the name of the tablespace to be altered.

<file specification>

The following syntax must be used based on the tablespace type.

The file of an OFFLINE tablespace can not be dropped.

The first file of a tablespace can not be dropped.

A data file that has been used at least once can not be dropped.

<domain name>

It is the name of the member or group on which the statement is performed.
If not specified, the statement is performed on all groups.

Description

For more information, refer to the syntax rules for each statement.

Example

The following is an example of dropping a file from the tablespace.

gSQL> ALTER TABLESPACE space1 DROP DATAFILE 'test_file_f2.dbf';

Tablespace altered.

Compatibility

The SQL standard does not define the concept of a tablespace.

For More Information

Refer to the following.

ALTER TABLESPACE name [ONLINE|OFFLINE]

Function

It alters the status of the tablespace.

Syntax

<on/off tablespace statement> ::=
    ALTER TABLESPACE tablespace_name { ONLINE | OFFLINE [ NORMAL | IMMEIDATE ] }
    [ AT <domain name> ]
    ;

Invocation and Access Rules

The ALTER TABLESPACE ON DATABASE privilege is required to execute the <on/off tablespace statement>.

Syntax Rules and Parameters

ONLINE

It alters the tablespace status from OFFLINE to ONLINE

OFFLINE NORMAL

It alters the tablespace status from ONLINE to OFFLINE.
A tablespace that has been changed to OFFLINE is in a consistent state, so media recovery is not required when switching it back to ONLINE.

OFFLINE NORMAL is not allowed in the MOUNT phase.

OFFLINE IMMEDIATE

It alters the tablespace status from ONLINE to OFFLINE.
A tablespace that has been changed to OFFLINE is in an inconsistent state, so media recovery is required when switching it back to ONLINE.

The SYSTEM tablespace can not be altered to the OFFLINE state.

OFFLINE IMMEDIATE requires media recovery and can only be performed in ARCHIVELOG mode.

<domain name>

It is the name of the member or group on which the statement is performed.
If not specified, the statement is performed on all groups.

Description

For more information, refer to the syntax rules for each statement.

In a cluster database, when the system is in the OPEN phase or above, all tables in the tablespace must be in the OFFLINE state in order to take the tablespace OFFLINE.

Therefore, the ALTER TABLESPACE name OFFLINE TABLES command must be executed beforehand.

Examples

The following is an example of setting the tablespace to OFFLINE state.

gSQL> ALTER TABLESPACE space1 OFFLINE;

Tablespace altered.

The following is an example of OFFLINE NORMAL for the tablespace failing in the MOUNT phase.

gSQL> ALTER TABLESPACE space1 OFFLINE;

ERR-42000(16290): OFFLINE NORMAL is only allowed if the database is in OPEN phase : 
ALTER TABLESPACE space1 OFFLINE
                 *
ERROR at line 1:

gSQL> ALTER TABLESPACE space1 OFFLINE NORMAL;

ERR-42000(16290): OFFLINE NORMAL is only allowed if the database is in OPEN phase : 
ALTER TABLESPACE space1 OFFLINE NORMAL
                 *
ERROR at line 1:

Compatibility

The SQL standard does not define the concept of a tablespace.

For More Information

Refer to the following.

ALTER TABLESPACE name OFFLINE TABLES

Function

It alters the tables related to the tablespace to the OFFLINE state.

Syntax

<alter tablespace offline tables statement> ::=
    ALTER TABLESPACE tablespace_name OFFLINE TABLES [ <domain name> ]
    ;

Invocation and Access Rules

It can be performed in a cluster system.

The ALTER TABLESPACE ON DATABASE privilege is required to execute the <alter tablespace offline tables statement>.

Syntax Rules and Parameters

<domain name>

It is the name of the member or group on which the statement is performed.
If not specified, the statement is performed on all groups.

Description

<alter tablespace offline tables statement> must be executed in a cluster database when the system is in the OPEN phase, prior to executing ALTER TABLESPACE tablespace_name OFFLINE.

If TABLESPACE OFFLINE is performed without prior execution of this statement, the following error occurs.

gSQL> ALTER TABLESPACE space1 OFFLINE;

ERR-42000(16632): tables related to tablespace 'SPACE1' must be offline

The tables that are changed to the OFFLINE state are as follows:

The <alter tablespace offline tables statement> can be executed even if there are inactive members.

Examples

The following is an example of executing the <alter tablespace offline tables statement>.

gSQL> ALTER TABLESAPCE space1 OFFLINE TABLES;

Tablespace altered.

Compatibility

The SQL standard does not define the concept of the tablespace.

For More Information

Refer to ALTER TABLESPACE name [ONLINE|OFFLINE].

ALTER TABLESPACE name RENAME DATAFILE

Function

It renames the datafiles that configure the tablespace.

Syntax

<rename datafile statement> ::=
    ALTER TABLESPACE tablespace_name RENAME DATAFILE <filename_list> TO <filename_list>
    ;

    <filename_list> ::= 
        'filename' [, ...]

Invocation and Access Rules

The ALTER TABLESPACE ON DATABASE privilege is required to execute the <rename datafile statement>.

An ONLINE tablespace file can not be altered when it is in TDS mode and the database is in the OPEN phase. (Except for the temporary memory tablespace.)

The file must exist even after the alteration.

Syntax Rules and Parameters

tablespace_name

It is the name of the tablespace to be altered.

'filename'

The memory temporary tablespace refers to 'memory_name', while other types of tablespaces refer to 'filename'.

<domain name>

It is the name of the member or group on which the statement is performed.
If not specified, the statement is performed on all groups.

Description

The tablespace status determines whether the operation can be performed.

Example

The following is an example of renaming 'test.dbf' to 'test1.dbf'.

gSQL> ALTER TABLESPACE TEST_TBS RENAME DATAFILE 'test.dbf' TO 'test1.dbf';

Tablespace altered.

Compatibility

The SQL standard does not define the concept of a tablespace.

For More Information

Refer to the following.

ALTER TABLESPACE name RENAME TO

Function

It renames the tablespace.

Syntax

<rename tablespace statement> ::=
    ALTER TABLESPACE tablespace_name RENAME TO <new_tablespace_name>
    ;

Invocation and Access Rules

The ALTER TABLESPACE ON DATABASE privilege required to execute the <rename space statement>.

Syntax Rules and Parameters

tablespace_name

It is the name of the old tablespace.

new_tablespace_name

It is the name of the new tablespace.

Description

Even if the tablespace is renamed, the tables or indexes that were already created in the existing tablespace do not need to be renamed.

Example

The following is an example of renaming a tablespace.

gSQL> ALTER TABLESPACE space1 RENAME TO space2;

Tablespace altered.

Compatibility

The SQL standard does not define the concept of a tablespace.

For More Information

Refer to ALTER TABLESPACE.

ALTER USER

Function

It alters the user definition of the database.

Syntax

<alter user statement> ::=
      ALTER USER user_identifier <alter user action>
    | ALTER USER PUBLIC <alter schema path>
    ;

<alter user action> ::=
      <alter password>
    | <alter profile>
    | <password expire>
    | <account lock>
    | <alter default tablespace>
    | <alter temporary tablespace>
    | <alter schema path>

<alter password> ::=
    IDENTIFIED BY new_password [ REPLACE old_password ]

<alter profile> ::=
    PROFILE { profile_name | DEFAULT | NULL }

<password expire> ::=
    PASSWORD EXPIRE

<account lock> ::=
    ACCOUNT { LOCK | UNLOCK }

<alter default tablespace> ::=
    DEFAULT TABLESPACE tablespace_name

<alter temporary tablespace> ::=
    TEMPORARY TABLESPACE tablespace_name

<alter schema path> ::=
    SCHEMA PATH ( { schema_name | CURRENT PATH } [, ...] )

Invocation and Access Rules

The ALTER USER ON DATABASE privilege is required to execute the <alter user statement>.
However, <alter password> can be performed without any privileges when the user and user_identifier are identical.

Syntax Rules and Parameters

user_identifier

It is the username to be altered.

<alter password>

It alters the user's password.

<alter profile>

It alters the profile for the password management policy.

<password expire>

It expires the user's password.

<account lock>

<alter default tablespace>

It alters the user's default tablespace.
The tablespace_name must be a data tablespace.

<alter temporary tablespace>

It alters the user's temporary tablespace.
The tablespace_name must be a temporary tablespace.

<alter index tablespace>

It alters the user's index tablespace.

<alter schema path>

It alters the user's schema access path.
If the schema is not specified in the user's SQL statement, the schema access path is determined based on the schema order for object naming resolution.
If the schema name is the same as another schema previously listed, it is not applied.
The following is an example of objects existing in a schema when performing the ALTER USER u1 SCHEMA PATH ( u1, s2, public );  statement.

Schema name

u1

s2

public

-

t1

-

t1

-

-

t2

-

-

-

-

t3

The object name, whose schema is not specified, is interpreted by the SCHEMA PATH when user u1 executes the schema as follows.

CURRENT PATH

It is the schema path of the current user.
A new schema path can be added using CURRENT PATH, while maintaining the existing schema path as follows.

ALTER USER PUBLIC <alter schema path>

It alters the schema path of the PUBLIC account.
The schema path of the PUBLIC account is included in every user's schema path.
The initial schema path allocated to the PUBLIC account is as follows.

Description

For more information, refer to the syntax rules for each statement.

Examples

The following is an example of altering the user's password.

gSQL> ALTER USER u1 IDENTIFIED BY new_password;

User altered.

The following is an example of assigning a profile to the user.

gSQL> ALTER USER u1 PROFILE prof1;

User altered.

gSQL> COMMIT;

Commit complete.

The following is an example of dropping the user's profile.

gSQL> ALTER USER u1 PROFILE NULL;

User altered.

gSQL> COMMIT;

Commit complete.

The following is an example of expiring the user's password.

gSQL> ALTER USER u1 PASSWORD EXPIRE;

User altered.

gSQL> COMMIT;

Commit complete.

The following is an example of unlocking a user's account.

gSQL> ALTER USER u1 ACCOUNT UNLOCK;

User altered.

gSQL> COMMIT;

Commit complete.

The following is an example of altering the user's DEFAULT TABLESPACE.

gSQL> ALTER USER u1 DEFAULT TABLESPACE mem_data_tbs;

User altered.

The following is an example of altering the user's TEMPORARY TABLESPACE.

gSQL> ALTER USER u1 TEMPORARY TABLESPACE mem_temp_tbs;

User altered.

The following is an example of altering the user's INDEX TABLESPACE.

gSQL> ALTER USER u1 INDEX TABLESPACE mem_temp_tbs;

User altered.

The following is an example of altering the user's schema path.

gSQL> ALTER USER u1 SCHEMA PATH ( s1, CURRENT PATH );

User altered.

Compatibility

The SQL standard covers the concept of a user, but it does not define the SQL statements for creating, altering, or dropping a user.

For More Information

Refer to the following.

ALTER VIEW

Function

It alters the view definition.

Syntax

<alter view statement> ::=
    ALTER VIEW view_name <alter view action>
    ;

<alter view action> ::=
    COMPILE

Invocation and Access Rules

One of the following privileges is required to execute the <alter view statement>.

Syntax Rules and Parameters

view_name

It is the name of the view to be altered.
The schema to which the view belongs can be defined using the format schema_name.view_name. If schema_name is omitted, the default schema name of the user executing the statement is used.

COMPILE

It recompiles the view.
The COMMENT assigned to the view column is reset.

Description

When the table or view referenced by the view is altered or dropped, the view is also affected.
This information can be retrieved from the INFORMATION_SCHEMA.VIEWS.

Example

The following is an example of compiling a view that has been affected by a change in the table it references.

gSQL> SELECT TABLE_NAME, IS_AFFECTED 
        FROM INFORMATION_SCHEMA.VIEWS 
       WHERE TABLE_SCHEMA = 'PUBLIC'
         AND TABLE_NAME = 'V1';

TABLE_NAME IS_AFFECTED
---------- -----------
V1         TRUE       

1 row selected.


gSQL> ALTER VIEW v1 COMPILE;

View altered.

COMMIT;

Commit complete.

gSQL> SELECT TABLE_NAME, IS_AFFECTED 
        FROM INFORMATION_SCHEMA.VIEWS 
       WHERE TABLE_SCHEMA = 'PUBLIC'
         AND TABLE_NAME = 'V1';

TABLE_NAME IS_AFFECTED
---------- -----------
V1         FALSE      

1 row selected.

Compatibility

The SQL standard does not define the <alter view statement>.

For More Information

Refer to the following.

ANALYZE SYSTEM

Function

It controls the system's statistical information.

Syntax

<analyze system statement> ::=
    ANALYZE SYSTEM [ <analyze action> ]
    ;

<analyze action> ::=
      COMPUTE STATISTICS
    | DELETE STATISTICS

Invocation and Access Rules

The ANALYZE ANY ON DATABASE privilege is required to execute the <analyze system statement>.

Syntax Rules and Parameters

<analyze action>

When omitted, the default value is COMPUTE STATISTICS.

COMPUTE STATISTICS

It builds the following statistical information related to the system.

DELETE STATISTICS

It deletes the system's statistical information.

Description

The built system statistical information is used to calculate the cost of the optimization process for query processing.

Examples

The following is an example of building the system's statistical information using the <analyze system statement>.

gSQL> ANALYZE SYSTEM COMPUTE STATISTICS;

analyzed.

The following is an example of retrieving the system's built statistical information.

gSQL> 
SELECT * FROM DBA_STAT_SYSTEM;

 CPU_OPS NETWORK_IOPS NETWORK_BUFSIZE BUFFER_MISS_PERCENT LAST_ANALYZED             
-------- ------------ --------------- ------------------- ---------------------------
53000412         2914           65536                  99  2017-03-30 16:49:42.200000

1 row selected.

Compatibility

The SQL standard does not define the concept of statistical information.

For More Information

Refer to ANALYZE TABLE.

ANALYZE TABLE

Function

It controls the table's statistical information.

Syntax

<analyze table statement> ::=
    ANALYZE TABLE table_name
    [ <parallel clause> ]
    [ <analyze action> ]
    ;

<parallel clause> ::=
      NOPARALLEL
    | PARALLEL [thread_count]

<analyze action> ::=
      COMPUTE STATISTICS [ <for_clause> | <for_clause_extension> ]
    | ESTIMATE STATISTICS <sample_clause> [ <for_clause> | <for_clause_extension> ]
    | DELETE STATISTICS
    | DELETE STATISTICS <for_clause_extension>

<sample_clause>
      SAMPLE row_count ROWS
    | SAMPLE percentage PERCENT

<for_clause>
      FOR ALL COLUMNS
    | FOR ALL INDEXED COLUMNS
    | FOR COLUMNS column_name [, ...]
    | FOR ALL INDEXES
    | FOR INDEXES index_name [, ...]

<for_clause_extension>
    FOR COLUMN GROUPS( column_name [, ...] )

Invocation and Access Rules

The ANALYZE ANY ON DATABASE privilege is required to execute the <analyze table statement>.

Syntax Rules and Parameters

table_name

It is the name of the table. 
The schema to which the table belongs can be defined using the format schema_name.table_name. If schema_name is omitted, the default schema name of the user executing the statement is used.

<parallel clause>

It specifies the number of threads to be used in the analysis process.
If not specified, the default value is PARALLEL.

<analyze action>

When omitted, the default value is COMPUTE STATISTICS.

COMPUTE STATISTICS

It builds the following statistical information related to the table through a full scan.

The statistical information built according to the column's data type is as follows.

Statistical information built according to the column's data type

Data type

NUM_DISTINCT

NUM_NULLS

AVG_LENGTH

MIN/MAX

Height-balanced Histogram

Frequency Histogram

BOOLEAN

O

O

O

X

X

O

NATIVE_SMALLINT

O

O

O

O

O

O

NATIVE_INTEGER

O

O

O

O

O

O

NATIVE_BIGINT

O

O

O

O

O

O

NATIVE_REAL

O

O

O

O

O

O

NATIVE_DOUBLE

O

O

O

O

O

O

NUMBER

O

O

O

O

O

O

NUMERIC

O

O

O

O

O

O

FLOAT

O

O

O

O

O

O

CHAR(n)

O

O

O

It is built when the size is 64 bytes or smaller.

It is built when the size is 64 bytes or smaller.

It is built when the size is 64 bytes or smaller.

VARCHAR(n)

O

O

O

It is built when the size is 64 bytes or smaller.

It is built when the size is 64 bytes or smaller.

It is built when the size is 64 bytes or smaller.

LONG VARCHAR

X

X

X

X

X

X

BINARY

O

O

O

X

X

X

VARBINARY

O

O

O

X

X

X

LONG VARBINARY

X

X

X

X

X

X

DATE

O

O

O

O

O

O

TIME

O

O

O

O

O

O

TIMESTAMP

O

O

O

O

O

O

INTERVAL

O

O

O

O

O

O

ROWID

O

O

O

X

X

X

ESTIMATE STATISTICS <sample_clause>

It builds the statistical information for the column and index using as many samples as specified in the <sample_clause>.

If the number of sampling rows is smaller than the value specified in the MIN_SAMPLE_ROW_COUNT property, the property value will be used instead.

<for_clause>

If omitted, it builds the statistical information for all columns and indexes for which statistics can be generated.

FOR ALL COLUMNS

It builds the statistical information for all columns for which statistics can be generated.
It does not build the statistical information for indexes.

FOR ALL INDEXED COLUMNS

It builds the statistical information for all columns included in an index.
It does not build the statistical information for other columns.
It does not build the statistical information for the index itself.

FOR COLUMNS column_name [, ...]

It builds the statistical information for the listed columns.
It does not build the statistical information for unlisted columns.
It does not build the statistical information for any indexes.

FOR ALL INDEXES

It builds the statistical information for all indexes.
It does not build the statistical information for columns.

FOR INDEXES index_name [, ...]

It builds the statistical information for the listed indexes.
It does not build the statistical information for unlisted indexes.
It does not build the statistical information for any columns.

FOR COLUMN GROUPS ( column_name [, ...] )

The statement builds the NUM_DISTINCT value for a COLUMN GROUP combination.
For example, if the item table has columns (i_company, i_brand), the value 'I-PHONE' in the i_brand column has a strong dependency on the value 'APPLE' in the i_company column.
Building statistical information for the FOR COLUMN GROUP (i_company, i_brand) helps the optimizer analyze queries like the following.
SELECT * 
  FROM item, sales, ...
 WHERE i_brand = 'I-PHONE'
   AND i_company = 'APPLE' 
   AND i_item_id = s_item_id
   AND ...

DELETE STATISTICS

It deletes the statistical information of the table.

DELETE STATISTICS FOR COLUMN GROUPS ( column_name [, ...] )

It deletes the statistical information for the specified COLUMN GROUP.

Description

Table statistics information is crucial as it affects the accuracy of query optimization.

The time to build statistical information increases in proportion to the amount of data in the table. Therefore, when the data volume is large, it is recommended to build statistics using sampling or to build statistics only for the key information that affects queries.

ANALYZE TABLE lineitem ESTIMATE STATISTICS SAMPLE 10 PERCENT;
ANALYZE TABLE lineitem COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;
ANALYZE TABLE lineitem COMPUTE STATISTICS FOR ALL INDEXES;

Examples

The following is an example of building the statistical information through a full scan.

gSQL> ANALYZE TABLE orders;

Table analyzed.

The following is an example of retrieving the statistical information that has been built for the table.

gSQL>
SELECT 
       TABLE_NAME
     , NUM_ROWS
  FROM
       DICTIONARY_SCHEMA.USER_TABLES
 WHERE
       TABLE_SCHEMA = 'PUBLIC'
   AND TABLE_NAME   = 'ORDERS'
;

TABLE_NAME NUM_ROWS
---------- --------
ORDERS      1500000

1 row selected.


gSQL>
SELECT 
       TABLE_NAME
     , COLUMN_NAME
     , NUM_DISTINCT
     , NUM_NULLS
     , LOW_VALUE
     , HIGH_VALUE
  FROM
       DICTIONARY_SCHEMA.USER_TAB_COLUMNS
 WHERE
       TABLE_SCHEMA = 'PUBLIC'
   AND TABLE_NAME   = 'ORDERS'
;

TABLE_NAME COLUMN_NAME     NUM_DISTINCT NUM_NULLS LOW_VALUE           HIGH_VALUE         
---------- --------------- ------------ --------- ------------------- -------------------
ORDERS     O_ORDERKEY           1500000         0 1                   6000000            
ORDERS     O_CUSTKEY              99996         0 1                   149999             
ORDERS     O_ORDERSTATUS              3         0 F                   P                  
ORDERS     O_TOTALPRICE         1464556         0 857.71              555285.16          
ORDERS     O_ORDERDATE             2406         0 1992-01-01 00:00:00 1998-08-02 00:00:00
ORDERS     O_ORDERPRIORITY            5         0 1-URGENT            5-LOW              
ORDERS     O_CLERK                 1000         0 Clerk#000000001     Clerk#000001000    
ORDERS     O_SHIPPRIORITY             1         0 0                   0                  
ORDERS     O_COMMENT            1482071         0 null                null               

9 rows selected.

gSQL>
SELECT 
       TABLE_NAME
     , INDEX_NAME
     , DISTINCT_KEYS
  FROM
       DICTIONARY_SCHEMA.USER_INDEXES
 WHERE
       TABLE_SCHEMA = 'PUBLIC'
   AND TABLE_NAME   = 'ORDERS'
;

TABLE_NAME INDEX_NAME        DISTINCT_KEYS
---------- ----------------- -------------
ORDERS     ORDERS_PK_INDEX         1500000
ORDERS     ORDERS_CUSTKEY_FK         99996

2 rows selected.

Compatibility

The SQL standard does not define the concept of statistical information.

For More Information

Refer to ANALYZE SYSTEM.

AUDIT POLICY

Function

It activates the audit policy.

Syntax

<audit policy statement> ::= 
    AUDIT POLICY policy_name
    [ <specified_user_option> ]
    [ <specified_success_option> ]
    ;

<specified_user_option> ::=
      BY user_name [, ...]
    | EXCEPT user_name [, ...]

<specified_success_option> ::=
      WHENEVER SUCCESSFUL
    | WHENEVER NOT SUCCESSFUL

Invocation and Access Rules

The AUDIT SYSTEM ON DATABASE privilege is required to execute the <audit policy statement>.

Syntax Rules and Parameters

policy_name

It is the name of the audit policy object to be activated.
The activated audit policy does not affect existing sessions, and it only applies to newly created sessions.

<specified_user_option>

It specifies the user to be audited.
If omitted, all users will be audited.

The BY clause and the EXCEPT clause can not be used together in the same audit policy.

<specified_success_option>

Description

Activating the audit policy does not affect existing sessions, but it will begin auditing newly created sessions.

Retrieving Audit Record

An audit record is created when it matches the audit policy and can be retrieved through the DICTIONARY_SCHEMA.AUDIT_TRAIL view as shown below.
SELECT logon_user
     , event_timestamp
     , action_name
     , object_name 
     , sql_text
  FROM audit_trail
 WHERE policy_name = 'P1'
;

The SELECT privilege must be granted to an ordinary user in order to retrieve the AUDIT_TRAIL.

GRANT SELECT ON DICTIONARY_SCHEMA.AUDTI_TRAIL TO user_name;

Retrieving Audit Policy Information

Information about the audit policy object can be retrieved through the DICTIONARY_SCHEMA.AUDIT_POLICY_OPTIONS view.

SELECT policy_name
     , audit_option
     , object_schema
     , object_name
  FROM audit_policy_options
;

Information about whether the audit policy object is activated can be retrieved through the DICTIONARY_SCHEMA.AUDIT_POLICY_ENABLED view.

SELECT policy_name
     , enabled_opt
     , user_name
     , when_success
     , when_failure
  FROM audit_policy_enabled
;

Cautions When Using BY and EXCEPT Clauses

Activate the user group if multiple AUDIT POLICY BY clauses are used for the same audit policy.
In other words, the following two examples are equivalent.
AUDIT POLICY p1 BY u1;
AUDIT POLICY p1 BY u2;
AUDIT POLICY p1 BY u1, u2;
If multiple AUDIT POLICY EXCEPT clauses are used for the same audit policy, only the last AUDIT POLICY clause is valid.
In other words, the following two examples have different meanings.
AUDIT POLICY p1 EXCEPT u1;
AUDIT POLICY p1 EXCEPT u2;
AUDIT POLICY p1 EXCEPT u1, u2;

BY and EXCEPT can not be used together for the same policy.

AUDIT POLICY p1 BY u1;
AUDIT POLICY p1 EXCEPT u2;
AUDIT POLICY p1 EXCEPT u1;
AUDIT POLICY p1;
If a user wants to convert an audit policy activated with the BY clause to the EXCEPT clause, or an audit policy activated with the EXCEPT clause to the BY clause, the activated audit policy must first be deactivated before it can be converted.

Deactivate the audit policy using the NOAUDIT POLICY statement as shown below.

The WHENEVER clause used with the BY clause is accumulated.

The following two examples are equivalent.

AUDIT POLICY p1 BY u1 WHENEVER SUCCESSFUL;
AUDIT POLICY p1 BY u1 WHENEVER NOT SUCCESSFUL;
AUDIT POLICY p1 BY u1;

If WHENEVER clauses are used with the EXCEPT clause, only the last WHENEVER clause is valid.

The following two examples have different meanings.

AUDIT POLICY p1 EXCEPT u1 WHENEVER SUCCESSFUL;
AUDIT POLICY p1 EXCEPT u1 WHENEVER NOT SUCCESSFUL;
AUDIT POLICY p1 EXCEPT u1;

Examples

The following is an example of activating the audit policy for all users.

AUDIT POLICY table_pol;

Information about the activation can be viewed using the following query.

SELECT policy_name
     , enabled_opt
     , user_name
  FROM audit_policy_enabled
 WHERE policy_name = 'TABLE_POL';

POLICY_NAME  ENABLED_OPT  USER_NAME
-----------  -----------  ---------
TABLE_POL    BY           ALL USERS

The following is an example of activating the audit policy by specifying specific users.

AUDIT POLICY dml_pol BY u1, u2;

The following is an example of activating the audit policy by excluding a specific user.

AUDIT POLICY read_seq_pol EXCEPT sys;

The following is an example of auditing the failure of a SQL statement by a specific user.

AUDIT POLICY delete_pol BY u1 WHENEVER NOT SUCCESSFUL;

Compatibility

The SQL standard does not include audit policy.

For More Information

Refer to the following.