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> |  <action_audit_clause> | <privilege_audit_clause> <action_audit_clause> }

<drop_audit_option> ::=
    DROP { <privilege_audit_clause> |  <action_audit_clause> | <privilege_audit_clause> <action_audit_clause> }


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

<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

AUDIT SYSTEM ON DATABASE privilege is required to perform <alter audit policy statement>.

Syntax Rules and Parameters

policy_name

It is the name of an 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.

<action_audit_clause>

For more information, refer to CREATE AUDIT POLICY.

Description

It can alter an audit policy which is already activated, and it does not effect the existing session but it effects only the newly created session.

When dropping 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 have the audit policy.

For More Information

Refer to the followings.

ALTER CLUSTER GROUP name ADD MEMBER

Function

It adds a cluster member to a 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.
ADMINISTRATION ON DATABASE privilege is required to perform <alter cluster group add member statement>.

Syntax Rules and Parameters

group_name

It is the cluster group name.

<cluster member definition>

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

member_name

It is the name of a cluster member.
The cluster member name should be as same as the member name which was defined when the database of that cluster member was created.
There should not be the same cluster group, nor the same cluster member.
The length of the name should be shorter than 128 bytes.

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

<connection attribute>

It defines the connection information for the communication between the cluster members.
<connection attribute> should be as same as the HOST and PORT which were defined when the database of that cluster member was created.
The combination of HOST and PORT should be unique in the cluster system.

<member position>

It assigns the position number of the cluster member.

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

SELECT member_name, member_id, member_position FROM dba_cluster;

If the following position numbers are being used,

The following values are assigned according to each option.

Description

<alter cluster group add member statement> statement does not rebalance shards in the tables.
The following statement should be performed to rebalance 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 concepts of the cluster.

For More Information

Refer to the followings.

ALTER CLUSTER GROUP name OFFLINE MEMBER

Function

It sets a cluster member of 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.
ADMINISTRATION ON DATABASE privilege is required to perform <alter cluster group offline member statement>.

Syntax Rules and Parameters

group_name

It is the cluster group name.

member_name

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

Description

It sets the inactive cluster member to offline.

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

Examples

If trying to set the cluster member which is not inactive to offline, then the following error occurs.

gSQL>

ALTER CLUSTER GROUP g1 OFFLINE CLUSTER MEMBER g1n2;

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

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

gSQL>

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

Compatibility

The SQL standard does not define the concepts of the cluster.

For More Information

Refer to the followings.

ALTER CLUSTER LOCATION

Function

It alters a 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.
ADMINISTRATION ON DATABASE privilege is required to perform <alter cluster location statement>.

Syntax Rules and Parameters

member_name

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

<cluster connection attribute>

It defines the connection information for the communication between the cluster members.
The combination of HOST and PORT should be unique in 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 by using 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 concepts of the cluster.

For More Information

Refer to the followings.

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

ALTER DATABASE ON DATABASE privilege is required to perform <alter database add logfile statement>.

Syntax Rules and Parameters

<alter database add logfile statement>

The database should be in MOUNT phase.

<add logfile member statement>

The 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 just in case for the file damage because the newly added 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 an 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 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 should be used, and multiple log files can be added to an existing group as a member.

Compatibility

The SQL standard does not define ALTER DATABASE statement.

For More Information

Refer to the followings.

ALTER DATABASE ARCHIVELOG

Function

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

Syntax

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

Invocation and Access Rules

ALTER DATABASE ON DATABASE privilege is required to perform <alter database archivelog statement>.

Syntax Rules and Parameters

<alter database archivelog statement>

Description

For the database backup and the media recovery using the backup, the system should be operated in ARCHIVELOG mode.

Example

The following is an example of how to set up a database to archive mode.

ALTER DATABASE ARCHIVELOG;

Compatibility

The SQL standard does not define ALTER DATABASE statement.

For More Information

Refer to the followings.

ALTER DATABASE BACKUP

Function

The backup state is set to ACTIVE or INACTIVE to perform a full backup of the database. Then, the incremental database backup and control file backup are performed.

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> [ AT <domain name> ]    ;

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

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

Invocation and Access Rules

ALTER DATABASE ON DATABASE privilege is required to perform <alter database backup statement>.

Syntax Rules and Parameters

<database begin backup clause>

The database is set to the state which the full backup is available.

<database end backup clause>

The database is set to the state which the full backup is not available.

<database incremental backup statement>

<incremental backup option>

<database controlfile backup statement>

The maximum length of the 'target_name' managed by GOLDILOCKS is 1024 bytes. However, the maximum lengths of the file name varies depending on the OS, so the actual length of 'target_name' which is available to be created can be shorter than 1024 bytes.

<domain name>

It is a name of a member or a group for which the statement is performed.
If it is omitted, it is performed for all groups.

Description

It backs up data files and control files in the database. A full backup of the database begins with BEGIN BACKUP, and copies the datafiles using OS file copy, then 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 to create the incremental backup of LEVEL 1 by using DIFFERENTIAL.

ALTER DATABASE BACKUP INCREMENTAL LEVEL 1 DIFFERENTIAL;

The following is an example to create the 'controlfile.bak' backup file for the control file. If the absolute path is not included, a backup file is created in the path set by the LOG_DIR property.

ALTER DATABASE BACKUP CONTROLFILE TO 'controlfile.bak';

Compatibility

The SQL standard does not define ALTER DATABASE statement.

For More Information

Refer to the followings.

ALTER DATABASE CLEAR AUDIT TRAIL

Function

It purges audit records which are accumulated when applying an audit policy.

Syntax

<clear audit trail statement> ::= 
    ALTER DATABASE CLEAR AUDIT TRAIL
;

Invocation and Access Rules

AUDIT SYSTEM ON DATABASE privilege is required to perform <clear audit trail statement>.

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

A user should purge an audit trail after storing it according to the following procedure to store an audit trail when it is necessary.

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;

Examples

Purge an audit trail by using the following statement.

ALTER DATABASE CLEAR AUDIT TRAIL;

Compatibility

The SQL standard does not have the audit policy.

For More Information

Refer to the followings.

ALTER DATABASE CLEAR PASSWORD HISTORY

Function

It deletes the user's password change history which is accumulated due by applying the profile.

Syntax

<clear password history statement> ::= 
    ALTER DATABASE CLEAR PASSWORD HISTORY
    ;

Invocation and Access Rules

ALTER DATABASE ON DATABASE privilege is required to perform <clear password history statement>.

Description

When a profile is applied to a user, the user's password change history is accumulated according to 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 value range, and the change history out of the value range is automatically deleted.

value

UNLIMITED

It accumulates all change history and it does not delete any change history because all change history should be checked.

UNLIMITED

value

It accumulates all change history and it does not delete any change history because all change history should be checked.

UNLIMITED

UNLIMITED

It does not manage the change history because the change history is not checked.

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

Examples

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

Database altered.

gSQL> COMMIT;

Commit complete.

Compatibility

The SQL standard does not define ALTER DATABASE statement.

For More Information

Refer to the followings.

ALTER DATABASE DATAFILE AUTOEXTEND

Function

It alters the property to automatically extend disk tablespace data file. If the property is ON, then the size to be extended and the maximum size of the data file also can 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

ALTER DATABASE ON DATABASE privilege is required to perform <alter database datafile autoextend statement>.

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

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 it is set to ON, then it can specify the automatic expanded size and the maximum size of the data file.

<next size clause>

It specifies the size to be extended when the data file in use does not have available space.

<max size clause>

It specifies the maximum expanded size of the data file.

Description

Refer to the syntax rules of each statement.

Examples

The following is an example of altering the automatic expand property of the datafile, the automatic expanded size, and 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 concepts 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 incremental backup. It can delete all incremental backup of the database or no longer usable obsolete backup.

Syntax

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

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

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

Invocation and Access Rules

ALTER DATABASE ON DATABASE privilege is required to perform <alter database delete backup statement>.

Syntax Rules and Parameters

<alter database delete backup statement>

The database should be in MOUNT or OPEN phase.

<delete backup list option>

It selects the backups to be deleted among the existing incremental backups.

<including backup file option>

Description

Deletion of the OBSOLETE incremental backup deletes the incremental backup of which is before the most recent LEVEL 0 database backup. When non-LEVEL 0 incremental backup is performed, it is not deleted even if it includes the previously performed incremental backup. It is because it can be used when performing the incomplete recovery by using the incremental backups.

Be cautious of deleting the backup file together when an incremental backup is deleted. It can not be recovered even by using the control file which has incremental backup information.

Example

The following is an example 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 ALTER DATABASE statement.

For More Information

Refer to the followings.

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.
ADMINISTRATION ON DATABASE privilege is required to perform <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 that it is not connected to the cluster system, and it occurs in the following cases.

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

Also, if there is a possibility of data loss when dropping the cluster member, then an inactive cluster member can not be dropped. The data is not lost when it is guaranteed that the data in replica of the table or the shard which belongs the inactive cluster member to be dropped is not latest comparing to that in the members of that cluster group. Therefore, an inactive cluster member can be dropped when at least one online member exists in the same cluster group in case for the sharded table, and in the entire cluster in case for the cloned table.

However, if an online cluster member does not exist in the cluster group and the service is not available due to an inactive cluster member, then the inactive cluster member can be dropped by using FORCE option despite of the possibility of the data loss.

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

Examples

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

gSQL> ALTER DATABASE DROP INACTIVE CLUSTER MEMBERS;

Database altered.

Compatibility

The SQL standard does not define the concepts of the cluster.

For More Information

Refer to ALTER SYSTEM JOIN DATABASE.

ALTER DATABASE DROP LOGFILE

Function

It drops a log file group or a member which 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

ALTER DATABASE ON DATABASE privilege is required to perform <alter database drop logfile statement>.

Syntax Rules and Parameters

<alter database drop logfile statement>

The database should be in MOUNT phase.
An error occurs when the log file to be deleted is in CURRENT or ACTIVE stage.
At least four log file groups should be remained 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 rules for each syntax.

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 ALTER DATABASE statement.

For More Information

Refer to the respective syntax rules, and the followings.

ALTER DATABASE DROP OFFLINE SEGMENTS

Function

It drops 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 in the cluster system.

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

Description

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

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

<alter database drop offline segments statement> performs <alter table drop offline segments statement> per each table, and it is equivalent to the sum of 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;

<alter database drop offline segments statement> is not terminated even when an error occurs in a specific table, but proceeds in 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 means that one of the five tables failed.

If <alter database drop offline segments statement> is performed again after taking an appropriate action for the error, then it is operated only for the failed table.

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

Example

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

gSQL> ALTER DATABASE DROP OFFLINE SEGMENTS;

Database altered.

Compatibility

The SQL standard does not define the concepts of the cluster.

For More Information

Refer to ALTER TABLE name DROP OFFLINE SEGMENTS.

ALTER DATABASE MOVE SHARD

Function

It rebalances shard 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 ] 
       [ <shard divisor> ] 
       [ <parallel clause> ]
    ;

<shard divisor> ::= 
    SHARD DIVISOR integer

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

Invocation and Access Rules

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

Syntax Rules and Parameters

src_cluster_group

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

dest_cluster_group

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

[ ONLINE | OFFLINE ]

It determines whether to allow DML when rebalancing table shard.

<shard divisor>

It specifies the number of shard's partitions.

<parallel clause>

It specifies the number of threads to use when rebalancing the table.

Description

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

Perform <alter database rebalance statement> to rebalance the shard of the entire table which was not rebalanced when adding a cluster group and a cluster member.

<alter database move shard statement> is performed as the following concepts for tables which did not rebalance the shard.

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 above for all tables except for a CLONED table and a CLUSTER WIDE table. The <alter database move shard statement> is proceeded even when the rebalancing the shard of a specific table fails. It does not rollback the table which succeeded in rebalancing the shard.

Therefore, when performing <alter database move shard statement> again after appropriately processed an error, then it rebalances only the shard for the table requiring the rebalancing. In this case, the table which succeeded in rebalancing the shard is not included in a target of the rebalancing.

Examples

The following is an example of performing <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 concepts of the cluster.

For More Information

Refer to the followings.

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

It can be performed in a cluster system.
ADMINISTRATION ON DATABASE privilege is required to perform <alter database offline inactive cluster members statement>.

Syntax Rules and Parameters

It sets the entire inactive cluster member to offline.
The inactive state of a cluster member means that it is not connected to the cluster system, and it occurs in the following cases.

Description

It is recommended to use <alter database offline inactive members statement> when an inactive cluster member can not be included in the cluster system any more.

If an inactive cluster member can participate in a cluster system, then perform ALTER SYSTEM JOIN DATABASE to include it in a cluster system.

The cluster member which is set to offline can be shifted to online again by using the following statements after the join.

Examples

gSQL> ALTER DATABASE OFFLINE INACTIVE CLUSTER MEMBERS;

Compatibility

The SQL standard does not define the concepts of the cluster.

For More Information

Refer to the followings.

ALTER DATABASE REBALANCE

Function

It rebalances shard of all tables.

Syntax

<alter database rebalance statement> ::=
    ALTER DATABASE REBALANCE
       [ ONLINE | OFFLINE ] 
       [ <shard divisor> ] 
       [ <parallel clause> ]
    ;

<shard divisor> ::= 
    SHARD DIVISOR integer

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

Invocation and Access Rules

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

Syntax Rules and Parameters

[ ONLINE | OFFLINE ]

It determines whether to allow DML when rebalancing table shard.

<shard divisor>

It specifies the number of shard's partitions.

<parallel clause>

It specifies the number of threads to use when rebalancing the table.

Description

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

Perform <alter database rebalance statement> to rebalance the shard of the entire table which was not rebalanced when adding a cluster group and a cluster member.

<alter database rebalance statement> is performed as the following concepts for tables which did not rebalance the shard.

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> is proceeded even when the rebalancing the shard of a specific table fails. It does not rollback the table which succeeded in rebalancing the shard.
Therefore, when performing <alter database rebalance statement> again after appropriately processed an error, then it rebalances only the shard for the table requiring the rebalancing. In this case, the table which succeeded in rebalancing the shard is not included in a target of the rebalancing.

Examples

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

gSQL> ALTER DATABASE REBALANCE;

Database altered.

Compatibility

The SQL standard does not define the concepts of the cluster.

For More Information

Refer to ALTER TABLE name REBALANCE.

ALTER DATABASE REBALANCE EXCLUDE CLUSTER GROUP

Function

It rebalances shard of all tables excluding 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 ]        
       [ <shard divisor> ]        
       [ <parallel clause> ]
    ;

<shard divisor> ::= 
    SHARD DIVISOR integer

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

Invocation and Access Rules

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

Syntax Rules and Parameters

cluster_group_name

It is a name of the cluster group excluding a shard of the table.
If the specified cluster group is the only cluster group, then the statement can not be performed.

[ ONLINE | OFFLINE ]

It determines whether to allow DML when rebalancing table shard.

<shard divisor>

It specifies the number of shard's partitions.

<parallel clause>

It specifies the number of threads to use when rebalancing the table.

Description

To drop a cluster group by using DROP CLUSTER GROUP, there should not be a shard in the cluster group.

Perform <alter database rebalance exclude cluster group statement> to exclude a shard from the cluster group. <alter database rebalance exclude cluster group statement> is performed as the following concepts for tables which include a shard in the cluster group.

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 the lack of storage space, it does not rollback the table which succeed in excluding a shard.
Therefore, when performing <alter database rebalance exclude cluster group statement> again after appropriately processed an error, then it excludes and rebalances only the shard for the table requiring the rebalancing. In this case, the table which succeeded in excluding the shard is not included in a target of the rebalancing.

Examples

The following is an example of performing <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 concepts of the cluster.

For More Information

Refer to the followings.

ALTER DATABASE RECOVER

Function

It recovers the entire data file or part of the data files in the database by 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

<datafile recover statement> ::=
    ALTER DATABASE RECOVER DATAFILE <datafile recovery clause>

<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

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

<batch incomplete recovery statement> ::=
    ALTER DATABASE RECOVER <until clause> [<using backup controlfile option>]
    
<until clause> ::=
      UNTIL CHANGE integer

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

<interactive incomplete recovery statement> ::=
    ALTER DATABASE <incomplete recovery option> [<using backup controlfile option>]

<incomplete recovery option> ::=
      BEGIN INCOMPLETE RECOVERY
    | END INCOMPLETE RECOVERY
    | RECOVER 'logfile name'
    | RECOVER AUTOMATICALLY
    | RECOVER SUGGESTION
    ;

Invocation and Access Rules

ALTER DATABASE ON DATABASE privilege is required to perform <alter database recover statement>.

Syntax Rules and Parameters

<complete database recover statement>

The data files of the database are recovered up to date by using the online and archive log files.

<datafile recover statement>

It recovers the backuped datafile, the datafile of the tablespace which requires the recovery by using the archive logfile due to an error during the backup, or the datafile of the tablespace which was set to offline by an immediate option, to the latest status.

<complete tablespace recover statement>

The data files of the tablespace is recovered up to date.

<incomplete database recover statement>

<batch incomplete database recover statement>

The datafiles in the database are recovered in a batch up to a specific point of time by using the online and archive logfile.

<interactive incomplete database recover statement>

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

Description

Incomplete recovery of the database is not easy to find a recovery completion point at a time. Therefore, the desired recovery point is found by performing it several times.
However, it becomes a new database if the database is started up with RESETLOGS option after an incomplete recovery. Therefore, the incomplete recovery should be performed several times after creating a copy of the archived log files and online redo log files.

Examples

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

ALTER DATABASE RECOVER;

The following is an example of a datafile recovery.

ALTER DATABASE RECOVER DATAFILE 'test.dbf';

The following is an example of a tablespace recovery.

ALTER DATABASE RECOVER TABLESPACE test_tbs;

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

ALTER DATABASE RECOVER UNTIL CHANGE 11123;

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 ALTER DATABASE statement.

For More Information

Refer to the followings.

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

ALTER DATABASE ON DATABASE privilege is required to perform <alter database register statement>.

Syntax Rules and Parameters

<alter database register statement>

It registers the unrecoverable segments in the database. The statement can be used on the assumption that the segment is not used any more, when the database is not recoverable and the backup does not exist.

<segment physical identifier list>

The list of unrecoverable segment identifier
• Integer: 8 bytes integer segment identifier

Description

When a server restarts after abnormal termination, the database performs the recovery process. During this process, it executes pages again by using the REDO log to recover pages which was not reflected in the disk in the previous service stage.

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

Example

The following is an example of giving up the recovery of the segment whose identifier is 4028679323648.

ALTER DATABASE REGISTER IRRECOVERABLE SEGMENT 4028679323648;

Compatibility

The SQL standard does not define ALTER DATABASE statement.

For More Information

Refer to the followings.

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

ALTER DATABASE ON DATABASE privilege is required for performing <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 rules for each syntax.

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

ALTER DATABASE ON DATABASE privilege is required for performing <alter database rename logfile statement>.

Syntax Rules and Parameters

<alter database rename logfile statement>

Description

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

Example

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

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

Compatibility

The SQL standard does not define ALTER DATABASE statement.

For More Information

Refer to the followings.

ALTER DATABASE RESET LOCAL CLUSTER MEMBER

Function

It resets the local cluster member except for the tablespace object to the time of creating the database.

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 should be LOCAL OPEN.
ADMINISTRATION ON DATABASE privilege is required to perform <alter database reset local cluster member statement>.

Description

It resets the local cluster member except for the tablespace object to the time of creating the database. It drops all objects created by a user except for the tablespace object.
<alter database reset local cluster member statement> statement resets an inactive cluster member, and makes the new cluster member to participate in a cluster system.
An inactive cluster member which is disconnected from the cluster system is processed as follows.

In this case, the device corresponding to the cluster member which is excluded from a cluster system can be used again by using the following two methods.

The method 2 reduces the cost of recreating the tablespace comparing to the method 1.

Examples

The following is an example of a reset by using <alter database reset local cluster member statement> after driving the local cluster member, which is excluded from the cluster system, up to 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 concepts of the cluster.

For More Information

Refer to the followings.

ALTER DATABASE RESTORE

Function

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

Syntax

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

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

<until clause> ::=
    UNTIL CHANGE integer

<tablespace restore statement> ::=
    ALTER DATABASE RESTORE TABLESPACE tablespace_name

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

Invocation and Access Rules

ALTER DATABASE ON DATABASE privilege is required to perform <alter database restore statement>.

Syntax Rules and Parameters

<database restore statement>

It restores the data files in the database by using the incremental backup. 
The database should be in MOUNT phase.

<tablespace restore statement>

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

<controlfile restore statement>

The control file is recovered using 'file_name'.

Description

The data recovery using full backup uses OS copy command to directly copy the backup file to the data file path. The data recovery using incremental backup restores only the deleted data files or old data files.

Examples

The following is an example of recovering the database by using the incremental backup.

ALTER DATABASE RESTORE;

The following is an example of recovering the tablespace by using the incremental backup.

ALTER DATABASE RESTORE TABLESPACE test_tbs;

The following is an example of recovering the database by using only the incremental backup whose LSN is smaller than 11123.

ALTER DATABASE RESTORE UNTIL CHANGE 11123;

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

ALTER DATABASE RESTORE CONTROLFILE FROM 'controlfile.bak'

Compatibility

The SQL standard does not define ALTER DATABASE statement.

For More Information

Refer to the followings.

ALTER DATABASE SYNCHRONIZE

Function

It remotely synchronizes shards and sequences in all tables.

Syntax

<alter database synchronize statement> ::=
    ALTER DATABASE SYNCHRONIZE 
       [ <synchronize target> ] 
       [ ONLINE | OFFLINE ] 
       [ <shard divisor> ] 
       [ <parallel clause> ]
    ;

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

<shard divisor> ::= 
    SHARD DIVISOR integer

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

Invocation and Access Rules

It can be performed in the cluster system.

ALTER DATABASE ON DATABASE privilege is required to perform <alter database synchronize statement>.

Syntax Rules and Parameters

<synchronize target>

It specifies the synchronization target object.

[ ONLINE | OFFLINE ]

It determines whether to allow DML when performing the synchronization.

<shard divisor>

It specifies the number of shard's partitions.

If <synchronize target> specifies only SEQUENCE, then it is ignored.

<parallel clause>

It specifies the number of threads to use when synchronizing the table.

If <synchronize target> specifies only SEQUENCE, then it is ignored.

Description

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

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

<alter database synchronize statement> performs <alter table synchronize statement> per each table, and it is equivalent to the sum of following queries.

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

...

ALTER TABLE tn SYNCHRONIZE;
COMMIT;

<alter database synchronize statement> is not terminated even when an error occurs while synchronizing a specific table, but proceeds to synchronize the next table, and succeeds with 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 means that one of the five tables failed.

If <alter database synchronize statement> is performed again after taking an appropriate action for the error, then it is operated only for the failed table.

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

Example

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

gSQL> ALTER DATABASE SYNCHRONIZE;

Database altered.

Compatibility

The SQL standard does not define the concepts of the cluster.

For More Information

Refer to the followings.

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

Invocation and Access Rules

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

Syntax Rules and Parameters

<alter index physical attribute statement>

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

<rename index statement>

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

<aging index statement>

It deletes the empty page 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 drops the index fragmentation.
For more information, refer to ALTER INDEX name COALESCE.

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 concepts of the index.

ALTER INDEX name AGING

Function

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

Syntax

<aging index statement> ::=
    ALTER INDEX index_name AGING
    ;

Invocation and Access Rules

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

Syntax Rules and Parameters

index_name

It is the name of the target index.

Description

This syntax returns pages whose all keys are deleted among index pages to a segment. Aging is processed in two steps which are logical deletion and physical deletion. A logical deletion is disconnection of index page, and it is performed when SCN of when deleting the last key of a page is smaller than the agable SCN of the system. Then the physical deletion is performed when the SCN of the logical deletion is smaller then the agable SCN of the system.

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

Examples

The following is an example of aging the index.

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 concepts of the index.

For More Information

Refer to the followings.

ALTER INDEX name COALESCE

Function

It coalesces adjacent leaf pages of the index so that it decreases the index space in use. It can be performed concurrently with DML.

Syntax

<index coalesce statement> ::=
    ALTER INDEX index_name COALESCE
    ;

Invocation and Access Rules

The user should satisfy the following conditions to perform <index coalesce statement>.

At least one of the following privileges for a 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 the user's default schema name is used when it is omitted.

Description

Index coalesce

Index coalesce

Comparing to 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 concepts of the index.

For More Information

Refer to the followings.

ALTER INDEX name REBUILD

Function

It rebuilds an index.

Syntax

<rebuild index statement> ::=
    ALTER INDEX index_name REBUILD
        [ ONLINE | OFFLINE ]
        [ <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>
    | MINSIZE <size_clause>
    | MAXSIZE <size_clause>

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

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

Invocation and Access Rules

The user should satisfy the following conditions to perform <rebuild index statement>.

At least one of the following privileges for a 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 the user's default schema name is used when it is omitted.

[ ONLINE | OFFLINE ]

It determines whether to allow DML on the table when 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 the unit is omitted, the default value is bytes.)

NOPARALLEL | PARALLEL [ integer ]

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

TABLESPACE tablespace_name

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

Description

Examples

The following is an example of altering the index logging setting and the 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 cover the concepts of the index.

For More Information

Refer to the followings.

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 perform <rename index statement>.

Syntax Rules and Parameters

index_name

It is the name of the target index.
The schema name can not be described and it has the same schema name as same as that of the existing index.

new_index_name

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

Description

Refer to the syntax rules of each statement.

Examples

The following is an example of altering the index name.

gSQL> ALTER INDEX t1_idx1 RENAME TO idx_t1_id;

Index altered.

Compatibility

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

For More Information

Refer to the followings.

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>
    | MINSIZE <size_clause>
    | MAXSIZE <size_clause>

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

Invocation and Access Rules

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

Syntax Rules and Parameters

index_name

It is the target index name.

<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 byte. (If it is omitted, the default unit is bytes.)

Description

Refer to the syntax rules of each statement.

Examples

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

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

Index altered.

Compatibility

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

For More Information

Refer to the followings.

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

ALTER PROFILE ON DATABASE privilege is required to perform <alter profile statement>.

Syntax Rules and Parameters

profile_name

It is a profile name to be altered.

FAILED_LOGIN_ATTEMPTS

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

PASSWORD_LOCK_TIME

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

PASSWORD_LIFE_TIME

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

PASSWORD_GRACE_TIME

It sets a password expiration grace period when log in after PASSWORD_LIFE_TIME.
For more information, refer to CREATE PROFILE.

PASSWORD_REUSE_MAX

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

PASSWORD_REUSE_TIME

It specifies the duration which the password can not be reused when a user wants to reuse the old password.
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 changing the profile to control the 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 changing 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 changing the profile to control the 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 changing the profile to control the 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 concepts 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 perform <alter sequence generator statement>.

Syntax Rules and Parameters

sequence_name

It is the sequence name to be altered.
It can define schema to which the sequence belongs such as schema_name.sequence_name and if schema_name is omitted, the default schema name of the user performing the statement is used.

<alter sequence generator restart option>

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

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

<sequence generator increment by option>

It changes the interval of the sequence number.
The constraints and characteristics are as follows.

<sequence generator maxvalue option>

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

<sequence generator minvalue option>

It changes the minimum value which the sequence can generate.
However, the MINVALUE should not be bigger than the current sequence value.

<sequence generator cycle option>

It changes whether to continue generating a value when the sequence value becomes the maximum or minimum value.

<sequence generator cache option>

For quick access of a sequence, it defines the number of sequence values to be pre-loaded on the memory.
When restarting the database, the sequence value loaded on the memory is lost, and it starts from the value after loading.

Description

It can not change START WITH which is one of the sequence attributes defined in CREATE SEQUENCE statement. To change START WITH attribute, it should be re-created by performing CREATE SEQUENCE statement after performing DROP SEQUENCE statement.

Examples

The following is an example of restating the sequence value by using RESTART option, then assigning a new ID.

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 CACHE/ NO CACHE statement.

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 followings.

ALTER SESSION CLEANUP GLOBAL TEMPORARY SEGMENT POOL;

Function

It returns all segments which were caught to be reused in a session to tablespaces.

Syntax

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

Description

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

Examples

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

gSQL> ALTER SESSION CLEANUP GLOBAL TEMPORARY SEGMENT POOL;

Session altered.

Compatibility

The SQL standard does not define the concepts of the segment cache of a global temporary table and a global temporary index.

For More Information

Refer to Global Temporary Table.

ALTER SESSION SET property_name

Function

It sets the property value of 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 Server Property in an administration manual.

<property value>

It is the property value to be set.

TO DEFAULT

It sets the session property value as a system property value.

Description

For more information about property, refer to Server Property in an administration manual.

Examples

The following is an example of an error when setting ERROR HINT property so the hint clause includes an error.

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 as the system property value.

gSQL> ALTER SESSION SET HINT_ERROR TO DEFAULT;

Session altered.

Compatibility

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

For More Information

Refer to ALTER SESSION SET property_name.

ALTER SYSTEM CHECKPOINT

Function

It performs CHECKPOINT.

Syntax

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

Invocation and Access Rules

ALTER SYSTEM ON DATABASE privilege is required to perform <alter system checkpoint statement>.

Syntax Rules and Parameters

<alter system checkpoint statement>

CHECKPOINT is an operation to ensure that all altered data by the committed transactions are written to disk.

<domain name>

It is a name of a member or a group for which the statement is performed.
If it is omitted, it is performed for all groups.

Description

The checkpoint operation records all changes by the committed transactions to disk, so it enables a rapid recovery at system error.

Example

The following is an example of performing CHECKPOINT.

ALTER SYSTEM CHECKPOINT;

Compatibility

The SQL standard does not define the concepts of CHECKPOINT.

ALTER SYSTEM CLEANUP BUFFER_CACHE

Function

It clears all buffer pages which can be free from the buffer cache.

Syntax

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

Invocation and Access Rules

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

Syntax Rules and Parameters

<alter system cleanup buffer_cache statement>

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

<domain name>

It is a name of a member or a group for which the statement is performed.
If it is omitted, it is performed for all groups.

Description

It flushes and frees all free buffer pages cached in the buffer.

It should be used to clear the buffer cache before the performance measuring.

If it is used on the operating server, then it could have fatal effect for the performance.

Example

The following is an example of performing CLEANUP BUFFER_CACHE.

ALTER SYSTEM CLEANUP BUFFER_CACHE;

Compatibility

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

ALTER SYSTEM CLEANUP PLAN

Function

It cleans up all SQL plans.

Syntax

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

Invocation and Access Rules

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

Syntax Rules and Parameters

<alter system cleanup plan statement>

There is not any syntax rules or parameters for <alter system cleanup plan statement>.

<domain name>

It is a name of a member or a group for which the statement is performed.
If it is omitted, it is performed for all groups.

Description

It cleans up all of the cached SQL plan. However, the plan whose V$SQL CACHE.REF COUNT is bigger than 0 (the plan referenced by the prepared statement) is excluded from cleanup.

Examples

The following is an example of executing CLEANUP PLAN.

ALTER SYSTEM CLEANUP PLAN;

Compatibility

The SQL standard does not define the concepts of CLEANUP PLAN.

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

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

Syntax Rules and Parameters

<alter system irrecoverable cluster member statement>

There is not any syntax rules or parameters for <alter system irrecoverable cluster member statement>.

<domain name>

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

Description

It is used to restart the system excluding the corresponding member if the cluster failed to restart due to an irrecoverable member. The corresponding member should be dropped by using ALTER DATABASE DROP INACTIVE CLUSTER MEMBERS after the system succeeded to restart.

Examples

The following is an example of executing IRRECOVERABLE CLUSTER MEMBER.

gSQL> ALTER SYSTEM IRRECOVERABLE CLUSTER MEMBER g1n1;

Compatibility

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

ALTER SYSTEM JOIN DATABASE

Function

It includes a specific inactive cluster member in a cluster system again.

Syntax

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

Invocation and Access Rules

It can be performed in a cluster system.
ADMINISTRATION ON DATABASE privilege is required to perform <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 cases.

If a specific cluster member is inactive, then the member can be included in a cluster system again according to 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 <alter system join database statement> to make an inactive cluster member which is started up to the local open phase to participate in the cluster system without shutting it down.

To make the inactive cluster member to participate in the cluster system again, the database state of the cluster system and that of the inactive cluster member should be same.

The inactive cluster member can not participate in the cluster system again after the transaction altering the database in the cluster system completed.

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

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

gSQL> ALTER SYSTEM JOIN DATABASE;

  1. DROP inactive cluster members which can not participate in the cluster system.

gSQL> ALTER DATABASE DROP INACTIVE CLUSTER MEMBERS;

All inactive cluster members which can participate in the cluster system should be included in the cluster system before dropping because all inactive cluster members are dropped from the cluster system when performing <alter database drop inactive cluster member statement>.

Examples

gSQL> ALTER SYSTEM JOIN DATABASE;

Compatibility

The SQL standard does not define the concepts of the cluster.

For More Information

Refer to ALTER DATABASE DROP INACTIVE CLUSTER MEMBERS.

ALTER SYSTEM [KILL | DISCONNECT] SESSION

Function

It terminates a 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

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

Syntax Rules and Parameters

<member_position>

It is a member position of a session which 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 <disconnect_option> is not used, then it is operated in IMMEDIATE.

<domain name>

It is a name of a member or a group for which the statement is performed.
If it is omitted, it is performed for all groups.

Description

DISCONNECT SESSION can specify the options such as POST TRANSACTION and IMMEDIATE. 
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 the abnormal session which remains on the system without its 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 it.

ALTER SYSTEM {MOUNT | OPEN} DATABASE

Function

It mounts the database on system, or alters the database to the state which is available for the 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

ADMINISTRATION ON DATABASE privilege is required to perform <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 concepts 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 which is connected in GLOBAL CONNECTION form.

Syntax

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

Invocation and Access Rules

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

Description

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

The client does not necessarily reconnect immediately after this statement is performed. The client reconnects by comparing SCN when the client executes a command in a server, and it does not try to reconnect if connections to all members from a 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 concepts of GLOBAL CONNECTION.

ALTER SYSTEM RESET property_name

Function

It removes a property value from the property file.

Syntax

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

Invocation and Access Rules

ALTER SYSTEM ON DATABASE privilege is required to perform <alter system reset statement>.

Syntax Rules and Parameters

{ RESET | UNSET }

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

<property name>

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

[ SCOPE = { FILE | SPFILE } ]

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

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

<domain name>

It is a name of a member or a group for which the statement is performed.
If it is omitted, it is performed for all groups.

Description

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

Examples

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

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

System altered.

The following is an example of removing the property 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 concepts of the system property.

For More Information

Refer to ALTER SYSTEM SET property_name.

ALTER SYSTEM SET property_name

Function

It sets the system property value.

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

ALTER SYSTEM ON DATABASE privilege is required to perform <alter system set statement>.

Syntax Rules and Parameters

<property name>

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

<property value>

It is the property value to be set.

TO DEFAULT

It sets the system property value as the initial value of system driving.

[ DEFERRED ]

It defines the point of time to apply the altered property.

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

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

It specifies the range which is affected by the property changes of the system.

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

<domain name>

It is a name of a member or a group for which the statement is performed.
If it is omitted, it is performed for all groups.

Description

For more information, refer to Server Property in an administration manual.

Examples

The following is an example of changing the property whose SYS_MODIFIABLE property 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 the property whose SYS_MODIFIABLE property 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 to the default value of when 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 concepts of the system property.

For More Information

Refer to ALTER SYSTEM RESET property_name.

ALTER SYSTEM SWITCH LOGFILE

Function

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

Syntax

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

Invocation and Access Rules

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

Syntax Rules and Parameters

<alter system switch logfile statement>

The database should be in MOUNT or OPEN phase.

<domain name>

It is a name of a member or a group for which the statement is performed.
If it is omitted, it is performed for all groups.

Description

Basically, if the log file in CURRENT state is filled, the log switch automatically occurs. That statement is used to forcibly execute log switch in special circumstances.

Example

ALTER SYSTEM SWITCH LOGFILE;

Compatibility

The SQL standard does not define the concepts of the 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>
    | <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 perform <alter table statement>.

Syntax Rules and Parameters

<alter table physical attribute statement>

It alters 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 offline shards in 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 sets to add information to the redo log when the data is altered in the table.
For more information, refer to  ALTER TABLE name ADD SUPPLEMENTAL LOG.

<drop table supplemental log statement>

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

<rebalance statement>

It restores consistency by rebalancing the shard of the table or by synchronizing the broken shard in a cluster environment. 
For more information, refer to ALTER TABLE name REBALANCE.

<alter table synchronize statement>

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

<move shard statement>

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

<merge shards statement>

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

<split shard statement>

It rebalances a specific shard of a table on a specific 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 cluster environment. 
For more information, refer to ALTER TABLE name RENAME SHARD.

<read { only | write } statement>

It sets READ ( only | write } to a table.
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 should satisfy the following conditions to perform <add column definition>.

Syntax Rules and Parameters

table_name

It is the table name to be altered.
It can define the schema to which the table belongs, such as schema_name.table_name. If schema_name is omitted, the default schema name of the user performing 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 <column definition> clause of CREATE TABLE statement.
There should not be columns with the same name in a table.
If DEFAULT clause is specified when defining the column, the default value of all rows are stored in the added column.
If <identity column specification> clause is specified when defining the column, each automatically generated value of all rows is stored in the added column.
If NOT NULL constraint is specified when defining the column, the table should be empty or it should be specified together with DEFAULT or <identity column specification> clause.

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

It adds multiple columns.
It lists multiple <column definition> inside the parentheses.

Description

The added column is positioned at the end of the existing columns.
When specifying DEFAULT or <identity column specification> clause, the processing time is increased in proportion to the number of the 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 the identity column and the column including 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 the column including the 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 of adding multiple column definitions.

For More Information

Refer to the followings.

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 should satisfy the following conditions to perform <add table constraint definition> clause.

Constraints of PRIMARY KEY, UNIQUE in a cluster system should include all sharding keys.

Syntax Rules and Parameters

table_name

It is the table name to be altered.
It can define the schema to which the table belongs, such as schema_name.table_name. If schema_name is omitted, the default schema name of the user performing the statement is used.

<table constraint definition>

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

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

Description

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

Examples

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

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

Table altered.

The following is an example of specifying the constraint name when adding a primary key constraint to the 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 followings.

ALTER TABLE name ADD GLOBAL SECONDARY INDEX

Function

It creates a global secondary index in a 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>
    | MINSIZE <size_clause>
    | MAXSIZE <size_clause>

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

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

Invocation and Access Rules

<alter table add global secondary index definition> can be defined in a cluster system, and a user should 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.
It can define a schema to which the table belongs such as schema_name.table_name.
If schema_name is omitted, the default schema name of the user performing 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 byte. (If it is omitted, the default unit is bytes.)

NOPARALLEL | PARALLEL [ integer ]

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

TABLESPACE tablespace_name

It specifies the name of the tablespace in which the index is to be stored.

Description

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

Examples

The following is an example of adding a global secondary index to the 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 concepts of the global secondary index.

For More Information

Refer to the followings.

ALTER TABLE name ADD SUPPLEMENTAL LOG

Function

If the primary key exists in the table when table data is altered, it sets to add the primary key value to 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 perform <add table supplemental log statement>.

Syntax Rules and Parameters

table_name

It is the table name to be altered.
It can define the schema to which the table belongs, such as schema_name.table_name. If schema_name is omitted, the default schema name of the user performing the statement is used.
Even when the primary key does not exist in the table, the statement can be executed.

Description

It additionally records SUPPLEMENTAL LOG when executing UPDATE/DELETE on the corresponding TABLE. The recorded SUPPLEMENTAL LOG is used to analyze logs or tools such as CDC.
To record SUPPLEMENTAL LOG of every TABLE, set the property as SUPPLEMENTAL_LOG_DATA_PRIMARY_KEY = YES.

Example

The following is an example of setting to additionally add a primary key value to the redo log when changing the data in the table.

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

Table altered.

Compatibility

The SQL standard does not cover <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 check time> [ [ NOT ] DEFERRABLE ]

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

<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 performing <alter column definition>.

Syntax Rules and Parameters

table_name

It is the table name to be altered.
It can define the schema to which the table belongs, such as schema_name.table_name. If schema_name is omitted, the default schema name of the user performing the statement is used.

ALTER [ COLUMN ]

The reserved word COLUMN can be omitted.

column_name

It is the column name to be altered.

<set column default clause>

It sets the default value of the column.
It should not be an identity column.
The default value set when using the DEFAULT clause is used in INSERT statement later.
The data type of DEFAULT expression should be compatible with the data type of the column.
If the data type is not compatible or the expression is not valid, an error occurs.
For more information, refer to <default clause> of CREATE TABLE statement.

<drop column default clause>

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

<set column not null clause>

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

<drop column not null clause>

<alter column data type clause>

SET DATA TYPE is a DDL statement which is automatically committed.

The type conversion can be executed among the same family, and it should satisfy the following conditions.

Conversion of character string type

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 char length unit should satisfy the following condition.

Conversion of 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

Decimal digit count (ddc) value for the 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 in the same structure, and each numeric type is as same as the following NUMBER (p, s) expression.

NUMBER expressions of the 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 )

Native numeric type is as same with as C language numeric type, and it can not be converted to another type.

Conversion of native numeric type

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

Conversion of boolean type

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 INTERVAL YEAR TO MONTH family (If p,q are omitted, then it 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 INTERVAL DAY TO TIME family (If p,q are omitted, then it is 2.) (If f,g are omitted, then it 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

Conversion of ROWID type

from \ to

ROWID

ROWID

O

<alter identity column specification>

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

<drop identity property clause>

It drops the identity property of the column.
The column should be the identity column.

Description

SET NOT NULL clause requires the time for checking null in proportion to the number of table rows.
The following columns do not allow NULL values. In other words, even if DROP NOT NULL clause is performed, NULL is not allowed in the following cases.
The change of the default value using SET DEFAULT clause and the change of the identity property using <alter identity column specification> clause, is applied to INSERT or UPDATE statement which is performed later.

Examples

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

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

Table altered.

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

gSQL> ALTER TABLE region ALTER COLUMN r_comment DROP DEFAULT;

Table altered.

The following is an example of setting NOT NULL constraint to 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 data type size of the column.

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 followings.

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 check time> [ [ NOT ] DEFERRABLE ] 

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

Invocation and Access Rules

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

Cluster does not support the deferrable constraints.

Syntax Rules and Parameters

table_name

It is the table name to be altered.
It can define the schema to which the table belongs, such as schema_name.table_name. If schema_name is omitted, the default schema name of the user performing 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 the deferrable constraints, refer to SET CONSTRAINTS.

INITIALLY IMMEDIATE | INITIALLY DEFERRED

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

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

Description

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

Example

The following is an example that the constraint t1_uk is set as deferrable and its checking time is set as 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

X

ALTER TABLE name ALTER GLOBAL SECONDARY INDEX

Function

It alters the physical attributes of the global secondary index in 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>
    | MINSIZE <size_clause>
    | MAXSIZE <size_clause>

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

Invocation and Access Rules

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

Syntax Rules and Parameters

table_name

It is the name of a table in which the index is to be created.
It can define a schema to which the table belongs such as schema_name.table_name.
If schema_name is omitted, the default schema name of the user performing 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 byte. (If it is omitted, the default unit is bytes.)

Description

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

Examples

It alters the maximum available size to be used by a global secondary index in the table T1 to 100 MBytes.

gSQL> ALTER TABLE T1 ALTER GLOBAL SECONDARY INDEX STORAGE( MAXSIZE 100M );

Table altered.

gSQL> COMMIT;

Commit complete.

It alters the INITRANS value and the MAXTRANS value to 2 and 4 each which are to be used by a global secondary index in the 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 concepts of the global secondary index.

For More Information

Refer to the followings.

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
    ;

Invocation and Access Rules

The user should satisfy the following conditions to perform <global secondary index coalesce.

Syntax Rules and Parameters

table_name

It is the name of the target table.
It can define the schema to which the table belongs, such as schema_name.table_name. If schema_name is omitted, the default schema name of the user performing the statement is used.

Description

Comparing to 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 in table T1.

gSQL> ALTER TABLE T1 ALTER GLOBAL SECONDARY INDEX COALESCE;

Table altered.

Compatibility

The SQL standard does not define the concepts of the 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 a 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 ]
    ;

<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>
    | MINSIZE <size_clause>
    | MAXSIZE <size_clause>

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

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

Invocation and Access Rules

The user should satisfy the following conditions to perform <global secondary index rebuild statement>.

Syntax Rules and Parameters

table_name

It is the table name on which the index is to be rebuilt. 
It can define the schema to which the table belongs, such as schema_name.table_name. If schema_name is omitted, the default schema name of the user performing the statement is used.

[ ONLINE | OFFLINE ]

It determines whether to allow DML on the table when 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 the unit is omitted, the default value is bytes.)

NOPARALLEL | PARALLEL [ integer ]

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

TABLESPACE tablespace_name

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

Description

Examples

Rebuild the global secondary index in the table T1.

gSQL> ALTER TABLE T1 ALTER GLOBAL SECONDARY INDEX REBUILD;

Alter the tablespace and logging settings of the global secondary index in the 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 cover the concepts of the global secondary index.

For More Information

Refer to the followings.

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 perform <drop table constraint definition>.

Syntax Rules and Parameters

table_name

It is the table name to be altered.
It can define the schema to which the table belongs, such as schema_name.table_name. If schema_name is omitted, the default schema name of the user performing the statement is used.

CONSTRAINT constraint_name

It is the constraint name to be dropped.

PRIMARY KEY

It is the primary key constraint for the table.

UNIQUE( column_name [, ...] )

It is the unique constraint for the columns.

<drop behavior>

When it is omitted, the default value is RESTRICT.
Currently, RESTRICT/CASCADE is operated in the same way.

Description

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

Examples

The following is an example of dropping a primary key constraint from the table.

gSQL> ALTER TABLE t1 DROP PRIMARY KEY;

Table altered.

The following is an example of dropping the table constraint by specifying the 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 followings.

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

<alter table drop global secondary index definition> statement can be defined in a cluster system, and the user should satisfy the following conditions.

Syntax Rules and Parameters

table_name

It is the name of a table from which the index is to be dropped.

Description

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

Examples

It drops a global secondary index from the table T1.

gSQL> ALTER TABLE T1 DROP GLOBAL SECONDARY INDEX;

Table altered.

gSQL> COMMIT;

Commit complete.

Compatibility

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

For More Information

Refer to the followings.

ALTER TABLE name DROP OFFLINE SEGMENTS

Function

It drops 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 the cluster system.

One of the following privileges is required to perform <alter table drop offline segments statement>.

Syntax Rules and Parameters

table_name

It is the table name.
It can define the schema to which the table belongs, such as schema_name.table_name. If schema_name is omitted, the default schema name of the user performing the statement is used.

Description

It drops segments of offline shards.

<alter table drop offline segments statement> can be performed even when an inactive cluster member exists.

If it can not satisfy the following conditions, then it fails.

For example, if all replicas in cluster group G3 of the sharded table t1 are offline, then the following error occurs.

gSQL> ALTER TABLE t1 DROP OFFLINE SEGMENTS;

ERR-42000(16361): sharded table "PUBLIC"."T1" must be accessible to at least one member of group 'G3'

Use <alter database drop offline segments statement> to perform it for all tables.

Example

The following is an example of performing <alter table drop offline segments statement> for the table T1.

gSQL> ALTER TABLE t1 DROP OFFLINE SEGMENTS;

Table altered.

Compatibility

The SQL standard does not define the concepts of the cluster.

For More Information

Refer to ALTER DATABASE DROP OFFLINE SEGMENTS.

ALTER TABLE name DROP SUPPLEMENTAL LOG

Function

It sets not to leave the primary key information on the redo log when changing the data in the table.

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 perform <drop table supplemental log statement>.

Syntax Rules and Parameters

table_name

It is the table name to be altered.
It can define the schema to which the table belongs, such as schema_name.table_name. If schema_name is omitted, the default schema name of the user performing the statement is used.
It should have been set by using ALTER TABLE name ADD SUPPLEMENTAL LOG statement.

Description

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

Example

The following is an example of setting not to leave the primary key information on the redo log when changing the data in the table.

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

Table altered.

Compatibility

The SQL standard does not cover <drop table supplemental log statement>.

ALTER TABLE name MERGE SHARDS

Function

It merges specific shards in a table in 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 the cluster system.

One of the following privileges is required to perform <alter table merge shards statement>.

Syntax Rules and Parameters

table_name

It is the name of a table.
It can define a schema to which the table belongs such as schema_name.table_name. If schema_name is omitted, the default schema name of the user performing the statement is used.
The statement can be performed only when the table is a cluster-specific, and a list shard or a range shard.

<source shard list>

It is the list of original shards to be merged.
The shard specified by a list should 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, then the statement can not be performed.

start_shard_name

It is the name of the first shard in the range of merging. 
It is used only in a range shard.

end_shard_name

It is the name of the last shard in the range of merging. 
It is used only in a range shard.

dest_shard_name

It is the name of a target shard.

<dest shard placement>

It is the name of a cluster group in which the target shard is to be placed.
If the corresponding clause is omitted, dest_shard_name should be included in <source shard list>.

Description

It merges specific shards in a specific table, then places them in an arbitrary cluster group.

The following is an error which occurred when merging shards which are not neighboring in a range sharded table in a way of listing.

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 in a way of listing.

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 in a way of ranging.

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 concepts of the cluster.

For More Information

Refer to the followings.

ALTER TABLE name MOVE SHARD

Function

It rebalances a specific shard of a table, or the entire shard in a specific cluster group to a specific 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 ] 
       [ <shard divisor> ] 
       [ <parallel clause> ]
    ;

<shard divisor> ::= 
    SHARD DIVISOR 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 perform <alter table move shard statement>.

Syntax Rules and Parameters

table_name

It is the table name.
It can define a schema to which the table belongs such as schema_name.table_name.
If schema_name is omitted, the default schema name of the user performing the statement is used.
The statement can be performed only when that table is a cluster group specific table.

shard_name_list

It is the shard name list to be rebalanced.
If the shard does not exist in that table, then the statement can not be performed.

src_cluster_group

It is the name of a specific cluster group to be rebalanced.

dest_cluster_group

It is the name of a target cluster group on which the shard of the table is to be rebalanced.
If the shard of the table already exists in the specified cluster group, the statement cannot be performed.

[ ONLINE | OFFLINE ]

It determines whether to allow DML when rebalancing table shard.

<shard divisor>

It specifies the number of shard's partitions.

<parallel clause>

It specifies the number of threads to use when rebalancing the table.

Description

It rebalances a specific shard of the table from a specific cluster group to another cluster group.
To drop a specific cluster group, rebalance the shard of the table then perform the DROP CLUSTER GROUP statement.

To move shards of all tables from a specific cluster group to another cluster group, then perform the ALTER DATABASE MOVE SHARD FROM CLUSTER GROUP TO CLUSTER GROUP statement.

If it is a CLONED table or a CLUSTER WIDE table, then an error occurs and it fails.

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 of which a CLONED table and a CLUSTER WIDE table fails to move shard.

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 concepts of the cluster.

For More Information

Refer to ALTER DATABASE MOVE SHARD.

ALTER TABLE name READ { ONLY | WRITE }

Function

It sets READ { ONLY | WRITE } in a 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 perform <alter table read { only | write } statement>.

Syntax Rules and Parameters

table_name

It is the table name.
It can define a schema to which the table belongs such as schema_name.table_name.
If schema_name is omitted, the default schema name of the user performing the statement is used.

Description

It sets table property to READ { ONLY | WRITE }.

If it is set to READ ONLY, neither SELECT .. FOR UPDATE statement, nor DML/ DDL statement which updates table data can be used. However, DDL statement which does not update the table data is allowed.

Disallowed SQL statements when it is 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 it is 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 <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 <alter table read { only | write } statement>.

For More Information

Refer to ALTER TABLE.

ALTER TABLE name REBALANCE

Function

It rebalances the shard in a table.

Syntax

<alter table rebalance statement> ::=
    ALTER TABLE table_name REBALANCE 
       [ ONLINE | OFFLINE ] 
       [ <shard divisor> ] 
       [ <parallel clause> ]
    ;

<shard divisor> ::= 
    SHARD DIVISOR 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 perform <alter table rebalance statement>.

Syntax Rules and Parameters

table_name

It is the table name.
It can define the schema to which the table belongs, such as schema_name.table_name. If schema_name is omitted, the default schema name of the user performing the statement is used.

[ ONLINE | OFFLINE ]

It determines whether to allow DML when rebalancing table shard.

<shard divisor>

It specifies the number of shard's partitions.

<parallel clause>

It specifies the number of threads to use when rebalancing the table.

Description

It does not rebalance shards in a table when adding a cluster member or a cluster group by using the following statements.

To rebalance the shards of a table in the added cluster group and the cluster member, perform the <alter table rebalance statement> statement. The operation succeeds without a separate rebalancing if the shard of the table is already rebalanced.

To rebalance shards in all tables, perform 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 concepts of the cluster.

ALTER TABLE name REBALANCE EXCLUDE CLUSTER GROUP cluster_group_list

Function

It rebalances the shard of the table not to include a shard in 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 ] 
       [ <shard divisor> ] 
       [ <parallel clause> ]
    ;

<shard divisor> ::= 
    SHARD DIVISOR 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 perform <alter table rebalance exclude cluster group statement>.

Syntax Rules and Parameters

table_name

It is the table name.
It can define a schema to which the table belongs such as schema_name.table_name.
If schema_name is omitted, the default schema name of the user performing the statement is used.
The statement can be performed only when that table is a cluster-wide table.

cluster_group_list

It is a list of the cluster group which does not include a shard of a table.
If the cluster group to be excluded from the rebalancing is the entire group, the statement can not be performed.

[ ONLINE | OFFLINE ]

It determines whether to allow DML when rebalancing table shard.

<shard divisor>

It specifies the number of shard's partitions.

<parallel clause>

It specifies the number of threads to use when rebalancing the table.

Description

It excludes a specific cluster group and rebalances the shard of the table.
If the shard of the table does not exist in that cluster group, the operation succeeds without a separate rebalancing. 
It rebalances the shard based on the cluster group in which the shard of the table is located.
To drop a specific cluster group, rebalance the shard of the table and perform  DROP CLUSTER GROUP statement.
To rebalance the shard excluding a cluster group from all tables, perform 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 concepts of the 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 perform <rename column statement>.

Syntax Rules and Parameters

table_name

It is the table name to be altered.
It can define the schema to which the table belongs, such as schema_name.table_name. If schema_name is omitted, the default schema name of the user performing the statement is used.

old_column_name

It is the old column name to be altered.

new_column_name

It is the new column name to be altered.
The same column name should not exist in a table.

Description

Even when the column name is altered it does not require the object change such as index, constraint which is generated based on the previous column.

Example

The following is an example of exchanging 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 <rename column statement>.

For More Information

Refer to the followings.

ALTER TABLE name RENAME CONSTRAINT

Function

It renames the table constraints.

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 perform <rename table constraint statement>.

Syntax Rules and Parameters

table_name

It is the table name to be altered.
It can define the schema to which the table belongs, such as schema_name.table_name. If schema_name is omitted, the default schema name of the user performing the statement is used.

<constraint object>

The existing name of the constraint to be altered is specified as follows.

new_column_name

It is the new name of a constraint to be altered.

Description

The index name which was automatically created with a key constraint such as primary key, unique key is not altered. Use ALTER INDEX name RENAME TO statement to rename the index.

Examples

The following is an example of renaming the primary key constraint of the table.

gSQL> ALTER TABLE t1 RENAME PRIMARY KEY TO pk_t1;

Table altered.

The following is an example of renaming the table constraint by specifying the 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 followings.

ALTER TABLE name RENAME SHARD

Function

It renames a specific shard of a table in 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 perform <alter table rename shard statement>.

Syntax Rules and Parameters

table_name

It is the table name to be altered.
It can define a schema to which the table belongs such as schema_name.table_name.
If schema_name is omitted, the default schema name of the user performing the statement is used.

shard_name

It is the existing name of a shard to be altered.
If the shard does not exist in that table, then the statement can not be performed.

new_shard_name

It is the new name of a shard to be altered. 
The same shard name should not exist in the table.

Description

It alters the name of a specific shard of a hash, a range, or a list table. This statement can not be performed for a cloned table.

Examples

The following is an example of executing <alter table rename shard statement> 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 concepts of the cluster.

For More Information

Refer to the followings.

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 perform <rename table statement>.

Syntax Rules and Parameters

table_name

It is the existing name of the table.
It can define the schema to which the table belongs, such as schema_name.table_name. If schema_name is omitted, the default schema name of the user performing the statement is used.

new_table_name

It is a new name of the table.
The same table name should not exist in the schema.

Description

Even when the table is renamed, the object referring to the table such as index, constraint does not need to be renamed.

Example

The following is an example of exchanging the name of the two tables t1, 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 <rename table statement>.

For More Information

Refer to ALTER TABLE.

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 perform <drop column definition>.

Syntax Rules and Parameters

table_name

It is the table name to be altered.
It can define the schema to which the table belongs, such as schema_name.table_name. If schema_name is omitted, the default schema name of the user performing the statement is used.

SET UNUSED [ COLUMN ]

It sets the column not to be used.

column_name_list

One or more column names to be dropped.

column_name

It is the column name to be dropped.
It also drops the constraints and indexes which use the column.

drop behavior

When it is omitted, the default value is RESTRICT.
Currently, RESTRICT/CASCADE is operated in the same way.

Description

SET UNUSED COLUMN does not delete the data physically, so it ensures consistent performance regardless of the number of the rows.

Example

The following is an example of setting the column not to be used.

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 followings.

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 perform <alter table split shard statement>.

Syntax Rules and Parameters

table_name

It is the table name.
It can define a schema to which the table belongs such as schema_name.table_name.
If schema_name is omitted, the default schema name of the user performing the statement is used.
The statement can be performed only when that table is a cluster group specific table, and when the shard is a list shard or a range shard.

source_shard_name

It is the name of an original shard to be split. 
If the shard does not exist in that table, the statement can not be performed.

<split shard placement>

It defines the target shard to which the original shard is rebalanced by splitting.

<split shard bound def>

It defines the bound of a target shard to be split.

It can be defined as one of two following bound defs.

<split list shard def>

It defines a split shard bound for a list shard.

<split range shard def>

It defines a split shard bound for a range shard.

dest_group_name

It is the name of a cluster group in which the split shard is to be rebalanced.

Description

It splits a specific shard of a specific table and rebalances it to a random cluster group.
This is used to distribute records and loads by splitting the shards when records corresponding to a specific shard is too much or when a specific group member is overloaded.

Examples

The following is an example of executing the <alter table split shard statement> 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 concepts of the cluster.

For More Information

Refer to the followings.

ALTER TABLE name STORAGE

Function

It alters 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>
    | MINSIZE <size_clause>
    | MAXSIZE <size_clause>

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

Invocation and Access Rules

One of the following privileges is required to perform <alter table physical attribute statement>.

Syntax Rules and Parameters

table_name

It is the table name to be altered.
It can define the schema to which the table belongs, such as schema_name.table_name. If schema_name is omitted, the default schema name of the user performing the statement is used.

<physical attribute clause>

It alters the physical attribute of a page which configures the table.
It is not applied to the already allocated page, but is applied to the newly allocated page.
For more information, refer to <table physical attribute clause> of CREATE TABLE.

<segment attr clause>

It alters the physical attribute of the extent configuring the segment. It is not applied to the already allocated extent but is applied to the newly allocated extent.

Description

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

Example

The following is an example of changing the physical attribute 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 attribute of a table.

For More Information

Refer to ALTER TABLE.

ALTER TABLE name SYNCHRONIZE

Function

It remotely synchronizes shards of the existing table.

Syntax

<alter table synchronize statement> ::=
    ALTER TABLE table_name SYNCHRONIZE 
       [ ONLINE | OFFLINE ] 
       [ <shard divisor> ] 
       [ <parallel clause> ]
    ;

<shard divisor> ::= 
    SHARD DIVISOR integer

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

Invocation and Access Rules

It can be performed in the cluster system.

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

Syntax Rules and Parameters

table_name

It is the table name. 
It can define the schema to which the table belongs, such as schema_name.table_name. If schema_name is omitted, the default schema name of the user performing the statement is used.

[ ONLINE | OFFLINE ]

It determines whether to allow DML when synchronizing the table shard.

<shard divisor>

It specifies the number of shard's partitions.

<parallel clause>

It specifies the number of threads to use when synchronizing the table.

Description

The table synchronization synchronizes the existing offline shards to restore consistency. Unlike <alter table rebalance statement>, it can be performed even when an inactive cluster member exists.

If it can not satisfy the following conditions, then it fails.

For example, if all replicas in cluster group G3 of the sharded table t1 are offline, then 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'

Perform <alter database synchronize statement> to synchronize shards in all tables.

Example

The following is an example of performing <alter table synchronize statement> for the table T1.

gSQL> ALTER TABLE t1 SYNCHRONIZE;

Table altered.

Compatibility

The SQL standard does not define the concepts of the cluster.

For More Information

Refer to the followings.

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

ALTER TABLESPACE privilege is required to perform <alter tablespace statement>.

Syntax Rules and Parameters

<rename tablespace statement>

It renames the tablespace.
For more information, refer to ALTER TABLESPACE name RENAME TO statement.

<backup tablespace statement>

It backs up the tablespace.
For more information, refer to ALTER TABLESPACE name BACKUP statement.

<on-offline tablespace statement>

It changes all files in the tablespace to the online state or offline state.
For more information, refer to ALTER TABLESPACE name [ONLINE|OFFLINE] statement.

<add file statement>

It adds a file to the tablespace.
For more information, refer to ALTER TABLESPACE name ADD [DATAFILE|MEMORY] statement.

<drop file statement>

It drops a file from the tablespace.
For more information, refer to ALTER TABLESPACE name DROP [DATAFILE|MEMORY] statement.

<rename datafile statement>

It renames the datafile in the data tablespace. 
For more information, refer to ALTER TABLESPACE name RENAME DATAFILE statement.

Description

Unlike other Data Definition Language (DDL), ALTER TABLESPACE statement is not allowed to ROLLBACK, and its transaction is automatically committed after executing the statement.

Example

Refer to the examples of each detailed statement.

Compatibility

The SQL standard does not define the concepts of the tablespace.

For More Information

Refer to the followings.

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

ALTER TABLESPACE ON DATABASE privilege is required to perform <add space statement>.

Syntax Rules and Parameters

tablespace_name

It is the tablespace name to be altered.

<file specification>

The following syntax should be used according to the tablespace type.

<add datafile clause>

It defines the memory datafile to be added.

<memory clause>

For more information, refer to  <memory clause> of CREATE MEMORY TEMPORARY TABLESPACE statement.

<autoextend clause>

It sets the automatic extending property when adding the data file of the disk tablespace. Set the automatic extending property to ON or OFF. If it is set to ON, then it can define the size of the automatic extending and the maximum size of the data file.

<next size clause>

It defines the space size to be extended when there is not any space available in the current data file in use.

<max size clause>

It defines the maximum size of the data file which can be extended.

<domain name>

It is a name of a member or a group for which the statement is performed.
If it is omitted, it is performed for all groups.

Description

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

Example

The following is an example of adding 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 concepts of the tablespace.

For More Information

Refer to the followings.

ALTER TABLESPACE name BACKUP

Function

It switches the tablespace to backup enabled state and backup disabled state to perform 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> [AT <domain_name>]    ;

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

Invocation and Access Rules

ALTER TABLESPACE ON DATABASE privilege is required to perform <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 tablespace name 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 the incremental backup of the tablespace.
The database is in OPEN phase and it should be operated in ARCHIVELOG mode.

<incremental backup option>

<domain name>

It is a name of a member or a group for which the statement is performed.
If it is omitted, it is performed for all groups.

Description

It backs up the datafiles which are created in the tablespace. A full backup of the tablespace begins with BEGIN BACKUP, and copies the datafiles by OS file copy and ends with END BACKUP. The incremental backup file is created in the path set by 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 the LEVEL 0 incremental backup of the tablespace DICTIONARY_TBS.

ALTER TABLESPACE DICTIONARY_TBS BACKUP INCREMENTAL LEVEL 0;

Compatibility

The SQL standard does not define the concepts of the tablespace.

For More Information

Refer to the followings.

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

ALTER TABLESPACE ON DATABASE privilege is required to perform <drop space statement>.

Syntax Rules and Parameters

tablespace_name

It is the tablespace name to be altered.

<file specification>

The following syntax should be used according to the tablespace type.

The file of OFFLINE tablespace can not be dropped.

The first file of the tablespace can not be dropped.

The data file which has been used once can not be dropped.

<domain name>

It is a name of a member or a group for which the statement is performed.
If it is omitted, it is performed for all groups.

Description

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

Example

The following is an example of dropping the file from the tablespace.

gSQL> ALTER TABLESPACE space1 DROP DATAFILE 'test_file_f2.dbf';

Tablespace altered.

Compatibility

The SQL standard does not define the concepts of the tablespace.

For More Information

Refer to the followings.

ALTER TABLESPACE name [ONLINE|OFFLINE]

Function

It alters the tablespace status.

Syntax

<on/off tablespace statement> ::=
    ALTER TABLESPACE tablespace_name { ONLINE | OFFLINE [ NORMAL | IMMEIDATE ] }
    [ AT <domain name> ]
    ;

Invocation and Access Rules

ALTER TABLESPACE ON DATABASE privilege is required to perform <on/off tablespace statement>.

Syntax Rules and Parameters

ONLINE

It alters the tablespace status in OFFLINE state to ONLINE state.

OFFLINE NORMAL

It alters the tablespace status in ONLINE state to OFFLINE state.
The media recovery is not required in ONLINE state because the tablespace which was altered to OFFLINE state is in consistent state.

OFFLINE NORMAL is not allowed in MOUNT phase.

(However, if the previous instance is terminated by \SHUTDOWN NORMAL, OFFLINE NORMAL is allowed.)

OFFLINE IMMEDIATE

It alters the tablespace status in ONLINE state to OFFLINE state.
The media recovery is required in ONLINE state because the tablespace which was altered to OFFLINE state is in inconsistent state.

The SYSTEM tablespace can not be altered to OFFLINE state.

OFFLINE IMMEDIATE requires the media recovery, so it can be performed only in ARCHIVELOG mode.

<domain name>

It is a name of a member or a group for which the statement is performed.
If it is omitted, it is performed for all groups.

Description

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

Examples

The following is an example of setting the tablespace to OFFLINE.

gSQL> ALTER TABLESPACE space1 OFFLINE;

Tablespace altered.

The following is an example of that OFFLINE NORMAL for the tablespace fails in 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 concepts of the tablespace.

For More Information

Refer to the followings.

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

ALTER TABLESPACE ON DATABASE privilege is required to perform <rename datafile statement>.

ONLINE tablespace file can not be altered when it is in TDS mode and the database is in OPEN phase. (Except for the temporary memory tablespace.)

The file should exist even after the alteration.

Syntax Rules and Parameters

tablespace_name

It is the tablespace name to be altered.

'filename'

The memory temporary tablespace is 'memory_name' and the other kinds of tablespace is 'filename'.

<domain name>

It is a name of a member or a group for which the statement is performed.
If it is omitted, it is performed for 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 concepts of the tablespace.

For More Information

Refer to the followings.

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

ALTER TABLESPACE ON DATABASE privilege required to perform <rename space statement>.

Syntax Rules and Parameters

tablespace_name

It is a name of the old tablespace.

new_tablespace_name

It is a name of the new tablespace.

Description

Even when the tablespace is renamed, the table or index which was already created in the existing tablespace does not need to be renamed.

Example

The following is an example of renaming the tablespace.

gSQL> ALTER TABLESPACE space1 RENAME TO space2;

Tablespace altered.

Compatibility

The SQL standard does not define the concepts of the 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

ALTER USER ON DATABASE privilege is required to perform <alter user statement>.
However, <alter password> can be performed without any privilege, 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 should be a data tablespace.

<alter temporary tablespace>

It alters the user's temporary tablespace.
The tablespace_name should be a temporary tablespace.

<alter index tablespace>

It alters an index tablespace of the user.

<alter schema path>

It alters the user's schema access path.
If the schema is not specified in user's SQL statement, the schema access path is determined in the schema order for the naming resolution of the object.
If the schema name is as same as another schema which is previously listed, it is not applied.
The following is an example of objects existing in a schema when performing ALTER USER u1 SCHEMA PATH ( u1, s2, public );  statement.

Schema name

u1

s2

public

-

t1

-

t1

-

-

t2

-

-

-

-

t3

The object name whose schema name is not specified when the user u1 executes the schema is interpreted by the SCHEMA PATH as follows.

CURRENT PATH

It is the current user's schema path.
A new schema path can be added using CURRENT PATH maintaining the existing schema path as follows.

ALTER USER PUBLIC <alter schema path>

It alters the schema path of PUBLIC account.
The schema path of PUBLIC account is included in every user's schema path.
The initial schema path which is allocated to PUBLIC account is as follows.

Description

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

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 allocating the 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 the 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

SQL standard covers the concepts of a user, but it does not define the SQL statements associated with creating, altering, dropping a user.

For More Information

Refer to the followings.

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 perform <alter view statement>.

Syntax Rules and Parameters

view_name

It is the view name to be altered.
It can define the schema to which the view belongs, such as schema_name.view_name. If schema_name is omitted, the default schema name of the user performing the statement is used.

COMPILE

It compiles the view again.
COMMENT which is given to the view column is initialized.

Description

When the table or the view which is referenced by the view is altered or dropped, then it affects that view.
This information can be retrieved from INFORMATION_SCHEMA.VIEWS.

Example

The following is an example of compiling the view which is affected by the table change referenced by that view.

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 <alter view statement>.

For More Information

Refer to the followings.

ANALYZE SYSTEM

Function

It controls the statistics information of the system.

Syntax

<analyze system statement> ::=
    ANALYZE SYSTEM [ <analyze action> ]
    ;

<analyze action> ::=
      COMPUTE STATISTICS
    | DELETE STATISTICS

Invocation and Access Rules

ANALYZE ANY ON DATABASE privilege is required to perform <analyze system statement>.

Syntax Rules and Parameters

<analyze action>

When it is omitted, the default value is COMPUTE STATISTICS.

COMPUTE STATISTICS

It builds the following statistics information related to the system.

DELETE STATISTICS

It deletes the statistics information of the system.

Description

The built statistics information of the system is used to calculate the cost of optimization for the query process.

Examples

The following is an example of building the statistics information of the system by using the <analyze system statement> statement.

gSQL> ANALYZE SYSTEM COMPUTE STATISTICS;

analyzed.

The following is an example of retrieving the built statistics information of the system.

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 concepts of the statistics information.

For More Information

Refer to ANALYZE TABLE.

ANALYZE TABLE

Function

It controls the statistics information of the table.

Syntax

<analyze table statement> ::=
    ANALYZE TABLE table_name
    [ <parallel clause> ]
    [ <analyze action> ]
    ;

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

<analyze action> ::=
      COMPUTE STATISTICS [ <for_clause> ]
    | ESTIMATE STATISTICS <sample_clause> [ <for_clause> ]
    | DELETE STATISTICS

<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 [, ...]

Invocation and Access Rules

ANALYZE ANY ON DATABASE privilege is required to perform <analyze table statement>.

Syntax Rules and Parameters

table_name

It is the table name.
It can define the schema to which the table belongs, such as schema_name.table_name. If schema_name is omitted, the default schema name of the user performing the statement is used.

<parallel clause>

It specifies the number of threads to be used in a analyzing process.
If it is not specified, the default value is PARALLEL.

<analyze action>

When it is omitted, the default value is COMPUTE STATISTICS.

COMPUTE STATISTICS

It builds the following statistics information related to the table through the all inspection.

The statistics information which is built according to the data type of the column is as follows.

Statistics information built according to the data type

Data type

NUM_DISTINCT

NUM_NULLS

AVG_LENGTH

MIN/MAX

BOOLEAN

O

O

O

X

NATIVE_SMALLINT

O

O

O

O

NATIVE_INTEGER

O

O

O

O

NATIVE_BIGINT

O

O

O

O

NATIVE_REAL

O

O

O

O

NATIVE_DOUBLE

O

O

O

O

NUMBER

O

O

O

O

NUMERIC

O

O

O

O

FLOAT

O

O

O

O

CHAR(n)

O

O

O

It is built when it is 64 bytes or smaller.

VARCHAR(n)

O

O

O

It is built when it is 64 bytes or smaller.

LONG VARCHAR

X

X

X

X

BINARY

O

O

O

X

VARBINARY

O

O

O

X

LONG VARBINARY

X

X

X

X

DATE

O

O

O

O

TIME

O

O

O

O

TIMESTAMP

O

O

O

O

INTERVAL

O

O

O

O

ROWID

O

O

O

X

ESTIMATE STATISTICS <sample_clause>

It builds the statistics information of the column and the index by using as many samples as the specified <sample_clause>.

If the number of the sampling rows is smaller than the value of MIN_SAMPLE_ROW_COUNT property, then it follows the property value.

<for_clause>

If it is omitted, it builds the statistics information of all possible columns and indexes.

FOR ALL COLUMNS

It builds the statistics information of all possible columns.
It does not build the statistics information of an index.

FOR ALL INDEXED COLUMNS

It builds the statistics information of all columns included in an index.
It does not build the statistics information of other columns.
It does not build the statistics information of an index.

FOR COLUMNS column_name [, ...]

It builds the statistics information of the listed columns.
It does not build the statistics information of unlisted columns.
It does not build the statistics information of an index.

FOR ALL INDEXES

It builds the statistics information of all indexes.
It does not build the statistics information of columns.

FOR INDEXES index_name [, ...]

It builds the statistics information of the listed indexes.
It does not build the statistics information of unlisted indexes.
It does not build the statistics information of a column.

DELETE STATISTICS

It deletes the statistics information of the table.

Description

The statistics information of the table affects the query optimization, so it is very important information.

The time to build the statistics information is increased in proportion to the data volumes in a table. Therefore, when the data volume is big, then it is recommended to build the statistics information by using the sampling or to build only the statistics of major information which affects the query.

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 statistics information through the all inspection.

gSQL> ANALYZE TABLE orders;

Table analyzed.

The following is an example of retrieving the built statistics information of 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 concepts of the statistics 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

AUDIT SYSTEM ON DATABASE privilege is required to perform <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 effect on the existing session, and it effects only on the newly created session.

<specified_user_option>

It specifies the user to be audited.
If omitted, all users are audited.

BY clause and EXCEPT clause can not be used together for the same audit policy.

<specified_success_option>

Description

Activating the audit policy does not affect the existing session, but it starts to audit the newly created session.

Retrieving Audit Record

The audit record is created when it corresponds to the audit policy, and it can be retrieved through DICTIONARY_SCHEMA.AUDIT_TRAIL view as follows.
SELECT logon_user
     , event_timestamp
     , action_name
     , object_name 
     , sql_text
  FROM audit_trail
 WHERE policy_name = 'P1'
;

SELECT privilege should be given to an ordinary user to retrieve AUDIT_TRAIL.

GRANT SELECT ON DICTIONARY_SCHEMA.AUDTI_TRAIL TO user_name;

Retrieving Audit Policy Information

The information about the audit policy object can be retrieved through DICTIONARY_SCHEMA.AUDIT_POLICY_OPTIONS view.

SELECT policy_name
     , audit_option
     , object_schema
     , object_name
  FROM audit_policy_options
;

The information about whether the audit policy object is activated can be retrieved through 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 users group if multiple AUDIT POLICY BY clauses are used for the same audit policy.
In other words, the following two examples have the same meaning.
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 the audit policy activated with BY to EXCEPT or to convert the audit policy activated with EXCEPT to BY, the activated audit policy should be deactivated first, then it can be converted.

Deactivate the audit policy with NOAUDIT POLICY statement as follows.

WHENEVER clause which is used together with BY clause is accumulated.

The following two examples have the same meaning.

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 together with 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;

The information about activation can be viewed through 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 defining 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 SQL statement by a specific user.

AUDIT POLICY delete_pol BY u1 WHENEVER NOT SUCCESSFUL;

Compatibility

The SQL standard does not have the audit policy.

For More Information

Refer to the followings.