SQL References (C~G)

CLOSE cursor_name

Function

It closes the cursor.

Syntax

<close statement> ::=
    CLOSE cursor_name
    ;

Syntax Rules and Parameters

cursor_name

The cursor must be open.
It should be declared within the session using the DECLARE cursor_name statement.

Description

A cursor is an object that exists within a session and does not affect cursors in other sessions.

Example

The following is an example of DECLARE, OPEN, FETCH, and CLOSE of a cursor using the interactive SQL tool, gsql.

gSQL> DECLARE cur1 CURSOR FOR SELECT id, data FROM t1;

Cursor declared.

gSQL> OPEN cur1;

Cursor is open.

gSQL> \var v_id   INTEGER
gSQL> \var v_data VARCHAR(128)

gSQL> FETCH cur1 INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   1 data_1

1 row fetched.


gSQL> FETCH cur1 INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   2 data_2

1 row fetched.

gSQL> FETCH cur1 INTO :v_id, :v_data;

no rows fetched.

gSQL> CLOSE cur1;

Cursor closed.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

B031

Basic dynamic SQL

O

For More Information

Refer to the following.

COMMENT ON name IS

Function

It stores comments about the object in the dictionary.

Syntax

<comment statement> ::=
    COMMENT ON <comment object> IS 'comment string'
    ;

<comment object> ::=
      CLUSTER GROUP group_name
    | CLUSTER MEMBER member_name
    | DATABASE
    | PROFILE profile_name
    | AUDIT POLICY policy_name
    | AUTHORIZATION user_name
    | TABLESPACE tablespace_name
    | SCHEMA schema_name
    | TABLE [schema_name].table_name
    | COLUMN [schema_name].table_name.column_name
    | INDEX [schema_name].index_name
    | SEQUENCE [schema_name].sequence_name
    | CONSTRAINT [schema_name].constraint_name
    | LIBRARY [schema_name].library_name
    | PROCEDURE [schema_name].procedure_name
    | PACKAGE [schema_name].package_name
    | TRIGGER [schema_name].trigger_name

Invocation and Access Rules

The privileges on each object must be altered as follows to execute the <COMMENT> statement.

Syntax Rules and Parameters

<comment object>

It is an object where comments are stored. Comments for the following database objects are stored.

If the schema_name for the schema object is not specified, the schema name is determined by the Schema Path of the user executing the statement.
COMMENT ON TABLE test_table IS 'test comment'; 
→ COMMENT ON TABLE user_default_schema.test_table IS 'test comment';

'comment string'

It describes the comments to be stored.
Use an empty string ('') to delete the comments as shown below.
COMMENT ON TABLE test_table IS '';
The length of the comment string must not exceed 1024 bytes.

Description

Information by object type can be found in the COMMENTS column of the following dictionary views.

For more information about each view, refer to the DICTIONARY_SCHEMA.

Examples

The following is an example of creating a comment on a table.

gSQL> COMMENT ON TABLE t1 IS 'test comment on table t1';

Comment created.

The following is an example of creating a comment on a column.

gSQL> COMMENT ON COLUMN t1.id IS 'test comment on column t1.id';

Comment created.

The following is an example of creating a comment on a schema.

gSQL> COMMENT ON SCHEMA s1 IS 'test comment on schema s1';

Comment created.

Compatibility

<comment statement> is not part of the SQL standard.

COMMIT

Function

It terminates the current transaction and commits all changes permanently.

Syntax

<commit statement> ::=
    COMMIT [ WORK ] 
       [ [ <commit comment clause> ] [ <commit write clause> ] |
         [ <commit force clause> ] [ <commit comment clause> ] ]
    ;

<commit comment clause> ::=
      COMMENT 'comment_string'

<commit write clause> ::=
      WRITE [ WAIT | NOWAIT ]

<commit force clause> ::=
    FORCE 'xid_string'

Syntax Rules and Parameters

WORK

It is a reserved word that does not affect the operation.

<commit comment clause>

<commit write clause>

It determines whether to wait until the redo logs generated by the commit operation are written to the redo log file.

<commit force clause>

It is used to manually commit a distributed transaction.

Description

The COMMIT statement completes the following statements executed within a transaction.

Exceptionally, the following DDL statements, which manage OS resources or alter the DATA TYPE, are automatically committed.

When performing a COMMIT, the cursor opened with the WITHOUT HOLD option is automatically closed. For more information about cursors, refer to the following cursor-related statements.

If a transaction violates a DEFERRED constraint, the COMMIT statement will fail and the transaction will be rolled back. For more information about DEFERRED constraints, refer to the SET CONSTRAINTS.

Example

The following is an example of performing a COMMIT after executing an INSERT statement.

gSQL> INSERT INTO t1 VALUES ( 1, 'anonymous' );

1 row created.

gSQL> COMMIT WORK COMMENT 'INSERT T1';

Commit complete.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

T261

Chained transactions

X

For More Information

Refer to the following.

CREATE AUDIT POLICY

Function

This creates an audit policy object.
To activate the created audit policy, the AUDIT POLICY statement must be executed.

Syntax

<audit policy definition> ::= 
    CREATE AUDIT POLICY policy_name
    { <privilege_audit_clause> | <role_audit_clause> | <action_audit_clause> }  [, ...]
    ; 

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

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


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

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

<system_action_audit> ::=
      ALL
    | DDL
    | <system_action>

Invocation and Access Rules

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

Syntax Rules and Parameters

policy_name

It is the name of the audit policy to be created.

<privilege_audit_clause>

Privilege auditing audits cases where SQL statements are successfully executed using database privileges. It can audit specific users who execute SQL statements using such privileges, but does not record audit logs for the SYS user, who is the owner of the database.

The following is an example of granting the SELECT ANY TABLE privilege to user u1 and enabling the audit policy.

CREATE AUDIT POLICY p1 
       PRIVILEGES SELECT ANY TABLE;

AUDIT POLICY p1;

If user u1 executes the following SQL statement, the privilege audit operates differently.

The <database_privilege> that can be described in the privilege audit can be viewed with the following query.

SELECT PRIVILEGE_NAME FROM V$AUDITABLE_DB_PRIVILEGES;

<role_audit_clause>

Role auditing monitors the execution of SQL statements using a specific role. 
The following is an example of granting the dba role privilege to user u1 and activating the audit policy.
gSQL> GRANT dba TO u1;
gSQL> CREATE AUDIT POLICY p1 ROLES dba;
gSQL> AUDIT POLICY p1;

The privilege audit operates differently when user u1 executes the following SQL statement.

<action_audit_clause>

It audits actions on specific objects as well as actions across the entire database.

<object_action_audit>

ALL ON object_name

It refers to all actions that can list objects corresponding to the object_name.

The following table describes the audit actions that can be audited for each object type.

Audit action per object type

Object type

Action

Table

ALTER, COMMENT, DELETE, GRANT, INDEX, INSERT, LOCK, RENAME, SELECT, UPDATE

View

ALTER, COMMENT, GRANT, SELECT

Sequence

ALTER, COMMENT, GRANT, SELECT

Stored Function/Procedure

ALTER, COMMENT, EXECUTE, GRANT

<object_action> ON object_name

Each separate action for a specific object must be listed by specifying the ON clause as follows.

CREATE AUDIT POLICY p1
       ACTIONS INSERT ON u1.t1
             , DELETE ON u1.t1
             , UPDATE ON u1.t1
;

Caution of EXECUTE action

Auditing the success or failure of a stored function or stored procedure is determined solely by whether it is executable at the time of execution.

<system_action_audit>

It audits system actions that occur in the database, regardless of a specific object.

The valid system actions can be retrieved using the following query.

SELECT ACTION_NAME FROM V$AUDITABLE_SYSTEM_ACTIONS;

It refers to all system actions.

It refers to all Data Definition Language (DDL) statements.

Description

An audit policy object is an object that defines auditing targets.  
Execute the AUDIT POLICY statement to activate the audit policy.
Although it is possible to define and activate multiple audit policies, it is recommended to maintain a limited number of audit policies.  
It is also recommended to group multiple small policies into a smaller number of policy groups.

Information about an option of the created audit policy object can be retrieved through the AUDIT_POLICY_OPTIONS view as shown below.

SELECT audit_option
     , audit_option_type
     , object_schema
     , object_name
  FROM audit_policy_options
 WHERE policy_name = 'P1'
;

AUDIT_OPTION AUDIT_OPTION_TYPE OBJECT_SCHEMA  OBJECT_NAME
------------ ----------------- -------------- ------------
DELETE         OBJECT ACTION     U1          T1
INSERT         OBJECT ACTION     U1          T1
UPDATE         OBJECT ACTION     U1          T1

Creating Audit Record

If an action corresponding to multiple audit policies occurs, one or more audit records will be created.

If similar audit options are listed as shown below, one audit record will be created.

CREATE AUDIT POLICY p1
       PRIVILEGES SELECT ANY TABLE
       ACTIONS SELECT;

AUDIT POLICY p1;
SELECT * FROM other_user.t1;

If two different audit options are listed as shown below, two audit records will be created.

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

AUDIT POLICY p1;
SELECT COUNT(*) FROM u1.t1 A, u2.t2 B WHERE A.id = B.id;

If multiple audit policies are activated for the same action as shown below, two audit records will be created.

CREATE AUDIT POLICY p1
       PRIVILEGES SELECT ANY TABLE;
AUDIT POLICY p1;

CREATE AUDIT POLICY p2
       ACTIONS SELECT;
AUDIT POLICY p2;
SELECT * FROM other.t1;

Examples

The following is an example of defining an audit policy that audits a privilege.

CREATE AUDIT POLICY policy_table
       PRIVILEGES CREATE ANY TABLE
                , DROP ANY TABLE
;

The following is an example of defining an audit policy to track a specific action on an object.

CREATE AUDIT POLICY policy_dml
       ACTIONS INSERT ON u1.t1
             , DELETE ON u1.t1
             , UPDATE ON u1.t1
             , ALL    ON u1.t2
;

The following is an example of defining an audit policy to track a system action.

CREATE AUDIT POLICY policy_drop
       ACTIONS DROP TABLE, TRUNCATE TABLE
;

The following is an example of defining an audit policy that combines all examples above.

CREATE AUDIT POLICY policy_group
       PRIVILEGES CREATE ANY TABLE
                , DROP ANY TABLE
       ACTIONS INSERT ON u1.t1
             , DELETE ON u1.t1
             , UPDATE ON u1.t1
             , ALL    ON u1.t2
             , DROP TABLE
             , TRUNCATE TABLE
;

Compatibility

The audit policy is not part of the SQL standard.

For More Information

Refer to the following.

CREATE CLUSTER GROUP

Function

It creates a cluster group to participate in the cluster system.

Syntax

<cluster group definition> ::=
    CREATE CLUSTER GROUP group_name 
        <cluster member definition> [, ...]
    ;

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

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

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

Invocation and Access Rules

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

Syntax Rules and Parameters

group_name

It is the name of a cluster group.
An identical cluster group name or cluster member name must not exist.
The name length must be shorter than 128 bytes.

<cluster member definition>

It defines a cluster member to be included in a cluster group.
A cluster group can include up to 32 cluster members.
The first  cluster group created in a cluster system can define only one cluster member, and it must include itself as the member.

member_name

It is the name of a cluster member.
The name must be the same as the member name defined when creating the database for that member.
No duplicate names are allowed among cluster groups or cluster members.
The name must be shorter than 128 bytes.

The start-up phase of the cluster member must be GLOBAL OPEN.

<connection attribute>

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

<member position>

It assigns the position number to the cluster member.

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

SELECT member_name, member_id, member_position FROM dba_cluster;

If the following position numbers are being used,

The following values are assigned according to the selected option.

Description

The <cluster group definition> statement does not rebalance table shards.

Perform the following statements to rebalance shards to the newly added cluster group.

Examples

The following is an example of how to create a cluster group consisting of two cluster members.

gSQL> 
CREATE CLUSTER GROUP g1
    CLUSTER MEMBER g1n1 HOST '192.168.0.11' PORT 10110
;

Cluster Group created.

gSQL>
ALTER CLUSTER GROUP g1
    ADD CLUSTER MEMBER g1n2 HOST '192.168.0.12' PORT 10120
;

Cluster Group altered.

gSQL> 
CREATE CLUSTER GROUP g2
    CLUSTER MEMBER g2n1 HOST '192.168.0.21' PORT 10210,
    CLUSTER MEMBER g2n2 HOST '192.168.0.22' PORT 10220
;

Cluster Group created.

Compatibility

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

For More Information

Refer to the following.

CREATE CLUSTER LOCATION

Function

It creates the connection information for a cluster member.

Syntax

<cluster location definition> ::=
    CREATE CLUSTER LOCATION member_name 
        <cluster connection attribute>
        [ AT <domain name> ]
    ;

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

Invocation and Access Rules

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

Syntax Rules and Parameters

member_name

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

<cluster connection attribute>

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

<domain name>

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

Description

The cluster location information is typically created automatically using the connection details provided during the creation of a cluster group or the addition of a cluster member. This information is deleted when the cluster member and group are deleted.

If the cluster location information is modified, the connection details can be updated using ALTER CLUSTER LOCATION without deleting or recreating the cluster member.

Examples

gSQL> 
CREATE CLUSTER LOCATION g1n2
    HOST '192.168.0.12' PORT 10120,
;

Created

Compatibility

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

For More Information

Refer to DROP CLUSTER LOCATION .

CREATE DISK DATA TABLESPACE

Function

It defines the disk data tablespace.

Syntax

<disk data tablespace statement> ::=
    CREATE DISK [ DATA ] TABLESPACE tablespace_name
        DATAFILE <disk datafile clause> [, ...]
        [ <data tablespace management clause> [, ...] ]

<disk datafile clause> ::=
     'filename' 
        [ SIZE <size clause> | REUSE | SIZE <size clause> REUSE ]
        [ <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 }

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

<data tablespace management clause> ::=
      { ONLINE | OFFLINE }
    | EXTSIZE <size clause>

Invocation and Access Rules

The CREATE TABLESPACE ON DATABASE privilege is required to execute the <disk data tablespace definition>.

The user who executed the statement has the CREATE OBJECT ON TABLESPACE privilege on the created tablespace.

The following privileges are required to create an object in the created tablespace.

Syntax Rules and Parameters

tablespace_name

It is the name of the tablespace to be created.
The length of the tablespace name must be shorter than 128 bytes.

<disk datafile clause>

<autoextend clause>

It sets the auto-expand property to either ON or OFF. When set to ON, the auto-extend size and the maximum size of the data file can be specified.

<next size clause>

It specifies the size to extend when the current data file runs out of available space.

<max size clause>

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

<size clause>

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

<domain_name>

It is the name of the member or group that executes the statement.
If omitted, it is executed for all groups.

ONLINE | OFFLINE

It sets the tablespace to ONLINE or OFFLINE.

EXTSIZE <size clause>

It specifies the extent size of the tablespace.

Description

A data tablespace is an object that provides physical storage for SQL schema objects such as tables and indexes (LOGGING).

Examples

The following is an example of how to create a disk data tablespace.

gSQL> CREATE DISK TABLESPACE space1 DATAFILE 'test_file_1.dbf' SIZE 10M REUSE;

Tablespace created.

The following is an example of how to create a tablespace that consists of multiple data files.

gSQL> CREATE DISK TABLESPACE space1 
             DATAFILE 'test_file_3_1.dbf' SIZE 10M REUSE,
                      'test_file_3_2.dbf' SIZE 10M REUSE;

Tablespace created.

Compatibility

The SQL standard does not define the concept of tablespaces.

For More Information

Refer to the following.

CREATE GLOBAL TEMPORARY TABLE

Function

It creates a new global temporary table.

Syntax

<global temporary table definition> ::=
    CREATE GLOBAL TEMPORARY TABLE table_name
        ( <table element> [, ...] )
        [ <table commit action clause> ]
        [ TABLESPACE tablespace_name ]
    ;

<global temporary table definition: AS query expression> ::=
    CREATE GLOBAL TEMPORARY TABLE table_name 
        [ TABLESPACE tablespace_name ]
        AS <query expression> [ WITH [ NO ] DATA ]
    ;

<table commit action clause> ::=
    ON COMMIT { PRESERVE | DELETE } ROWS

The definition of <table element> is the same as that of <table_definition>.

For more information, refer to CREATE TABLE.

Invocation and Access Rules

The user must meet the following conditions to execute a <global temporary table definition> statement.

Syntax Rules and Parameters

table_name

It is the table name to be created.
For more information, refer to table_name.

other syntax

For more information about other syntax rules, refer to the syntax of the CREATE TABLE and CREATE TABLE AS SELECT statements.

Description

A GLOBAL TEMPORARY TABLE is a temporary table used to store data that is preserved during the execution of a transaction or session.
It is typically used in a similar way to a variable that temporarily holds intermediate data during application development.
The global temporary table has the following characteristics.

Whether to specify tablespace

The tablespace where the table is created

A tablespace is explicitly specified.

The table is created in the specified tablespace.

No tablespace is specified.

The table is created in the current session user's default temporary tablespace.

Table commit action

Description

ON COMMIT PRESERVE ROWS

It retains the data in the table even after a COMMIT or ROLLBACK.

ON COMMIT DELETE ROWS (default)

It deletes all the data remaining in the table at the time of COMMIT or ROLLBACK (TRUNCATE).

TEMP_UNDO_ENABLED value

Description

TRUE

The undo log is recorded in the default temporary tablespace of the database system.

FALSE

The undo log is recorded in the undo tablespace of the database system.

Examples

The following is an example of executing the CREATE GLOBAL TEMPORARY TABLE statement.

gSQL> CREATE  GLOBAL TEMPORARY TABLE SESSION_TABLE1(
        COL1    CHAR(10)
       ,COL2    VARCHAR2(20)
       ,COL3    NUMBER(10)
)   ON  COMMIT  DELETE ROWS;

Table created.

The following is an example of executing the CREATE GLOBAL TEMPORARY TABLE ... AS SELECT statement.

gSQL> CREATE  GLOBAL TEMPORARY TABLE SESSION_TABLE2
    ON  COMMIT  PRESERVE ROWS
    AS  SELECT  *
          FROM  EMPLOYEES;

Table created.

Compatibility

The CREATE GLOBAL TEMPORARY TABLE and CREATE GLOBAL TEMPORARY TABLE AS SELECT statements follow the definition of <table definition> as specified in the SQL standard.

However, the following is an extension of the standard.
SQL standard compatibility

Feature ID

Description

Compatibility

T171

LIKE clause in table definition

X

T172

AS subquery clause in table definition

O

F531

Temporary tables

X

S051

Create table of type

X

S043

Enhanced reference types

X

S081

Subtables

X

T173

Extended LIKE clause in table definition

X

T180

System-versioned tables

X

F692

Extended collation support

X

T174

Identity columns

O

T175

Generated columns

X

S071

SQL paths in function and type name resolution

X

F321

User authorization

O

T322

Extended roles

X

F762

CURRENT_CATALOG

O

F763

CURRENT_SCHEMA

O

For More Information

Refer to the following.

CREATE IMMUTABLE TABLE

Function

It creates a new immutable table.

Syntax

<immutable table definition> ::=
    CREATE IMMUTABLE TABLE table_name
        ( <table element> [, ...] )
        [ <table sharding strategy> ]
        [ <table attribute clause> [...] ]
        [ TABLESPACE tablespace_name ]
        [ <table global secondary index clause> ]
    ;

<immutable table definition: AS query expression> ::=
    CREATE IMMUTABLE TABLE table_name
        [ ( column_name [, ...] ) ]
        [ <table sharding strategy> ]
        [ <table attribute clause> [, ...] ]
        [ TABLESPACE tablespace_name ]
        [ <table global secondary index clause> ]
        AS <query expression> [ WITH [ NO ] DATA ]
    ;

The definitions of <table element>, <table sharding strategy>, <table attribute clause>, and <table global secondary index clause> are the same as those in <table_definition>. For more information, refer to the CREATE TABLE.

Invocation and Access Rules

The user must meet the following conditions to execute a <immutable table definition> statement.

Syntax Rules and Parameters

table_name

It is the name of the table to be created, and it must be unique within the schema.
The schema to which the table belongs can be defined using the format schema_name.table_name. If schema_name is omitted, the default schema of the user executing the statement will be used.
The length of the table name must be less than 128 bytes.

Other Syntax

For other syntaxes, refer to the syntax for CREATE TABLE and CREATE TABLE AS SELECT.

Description

An immutable table is used when it is necessary to prevent the records stored in the table from being altered or deleted, as well as to prevent the table from being dropped.

An immutable table can be dropped when the user, schema, tablespace or cluster group is dropped.

The following SQL statements are not allowed for an immutable table.


The following SQL statements are allowed for an immutable table.

Examples

The following is an example of executing the CREATE IMMUTABLE TABLE statement.

gSQL> CREATE IMMUTABLE TABLE t1
(
    id INTEGER PRIMARY KEY,
    name VARCHAR(128),
    addr VARCHAR(128)
);

Table created.

The following is an example of executing the CREATE IMMUTABLE TABLE ... AS SELECT statement.

gSQL> CREATE IMMUTABLE TABLE T2
       AS SELECT *
             FROM T1;

Table created.

Compatibility

The SQL standard does not define the concepts of the CREATE IMMUTABLE TABLE and CREATE IMMUTABLE TABLE AS SELECT statements.

For More Information

Refer to the following.

CREATE INDEX

Function

It creates an index.

Syntax

<index definition> ::=
    CREATE [ UNIQUE ] INDEX index_name
        ON table_name ( <index column element> [, ...] )
        [ <index attributes> [...] ]
        [ TABLESPACE tablespace_name ]
        [ <index enforcement> ]
    ;

<index column element> ::=
    column_name [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]

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

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

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

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

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

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

Invocation and Access Rules

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

Unique indexes in a cluster system must include all sharding keys.

Syntax Rules and Parameters

UNIQUE

Duplicate values are not allowed in the columns that make up the index.

index_name

It is the name of the index to be created, and it must be unique within the schema.
If the schema name is omitted, the index is created in the schema of the referenced table.
The length of the index name must be less than 128 bytes.

table_name

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

column_name

It is the name of the column to be used as an index key.
At least one column must be defined, and up to 32 columns can be used as index keys.
The following constraints may arise depending on the implementation.

ASC | DESC

It specifies the sort order of a column.

NULLS FIRST | NULLS LAST

It specifies the sort order of NULL values.

<physical attribute clause>

It defines the physical attributes of the index.

<segment attr clause>

It specifies the information regarding the storage space for the index.

<size clause>

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

NOPARALLEL | PARALLEL [ integer ]

It specifies the number of threads to be used during the index creation process.

TABLESPACE tablespace_name

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

<index enforcement>

If omitted, the default value is ENABLE.

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

Description

LOGGING and NOLOGGING indexes each have the following trade-offs:

Examples

The following is an example of creating a unique index.

gSQL> CREATE UNIQUE INDEX idx_t1_id ON t1( id );

Index created.

The following is an example of creating an index for multiple columns.

gSQL> CREATE INDEX idx_t1_id_name ON t1( id, name );

Index created.

The following is an example of specifying the sort order of an index column.

gSQL> CREATE INDEX idx_t1_dept_id ON t1( dept_id DESC );

Index created.

The following is an example of specifying the sort order for NULL values in the index column.

gSQL> CREATE INDEX idx_t1_name ON t1( name NULLS FIRST );

Index created.

The following is an example of setting the information about the space where the index is stored.

gSQL> CREATE INDEX idx_t1_id ON t1( id )
             STORAGE ( INITIAL 10M NEXT 1M );

Index created.

The following is an example of creating redo logging for the index.

gSQL> CREATE INDEX idx_t1_id ON t1( id );

Index created.

The following is an example of creating an index with the parallel option.

gSQL> CREATE INDEX idx_t1_name ON t1( name ) PARALLEL;

Index created.

The following is an example of specifying the tablespace when creating an index.

gSQL> CREATE INDEX idx_t1_name ON t1( name ) TABLESPACE mem_temp_tbs;

Index created.

Compatibility

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

For more information

Refer to DROP INDEX.

CREATE MEMORY DATA TABLESPACE

Function

It defines a tablespace for memory data.

Syntax

<memory data tablespace statement> ::=
    CREATE [ MEMORY ] [ DATA ] TABLESPACE tablespace_name
        DATAFILE <memory datafile clause> [, ...]
        [ <data tablespace management clause> [, ...] ]

<memory datafile clause> ::=
     'filename' 
        [ SIZE <size clause> | REUSE | SIZE <size clause> REUSE ]
        [ AT <domain_name> ]
<size clause> ::=
    integer [ K | M | G | T ]

<data tablespace management clause> ::=
      { ONLINE | OFFLINE }
    | EXTSIZE <size clause>

Invocation and Access Rules

The CREATE TABLESPACE ON DATABASE privilege is required to execute the <memory data tablespace definition>.

The user who executed the statement has the CREATE OBJECT ON TABLESPACE privilege on the created tablespace.

One of the following privileges is required to create objects in the created tablespace.

Syntax Rules and Parameters

[ MEMORY ] [ DATA ]

It is a memory tablespace used to store permanent objects such as tables, indexes, and more.
The reserved words MEMORY and DATA may be omitted.

tablespace_name

It is the name of the tablespace to be created.
The length of the tablespace name must be less than 128 bytes.

<memory datafile clause>

<size clause>

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

<domain name>

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

ONLINE | OFFLINE

It sets the tablespace to ONLINE or OFFLINE.

EXTSIZE <size clause>

It specifies the extent size of the tablespace.

Description

A data tablespace is an object that provides the physical storage space for SQL schema objects such as tables and indexes (LOGGING).

Examples

The following is an example of how to create a memory data tablespace.

gSQL> CREATE TABLESPACE space1 DATAFILE 'test_file_1.dbf' SIZE 10M REUSE;

Tablespace created.

The following is an example of how to create a tablespace that consists of multiple data files.

gSQL> CREATE TABLESPACE space1 
             DATAFILE 'test_file_3_1.dbf' SIZE 10M REUSE,
                      'test_file_3_2.dbf' SIZE 10M REUSE;

Tablespace created.

Compatibility

The SQL standard does not define the concept of tablespaces.

For More Information

Refer to the following.

CREATE MEMORY TEMPORARY TABLESPACE

Function

It defines a memory temporary tablespace.

Syntax

<memory temporary tablespace statement> ::=
    CREATE [ MEMORY ] TEMPORARY TABLESPACE tablespace_name
        MEMORY <memory clause> [, ...]
        <temporary tablespace management clause>

<memory clause> 
     'memory_name' { SIZE <size clause> } [ AT <domain_name> ]

<temporary tablespace management clause> ::=
    EXTSIZE <size clause>

Invocation and Access Rules

The CREATE TABLESPACE ON DATABASE privilege is required to execute the <memory temporary tablespace definition>.

The user who executed the statement has the CREATE OBJECT ON TABLESPACE privilege on the created tablespace.

One of the following privileges is required to create objects in the created tablespace.

Syntax Rules and Parameters

[ MEMORY ] TEMPORARY

It is a memory temporary tablespace used to store no-logging indexes or temporary objects, such as intermediate results generated during query processing.
The reserved word MEMORY can be omitted.

tablespace_name

It is the name of the tablespace to be created.
The name must be less than 128 bytes in length.

<memory clause>

<size clause>

It specifies the size of the shared memory space in bytes. (If omitted, bytes are used by default.)
For temporary memory data, the image is not managed as a file.

<domain name>

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

EXTSIZE <size clause>

It specifies the extent size of the tablespace.

Description

A temporary tablespace is an object that provides the physical storage space for SQL schema objects such as indexes (NOLOGGING), as well as for intermediate results used in operations like sorting and hashing during query processing.

Examples

The following is an example of how to create a temporary tablespace.

gSQL> CREATE TEMPORARY TABLESPACE temp_space1 MEMORY 'test_memory_1' SIZE 10M;

Tablespace created.

The following is an example of how to create a temporary tablespace that includes multiple memory spaces.

gSQL> CREATE TEMPORARY TABLESPACE temp_space1 
             MEMORY 'test_memory_3_1' SIZE 10M,
                    'test_memory_3_2' SIZE 10M;

Tablespace created.

Compatibility

The SQL standard does not define the concept of tablespaces.

For More Information

Refer to the following.

CREATE PROFILE

Function

This statement creates the profile and sets the password management method. 
When a profile is assigned to a user, the user's password is managed according to the method defined in the profile.

Syntax

<profile definition> ::=

    CREATE PROFILE profile_name LIMIT 
    { <password_parameters>, ...}
    ; 

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

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

<password_parameter_number_interval> ::=
   integer 
 | integer / integer

Invocation and Access Rules

The CREATE PROFILE ON DATABASE privilege is required to execute the <profile definition>.

Syntax Rules and Parameters

profile_name

It specifies the name of the profile to be created.

password_parameters

It sets the parameters for password management.

Omitted parameters default to the "DEFAULT" profile policy.

FAILED_LOGIN_ATTEMPTS

It sets the number of consecutive failed login attempts allowed.
If the number of failed attempts exceeds the specified value, the account is locked.

PASSWORD_LOCK_TIME

It sets the duration (in days) for which the account remains locked after consecutive failed login attempts.

PASSWORD_LIFE_TIME

It sets the password lifetime (in days).

PASSWORD_GRACE_TIME

It defines the grace period during which users can still log in after the password expires as specified by PASSWORD_LIFE_TIME.

PASSWORD_GRACE_TIME begins at the first login attempt after the password validity period has passed. If the password is not changed during this period, it will expire.

PASSWORD_REUSE_MAX

It sets the number of the recent passwords that cannot be reused when a user attempts to use a previously used password.

PASSWORD_REUSE_MAX must be used together with PASSWORD_REUSE_TIME.

PASSWORD_REUSE_TIME

It sets the duration for which a previously used password is prohibited from being reused when a user attempts to reuse it.

PASSWORD_REUSE_TIME must be used together with PASSWORD_REUSE_MAX.

PASSWORD_VERIFY_FUNCTION

It specifies the methods for verifying password complexity.

KISA_VERIFY_FUNCTION

It is the password verification method defined by KISA (Korea Internet & Security Agency).

ORA12C_VERIFY_FUNCTION

It is the password verification method used by Oracle's ORA12C_VERIFY_FUNCTION.

ORA12C_STRONG_VERIFY_FUNCTION

It is the password verification method used by Oracle's ORA12C_STRONG_VERIFY_FUNCTION.

VERIFY_FUNCTION_11G

It is the password verification method used by Oracle's VERIFY_FUNCTION_11G.

VERIFY_FUNCTION

It is the password verification method used by Oracle's VERIFY_FUNCTION.

Description

Account Lockout

The following parameters affect account lockout.

For example, when creating the following profile and user:

CREATE PROFILE prof LIMIT
    FAILED_LOGIN_ATTEMPTS 4
    PASSWORD_LOCK_TIME 30;

ALTER USER u1 PROFILE prof;
If user u1 fails to log in more than four times, the account is locked for 30 days and will be automatically unlocked afterward.
If PASSWORD_LOCK_TIME is set to UNLIMITED, the account lockout must be manually released using the ALTER USER statement.
ALTER USER user1 ACCOUNT UNLOCK;

Password Expiration

The following parameters affect password expiration.

The password expires according to the following sequence.

  1. Password is set.

    • The password expiration time is defined as the period elapsed since the password was last changed, based on PASSWORD_LIFE_TIME.

    • When the password expiration status is OPEN, normal login is allowed.

  1. When a user logs in after the password has expired,

    • The login is successful, but the password expiration status changes to EXPIRED (GRACE). The following warnings will be displayed:

      • ERR-28000(16310): The password will expire in n days

      • ERR-28000(16311): The password will expire soon

      • The SQL standard does not define the concept of password expiration.

      • 28000 is the authentication warning or the SQL standard status code of an error. 16310, 16311 are the GOLDILOCKS error codes.

    • The password expiration time is reset based on the time elapsed since the user logged in, according to PASSWORD_GRACE_TIME.

  1. When a user logs in after the grace period,

    • The password expiration status changes to EXPIRED, and the user will not be able to log in. The following error occurs:

      • ERR-28000(16312): The password has expired

      • The SQL standard does not define the concept of password expiration.

      • 28000 is the authentication warning or SQL standard status code of an error. 16312 is the GOLDILOCKS error code.

      • The GOLDILOCKS internal error code 16312 should be used to control password re-entry through the program.

Password expiration state transition

Step

Timing

Login result

Password status

1

Password is changed

Success

OPEN

2

After PASSWORD_LIFE_TIME

Success with warning

EXPIRED(GRACE)

3

After PASSWORD_GRACE_TIME

Error

EXPIRED

Refer to the following example.

CREATE PROFILE prof LIMIT
   PASSWORD_LIFE_TIME 90
   PASSWORD_GRACE_TIME 3;

ALTER USER u1 PROFILE prof;
In the above example, user u1 successfully logs in after 90 days, but receives a warning message that the password will expire in three days.
If the password is not changed within three days, it will expire.
Once the password has expired, a message prompting the user to enter a new password is displayed at login, and access to the account is denied.

Password Reusability

The following are the parameters affecting password reusability.

The password reusability of the two parameters above is determined according to the following table.

Conditions for password reusability

PASSWORD_REUSE_MAX

PASSWORD_REUSE_TIME

Conditions for password reusability

value

value

Both PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX conditions must be met.

value

UNLIMITED

Always prohibited

UNLIMITED

value

Always prohibited

UNLIMITED

UNLIMITED

Always permitted

If a profile is created as follows:

CREATE PROFILE prof LIMIT
   PASSWORD_REUSE_MAX 5
   PASSWORD_REUSE_TIME 3;

the last five passwords and any password changed within the past three days cannot be reused.

The following is an example of user u1's password change history. If the current password is P#_000007 and today's date is 2015-08-08, the reusability of previous passwords is as follows:

Example of password reusability

Password

Password_date

Password reusability status

P#_000001

2015-08-01

Possible

P#_000002

2015-08-02

Possible

P#_000003

2015-08-03

Violation of REUSE_MAX

P#_000004

2015-08-04

Violation of REUSE_MAX

P#_000005

2015-08-05

Violation of REUSE_MAX, REUSE_TIME

P#_000006

2015-08-06

Violation of REUSE_MAX, REUSE_TIME

P#_000007

2015-08-07

Violation of REUSE_MAX, REUSE_TIME

The accumulated password change history used to check password reusability can be deleted using the following statement.

ALTER DATABASE CLEAR PASSWORD HISTORY;

DEFAULT profile

When the database is created, the following 'DEFAULT' profile is automatically generated. The password parameters of the 'DEFAULT' profile are as follows.

Configuration of the DEFAULT profile

Parameter

Value

FAILED_LOGIN_ATTEMPTS

10

PASSWORD_LOCK_TIME

1

PASSWORD_LIFE_TIME

180

PASSWORD_GRACE_TIME

7

PASSWORD_REUSE_MAX

UNLIMITED

PASSWORD_REUSE_TIME

UNLIMITED

PASSWORD_VERIFY_FUNCTION

NULL

The default values of the "DEFAULT" profile have the following characteristics.

The DEFAULT profile can not be dropped, but it can be altered as follows:

ALTER PROFILE DEFAULT LIMIT ...

Examples

The following is an example of creating a profile to control account lockout. The account is locked for three days after three consecutive login failures.

gSQL> CREATE PROFILE prof1 LIMIT
        FAILED_LOGIN_ATTEMPTS 3
        PASSWORD_LOCK_TIME 3;

Profile created.

gSQL> COMMIT;

Commit complete.

The following is an example of creating a profile to control password expiration. The password lifetime is 90 days, and the grace period is seven days.

gSQL> CREATE PROFILE prof1 LIMIT
        PASSWORD_LIFE_TIME 90 
        PASSWORD_GRACE_TIME 7;

Profile created.

gSQL> COMMIT;

Commit complete.

The following is an example of creating a profile to control password reusability. This example does not require verifying the old password when changing the password.

gSQL> CREATE PROFILE prof1 LIMIT
        PASSWORD_REUSE_MAX  DEFAULT
        PASSWORD_REUSE_TIME DEFAULT;

Profile created.

gSQL> COMMIT;

Commit complete.

The following is an example of creating a profile to enforce password complexity requirements.

gSQL> CREATE PROFILE prof1 LIMIT
        PASSWORD_VERIFY_FUNCTION KISA_VERIFY_FUNCTION;

Profile created.

gSQL> COMMIT;

Commit complete.

The following is an example of creating a profile by setting all parameters.

gSQL> CREATE PROFILE prof1 LIMIT
        FAILED_LOGIN_ATTEMPTS 3
        PASSWORD_LOCK_TIME 3
        PASSWORD_LIFE_TIME 90 
        PASSWORD_GRACE_TIME 7
        PASSWORD_REUSE_MAX  DEFAULT
        PASSWORD_REUSE_TIME DEFAULT
        PASSWORD_VERIFY_FUNCTION KISA_VERIFY_FUNCTION;

Profile created.

gSQL> COMMIT;

Commit complete.

Compatibility

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

For More Information

Refer to the following.

CREATE ROLE

Function

It defines the role.

Syntax

<role definition> ::=
    CREATE ROLE <role_name> [ WITHOUT GRANT ]
    ;

Invocation and Access Rules

The CREATE ROLE ON DATABASE privilege is required to execute the <role definition>.

No additional privileges are granted to the created <role_name>.

By default, the user who creates the <role_name> is automatically granted the <role_name>.

Appropriate privileges must be granted to <role_name> to enable users assigned this <role_name> to access sessions and execute SQL statements.

Syntax Rules and Parameters

<role_name>

It is the name of the role to be defined.
There should be no existing user or role with the same name. 
The length of the <role_name> must be less than 128 bytes.

WITHOUT GRANT

It does not grant the <role_name> to the user who created it.

Description

The role is an authorization object that consists of a set of privileges.
When performing a <role definition>, the role is defined without any privileges.
Appropriate privileges should be granted to the role after it is defined.
By default, the user who creates a role is automatically granted that role.
To avoid being granted the created role, define it using the WITHOUT GRANT option.

Examples

A role can be defined by a user who has the CREATE ROLE ON DATABASE privilege.

gSQL> GRANT CREATE ROLE ON DATABASE TO u1;

Grant succeeded.

gSQL> commit;

Commit complete.

gSQL> \connect u1 u1

gSQL> CREATE ROLE role1;

Role created.
gSQL> \connect u2 u2

gSQL> CREATE ROLE role2;

ERR-42000(16210): lacks privilege (CREATE ROLE ON DATABASE)
By default, the created role is granted to the user who created it.
If a role is created with the WITHOUT GRANT option, the created role is not granted to the creator.
\connect u1 u1

gSQL> CREATE ROLE role1;

Role created.

gSQL> SELECT role_name
        FROM dba_roles 
       WHERE role_name = 'ROLE1';

ROLE_NAME
---------
ROLE1    

1 row selected.

gSQL> SELECT username , granted_role, admin_option
        FROM user_role_privs
       WHERE granted_role = 'ROLE1';

USERNAME GRANTED_ROLE ADMIN_OPTION
-------- ------------ ------------
U1       ROLE1        YES         

1 row selected.
\connect u1 u1

gSQL> CREATE ROLE role2 WITHOUT GRANT;

Role created.

gSQL> SELECT role_name
        FROM dba_roles 
       WHERE role_name = 'ROLE2';

ROLE_NAME
---------
ROLE2    

1 row selected.

gSQL> SELECT username , granted_role, admin_option
        FROM user_role_privs
       WHERE granted_role = 'ROLE2'; 

no rows selected.
Create an object under the role and grant it data manipulation privileges.
Then, grant the role, which has privileges to create objects and perform data manipulation, to the user.
The following is an example of a user who has been granted the role, creating an object and manipulating data.
gSQL> GRANT CREATE ANY TABLE, INSERT ANY TABLE ON DATABASE to role1;

Grant succeeded.

gSQL> GRANT create object on tablespace "MEM_DATA_TBS" to role1;

Grant succeeded.

gSQL> GRANT role1 TO u1;

Grant succeeded.

gSQL> SELECT grantee, privilege
        FROM dba_sys_privs
       WHERE grantee IN ( 'U1' , 'ROLE1' );

GRANTEE PRIVILEGE                                  
------- -------------------------------------------
ROLE1   CREATE ANY TABLE ON DATABASE               
ROLE1   INSERT ANY TABLE ON DATABASE               
ROLE1   CREATE OBJECT ON TABLESPACE "MEM_DATA_TBS" 
U1      CREATE SESSION ON DATABASE                 

4 rows selected.

gSQL> SELECT grantee, granted_role 
        FROM dba_role_privs 
       WHERE granted_role = 'ROLE1';

GRANTEE GRANTED_ROLE
------- ------------
U1      ROLE1       

1 rows selected.

\connect u1 u1

gSQL> CREATE TABLE t1( c1 INTEGER , c2 INTEGER );

Table created.

gSQL> INSERT INTO t1 VALUES( 1 , 2 );

1 row created.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

T331

Basic roles

O

T332

Extended roles

X

For More Information

Refer to DROP ROLE.

CREATE SCHEMA

Function

It defines the schema.

Syntax

<schema definition> ::=
    CREATE SCHEMA <schema name clause>
        [ <schema element> [...] ]
    ;

<schema name clause> ::=
      schema_name
    | AUTHORIZATION user_identifier
    | schema_name AUTHORIZATION user_identifier

<schema element> ::=
      <table definition>
    | <view definition>
    | <index definition>
    | <sequence generator definition>
    | <grant privilege statement>
    | <comment statement>

Invocation and Access Rules

The user must satisfy the following conditions to perform <schema definition>.

Syntax Rules and Parameters

schema_name

It is the name of the schema to be created.
An identical schema name must not already exist in the database.
The length of the schema name must be less than 128 bytes.

AUTHORIZATION user_identifier

If the schema name is omitted, a schema with the same name as the user_identifier is created.
If AUTHORIZATION is not specified, the user_identifier of the user executing the statement is used.

schema_name AUTHORIZATION user_identifier

It specifies the schema name and the owner of the schema to be created.
The owner can not be a role or PUBLIC.

<schema element>

It defines the objects to be created within the schema together with the schema at the time of schema creation.
The schema_elements are executed in the specified order and are separated by whitespace, not commas.
Objects cannot be defined under a schema with a name different from the schema being created.

Description

A schema is an object that logically classifies SQL schema objects such as tables, views, indexes, sequences, and constraints.
In GOLDILOCKS, the relationship between a user and schemas is 1:N. In other words, a user may own no schemas at all, or may own multiple schemas.
The SQL standard does not clearly define the relationships among non-schema objects such as users, schemas, and databases, and each DBMS defines the relationships between these non-schema objects differently, as shown below.

The relationship between users and schemas in other DBMSs





Examples

The following is an example of creating a schema.

gSQL> CREATE SCHEMA s1;

Schema created.

The following is an example of creating a schema and assigning its owner.

gSQL> CREATE SCHEMA s1 AUTHORIZATION test;

Schema created.

The following is an example of creating a schema along with the objects that belong to it.

gSQL> CREATE SCHEMA s1 
             CREATE TABLE t1 ( id INTEGER, name VARCHAR(128) )
             CREATE INDEX idx_t1_id ON t1 ( id )
             COMMENT ON TABLE t1 IS 'comment on s1.t1'
;

Schema created.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

S071

SQL paths in function and type name resolution

X

F461

Named character sets

X

F171

Multiple schemas per user

O

T332

Extended roles

X

For More Information

Refer to the following.

CREATE SEQUENCE

Function

It creates a sequence.

Syntax

<sequence generator definition> ::=
    CREATE SEQUENCE [schema_name.] sequence_name 
        [ <sequence generator option> [, ...] ]
    ;

<sequence generator option> ::=
      <sequence generator start with option> 
    | <basic sequence generator option>

<sequence generator start with option> ::=
    START 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

The user must have one of the following privileges to execute a <sequence generator definition> statement:
• (CREATE SEQUENCE or CONTROL SCHEMA) ON SCHEMA for the schema to which the sequence 
   belongs
• CREATE ANY SEQUENCE ON DATABASE
The sequence owner is determined as follows:
• The owner of the schema to which the sequence belongs
• If the schema to which the sequence belongs is PUBLIC, the owner is the user who executed the statement

The sequence owner has the USAGE ON SEQUENCE WITH GRANT OPTION privilege.

One of the following privileges is required to use the created sequence:
• USAGE ON SEQUENCE for the sequence
• (USAGE SEQUENCE or CONTROL SCHEMA) ON SCHEMA for the schema to which the sequence 
   belongs
• USAGE ANY SEQUENCE ON DATABASE

Syntax Rules and Parameters

sequence_name

It is the name of the sequence to be created, and it must be unique within the schema.
The schema to which the sequence belongs can be specified using the format schema_name.sequence_name. If schema_name is omitted, the default schema of the user executing the statement is used.
The length of the sequence name must be less than 128 bytes.

<sequence generator option>

If none of the <sequence generator options> are used, the following two statements have the same meaning.

<sequence generator start with option>

It defines the first sequence number to be generated.
Depending on whether the sequence is ascending or descending, it has the following characteristics.

<sequence generator increment by option>

It defines the interval between sequence numbers.
The constraints and characteristics are as follows.

<sequence generator maxvalue option>

It defines the maximum value that can be generated by the sequence.

<sequence generator minvalue option>

It defines the minimum value that can be generated by the sequence.

<sequence generator cycle option>

It specifies whether to continue generating values when the sequence reaches its maximum or minimum value.

<sequence generator cache option>

It defines the number of sequence values to preload into memory for faster access.
When the database is restarted, the preloaded sequence values in memory are lost, and the sequence resumes from the next value after the last loaded one.

Description

The created sequence object uses sequence values through the NEXTVAL and CURRVAL functions.

The sequence value does not have transactional properties. The sequence retains the most recent value even if an error occurs in the SQL statement using the sequence function or if an explicit ROLLBACK is performed.
The CURRVAL function returns the most recent NEXTVAL value called within the session.
By utilizing this feature, you can continue using the sequence value obtained by NEXTVAL in subsequent SQL statements. However, if NEXTVAL has not been called in the session, using CURRVAL will result in an error.

Examples

The sequence object seq1, created without explicitly defining sequence options, is an ascending sequence by default. This means it behaves identically to the sequence object seq2 in the following example:

gSQL> CREATE SEQUENCE seq1;

Sequence created.


gSQL> CREATE SEQUENCE seq2 START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE NO CYCLE CACHE 20;

Sequence created.

The following is an example of a sequence that generates an odd value.

gSQL> CREATE SEQUENCE seq1 START WITH 1 INCREMENT BY 2;

Sequence created.

The following is an example of creating a sequence that repeatedly generates even numbers starting from 0 up to 1000.

gSQL> CREATE SEQUENCE seq1 START WITH 0 MINVALUE 0 MAXVALUE 1000 INCREMENT BY 2 CYCLE;

Sequence created.

The following is an example of generating a descending sequence starting from -1.

gSQL> CREATE SEQUENCE seq1 INCREMENT BY -1;

Sequence created.

Compatibility

The SQL standard does not define the <sequence generator cache option> clause.

SQL standard compatibility

Feature ID

Description

Compatibility

T176

Sequence generator support

O

For More Information

Refer to the following.

CREATE SYNONYM

Function

It creates a synonym. A synonym is an alternative name for a table, view, sequence, or another synonym, and it can be used in the following statements.

Syntax

<synonym definition> ::=    
    CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema_name.]synonym_name 
    FOR [schema_name.]object_name
    ;

Invocation and Access Rules

The user must meet the following conditions to execute the <synonym definition> statement.

Syntax Rules and Parameters

[ OR REPLACE ]

It replaces the existing synonym if the synonym already exists.

[ PUBLIC ]

It is specified when creating a public synonym.
If omitted, a private synonym is created.

synonym_name

It is the name of the synonym to be created, and it must be unique within the schema.
The schema to which the synonym belongs can be defined using the format schema_name.table_name. If schema_name is omitted, the default schema of the user executing the statement will be used. 
The length of the synonym name must be less than 128 bytes.
A public synonym is a non-schema object; therefore, a schema name cannot be specified when creating a public synonym using the PUBLIC keyword.

object_name

The schema to which the object belongs can be defined using the format schema_name.table_name.
If schema_name is omitted, the default schema of the user executing the statement will be used.
The object types for which object_name can be specified are as follows.

When a statement using a synonym is executed, checks are performed for object existence, cycles, and privileges.

Description

A synonym is an alternative name for a table, view, sequence, or another synonym.

If a synonym is created, applications do not need to be modified even when the underlying object changes; only the synonym needs to be redefined. This makes it convenient to manage changes.
In addition, database security is improved by hiding the real names and schemas of objects, and usability is enhanced by allowing long object names to be replaced with shorter, more user-friendly names.

Since a synonym is literally an alternative name, creating it does not mean that the object can be accessed through the synonym. Appropriate privileges on the underlying object are required to access it.

When a statement is executed using a synonym, the object access procedure is as follows:

  1. Look for a table with the specified name.

  2. If the table does not exist, search for a private synonym with that name.

  3. If no private synonym is found, search for a public synonym with that name.

gSQL> CREATE PUBLIC SYNONYM syn1 FOR u1.t1;

Synonym created.

gSQL> CREATE PUBLIC SYNONYM syn2 FOR syn1;

Synonym created.

gSQL> SELECT * FROM syn2;
The following describes the object access sequence in the above SELECT statement example:

  1. It searched for the table syn2, but it does not exist.

  2. It searched for the private synonym syn2, but it does not exist.

  3. It searched for the public synonym syn2, and it exists.

    1. It searched for the table syn1, but it does not exist.

    2. It searched for the private synonym syn1, but it does not exist.

    3. It searched for the public synonym syn2, and it exists.

      1. It searched for the table u1.t1, and it exists.

Examples

The following is an example of creating a private synonym.

gSQL> CREATE SYNONYM MyEmp FOR branch.Employee;

Synonym created.


gSQL> SELECT * FROM MyEmp;

The following is an example of creating a public synonym.

gSQL> CREATE PUBLIC SYNONYM MainEmp FOR main.Employee;

Synonym created.


gSQL> SELECT * FROM MainEmp;

Compatibility

The SQL standard does not define the CREATE SYNONYM statement.

For More Information

Refer to DROP SYNONYM.

CREATE TABLE

Function

It defines a table.

Syntax

<table definition> ::=
    CREATE TABLE table_name
        ( <table element> [, ...] )
        [ <table sharding strategy> ]
        [ <table attribute clause> [...] ]
        [ TABLESPACE tablespace_name ]
        [ <table global secondary index clause> ]
    ;

<table element> ::=
      <column definition>
    | <table constraint definition>

<column definition> ::=
    column_name <data type> 
        [ <default clause> | <identity column specification> ]
        [ <column constraint definition> ]

<data type> ::=
      <character string type>
    | <binary string type>
    | <numeric type>
    | <boolean type>
    | <datetime type>
    | <interval type>

<character string type> ::=
      CHARACTER [ ( integer [ <character length units> ] ) ]
    | CHAR [ ( integer [ <character length units> ] ) ]
    | CHARACTER VARYING ( integer [ <character length units> ] )
    | CHAR VARYING ( integer [ <character length units> ] )
    | VARCHAR ( integer [ <character length units> ] )
    | CHARACTER LONG VARYING
    | LONG VARCHAR
  
<character length units> ::=
      CHARACTERS
    | CHAR
    | OCTETS
    | BYTE

<binary string type> ::=
      BINARY [ ( length ) ]
    | BINARY VARYING ( length )
    | VARBINARY ( length )
    | LONG BINARY VARYING
    | LONG VARBINARY

<numeric type> ::=
      <exact numeric type>
    | <approximate numeric type>
    | <native numeric type>

<exact numeric type> ::=
      NUMERIC [ ( precision [, scale ] ) ]
    | SMALLINT
    | INTEGER
    | INT
    | BIGINT

<approximate numeric type> ::=
    | FLOAT [ ( precision ) ]
    | REAL
    | DOUBLE PRECISION

<native numeric type> ::=
      NATIVE_SMALLINT
    | NATIVE_INTEGER
    | NATIVE_BIGINT
    | NATIVE_REAL
    | NATIVE_DOUBLE

<boolean type> ::=
    BOOLEAN

<datetime type> ::=
      DATE
    | TIME [ ( time_precision ) ] [ WITH TIME ZONE | WITHOUT TIME ZONE ]
    | TIMESTAMP [ ( timestamp_precision ) ] [ WITH TIME ZONE | WITHOUT TIME ZONE ]

<interval type> ::=
    INTERVAL <interval qualifier>

<interval qualifier> ::=
      <non-second primary datetime field> [ ( interval_leading_field_precision ) ]
          TO { <non-second primary datetime field> | SECOND [ ( interval_fractional_seconds_precision ) ] }
    | <non-second primary datetime field> [ ( interval_leading_field_precision ) ]
    | SECOND [ ( interval_leading_field_precision [, interval_fractional_seconds_precision ] ) ]

<non-second primary datetime field> ::=
      YEAR
    | MONTH
    | DAY
    | HOUR
    | MINUTE

<default clause> ::=
    DEFAULT <default option>

<default option> ::=
      constant
    | NULL
    | expression

<identity column specification> ::=
    GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY 
    [ ( <common sequence generator option> [, ...] ) ]

<common sequence generator option> ::=
      START WITH integer_constant
    | <basic sequence generator option>

<basic sequence generator option> ::=
      INCREMENT BY integer_constant 
    | { MAXVALUE integer_constant | NO MAXVALUE }
    | { MINVALUE integer_constant | NO MINVALUE }
    | { CYCLE | NO CYCLE }
    | { CACHE integer_constant | NO CACHE }

<column constraint definition> ::=
    [ CONSTRAINT constraint_name ] <column constraint> [ <constraint characteristics> ]

<column constraint> ::=
      NOT NULL
    | { UNIQUE | PRIMARY KEY } [ <index name clause> [ <index attributes> ] [ TABLESPACE index_tablespace_name ] ]
    | <references specification> [ <index name clause> [ <index attributes> ] [ TABLESPACE index_tablespace_name ] ]
    | <check constraint definition> 

<index name clause> ::=
    INDEX index_name

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


<table constraint definition> ::=
    [ CONSTRAINT constraint_name ] <table constraint> [ <constraint characteristics> ]

<table constraint> ::=
      <unique constraint definition> [ <index name clause> [ <index attributes> ] [ TABLESPACE index_tablespace_name ] ]
    | <referential constraint definition> [ <index name clause> [ <index attributes> ] [ TABLESPACE index_tablespace_name ] ]
    | <check constraint definition>

<unique constraint definition> ::=
    { UNIQUE | PRIMARY KEY } ( <key column element> [, ...] )

<check constraint definition> ::= 
    CHECK ( search_condition ) 

<referential constraint definition> ::= 
    FOREIGN KEY ( <referencing column list> ) <references specification> [ <key constraint index option> ]

<references specification> ::=  
    REFERENCES <referenced table and columns> [ MATCH SIMPLE ] [ <referential triggered action> ] 

<referencing column list> ::=   
    <key column element> [, <key column element> ... ]   

<referenced table and columns> ::= 
    <table name> [ ( <referenced column list> ) ] 

<referenced column list> ::=  
    <column name list>         

<referential triggered action> ::=     
      <update rule> [ <delete rule> ]  
    | <delete rule> [ <update rule> ]  

<update rule> ::=       
    ON UPDATE <referential action>  

<delete rule> ::=     
    ON DELETE <referential action>  

<referential action> ::=      
      CASCADE 
    | SET NULL      
    | SET DEFAULT    
    | RESTRICT      
    | NO ACTION      

<key column element> ::=
    column_name [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]


<table sharding strategy> ::=
      <cloned strategy>
    | <hash sharding strategy>
    | <range sharding strategy>
    | <list sharding strategy>

<cloned strategy> ::=
    CLONED [ <clone placement> ]

<clone placement> ::=
      AT CLUSTER WIDE
    | AT CLUSTER GROUP group_list

<hash sharding strategy> ::=
    SHARDING BY [HASH] ( column_list )
    [ <hash shard count> ]
    [ <hash shard placement> ]

<hash shard count> ::=
    SHARD COUNT integer

<hash shard placement> ::=
      AT CLUSTER WIDE
    | AT CLUSTER GROUP group_list

<range sharding strategy> ::=
    SHARDING BY RANGE ( column_list )
    { <cluster-wide range shard placement> | <group-specific range shard placement> }

<cluster-wide range shard placement> ::=
    AT CLUSTER WIDE
    <range shard definition> [, ...]

<group-specific range shard placement> ::=
    <group-specific range shard definition> [, ...]

<group-specific range shard definition> ::=
    <range shard definition> AT CLUSTER GROUP group_name

<range shard definition> ::=
    SHARD range_name VALUES LESS THAN ( <range value clause> )

<range value clause> ::=
    <range value> [, ...]

<range value> ::=
      constant
    | MAXVALUE

<list sharding strategy> ::=
    SHARDING BY LIST ( column_name )
    { <cluster-wide list shard placement> | <group-specific list shard placement> }

<cluster-wide list shard placement> ::=
    AT CLUSTER WIDE
    <list shard definition> [, ...]

<group-specific list shard placement> ::=
    <group-specific list shard definition> [, ...]

<group-specific list shard definition> ::=
    <list shard definition> AT CLUSTER GROUP group_name

<list shard definition> ::=
      SHARD shard_name VALUES IN ( <list value clause> )

<list value clause> ::=
    <list value> [, ...]

<list value> ::=
      constant
    | NULL
    | DEFAULT


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

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

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

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

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


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

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

<constraint enforcement> ::=  
    [ NOT ] ENFORCED 

<table global secondary index clause> ::=
      WITH GLOBAL SECONDARY INDEX [ <index attributes> [...] ] [ TABLESPACE tablespace_name ]
    |  WITHOUT GLOBAL SECONDARY INDEX

Invocation and Access Rules

The differences between a stand-alone database and a cluster database are as follows:

The user must satisfy the following conditions to execute a <table definition> statement.

<table sharding strategy> statement can be used in a cluster system.

Syntax Rules and Parameters

table_name

It is the table name to be created, and it must be unique within the schema.
The schema to which the table belongs can be defined using the format schema_name.table_name. If schema_name is omitted, the default schema of the user executing the statement will be used.
The length of the table name must be less than 128 bytes.

<column definition>

It defines the columns that configure the table.
The table must include one or more column definitions. 
The column definition can include the data type, default value, automatically generated value, and constraints.

column_name

It is name of the column that configures the table, and each column must have a unique name within the table.
The length of the column name must be less than 128 bytes.

<data type>

It defines the data type of the column.
When defining a column with automatically generated values (<identity column specification>), its data type must be one of SMALLINT, INTEGER or BIGINT.
For more information about data types, refer to Data Type.

<character length units>

It specifies the length unit per character for character data types.

The SQL standard defines CHARACTERS as the default value.

The default value of the char length unit in other DBMSs is as follows:

[ <default clause> | <identity column specification> ]

It specifies the default value of a column.
The <default clause> and <identity column specification> can not be used together.
If both are omitted, the default value is NULL.

<default clause>

The DEFAULT clause specifies the default value to be used when DEFAULT is explicitly stated in INSERT or UPDATE statements, or when the corresponding column name is omitted.

The data type of the DEFAULT expression must be compatible with the column's data type.
If it is not compatible, or if the expression is invalid, an error will occur.
--# result: error
CREATE TABLE t1 ( c1 INTEGER DEFAULT 1 / 0 );

ERR-22012(12122): divisor is equal to zero


--# result: success
CREATE TABLE t1 ( c1 INTEGER DEFAULT 1 / 1 );

Table created.
A DEFAULT expression can use any built-in functions, except for the following.

<identity column specification>

It defines a column with values that are automatically generated.
A table can have only one identity column.
Even if the NOT NULL constraint is not explicitly specified, the identity column is treated as a not null column.
The <identity column specification> clause can not be used together with the DEFAULT clause. The <identity column specification> clause, like the DEFAULT clause, provides a default value in INSERT and UPDATE statements, or defines the default value to be used when the column name is omitted.

The method for generating values is defined as follows.

For more information about <common sequence generator option> and <basic sequence generator option>, which are options for creating an identity column, refer to CREATE SEQUENCE.

<column constraint definition>

It defines the following constraints for the column.

constraint_name

It is the name of the constraint and can be omitted.
If constraint_name is omitted, it will be automatically generated as follows.
If the automatically generated name conflicts with an existing one, the constraint_name must be explicitly specified.

The length of the constraint name must be less than 128 bytes.

NOT NULL Constraint

NULL values are not allowed for the column.

CHECK Constraint

A CHECK constraint defines a condition that each row must satisfy.

The <search_condition> specified in the CHECK constraint must be a logical expression that returns a BOOLEAN value.

The constraint is considered satisfied when the result of <search_condition> is TRUE or UNKNOWN. If the result is FALSE, the constraint is violated.

When defining a CHECK constraint, the following restrictions must be observed:

There is no priority among multiple CHECK constraints, and the system does not check for logical contradictions between them. Therefore, care must be taken when defining multiple CHECK constraints to ensure they do not conflict with each other.
CREATE TABLE t1 
(
   value1 INTEGER,
   value2 INTEGER,
   CONSTRAINT t1_check_1 CHECK ( value1 > value2 ),
   CONSTRAINT t1_check_2 CHECK ( value1 < 0 ),
   CONSTRAINT t1_check_3 CHECK ( value2 > 0 )
);

A CHECK constraint can only be dropped by using its constraint name.

ALTER TABLE t1 DROP CONSTRAINT t1_check_1;

UNIQUE Constraint

Duplicate values are not allowed for the column, but NULL values are permitted.

PRIMARY KEY Constraint

NULL values and duplicate values are not allowed in the column.
Only one PRIMARY KEY constraint can be defined per table.

FOREIGN KEY Constraint

A FOREIGN KEY constraint, also known as a referential constraint, defines a relationship between a FOREIGN KEY column and a column that has a PRIMARY KEY or UNIQUE constraint.

The value in the FOREIGN KEY column must match a value in the referenced column that has a PRIMARY KEY or UNIQUE constraint.

However, if the FOREIGN KEY column contains a NULL value, the constraint is considered satisfied regardless of whether a matching value exists in the referenced column.

The table that defines the referential constraint is called the referencing table or child table. This table must be a base table; temp tables and views are not allowed.

<referential constraint definition>

A <referential constraint definition> is used to define a referential constraint.

When defining a referential constraint, the associated index is automatically created based on the <referencing column list> and the <key constraint index option>.

<referencing column list>

The columns of the referencing table identified by the <referencing column list> are called referencing columns.

<references specification>

When defining a FOREIGN KEY constraint as a column constraint, it must be specified using <references specification>. When defining it as a table constraint, it must be specified using <referential constraint definition>.

CREATE TABLE child ( fk1 INTEGER REFERENCES parent(pk) );
CREATE TABLE child ( fk1 INTEGER,
                     FOREIGN KEY (fk1) REFERENCES parent(pk) );

<referenced table and columns>

The table referenced by a referential constraint is called the referenced table or parent table.

<referenced column list>

The columns of the referenced table identified by the <referenced column list> are called referenced columns.

<referential triggered action>

A <referential triggered action> consists of a referential update action and a referential delete action.

Referential update actions and referential delete actions are collectively referred to as referential actions.

Referential actions are executed before the referential constraint is checked.

If an <update rule> is not specified in the <referential constraint definition>, the default <referential action> for the <update rule> is NO ACTION.

If a <delete rule> is not specified in the <referential constraint definition>, the default <referential action> for the <delete rule> is also NO ACTION.

ON UPDATE CASCADE

When a referenced column in the referenced table is updated, the corresponding referencing column in the matching row of the referencing table is also updated accordingly.

CREATE TABLE parent ( pk INTEGER PRIMARY KEY );
CREATE TABLE child ( fk INTEGER REFERENCES parent(pk) ON UPDATE CASCADE );
INSERT INTO parent VALUES ( 1 );
INSERT INTO child  VALUES ( 1 );
COMMIT;

gSQL> UPDATE parent SET pk = 2 WHERE pk = 1;
1 row updated.

gSQL> SELECT * FROM child;
FK
--
 2
1 row selected.

ON UPDATE SET NULL

When a referenced column in the referenced table is updated, the corresponding referencing column in the matching row of the referencing table is updated to NULL.

CREATE TABLE parent ( pk INTEGER PRIMARY KEY );
CREATE TABLE child ( fk INTEGER REFERENCES parent(pk) ON UPDATE SET NULL );
INSERT INTO parent VALUES ( 1 );
INSERT INTO child  VALUES ( 1 );
COMMIT;

gSQL> UPDATE parent SET pk = 2 WHERE pk = 1;
1 row updated.

gSQL> SELECT * FROM child;
  FK
----
null
1 row selected.

ON UPDATE SET DEFAULT

When a referenced column in the referenced table is updated, the corresponding referencing column in the matching row of the referencing table is updated to the default value.

CREATE TABLE parent ( pk INTEGER PRIMARY KEY );
CREATE TABLE child ( fk INTEGER DEFAULT 0 REFERENCES parent(pk) ON UPDATE SET DEFAULT );
INSERT INTO parent VALUES ( 0 ), ( 1 );
INSERT INTO child  VALUES ( 1 );
COMMIT;

gSQL> UPDATE parent SET pk = 2 WHERE pk = 1;
1 row updated.

gSQL> SELECT * FROM child;
FK
--
 0
1 row selected.

ON UPDATE RESTRICT

If a matching row exists in the referencing table, the referenced column in the referenced table cannot be updated.

CREATE TABLE parent ( pk INTEGER PRIMARY KEY );
CREATE TABLE child ( fk INTEGER REFERENCES parent(pk) ON UPDATE RESTRICT );
INSERT INTO parent VALUES (-1),(1);
INSERT INTO child  VALUES (-1),(1);
COMMIT;

gSQL> UPDATE parent SET pk = -pk;
ERR-23001(16660): referential constraint "PUBLIC"."CHILD_FOREIGN_KEY_FK_REFERENCES_PARENT_PK" restriction violated : can not delete or update parent row

ON UPDATE RESTRICT represents a stricter constraint than ON UPDATE NO ACTION.

The difference between ON UPDATE RESTRICT and ON UPDATE NO ACTION can be summarized as follows.

CREATE TABLE parent ( pk INTEGER PRIMARY KEY );
CREATE TABLE child ( fk INTEGER REFERENCES parent(pk) );
INSERT INTO parent VALUES (-1),(1);
INSERT INTO child  VALUES (-1),(1);
COMMIT;

gSQL> UPDATE parent SET pk = -pk;
2 rows updated.

ON UPDATE NO ACTION

Only referential constraint checking is performed without any referential update action.

ON DELETE CASCADE

When a referenced row in the referenced table is deleted, all matching rows in the referencing table are also deleted.

CREATE TABLE parent ( pk INTEGER PRIMARY KEY );
CREATE TABLE child ( fk INTEGER REFERENCES parent(pk) ON DELETE CASCADE );
INSERT INTO parent VALUES ( 1 );
INSERT INTO child  VALUES ( 1 );
COMMIT;

gSQL> DELETE FROM parent WHERE pk = 1;
1 row deleted.

gSQL> SELECT * FROM child;
no rows selected.

ON DELETE SET NULL

When a referenced row in the referenced table is deleted, the corresponding referencing column in the matching row of the referencing table is updated to NULL.

CREATE TABLE parent ( pk INTEGER PRIMARY KEY );
CREATE TABLE child ( fk INTEGER REFERENCES parent(pk) ON DELETE SET NULL );
INSERT INTO parent VALUES ( 1 );
INSERT INTO child  VALUES ( 1 );
COMMIT;

gSQL> DELETE FROM parent WHERE pk = 1;
1 row deleted.

gSQL> SELECT * FROM child;
  FK
----
null
1 row selected.

ON DELETE SET DEFAULT

When a referenced row in the referenced table is deleted, the corresponding referencing column in the matching row of the referencing table is updated to the default value.

CREATE TABLE parent ( pk INTEGER PRIMARY KEY );
CREATE TABLE child ( fk INTEGER DEFAULT 0 REFERENCES parent(pk) ON DELETE SET DEFAULT );
INSERT INTO parent VALUES ( 0 ), ( 1 );
INSERT INTO child  VALUES ( 1 );
COMMIT;

gSQL> DELETE FROM parent WHERE pk = 1;
1 row deleted.

gSQL> SELECT * FROM child;
FK
--
 0
1 row selected.

ON DELETE RESTRICT

If a matching row exists in the referencing table, the referenced row in the referenced table cannot be deleted.

CREATE TABLE parent ( pk INTEGER PRIMARY KEY );
CREATE TABLE child ( fk INTEGER REFERENCES parent(pk) ON DELETE RESTRICT );
INSERT INTO parent VALUES ( 1 );
INSERT INTO child  VALUES ( 1 );
COMMIT;

gSQL> DELETE FROM parent WHERE pk = 1;
ERR-23001(16660): referential constraint "PUBLIC"."CHILD_FOREIGN_KEY_FK_REFERENCES_PARENT_PK" restriction violated : can not delete or update parent row

ON DELETE RESTRICT represents a stricter constraint than ON DELETE NO ACTION.

ON DELETE NO ACTION

Only referential constraint checking is performed without any referential delete action.

Usage Restrictions on <referential triggered action>

If a referencing column is a generated column, the following <referential triggered action> cannot be specified.

CREATE TABLE parent ( pk INTEGER PRIMARY KEY );

gSQL>
CREATE TABLE child ( fk INTEGER GENERATED BY DEFAULT AS IDENTITY
                     REFERENCES parent(pk) ON DELETE SET NULL );
ERR-42000(16637): for referencing generated column, delete rule shall not specify SET NULL or SET DEFAULT : 
                     REFERENCES parent(pk) ON DELETE SET NULL )
                                                     *
ERROR at line 2:

<index name clause>

It specifies the index name to be created when a UNIQUE or PRIMARY KEY constraint is defined.

When a UNIQUE or PRIMARY KEY constraint is defined and the INDEX clause is omitted, an index that satisfies the constraint is automatically created.
The automatically generated index name is assigned as "constraint_name" + "_INDEX".

<table constraint definition>

<unique constraint definition>

Table constraint definitions differ from column constraint definitions in the following ways, syntactically.

key column element

It specifies the column that will be the target of the key.

<table sharding strategy>

It defines the sharding strategy for the table.
One of the following four strategies can be specified.

If omitted, the value is determined by the DEFAULT_SHARDING property.

<cloned strategy>

All data in the table is replicated to each shard.

<clone placement>

It defines the placement strategy for a clone.

<hash sharding strategy>

It shards the table data based on the hash value of the sharding key.

SHARDING BY [HASH] ( column_list )

It defines the sharding key used for hash sharding.

<hash shard count>

It defines the number of hash shards to be partitioned.
The number of shards can be defined from 1 to 512.
If omitted, the default value is 24.

<hash shard placement>

It defines the placement strategy for a hash shard.

<range sharding strategy>

It shards the table data based on the range of the sharding key values.

SHARDING BY RANGE ( column_list )

It defines the sharding key for range sharding.

<cluster-wide range shard placement>

Range shards are automatically distributed across all cluster groups in the cluster system.
The AT CLUSTER WIDE clause must be specified before the <range shard definition>.
When a cluster group or cluster member is added, shards can be automatically rebalanced using the ALTER TABLE name REBALANCE statement.
CREATE TABLE t1 
(
   id   INTEGER,
   name VARCHAR(32)
)
SHARDING BY RANGE (id)
    AT CLUSTER WIDE
    SHARD s1 VALUES LESS THAN ( 200000 ),
    SHARD s2 VALUES LESS THAN ( 400000 ),
    SHARD s3 VALUES LESS THAN ( 500000 ),
    SHARD s4 VALUES LESS THAN ( 600000 ),
    SHARD s5 VALUES LESS THAN ( 800000 ),
    SHARD s6 VALUES LESS THAN ( MAXVALUE )
;
CREATE CLUSTER GROUP g4 
       CLUSTER MEMBER g4n1 HOST '192.168.0.41' PORT 10401
;
ALTER TABLE t1 REBALANCE;

<group-specific range shard placement>

Range shards are placed in the specified cluster group.
The AT CLUSTER GROUP group_name clause is used along with the <range shard definition> to specify where the shard should be placed.
When a cluster member is added to the specified cluster group, shards can be automatically relocated using the ALTER TABLE name REBALANCE statement.
Adding a new cluster group does not affect the relocation of existing range shards.
CREATE TABLE t1 
(
   id   INTEGER,
   name VARCHAR(32)
)
SHARDING BY RANGE (id)
    SHARD s1 VALUES LESS THAN ( 200000 )   AT CLUSTER GROUP g1,
    SHARD s2 VALUES LESS THAN ( 400000 )   AT CLUSTER GROUP g2,
    SHARD s3 VALUES LESS THAN ( 500000 )   AT CLUSTER GROUP g3,
    SHARD s4 VALUES LESS THAN ( 600000 )   AT CLUSTER GROUP g2,
    SHARD s5 VALUES LESS THAN ( 800000 )   AT CLUSTER GROUP g3,
    SHARD s6 VALUES LESS THAN ( MAXVALUE ) AT CLUSTER GROUP g1
;
CREATE CLUSTER GROUP g4 
       CLUSTER MEMBER g4n1 HOST '192.168.0.41' PORT 10401
;
ALTER TABLE t1 REBALANCE;

<range shard definition>

The SHARD range_name must be unique within a table
Up to 512 <range shard definition> can be defined.
The listed <range shard definition> are sorted in the order of their <range value clause>, and each must use a different <range value clause>.
The <range shard definition> in which all values are defined as MAXVALUE is called the MAX shard.
A MAX shard must exist, and there must be only one.
gSQL>
CREATE TABLE t1 
(
   id INTEGER,
   name VARCHAR(32)
)
SHARDING BY RANGE (id)
   AT CLUSTER WIDE
   SHARD s1 VALUES LESS THAN ( 100000 ),
   SHARD s2 VALUES LESS THAN ( 200000 ),
   SHARD s3 VALUES LESS THAN ( MAXVALUE )
;

Table created.
gSQL>
CREATE TABLE t1 
(
   id INTEGER,
   name VARCHAR(32)
)
SHARDING BY RANGE (id)
   AT CLUSTER WIDE
   SHARD s1 VALUES LESS THAN ( 100000 ),
   SHARD s2 VALUES LESS THAN ( 200000 ),
   SHARD s3 VALUES LESS THAN ( 300000 )
;

ERR-42000(16377): MAX shard not defined : 
   SHARD s3 VALUES LESS THAN ( 300000 )
   *
ERROR at line 10:

<range value clause>

<range value> must be a constant or MAXVALUE (the maximum value).

NULL can not be used as a <range value>.

MAXVALUE is always greater than any other value, including NULL.

If there are multiple sharding keys, only MAXVALUE can be specified after a MAXVALUE.

If a sharding key is defined using multiple columns, there must be a single MAX shard that specifies MAXVALUE for all columns, as shown in SHARD s3 below.

CREATE TABLE t1 
(
   id INTEGER,
   name VARCHAR(32)
)
SHARDING BY RANGE (id, name)
   AT CLUSTER WIDE
   SHARD s1 VALUES LESS THAN ( 100000, MAXVALUE ),
   SHARD s2 VALUES LESS THAN ( 200000, 20000 ),
   SHARD s3 VALUES LESS THAN ( MAXVALUE, MAXVALUE )
;

<list sharding strategy>

It shards the table data based on the list of the sharding key values.

SHARDING BY LIST ( column_name )

It defines the sharding key for list sharding.

<cluster-wide list shard placement>

List shards are automatically distributed across all cluster groups in the cluster system.
The AT CLUSTER WIDE clause must be specified before the <range shard definition>.
When a cluster group or cluster member is added, shards can be automatically rebalanced using the ALTER TABLE name REBALANCE statement.
CREATE TABLE city 
(
   id   INTEGER,
   name VARCHAR(32)
)
SHARDING BY LIST (name)
    AT CLUSTER WIDE
    SHARD s1 VALUES IN ( 'SEOUL' ),
    SHARD s2 VALUES IN ( 'PUSAN', 'ULSAN', 'DAEGU' ),
    SHARD s3 VALUES IN ( 'DAEJEON', 'GWANGJU' ),
    SHARD s4 VALUES IN ( 'ANSAN', 'GOYANG' ),
    SHARD s5 VALUES IN ( DEFAULT )
;
CREATE CLUSTER GROUP g4 
       CLUSTER MEMBER g4n1 HOST '192.168.0.41' PORT 10401
;
ALTER TABLE t1 REBALANCE;

<group-specific list shard placement>

List shards are placed in the specified cluster group.
The AT CLUSTER GROUP group_name clause is used along with the <list shard definition> to specify where the shard should be placed.
When a cluster member is added to the specified cluster group, shards can be automatically relocated using the ALTER TABLE name REBALANCE statement.
Adding a new cluster group does not affect the relocation of existing list shards.
CREATE TABLE city 
(
   id   INTEGER,
   name VARCHAR(32)
)
SHARDING BY LIST (name)
    SHARD s1 VALUES IN ( 'SEOUL' )                   AT CLUSTER GROUP g1,
    SHARD s2 VALUES IN ( 'PUSAN', 'ULSAN', 'DAEGU' ) AT CLUSTER GROUP g2,
    SHARD s3 VALUES IN ( 'DAEJEON', 'GWANGJU' )      AT CLUSTER GROUP g3,
    SHARD s4 VALUES IN ( 'ANSAN', 'GOYANG' )         AT CLUSTER GROUP g2,
    SHARD s5 VALUES IN ( DEFAULT )                   AT CLUSTER GROUP g1
;
CREATE CLUSTER GROUP g4 
       CLUSTER MEMBER g4n1 HOST '192.168.0.41' PORT 10401
;
ALTER TABLE t1 REBALANCE;

<list shard definition>

The LIST list_name must be unique within a table.
Up to 512 <list shard definition> can be defined. 
All <list value> in the listed <list shard definition> must be distinct.
DFFAULT refers to all values not included in the listed <list value>.
DFFAULT can not be specified together with any other value.
A shard that includes DEFAULT is called the DEFAULT shard.
A MAX shard must exist, and there must be only one.
gSQL>
CREATE TABLE t1 
(
   category INTEGER,
   name     VARCHAR(32)
)
SHARDING BY LIST (category)
   AT CLUSTER WIDE
   SHARD s1 VALUES IN ( 1, 3, 5, 7 ),
   SHARD s2 VALUES IN ( 2, 4, 6, 8 ),
   SHARD s3 VALUES IN ( DEFAULT )
;

Table created.
gSQL>

CREATE TABLE t1 
(
   category INTEGER,
   name     VARCHAR(32)
)
SHARDING BY LIST (category)
   AT CLUSTER WIDE
   SHARD s1 VALUES IN ( 1, 3, 5, 7 ),
   SHARD s2 VALUES IN ( 2, 4, 6, 8 ),
   SHARD s3 VALUES IN ( 9, 10 )
;

ERR-42000(16385): DEFAULT shard not defined : 
   SHARD s3 VALUES IN ( 9, 10 )
   *
ERROR at line 10:

<list value clause>

<list value> must be a constant.

NULL or DEFAULT can be used as a <list value>.

<table physical attribute clause>

It defines the physical attributes of the table.

<index physical attribute clause>

It defines the physical attributes of the index.

<segment attr clause>

It describes the storage information for the table.

<size clause>

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

TABLESPACE tablespace_name

It specifies the name of the tablespace where the table will be stored.
If the TABLESPACE clause is omitted, the default tablespace_name of the user executing the statement is used.

TABLESPACE index_tablespace_name

It specifies the name of the tablespace where the index will be stored.
If the TABLESPACE clause is omitted, the user's index tablespace is used.
If the user's index tablespace is NULL, DISK tables use the user's default data tablespace, and MEMORY tables use the user's default temporary tablespace.

<constraint characteristics>

It defines the characteristics of a constraint.
The following characteristics can be specified when defining a constraint.

If <constraint characteristics> is omitted, it is set to NOT DEFERRABLE INITIALLY IMMEDIATE ENFORCED.

DEFERRABLE | NOT DEFERRABLE

It specifies whether constraint checking is deferrable, allowing constraints to be checked at COMMIT time instead of during DML statement execution.

The checking time of deferrable constraints is controlled by the SET CONSTRAINTS statement.

<constraint check time>

If the constraint is DEFERRABLE, it sets the initial timing for when the constraint is checked.

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

<constraint enforcement>

It specifies whether the constraint is enabled or disabled.

If not specified, the default is ENFORCED.

<table global secondary index clause>

It defines a global secondary index for the table.

Description

Constraint Characteristics

GOLDILOCKS automatically creates an index to enforce uniqueness when creating key constraints.

The following types of columns do not allow NULL values:

Cluster Table

In a cluster environment, a table manages data using one of the following sharding strategies:

The sharding strategy is determined based on the following factors when creating a table. In a cluster system, tables are classified as code tables or fact tables according to their characteristics.

The <cloned strategy> is suitable for code tables. For fact tables, an appropriate <table sharding strategy> should be selected based on the table's access pattern.

Examples

The following is an example of creating a regular table.

gSQL> CREATE TABLE region
(
    r_regionkey   INTEGER
  , r_name        CHAR(25)
  , r_comment     VARCHAR(152)
);

Table created.

The following is an example of defining constraints on columns when creating a table.

gSQL> CREATE TABLE supplier
(
    s_suppkey     INTEGER PRIMARY KEY
  , s_name        CHAR(25) NOT NULL
  , s_address     VARCHAR(40)
  , s_nationkey   INTEGER
  , s_phone       CHAR(15)
  , s_acctbal     NUMERIC(12,2)
  , s_comment     VARCHAR(101)
);

Table created.

The following is an example of specifying a constraint that includes multiple columns when creating a table.

gSQL> CREATE TABLE partsupp
(
    ps_partkey    INTEGER
  , ps_suppkey    INTEGER
  , ps_availqty   INTEGER
  , ps_supplycost NUMERIC(12,2)    
  , ps_comment    VARCHAR(199)
  , CONSTRAINT ps_unique_key UNIQUE(ps_partkey, ps_suppkey)
);

Table created.

The following is an example of specifying whether constraints are deferrable when creating a table.

gSQL> CREATE TABLE t1 
( 
    id     NUMBER        PRIMARY KEY 
                         NOT DEFERRABLE INITIALLY IMMEDIATE
  , name   VARCHAR(128)  CONSTRAINT t1_nn NOT NULL 
                         DEFERRABLE INITIALLY IMMEDIATE
  , addr   VARCHAR(1024) 
  , CONSTRAINT t1_uk UNIQUE ( id, name ) 
                     DEFERRABLE INITIALLY DEFERRED
);

Table created.

gSQL> COMMIT;

Commit complete.

The following is an example of specifying a column with an automatically generated values and a default value when creating a table.

CREATE TABLE customer
(
    c_custkey     INTEGER   GENERATED BY DEFAULT AS IDENTITY
  , c_name        VARCHAR(25)
  , c_address     VARCHAR(40) DEFAULT 'N/A'
  , c_nationkey   INTEGER
  , c_phone       CHAR(15)
  , c_acctbal     NUMERIC(12,2)
  , c_mktsegment  CHAR(10)
  , c_comment     VARCHAR(117)
);

Table created.

The following is an example of specifying the tablespace in which the table will be stored when creating it.

gSQL> CREATE TABLE lineitem
(
    l_orderkey      INTEGER
  , l_partkey       INTEGER
  , l_suppkey       INTEGER
  , l_linenumber    INTEGER
  , l_quantity      NUMERIC(12,2)
  , l_extendedprice NUMERIC(12,2)
  , l_discount      NUMERIC(12,2)
  , l_tax           NUMERIC(12,2)
  , l_returnflag    CHAR(1)
  , l_linestatus    CHAR(1)
  , l_shipdate      DATE
  , l_commitdate    DATE
  , l_receiptdate   DATE
  , l_shipinstruct  CHAR(25)
  , l_shipmode      CHAR(10)
  , l_comment       VARCHAR(44)
  , PRIMARY KEY (l_orderkey, l_linenumber) INDEX lineitem_pk_idx TABLESPACE mem_temp_tbs
) TABLESPACE mem_data_tbs;

Table created.

The following is an example of defining a cluster-wide cloned table. The table data is duplicated and distributed across the entire cluster system.

gSQL>
CREATE TABLE region
(
    r_regionkey   INTEGER
  , r_name        CHAR(25)
  , r_comment     VARCHAR(152)
)
CLONED
AT CLUSTER WIDE
;

Table created.

The following is an example of defining a group-specific cloned table. The table data is duplicated and placed in the cluster groups g1 and g2 as specified by the user.

gSQL> 
CREATE TABLE region
(
    r_regionkey   INTEGER
  , r_name        CHAR(25)
  , r_comment     VARCHAR(152)
)
CLONED
AT CLUSTER GROUP g1, g2
;

Table created.

The following is an example of defining a cluster-wide hash-sharded table. The table data is divided into 24 shards based on the hash value of the ps_partkey column, and each shard is automatically distributed across the entire the cluster system.

gSQL>
CREATE TABLE partsupp
(
    ps_partkey    INTEGER
  , ps_suppkey    INTEGER
  , ps_availqty   INTEGER
  , ps_supplycost NUMERIC(12,2)    
  , ps_comment    VARCHAR(199)
)
SHARDING BY HASH ( ps_partkey )
SHARD COUNT 24
AT CLUSTER WIDE
;

Table created.

The following is an example of defining a group-specific hash-sharded table. The table data is divided into 24 shards based on the hash value of the ps_partkey column, and each shard is automatically placed in the specified cluster groups g2 and g3.

gSQL>
CREATE TABLE partsupp
(
    ps_partkey    INTEGER
  , ps_suppkey    INTEGER
  , ps_availqty   INTEGER
  , ps_supplycost NUMERIC(12,2)    
  , ps_comment    VARCHAR(199)
)
SHARDING BY HASH ( ps_partkey )
SHARD COUNT 24
AT CLUSTER GROUP g2, g3
;

Table created.

The following is an example of defining a cluster-wide range-sharded table. The table data is divided into 24 shards based on the range values of the D_ID column, and each shard is automatically distributed across the entire cluster system.

gSQL>
CREATE TABLE DISTRICT (
    D_ID        INTEGER, 
    D_W_ID      INTEGER, 
    D_NAME      VARCHAR(10), 
    D_STREET_1  VARCHAR(20), 
    D_STREET_2  VARCHAR(20), 
    D_CITY      VARCHAR(20), 
    D_STATE     CHAR(2), 
    D_ZIP       CHAR(9), 
    D_TAX       NUMERIC(4,4), 
    D_YTD       NUMERIC(15,2), 
    D_NEXT_O_ID INTEGER,

    PRIMARY KEY (D_W_ID, D_ID) INDEX DISTRICT_PK_IDX
) 
    SHARDING BY RANGE (D_ID)
    AT CLUSTER WIDE
    SHARD s1 VALUES LESS THAN ( 100 ),
    SHARD s2 VALUES LESS THAN ( 200 ),
    SHARD s3 VALUES LESS THAN ( 300 ),
    SHARD s4 VALUES LESS THAN ( 400 ),
    SHARD s5 VALUES LESS THAN ( 500 ),
    SHARD s6 VALUES LESS THAN ( 600 ),
    SHARD s7 VALUES LESS THAN ( 700 ),
    SHARD s8 VALUES LESS THAN ( MAXVALUE );
;

Table created.
The following is an example of defining a group-specific range-sharded table. The table data is divided into three ranges based on the range values of the NO_D_ID column, and shard s1 is assigned to cluster group g1, shard s2 to cluster group g2, and shard s3 to cluster group g3, respectively
gSQL>
CREATE TABLE NEW_ORDER
(
    NO_O_ID INTEGER,
    NO_D_ID INTEGER,
    NO_W_ID INTEGER,

    PRIMARY KEY(NO_W_ID, NO_D_ID, NO_O_ID) INDEX NEW_ORDER_PK_IDX
) 
    SHARDING BY RANGE (NO_D_ID)
    SHARD s1 VALUES LESS THAN ( 5 )        AT CLUSTER GROUP g1,
    SHARD s2 VALUES LESS THAN ( 8 )        AT CLUSTER GROUP g2,
    SHARD s3 VALUES LESS THAN ( MAXVALUE ) AT CLUSTER GROUP g3
;

Table created.

The following is an example of defining a cluster-wide list-sharded table. The table data is divided into five list shards based on the city column, and each shard is automatically distributed across the entire cluster system.

gSQL>
CREATE TABLE t1 
(
    id   INTEGER
  , name VARCHAR(32)
  , city VARCHAR(128) 
) 
   SHARDING BY LIST (city)
      AT CLUSTER WIDE
      SHARD s1 VALUES IN ( 'seoul' ),
      SHARD s2 VALUES IN ( 'busan', 'ulsan' ),
      SHARD s3 VALUES IN ( 'suwon', 'ansan', 'osan' ),
      SHARD s4 VALUES IN ( 'goyang', 'paju', 'guri' ),
      SHARD s5 VALUES IN ( DEFAULT )            
;

Table created.

The following is an example of defining a group-specific list-sharded table. The table data is divided into five list shards based on the city column, and each shard is placed in a specified cluster group.

gSQL>
CREATE TABLE t1 
(
    id   INTEGER
  , name VARCHAR(32)
  , city VARCHAR(128) 
) 
   SHARDING BY LIST (city)
      SHARD s1 VALUES IN ( 'seoul' )                  AT CLUSTER GROUP g1,
      SHARD s2 VALUES IN ( 'busan', 'ulsan' )         AT CLUSTER GROUP g2,
      SHARD s3 VALUES IN ( 'suwon', 'ansan', 'osan' ) AT CLUSTER GROUP g1,
      SHARD s4 VALUES IN ( 'goyang', 'paju', 'guri' ) AT CLUSTER GROUP g2,
      SHARD s5 VALUES IN ( DEFAULT )                  AT CLUSTER GROUP g3
;

Table created.

The table T1 is created without defining a global secondary index.

gSQL> CREATE TABLE T1 ( I1 INTEGER, I1 CHAR(32) )  WITHOUT GLOBAL SECONDARY INDEX;

Table created.

After creating table T1, a global secondary index is created on it.

gSQL> CREATE TABLE T1 ( I1 INTEGER, I1 CHAR(32) )  WITH GLOBAL SECONDARY INDEX;

Table created.

After creating table T1, a global secondary index on T1 is created in the USER_DATA_TBS tablespace as a logging index.

gSQL> CREATE TABLE T1 ( I1 INTEGER, I1 CHAR(32) ) 
      WITH GLOBAL SECONDARY INDEX
      TABLESPACE USER_DATA_TBS;

Table created.
After creating table T1, a global secondary index on T1 is created in the USER_TEMP_TBS tablespace as a nologging index.
gSQL> CREATE TABLE T1 ( I1 INTEGER, I1 CHAR(32) ) 
      WITH GLOBAL SECONDARY INDEX
      TABLESPACE USER_TEMP_TBS;

Table created.

Compatibility

The SQL standard does not define the following clauses.

<table definition>

Feature ID

Description

Compatibility

T171

LIKE clause in table definition

X

F531

Temporary tables

X

S051

Create table of type

X

S043

Enhanced reference types

X

S081

Subtables

X

T172

AS subquery clause in table definition

O

T173

Extended LIKE clause in table definition

X

T180

System-versioned tables

X

T181

Application-time period tables

X

<column definition>

Feature ID

Description

Compatibility

F692

Extended collation support

X

T174

Identity columns

O

T175

Generated columns

X

T180

System-versioned tables

X

<default clause>

Feature ID

Description

Compatibility

S071

SQL paths in function and type name resolution

X

F321

User authorization

O

T322

Extended roles

X

F762

CURRENT_CATALOG

O

F763

CURRENT_SCHEMA

O

<unique constraint definition>

Feature ID

Description

Compatibility

S291

Unique constraint on entire row

X

T591

UNIQUE constraints of possibly null columns

O

T181

Application-time period tables

X

F292

UNIQUE null treatment

X

<referential constraint definition>

Feature ID

Description

Compatibility

T191

Referential action RESTRICT

O

F741

Referential MATCH types

X

F191

Referential delete actions

O

F701

Referential update actions

O

T201

Comparable data types for referential constraints

O

T181

Application-time period tables

X

<check constraint definition>

Feature ID

Description

Compatibility

F671

Subqueries in CHECK constraints

X

F672

Retrospective CHECK constraints

O

F673

Reads SQL-data routine invocations in CHECK constraints

X

For More Information

Refer to the following.

CREATE TABLE AS SELECT

Function

It creates a new table from the query result.

Syntax

<table definition: AS query expression> ::=
    CREATE TABLE table_name 
        [ ( column_name [, ...] ) ]
        [ <table sharding strategy> ]
        [ <table attribute clause> [, ...] ]
        [ TABLESPACE tablespace_name ]
        [ <table global secondary index clause> ]
        AS <query expression> [ WITH [ NO ] DATA ]
    ;

<table sharding strategy> ::=
      <cloned strategy>
    | <hash sharding strategy>
    | <range sharding strategy>
    | <list sharding strategy>

<cloned strategy> ::=
    CLONED [ <clone placement> ]

<clone placement> ::=
      AT CLUSTER WIDE
    | AT CLUSTER GROUP group_list

<hash sharding strategy> ::=
    SHARDING BY [HASH] ( column_list )
    [ <hash shard count> ]
    [ <hash shard placement> ]

<hash shard count> ::=
    SHARD COUNT integer

<hash shard placement> ::=
      AT CLUSTER WIDE
    | AT CLUSTER GROUP group_list

<range sharding strategy> ::=
    SHARDING BY RANGE ( column_list )
    { <cluster-wide range shard placement> | <group-specific range shard placement> }

<cluster-wide range shard placement> ::=
    AT CLUSTER WIDE
    <range shard definition> [, ...]

<group-specific range shard placement> ::=
    <group-specific range shard definition> [, ...]

<group-specific range shard definition> ::=
    <range shard definition> AT CLUSTER GROUP group_name

<range shard definition> ::=
    SHARD range_name VALUES LESS THAN ( <range value clause> )

<range value clause> ::=
    <range value> [, ...]

<range value> ::=
      constant
    | MAXVALUE

<list sharding strategy> ::=
    SHARDING BY LIST ( column_name )
    { <cluster-wide list shard placement> | <group-specific list shard placement> }

<cluster-wide list shard placement> ::=
    AT CLUSTER WIDE
    <list shard definition> [, ...]

<group-specific list shard placement> ::=
    <group-specific list shard definition> [, ...]

<group-specific list shard definition> ::=
    <list shard definition> AT CLUSTER GROUP group_name

<list shard definition> ::=
      SHARD shard_name VALUES IN ( <list value clause> )

<list value clause> ::=
    <list value> [, ...]

<list value> ::=
      constant
    | NULL
    | DEFAULT


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

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

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

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

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

<table global secondary index clause> ::=
      WITH GLOBAL SECONDARY INDEX [ <index attributes> [...] ] [ TABLESPACE tablespace_name ]
    |  WITHOUT GLOBAL SECONDARY INDEX

Invocation and Access Rules

A user must meet the following conditions to execute a <table definition:AS query expression> statement.

Syntax Rules and Parameters

table_name

It is the name of the table to be created.
For more information, refer to the table_name clause.

column_name_list

These are the names of the columns that make up the table. Each name must be unique within the table, and the number of columns must match the number of result columns in the SELECT clause.
If not specified, the column names from the SELECT clause in the <query expression> are used.
However, if an expression (such as a function, operation, or subquery) is used instead of a column in the SELECT clause, an alias or column name must be explicitly specified.
The column name must be less than 128 bytes in length.

WITH [NO] DATA

If WITH DATA is specified, the result of the SELECT clause is inserted into the table being created.
If WITH NO DATA is specified, the result of the SELECT clause is not inserted into the table.
If omitted, it behaves as if WITH DATA were specified.

Other Syntax

For more information about other syntaxes, refer to the syntax of the CREATE TABLE statement.

Description

When executing the CREATE TABLE AS SELECT statement, if a column with a NOT NULL constraint is specified in the SELECT list, the NOT NULL constraint is also created in the new table. However, if the NOT NULL constraint is deferrable, it is not created in the new table.
However, if the NOT NULL constraint was not explicitly created, but the column has a NOT NULL property such as being a primary key or an identity column, the NOT NULL constraint is not created in the new table.

Examples

The following is an example of executing the CREATE TABLE AS SELECT statement.

gSQL> CREATE TABLE recent_orders 
                AS SELECT order_id, order_item, order_date
                   FROM orders 
                   WHERE order_date >= '2015-03-03'
Table created.

The following is an example of specifying column names.

gSQL> CREATE TABLE recent_orders ( order_id, order_item, order_date )
                AS SELECT order_id, order_item, order_date
                   FROM orders 
                   WHERE order_date >= '2015-03-03'
Table created.

The following is an example of using a function in the SELECT list.

gSQL> CREATE TABLE recent_orders ( order_date, order_count )
                AS SELECT order_date, COUNT(*) 
                   FROM orders 
                   WHERE order_date >= '2015-03-03'
                   GROUP BY order_date;
Table created.

The following is an example of a statement that includes WITH DATA.

gSQL>CREATE TABLE orders 
( 
    order_id   NUMBER        
  , order_item VARCHAR(128)  
  , order_date DATE
);
gSQL> COMMIT;
gSQL> INSERT INTO orders VALUES ( 1, 'Pen', '2010-01-01' );
gSQL> INSERT INTO orders VALUES ( 2, 'Book', '2015-03-03' );
gSQL> COMMIT;
gSQL> CREATE TABLE recent_orders
                AS SELECT order_id, order_item, order_date 
                   FROM orders 
                   WHERE order_date >= '2015-03-03'
      WITH DATA;
Table created.
gSQL> SELECT COUNT(*) FROM recent_orders;

COUNT(*)
--------
       1

1 row selected.

The following is an example of a statement that includes WITH NO DATA.

gSQL>CREATE TABLE orders 
( 
    order_id   NUMBER        
  , order_item VARCHAR(128)  
  , order_date DATE
);
gSQL> COMMIT;
gSQL> INSERT INTO orders VALUES ( 1, 'Pen', '2010-01-01' );
gSQL> INSERT INTO orders VALUES ( 2, 'Book', '2015-03-03' );
gSQL> COMMIT;
gSQL> CREATE TABLE recent_orders
                AS SELECT order_id, order_item, order_date 
                   FROM orders 
                   WHERE order_date >= '2015-03-03'
      WITH NO DATA;
Table created.
gSQL> SELECT COUNT(*) FROM recent_orders;

COUNT(*)
--------
       0

1 row selected.

Compatibility

The CREATE TABLE AS SELECT statement follows the SQL standard. However, the following is an extension beyond the standard.

SQL standard compatibility

Feature ID

Description

Compatibility

T172

AS subquery clause in table definition

O

For More Information

Refer to the following.

CREATE TABLESPACE

Function

It creates a tablespace.

Syntax

<create tablespace statement> ::=
      <memory data tablespace statement>
    | <memory temporary tablespace statement>
    ;

Invocation and Access Rules

The CREATE TABLESPACE ON DATABASE privilege is required to execute the <create tablespace statement>.

The user who executes the statement is granted the CREATE OBJECT ON TABLESPACE privilege on the created tablespace.

One of the following privileges is required to create objects on the created tablespace.

Syntax Rules and Parameters

<memory data tablespace statement>

This is a memory temporary tablespace used to store no-logging indexes or temporary objects, such as intermediate results generated during query processing.
The reserved word MEMORY can be omitted.

<memory data tablespace clause>

It defines a memory data tablespace.
For more information, refer to the CREATE MEMORY DATA TABLESPACE statement.

<memory temporary tablespace definition>

It defines a memory temporary tablespace.
For more information, refer to the CREATE MEMORY TEMPORARY TABLESPACE statement.

Description

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

Example

For more information, refer to the usage examples for each detailed clause.

Compatibility

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

For More Information

Refer to the following.

CREATE USER

Function

It defines a database user.

Syntax

<user definition> ::=
    CREATE USER user_identifier IDENTIFIED BY password
    [ PROFILE { profile_name | DEFAULT | NULL } ]
    [ PASSWORD EXPIRE ]
    [ ACCOUNT { LOCK | UNLOCK } ]
    [ DEFAULT TABLESPACE tablespace_name ]
    [ TEMPORARY TABLESPACE tablespace_name ]
    [ INDEX TABLESPACE { tablespace_name | NULL } ]
    [ <schema clause> ]
    ;

<schema clause> ::=
      WITH SCHEMA [schema_name]
    | WITHOUT SCHEMA

Invocation and Access Rules

The CREATE USER ON DATABASE privilege is required to execute the <user definition>.

The created user, user_identifier, has ownership privileges for the schema created with the <schema> clause.

No separate privileges are granted to the created user_identifier.

Appropriate privileges must be granted to user_identifier to allow access and execution of SQL statements.

Syntax Rules and Parameters

user_identifier

It is the username to be created.
An identical username (user identifier) or role (role name) must not already exist.
The length of the user_identifier must be less than 128 bytes.

password

It is the user's password to be created. It is encrypted and stored.
The password length must be less than 128 bytes.
The password is case-sensitive.
It must start with an alphabetic character and can include alphabetic characters, numbers, underscores (_), and dollar signs ($).
Other special characters must be enclosed in double quotes (").

PROFILE { profile_name | DEFAULT | NULL }

The profile for the password management policy is assigned as follows:

If the PROFILE clause is omitted, it is treated as PROFILE NULL, and no profile is applied.
For more information about password management policies, refer to the CREATE PROFILE statement.

PASSWORD EXPIRE

It expires the user's password.
It is used to force the user to change their password before logging in.

ACCOUNT { LOCK | UNLOCK }

DEFAULT TABLESPACE tablespace_name

It specifies the default TABLESPACE where objects created by the user, such as tables and indexes (LOGGING), are stored.
If the DEFAULT TABLESPACE clause is omitted, the default data tablespace (MEM_DATA_TBS) defined at DATABASE creation is assigned.

TEMPORARY TABLESPACE tablespace_name

It specifies the TABLESPACE to store temporary tables created by the user, indexes (NOLOGGING), and intermediate results generated during query processing.
If the TEMPORARY TABLESPACE clause is omitted, the default temporary tablespace (MEM_TEMP_TBS) defined at database creation is assigned.

INDEX TABLESPACE { tablespace_name | NULL }

It specifies the default TABLESPACE to store index objects created by the user.

If the INDEX TABLESPACE clause is omitted, it defaults to INDEX TABLESPACE NULL.

<schema clause>

It creates the default schema for the user.
The schema name must be unique within the database
If the <schema clause> is not specified, the default is WITH SCHEMA, and a schema with the same name as user_identifier is created.
A schema to be owned by the user can also be created separately using the CREATE SCHEMA statement.

Description

A user is an authorization object that consists of a set of privileges.
When the <user definition> statement is executed for the first time, a user is created without any privileges. Appropriate privileges must be granted afterward, as needed.
In GOLDILOCKS, the relationship between a user and schemas is 1 : N.
In other words, a user may own multiple schemas or none at all.
The SQL standard does not explicitly define the relationship between non-schema objects such as users, schemas, and databases.
On the other hand, each DBMS defines the relationships between non-schema objects differently, as shown below.

Relationship Between User and Schema in Other DBMSs





Examples

To allow creating a user and enabling that user to create objects and manipulate data, the following privileges must be granted.

The following is an example of creating a user and granting privileges.

• Create a user.

gSQL> CREATE USER u1 IDENTIFIED BY u1_password
             DEFAULT   TABLESPACE mem_data_tbs
             TEMPORARY TABLESPACE mem_temp_tbs
             INDEX TABLESPACE NULL;

User created.

gSQL> COMMIT;

Commit complete.

• Grant database privileges.

gSQL> GRANT CREATE SESSION ON DATABASE TO u1;

Grant succeeded.

COMMIT;

Commit complete.

• Grant schema privileges.

GRANT CREATE TABLE, CREATE VIEW, CREATE INDEX, CREATE SEQUENCE 
      ON SCHEMA u1 TO u1;

Grant succeeded.

COMMIT;

Commit complete.

• Grant tablespace privileges.

GRANT CREATE OBJECT ON TABLESPACE mem_data_tbs TO u1;

Grant succeeded.

GRANT CREATE OBJECT ON TABLESPACE mem_temp_tbs TO u1;

Grant succeeded.

COMMIT;

Commit complete.

The following is an example of object creation by a user.

• It requires the CREATE SESSION ON DATABASE privilege.
gSQL> \connect u1 u1_password
• It requires the CREATE TABLE ON SCHEMA u1 privilege. 
• It requires the CREATE OBJECT ON TABLESPACE mem_data_tbs privilege.
gSQL> CREATE TABLE u1.t1 ( c1 INTEGER, c2 INTEGER ) TABLESPACE mem_data_tbs;

Table created.

gSQL> COMMIT;
• It requires the CREATE INDEX ON SCHEMA u1 privilege. 
• It requires the CREATE OBJECT ON TABLESPACE mem_temp_tbs privilege.
gSQL> CREATE INDEX u1.idx ON t1 (c2) TABLESPACE mem_temp_tbs;

Index created.

gSQL> COMMIT;
• It requires the CREATE SEQUENCE ON SCHEMA u1 privilege.
gSQL> CREATE SEQUENCE u1.seq;

Sequence created.

gSQL> COMMIT;

gSQL> INSERT INTO u1.t1 VALUES ( u1.seq.NEXTVAL, u1.seq.NEXTVAL );

1 row created

gSQL> COMMIT;

Compatibility

While the SQL standard covers the concept of a user, it does not specify the SQL statements for user creation and deletion.

For More Information

Refer to the following.

CREATE VIEW

Function

It defines a view.

Syntax

<view definition> ::=
    CREATE [ OR REPLACE ] [ FORCE | NO FORCE ] 
        VIEW view_name [ ( column_name [, ...] ) ]
        AS <query expression>
    ;

Invocation and Access Rules

The user must meet the following conditions to execute a <view definition> statement.

Syntax Rules and Parameters

[ OR REPLACE ]

It replaces the existing view if the view already exists.

[ FORCE | NO FORCE ]

view_name

It is the name of the view to be created, and it must be unique within the schema.
The schema to which the view belongs can be defined using the format schema_name.view_name. If schema_name is omitted, the default schema name of the user executing the statement is used.
The length of the view name must be less than 128 bytes.

[ ( column_name [, ...] ) ]

It defines the names of the columns that make up the view.
Each column name must be unique within the view.
The number of columns must match the number of columns returned by the SELECT clause.
If the list of column names is omitted, the column names from the SELECT clause in the query expression are used.

AS <query expression>

It is the SELECT query used to create the view.

The <query expression> must not include the following variables.

Description

A view is an object that assigns a name to a query and is used in a similar way to a table.

When a query that includes a view is executed, the view is interpreted as the query defined in the view definition.
As in the following example, if a table referenced by the view is modified, elements such as an asterisk (*) in the view definition are automatically reinterpreted according to the updated structure of the table.
gSQL> CREATE VIEW v1 AS SELECT * FROM t1;
gSQL> COMMIT;
gSQL> SELECT * FROM v1;

ID NAME     
-- ---------
 1 leekmo   
 2 mkkim    
 3 egonspace

3 rows selected.

gSQL> ALTER TABLE t1 ADD COLUMN ( dept_id  INTEGER,  addr  VARCHAR(1024) );
gSQL> COMMIT;

gSQL> select * from v1;

ID NAME      DEPT_ID ADDR
-- --------- ------- ----
 1 leekmo       null null
 2 mkkim        null null
 3 egonspace    null null

3 rows selected.

A view may be affected if it is created using the FORCE option while the query contains errors, or if the tables or views it references are modified or deleted.

This information can be retrieved from the INFORMATION_SCHEMA.VIEWS.

There is no limit to the number of views that can be created or the number of columns within a view. Therefore, as long as there is sufficient storage space, views can continue to be created without restriction.

Examples

The following is an example of creating a view.

gSQL> CREATE VIEW v1 AS SELECT * FROM t1 WHERE dept_id = 101;

View created.

The following is an example of defining column names while creating a view.

gSQL> CREATE VIEW v1 ( v_id, v_name )
          AS SELECT id, name FROM t1 WHERE dept_id = 101;

View created.

The following is an example of dropping an existing view and creating a new one using the REPLACE option.

gSQL> CREATE OR REPLACE VIEW v1(id, name) 
             AS SELECT id, name FROM t1;

View created.

The following is an example of forcing the creation of a view using the FORCE option, even when the objects referenced by the view do not exist.

gSQL> CREATE FORCE VIEW v1 
          AS SELECT * FROM t1 WHERE dept_id = 101;

ERR-01000(16243): Warning: View created with compilation errors
ERR-42000(16040): table or view does not exist : 
    AS SELECT * FROM t1 WHERE dept_id = 101
                     *
ERROR at line 2:

View created.

Compatibility

The SQL standard does not define the following clauses.

SQL standard compatibility

Feature ID

Description

Compatibility

T131

Recursive query

O

F751

View CHECK enhancements

X

S043

Enhanced reference types

X

T111

Updatable joins, unions, and columns

X

F852

Top-level <order by clause> in views

O

F864

Top-level <result offset clause> in views

O

F859

Top-level <fetch first clause> in views

O

S081

Subtables

X

For More Information

Refer to the following.

DECLARE cursor_name

Function

It declares a cursor.

Syntax

<declare cursor> ::=
    DECLARE cursor_name <cursor properties> { FOR | IS } <cursor specification>
    ;

<cursor properties> ::=
      [ <cursor sensitivity> ] [ <cursor scrollability>] ] CURSOR [ <cursor holdability> ] 
    | [ <odbc cursor type] CURSOR [ <cursor holdability> ] 

<cursor sensitivity> ::=
      INSENSITIVE
    | SENSITIVE
    | ASENSITIVE

<cursor scrollability> ::=
      NO SCROLL
    | SCROLL

<cursor holdability> ::=
      WITH HOLD
    | WITHOUT HOLD

<odbc cursor type> ::=
      STATIC
    | KEYSET

<cursor specification> ::=
      statement_name
    | <cursor query>  [ <updatability clause> ]

<cursor query> ::=
      <select statement>
    | <insert returning query statement>
    | <update returning query statement>
    | <delete returning query statement>

<updatability clause> ::=
      FOR READ ONLY 
    | FOR UPDATE [ OF <column name list> ] [ <lock wait mode> ]

<lock wait mode> ::=
    | WAIT
    | WAIT second
    | NOWAIT

Invocation and Access Rules

A dynamic cursor that uses a statement_name can be used in embedded SQL.

Appropriate access privileges are required depending on the type of the <cursor query>.
For more information about access privileges, refer to the following:

Syntax Rules and Parameters

cursor_name

It specifies the name of the cursor to be declared.
The name must be unique within the session.
The length of the cursor name must be less than 128 bytes.

{ FOR | IS }

According to the SQL standard, either FOR or IS is used as a syntax keyword.

<cursor properties>

It defines the properties of the cursor.

updatable query

To use a cursor property such as SENSITIVE or FOR UPDATE, a query of the cursor should identify changes in rows of the base table, or it should be the updatable query which can acquire a lock on the row.

An updatable query must satisfy all of the following conditions:

<cursor sensitivity>

It determines whether data changes that affect the query results can be detected while operating the cursor.

<cursor scrollability>

It specifies whether the result set of the cursor can be fetched sequentially or non-sequentially.

<cursor holdability>

It specifies whether the cursor remains open after a transaction is committed.

<odbc cursor type>

This cursor type is defined in the ODBC standard and supports the SCROLL property.

Determining sensitivity based on FOR [UPDATE / READ ONLY] clause and query type

Updatability

Query type

Sensitivity

FOR UPDATE

Updatable query

SENSITIVE

FOR UPDATE

Non-updatable query

Query error

FOR READ ONLY

Any query

INSENSITIVE

N/A

Updatable query

SENSITIVE

N/A

Non-updatable query

INSENSITIVE

<cursor specification>

It defines the query to be targeted by the cursor.
When statement_name is used, a dynamic cursor is declared, meaning the query is not yet defined.
When <cursor query> is used, a standing cursor is declared, meaning the query is already defined.

statement_name

It is a statement_name referenced by the cursor and can be used in embedded SQL.
The statement_name must exist before executing the <declare cursor> statement, and the SQL statement referenced by statement_name must be a query prepared using the PREPARE statement_name statement.
If it is not a query, an error will occur when the OPEN cursor_name statement is executed.

<cursor query>

For more information about the types of queries that can be used with a cursor, refer to the following.

<updatability clause>

It specifies whether to modify rows using the cursor.

FOR UPDATE OF …

It lists the columns related to lock acquisition when opening the cursor.

<lock wait mode>

It is used with the FOR UPDATE clause to specify the lock acquisition behavior.

Description

When controlling query properties, using the DECLARE CURSOR, OPEN, FETCH, and CLOSE statements may impose a greater performance overhead compared to using cursors through ODBC or JDBC.
This is because these SQL statements manage server-side cursors directly.
Before executing a query, cursor properties can be controlled using ODBC and JDBC statements.
The method of controlling SQL cursor properties using the DECLARE CURSOR statement, along with the corresponding cursor property control methods in the ODBC and JDBC standards, is as follows.
Cursor property control in ODBC/ JDBC

Property

GOLDILOCKS

cursor property

ODBC standard cursor property

JDBC standard cursor property

Sensitivity

INSENSITIVE

SQLSetStmtAttr(stmt, SQL_ATTR_CURSOR_SENSITIVITY, SQL_INSENSITIVE, len)

Not configurable

SENSITIVE

SQLSetStmtAttr(stmt, SQL_ATTR_CURSOR_SENSITIVITY, SQL_SENSITIVE, len)

Not configurable

ASENSITIVE

SQLSetStmtAttr(stmt, SQL_ATTR_CURSOR_SENSITIVITY, SQL_UNSPECIFIED, len)

Not configurable

Scrollability

NO SCROLL

SQLSetStmtAttr(stmt, SQL_ATTR_CURSOR_SCROLLABLE, SQL_NONSCROLLABLE, len)

Not configurable

SCROLL

SQLSetStmtAttr(stmt, SQL_ATTR_CURSOR_SCROLLABLE, SQL_SCROLLABLE, len)

Not configurable

Holdability

WITHOUT HOLD

Not configurable

java.sql.Connection::prepareStatement( query, type, conc, ResultSet.CLOSE_CURSORS_AT_COMMIT )

WITH HOLD

Not configurable

java.sql.Connection::prepareStatement( query, type, conc, ResultSet.HOLD_CURSORS_OVER_COMMIT )

The SQL cursor declarations corresponding to ODBC cursor types are as follows.

SQL cursor declaration corresponding to ODBC cursor types

ODBC cursor type

SQL cursor declaration

SQLSetStmtAttr(stmt, SQL_ATTR_CURSOR_TYPE, SQL_CURSOR_FORWARD_ONLY, len)

NO SCROLL CURSOR

SQLSetStmtAttr(stmt, SQL_ATTR_CURSOR_TYPE, SQL_CURSOR_STATIC, len)

STATIC CURSOR

SQLSetStmtAttr(stmt, SQL_ATTR_CURSOR_TYPE, SQL_CURSOR_KEYSET_DRIVEN, len)

KEYSET CURSOR

The SQL cursor declarations corresponding to JDBC cursor types are as follows.

SQL cursor declaration corresponding to JDBC cursor types

JDBC cursor type

SQL cursor declaration

java.sql.Connection::prepareStatement( query, ResultSet.TYPE_FORWARD_ONLY, conc, hold )

INSENSITIVE NO SCROLL CURSOR

java.sql.Connection::prepareStatement( query, ResultSet.TYPE_SCROLL_INSENSITIVE, conc, hold )

INSENSITIVE SCROLL CURSOR

java.sql.Connection::prepareStatement( query, ResultSet.TYPE_SCROLL_SENSITIVE, conc, hold )

KEYSET CURSOR

Examples

The following is an example of declaring and using a cursor with interactive SQL (gsql).

gSQL> DECLARE cur1 CURSOR FOR SELECT id, data FROM t1;

Cursor declared.

gSQL> OPEN cur1;

Cursor is open.

gSQL> \var v_id   INTEGER
gSQL> \var v_data VARCHAR(128)

gSQL> FETCH cur1 INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   1 data_1

1 row fetched.

gSQL> FETCH cur1 INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   2 data_2

1 row fetched.

gSQL> FETCH cur1 INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   3 data_3

1 row fetched.

gSQL> FETCH cur1 INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   4 data_4

1 row fetched.

gSQL> FETCH cur1 INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   5 data_5

1 row fetched.

gSQL> FETCH cur1 INTO :v_id, :v_data;

no rows fetched.

gSQL> CLOSE cur1;

Cursor closed.

The following is an example of declaring a KEYSET cursor, performing sequential fetches, completing the transaction for UPDATE and DELETE statements, and then fetching in the reverse direction.

gSQL> DECLARE cur_keyset KEYSET CURSOR FOR SELECT id, data FROM t1;

Cursor declared.

gSQL> OPEN cur_keyset;

Cursor is open.

gSQL> \var v_id   INTEGER
gSQL> \var v_data VARCHAR(128)

gSQL> FETCH NEXT cur_keyset INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   1 data_1

1 row fetched.


gSQL> FETCH NEXT cur_keyset INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   2 data_2

1 row fetched.

gSQL> FETCH NEXT cur_keyset INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   3 data_3

1 row fetched.

gSQL> FETCH NEXT cur_keyset INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   4 data_4

1 row fetched.

gSQL> FETCH NEXT cur_keyset INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   5 data_5

1 row fetched.

gSQL> FETCH NEXT cur_keyset INTO :v_id, :v_data;

no rows fetched.

gSQL> UPDATE t1 SET data = 'new data_2' WHERE id = 2;

1 row updated.

gSQL> COMMIT;

Commit complete.

gSQL> DELETE FROM t1 WHERE id = 4;

1 row deleted.

gSQL> COMMIT;

Commit complete.

gSQL> FETCH PRIOR cur_keyset INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   5 data_5

1 row fetched.


gSQL> FETCH PRIOR cur_keyset INTO :v_id, :v_data;

no rows fetched.


gSQL> FETCH PRIOR cur_keyset INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   3 data_3

1 row fetched.

gSQL> FETCH PRIOR cur_keyset INTO :v_id, :v_data;

V_ID V_DATA    
---- ----------
   2 new data_2

1 row fetched.

gSQL> FETCH PRIOR cur_keyset INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   1 data_1

1 row fetched.

gSQL> CLOSE cur_keyset;

Cursor closed.

The following is an example of declaring a SCROLL cursor and using it with fetch orientations.

gSQL> DECLARE cur_scroll SCROLL CURSOR FOR SELECT id, data FROM t1;

Cursor declared.

gSQL> OPEN cur_scroll;

Cursor is open.

gSQL> \var v_id   INTEGER
gSQL> \var v_data VARCHAR(128)

gSQL> FETCH LAST cur_scroll INTO :v_id, :v_data;
V_ID V_DATA
---- ------
   5 data_5

1 row fetched.


gSQL> FETCH PRIOR cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   4 data_4

1 row fetched.


gSQL> FETCH FIRST cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   1 data_1

1 row fetched.


gSQL> FETCH ABSOLUTE 3 cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   3 data_3

1 row fetched.


gSQL> FETCH RELATIVE -1 cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   2 data_2

1 row fetched.


gSQL> FETCH ABSOLUTE 3 cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   3 data_3

1 row fetched.

gSQL> CLOSE cur_scroll;

Cursor closed.

Compatibility

The <declare cursor> statement differs from the SQL standard in the following ways:

SQL standard compatibility

Feature ID

Description

Compatibility

F831

Full cursor update

O

T231

Sensitive cursors

O

F791

Insensitive cursors

O

F431

Read-only scrollable cursors

O

T471

Result sets return value

X

T551

Optional key words for default syntax

O

T111

Updatable joins, unions, and columns

X

B031

Basic dynamic SQL

O

For More Information

Refer to the following.

DELETE FROM

Function

It deletes rows from a table.

Syntax

<delete statement: searched> ::=
    DELETE [ FROM ] table_name [ [ AS ] alias_name ]
        [ WHERE <search condition> ]
        { [ <result offset clause> ] [ <fetch limit clause> ] | [ <local shard limit clause> ] }
    ;

<result offset clause> ::=
    OFFSET skip_count [ ROW | ROWS ]

<fetch limit clause> ::=
      <fetch first clause>
    | <limit clause>

<fetch first clause> ::=
    FETCH [ FIRST | NEXT ] [ row_count ] [ ROW ONLY | ROWS ONLY ]

<limit clause>
    LIMIT { fetch_row_count | offset_row_count, fetch_row_count | ALL }

<local shard limit clause>
    LOCAL SHARD LIMIT limit_row_count [ ROWS ]

Invocation and Access Rules

One of the following privileges is required to execute the <delete statement: searched>.

Syntax Rules and Parameters

table_name

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

[ AS alias_name ]

It is an alias for the table_name.

WHERE <search condition>

It deletes the rows that satisfy the WHERE condition.
If the WHERE condition is omitted, all rows in the table are deleted.
For more information about the WHERE condition, refer to the where clause of the SELECT statement.

<result offset clause>

It specifies the number of rows to skip in the query result.
For more information, refer to the offset limit clause in the SELECT statement.

<fetch limit clause>

There are two ways to specify the number of rows to be fetched:

<local shard limit clause>

It provides data switchover for sharded tables in a cluster environment.

It deletes up to limit_row_count records from the local group of a sharded table.

Description

Differences Between DELETE Statements

Examples

The following is an example of a DELETE statement.

gSQL> DELETE FROM t1 WHERE id > 3;

2 rows deleted.

The following example demonstrates how to skip a certain number of rows (two rows) and delete a specific number of rows (two rows) from the result set that meets the condition, using the <result offset clause> and <fetch first clause>.

gSQL> DELETE FROM t1 OFFSET 2 FETCH 2;

2 rows deleted.


gSQL> SELECT * FROM t1 ORDER BY 1;

ID DATA  
-- ------
 1 data_1
 2 data_2
 5 data_5

3 rows selected.

The following is an example of using <local group limit> to delete only the records in the local group of the current target sharded table.

gSQL> SELECT cluster_group_name, id, data FROM t_shard ORDER BY 1, 2;

CLUSTER_GROUP_NAME ID DATA  
------------------ -- ------
G1                  1 data_1
G2                  2 data_2

2 rows selected.

--# The local group is G1.
gSQL> SELECT local_group_name() FROM dual;

LOCAL_GROUP_NAME()
------------------
G1                

1 row selected.

--# Although the DELETE statement specifies a limit of two rows, only one row in the local group (G1) is deleted because it is the only row in the local group.
gSQL> DELETE FROM t_shard LOCAL SHARD LIMIT 2;

1 row deleted.

--# The row in the other local group is not deleted and remains unchanged.
gSQL> SELECT cluster_group_name, id, data FROM t_shard ORDER BY 1, 2;

CLUSTER_GROUP_NAME ID DATA  
------------------ -- ------
G2                  2 data_2

1 row selected.

The following is an example of performing data switchover for a sharded table by configuring an anonymous PL block that uses a DELETE statement with the <local group limit> clause.

--# Deletes all records in the lineitem table where l_commitdate is earlier than '1997-08-28'.
--# Deletes target records in batches of 50,000 and repeatedly performs COMMIT.

BEGIN
    LOOP
        DELETE FROM lineitem WHERE l_commitdate < date '1997-08-28' LOCAL SHARD LIMIT 50000;
        
        IF SQL%ROWCOUNT = 0 THEN
            EXIT;
        ELSE
            COMMIT;
        END IF;
    END LOOP;
END;
/
[shell]> gsqlnet test test --dsn=G1N1 --import bulk_delete.sql

It is recommended to execute the data switchover query in parallel on the master server of each group where sharded table records are distributed.

[shell]> gsqlnet test test --dsn=G1N1 --import bulk_delete.sql &
[shell]> gsqlnet test test --dsn=G2N1 --import bulk_delete.sql &
[shell]> gsqlnet test test --dsn=G3N1 --import bulk_delete.sql &

Compatibility

The SQL standard does not define the following clauses in the DELETE statement.

SQL standard compatibility

Feature ID

Description

Compatibility

F781

Self-referencing operations

X

T111

Updatable joins, unions, and columns

X

For More Information

Refer to the following.

DELETE FROM name RETURNING

Function

It deletes rows from the table and retrieves the deleted rows.

Syntax

<delete returning query statement> ::=
    DELETE [ FROM ] table_name [ [ AS ] alias_name ]
        [ WHERE <search condition> ]
        [ <result offset clause> ]
        [ <fetch limit clause> ]
        <returning clause>
    ;

<result offset clause> ::=
    OFFSET skip_count [ ROW | ROWS ]

<fetch limit clause> ::=
      <fetch first clause>
    | <limit clause>

<fetch first clause> ::=
    FETCH [ FIRST | NEXT ] [ row_count ] [ ROW ONLY | ROWS ONLY ]

<limit clause>
    LIMIT { fetch_row_count | offset_row_count, fetch_row_count | ALL }

<returning clause> ::=
    { RETURN | RETURNING } { * | { <value expression> [ [AS] alias_name] } [, ...] }

Invocation and Access Rules

The user must meet the following conditions to execute a <delete returning query statement>.

Syntax Rules and Parameters

table_name

It is the name of the target table from which rows will be deleted.

[ AS alias_name ]

It is an alias for the table_name.

WHERE <search condition>

It deletes the rows that satisfy the WHERE condition.
For more information, refer to the DELETE FROM statement.

<result offset clause>

It specifies the number of rows to skip in the query result.
For more information, refer to the DELETE FROM statement.

<fetch first clause>

It specifies the number of rows to fetch.
For more information, refer to the DELETE FROM statement.

<limit clause>

It specifies the number of rows to fetch, or both the number of rows to skip and the number of rows to fetch.
For more information, refer to the DELETE FROM statement.

<returning clause>

It specifies the columns to retrieve from the result set of deleted rows.

RETURN and RETURNING are keywords with the same meaning and can be used interchangeably.

Description

For more information, refer to Differences Between DELETE Statements.

Examples

The following is an example of deleting rows that satisfy a condition and retrieving the deleted rows.

gSQL> DELETE FROM t1 WHERE id > 3 RETURNING *;

ID DATA  
-- ------
 4 data_4
 5 data_5

2 rows deleted.

The following is an example of using expressions in the RETURNING clause to retrieve information about the deleted rows.

gSQL> DELETE FROM t1 
             WHERE id > 3 
             RETURNING 'ID: ' || id || ', DATA: ' || data AS id_data;

ID_DATA            
-------------------
ID: 4, DATA: data_4
ID: 5, DATA: data_5

2 rows deleted.

Compatibility

The <delete returning query statement> is not defined in the SQL standard.

For More Information

Refer to the following.

DELETE FROM name RETURNING .. INTO

Function

It deletes a single row from the table and retrieves the value of the deleted row into a host variable.

Syntax

<delete returning query statement> ::=
    DELETE [ FROM ] table_name [ [ AS ] alias_name ]
        [ WHERE <search condition> ]
        [ <result offset clause> ]
        [ <fetch limit clause> ]
        <returning into clause>
    ;

<result offset clause> ::=
    OFFSET skip_count [ ROW | ROWS ]


<fetch limit clause> ::=
      <fetch first clause>
    | <limit clause>


<fetch first clause> ::=
    FETCH [ FIRST | NEXT ] [ row_count ] [ ROW ONLY | ROWS ONLY ]


<limit clause>
    LIMIT { fetch_row_count | offset_row_count, fetch_row_count | ALL }


<returning into clause> ::=
    { RETURN | RETURNING } { * | { <value expression> [ [AS] alias_name] } [, ...] } INTO variable_name [, ...]

Invocation and Access Rules

The user must meet the following conditions to execute a <delete returning into statement>.

Syntax Rules and Parameters

table_name

It is the name of the target table from which rows will be deleted.

[ AS alias_name ]

It is an alias for the table_name.

WHERE <search condition>

It deletes the rows that satisfy the WHERE condition.
For more information, refer to the DELETE FROM statement.

<result offset clause>

It specifies the number of rows to skip in the query result.
For more information, refer to the DELETE FROM statement.

<fetch first clause>

It specifies the number of rows to fetch.
For more information, refer to the DELETE FROM statement.

<limit clause>

It specifies the number of rows to fetch, or both the number of rows to skip and the number of rows to fetch.
For more information, refer to the DELETE FROM statement.

<returning into clause>

Description

The number of rows to be deleted must be less than or equal to one.
If two or more rows are deleted, an error will occur.
For more information, refer to Differences Between DELETE Statements.

Example

The following is an example of deleting a row and retrieving the values of the deleted row into host variables in interactive SQL (gsql).

gSQL> \var v_id    INTEGER
gSQL> \var v_data  VARCHAR(128)

gSQL> DELETE FROM t1 WHERE id = 3 RETURNING id, data INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   3 data_3

1 row deleted.

Compatibility

The <delete returning into statement> is not defined in the SQL standard.

For More Information

Refer to the following.

DELETE FROM name WHERE CURRENT OF cursor_name

Function

It deletes a single row pointed to by the cursor.

Syntax

<delete statement: positioned> ::=
    DELETE [ FROM ] table_name [ [ AS ] alias_name ]
        WHERE CURRENT OF cursor_name
    ;

Invocation and Access Rules

The privilege to execute a DELETE FROM statement is required to perform a <delete statement: positioned> operation.

Syntax Rules and Parameters

table_name

It is the name of the target table from which rows will be deleted.

[ AS alias_name ]

It is an alias for the table_name.

cursor_name

The cursor corresponding to cursor_name must satisfy the following conditions:

Description

For more information, refer to Differences among DELETE-related Statements.

Example

The following is an example of how to declare a FOR UPDATE cursor and delete rows using the cursor in interactive SQL (gsql).

gSQL> DECLARE cur1 CURSOR FOR SELECT id, data FROM t1 FOR UPDATE;

Cursor declared.


gSQL> OPEN cur1;

Cursor is open.


gSQL> \var v_id   INTEGER
gSQL> \var v_data VARCHAR(128)

gSQL> FETCH cur1 INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   1 data_1

1 row fetched.

gSQL> FETCH cur1 INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   2 data_2

1 row fetched.

gSQL> DELETE FROM t1 WHERE CURRENT OF cur1;

1 row deleted.

gSQL> FETCH cur1 INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   3 data_3

1 row fetched.

gSQL> FETCH cur1 INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   4 data_4

1 row fetched.

gSQL> DELETE FROM t1 WHERE CURRENT OF cur1;

1 row deleted.

gSQL> FETCH cur1 INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   5 data_5

1 row fetched.

gSQL> FETCH cur1 INTO :v_id, :v_data;

no rows fetched.

gSQL> CLOSE cur1;

Cursor closed.

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

ID DATA  
-- ------
 1 data_1
 3 data_3
 5 data_5

3 rows selected.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

S111

ONLY in query expressions

X

B031

Basic dynamic SQL

O

For More Information

Refer to the following.

DROP AUDIT POLICY

Function

It drops an audit policy.

Syntax

<drop audit policy statement> ::= 
    DROP AUDIT POLICY [ IF EXISTS ] policy_name
;

Invocation and Access Rules

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

Syntax Rules and Parameters

IF EXISTS

No error is raised even if the specified policy_name does not exist.

policy_name

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

Description

An audit policy object that is currently active cannot be dropped. In such cases, the policy must first be deactivated using the NOAUDIT POLICY statement.

Examples

The following is an example of dropping an audit policy.

DROP AUDIT POLICY policy_table;

Compatibility

The SQL standard does not define audit policies.

For More Information

Refer to the following.

DROP CLUSTER GROUP

Function

It drops a cluster group from a cluster system.

Syntax

<drop cluster group statement> ::=
    DROP CLUSTER GROUP [IF EXISTS] group_name
    ;

Invocation and Access Rules

It can only be executed in a cluster system.
The ADMINISTRATION ON DATABASE privilege is required to execute the <drop cluster group statement>.

Syntax Rules and Parameters

[IF EXISTS]

No error is raised even if the specified cluster group does not exist.

group_name

It is the name of the cluster group.
Only cluster groups that do not contain any shards can be dropped.

Description

A cluster group can be dropped only if its removal does not result in data loss.

All members of the target cluster group must be inactive.

Otherwise, the following error will occur.

gSQL> DROP CLUSTER GROUP g3;

ERR-42000(16582): there are active cluster members in the target cluster group 'G3'

Examples

The following is an example of dropping a cluster group.

gSQL> DROP CLUSTER GROUP g3;

Cluster Group dropped.

Compatibility

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

For More Information

Refer to CREATE CLUSTER GROUP.

DROP CLUSTER LOCATION

Function

It drops the access information of a cluster member.

Syntax

<drop cluster location statement> ::=
    DROP CLUSTER LOCATION member_name 
        [ AT <domain name> ]
    ;

Invocation and Access Rules

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

Syntax Rules and Parameters

member_name

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

<domain name>

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

Description

By default, cluster location information is automatically created using the access details provided when creating a cluster group or adding a cluster member.
This information is automatically removed when the associated cluster member or group is deleted.

If the access information of a cluster location changes, it is not necessary to delete or recreate the cluster member. Instead, use the ALTER CLUSTER LOCATION statement to update the access information.

Example

gSQL> 
DROP CLUSTER LOCATION g1n2
;

Created

Compatibility

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

For More Information

Refer to the following.

DROP INDEX

Function

It drops an index.

Syntax

<drop index statement> ::=
    DROP INDEX [ IF EXISTS ] index_name
    ;

Invocation and Access Rules

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

Syntax Rules and Parameters

IF EXISTS

No error is raised even if the specified index does not exist.

index_name

It is the name of the index to be dropped.
The schema to which the index belongs  can be defined using the format schema_name.index_name. If schema_name is omitted, the default schema of the user executing the statement will be used.
Indexes created for UNIQUE or PRIMARY KEY constraints can not be dropped directly.
To drop such indexes, the associated constraints must first be dropped using the ALTER TABLE name DROP CONSTRAINT statement.

Description

Even Data Definition Language (DDL) statements such as DROP INDEX can be rolled back, as long as the transaction has not been committed.

Examples

The following is an example of dropping an index.

gSQL> DROP INDEX idx_t1_id;

Index dropped.

The following example uses the IF EXISTS clause to avoid raising an error if the specified index does not exist.

gSQL> DROP INDEX IF EXISTS not_exist_index;

Index dropped.

Compatibility

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

For More Information

Refer to the following.

DROP PROFILE

Function

It drops a profile.

Syntax

<drop profile statement> ::= 
    DROP PROFILE [ IF EXISTS ] profile_name [ CASCADE ] ;

Invocation and Access Rules

The DROP PROFILE ON DATABASE privilege is required to execute the <drop profile statement>.

Syntax Rules and Parameters

IF EXISTS

No error is raised even if the specified profile does not exist.

profile_name

It is the name of the profile to be dropped.
The DEFAULT profile cannot be dropped.

CASCADE

The CASCADE clause must be specified if there are users currently assigned to the profile being dropped.
When a profile is dropped with CASCADE, all users assigned to that profile are automatically reassigned to the DEFAULT profile.

Example

The following is an example of dropping a profile using the CASCADE clause.

gSQL> DROP PROFILE prof CASCADE;

Profile dropped.

gSQL> COMMIT;

Commit complete.

Compatibility

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

For More Information

Refer to the following.

DROP ROLE

Function

It drops a role.

Syntax

<drop role statement> ::=
    DROP ROLE [ IF EXISTS ] <role_name>
    ;

Invocation and Access Rules

One of the following conditions must be satisfied to execute a <drop role statement>.

Syntax Rules and Parameters

IF EXISTS

No error is raised even if the specified role does not exist.

<role_name>

It is the name of the role to be dropped.
Built-in roles such as ADMIN, SYSDBA, and DBA can not be dropped.

Description

It drops the role.

Examples

The following is an example of dropping a role by a user who has the DROP ROLE ON DATABASE privilege.

gSQL> GRANT DROP ROLE ON DATABASE TO u1;

Grant succeeded.

gSQL> SELECT grantee, privilege
        FROM dba_sys_privs
       WHERE grantee = 'U1';

GRANTEE PRIVILEGE                  
------- ---------------------------
U1      CREATE SESSION ON DATABASE 
U1      DROP ROLE ON DATABASE      

2 rows selected.

gSQL> SELECT grantee, granted_role, admin_option 
        FROM dba_role_privs 
       WHERE granted_role = 'ROLE1';

no rows selected.

gSQL> \connect u1 u1

gSQL> DROP ROLE role1;

Role dropped.

The following is an example of dropping a role by a user who has the WITH ADMIN OPTION privilege.

gSQL> GRANT role1 TO u1 WITH ADMIN OPTION;

Grant succeeded.

gSQL> SELECT grantee, privilege
        FROM dba_sys_privs
       WHERE grantee = 'U1';

GRANTEE PRIVILEGE                  
------- ---------------------------
U1      CREATE SESSION ON DATABASE 

1 row selected.

gSQL> SELECT grantee, granted_role, admin_option 
        FROM dba_role_privs 
       WHERE granted_role = 'ROLE1';

GRANTEE GRANTED_ROLE ADMIN_OPTION
------- ------------ ------------
U1      ROLE1        YES         

1 row selected.

gSQL> \connect u1 u1

gSQL> DROP ROLE role1;

Role dropped.

Compatibility

The SQL standard does not define the IF EXISTS clause.

SQL standard compatibility

Feature ID

Description

Compatibility

T331

Basic roles

O

T332

Extended roles

X

For More Information

Refer to CREATE ROLE.

DROP SCHEMA

Function

It drops a schema.

Syntax

<drop schema statement> ::=
    DROP SCHEMA [ IF EXISTS ] schema_name
        [ <drop behavior> ]
    ;

<drop behavior> ::=
      RESTRICT
    | CASCADE

Invocation and Access Rules

One of the following privileges is required to execute the <drop schema statement>.

Syntax Rules and Parameters

IF EXISTS

No error is raised even if the specified schema does not exist.

schema_name

It is the name of the schema to be dropped.
However, built-in schemas such as DICTIONARY_SCHEMA, INFORMATION_SCHEMA, and PUBLIC, which are automatically created when the database is created, cannot be dropped.

<drop behavior>

Description

It drops a schema. Any recycle bin objects contained in the schema are also dropped.

Examples

The following is an example of dropping a schema along with all its contained objects.

gSQL> DROP SCHEMA s1 CASCADE;

Schema dropped.

The following example uses the IF EXISTS clause to avoid raising an error if the specified schema does not exist.

gSQL> DROP SCHEMA IF EXISTS not_exist_schema;

Schema dropped.

Compatibility

The SQL standard does not define the IF EXISTS clause.

SQL standard compatibility

Feature ID

Description

Compatibility

F032

CASCADE drop behavior

O

F381

Extended schema manipulation

O

For More Information

Refer to CREATE SCHEMA.

DROP SEQUENCE

Function

It drops a sequence.

Syntax

<drop sequence generator statement> ::=
    DROP SEQUENCE [ IF EXISTS ] [schema_name.] sequence_name 
    ;

Invocation and Access Rules

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

Syntax Rules and Parameters

IF EXISTS

No error is raised even if the specified sequence does not exist.

sequence_name

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

Description

Even Data Definition Language (DDL) statements such as DROP SEQUENCE can be rolled back, as long as the transaction has not been committed.

Examples

The following is an example of dropping a sequence.

gSQL> DROP SEQUENCE seq1;

Sequence dropped.

The following example uses the IF EXISTS clause to avoid raising an error if the specified sequence does not exist.

gSQL> DROP SEQUENCE invalid_sequence;

ERR-42000(16044): sequence does not exist : 
DROP SEQUENCE invalid_sequence
              *
ERROR at line 1:


gSQL> DROP SEQUENCE IF EXISTS invalid_sequence;

Sequence dropped.

Compatibility

The SQL standard does not define the IF EXISTS clause.

SQL standard compatibility

Feature ID

Description

Compatibility

T176

Sequence generator support

O

For More Information

Refer to the following.

DROP SYNONYM

Function

It drops a synonym.

Syntax

<drop synonym statement> ::=
    DROP [ PUBLIC ] SYNONYM [ IF EXISTS ] [schema_name.]synonym_name
    ;

Invocation and Access Rules

To drop a public synonym with the PUBLIC keyword explicitly specified, the user must have the DROP PUBLIC SYNONYM ON DATABASE privilege.

To drop a private synonym, the user must have one of the following privileges.

Syntax Rules and Parameters

[ PUBLIC ]

This clause is specified when dropping a public synonym.
If omitted, a private synonym is dropped instead.

IF EXISTS

No error is raised even if the specified synonym does not exist.

synonym_name

It is the name of the synonym to be dropped.
It can define schema to which the synonym belongs using the format schema_name.synonym_name. If schema_name is omitted, the default schema of the user executing the statement will be used.
When the PUBLIC keyword is specified, a schema name must not be provided.

Description

Even Data Definition Language (DDL) statements such as DROP SYNONYM can be rolled back, as long as the transaction has not been committed.

Examples

The following is an example of dropping a private synonym.

gSQL> DROP SYNONYM MyEmp;

Synonym dropped.

The following is an example of dropping a public synonym.

gSQL> DROP PUBLIC SYNONYM MainEmp;

Synonym dropped.

Compatibility

The SQL standard does not define the DROP SYNONYM statement.

For More Information

Refer to CREATE SYNONYM.

DROP TABLE

Function

It drops a table.

If the recycle bin feature is enabled, the table is not immediately dropped but instead moved to the recycle bin.

Syntax

<drop table statement> ::=
    DROP TABLE [ IF EXISTS ] table_name
    [ <drop behavior> ]
    [ PURGE ]
    ;

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

Invocation and Access Rules

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

Syntax Rules and Parameters

IF EXISTS

No error is raised even if the specified table does not exist.

table_name

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

The following tables, which are automatically created when the database is created, cannot be dropped.

Constraints and indexes created on the table are also dropped together.

drop behavior

When omitted, the default value is RESTRICT.

CASCADE and CASCADE CONSTRAINTS have the same meaning.

If there are referencing tables that refer to the table with a FOREIGN KEY, the CASCADE CONSTRAINTS clause must be specified.

purge

When the recycle bin feature is enabled, using PURGE causes the table to be dropped immediately without being moved to the recycle bin.

Description

Even Data Definition Language (DDL) statements such as DROP TABLE can be rolled back, as long as the transaction has not been committed.

Examples

The following is an example of dropping a regular table.

gSQL> DROP TABLE region;

Table dropped.

The following example uses the IF EXISTS clause to avoid raising an error if the specified table does not exist.

gSQL> DROP TABLE IF EXISTS invalid_table;

Table dropped.

The following is an example of rolling back a dropped table.

gSQL> SELECT r_regionkey, r_name FROM region;

R_REGIONKEY R_NAME                   
----------- -------------------------
          0 AFRICA                   
          1 AMERICA                  
          2 ASIA                     
          3 EUROPE                   
          4 MIDDLE EAST              

5 rows selected.


gSQL> DROP TABLE region;

Table dropped.


gSQL> SELECT r_regionkey, r_name FROM region;

ERR-42000(16040): table or view does not exist : 
SELECT r_regionkey, r_name FROM region
                                *
ERROR at line 1:


gSQL> ROLLBACK;

Rollback complete.


gSQL> SELECT r_regionkey, r_name FROM region;

R_REGIONKEY R_NAME                   
----------- -------------------------
          0 AFRICA                   
          1 AMERICA                  
          2 ASIA                     
          3 EUROPE                   
          4 MIDDLE EAST              

5 rows selected.

Compatibility

The SQL standard does not define the following clauses.

SQL standard compatibility

Feature ID

Description

Compatibility

F032

CASCADE drop behavior

O

For More Information

Refer to CREATE TABLE.

DROP TABLESPACE

Function

It drops a tablespace.

Syntax

<drop tablespace statement> ::=
    DROP TABLESPACE [ IF EXISTS ] tablespace_name
        [ INCLUDING CONTENTS ]
        [ { AND | KEEP } DATAFILES ]
        [ <drop behavior> ]
    ;

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

Invocation and Access Rules

The DROP TABLESPACE ON DATABASE privilege is required to execute the <drop tablespace definition>.

Syntax Rules and Parameters

IF EXISTS

No error is raised even if the specified tablespace does not exist.

tablespace_name

It is the name of the tablespace to be dropped.
The following system tablespaces, which are automatically created when the database is created, cannot be dropped.

If the tablespace_name was used as the default tablespace for users, space cannot be allocated for objects once the tablespace is dropped. Therefore, after dropping the tablespace, the users' default tablespace must be changed using the ALTER USER statement.

INCLUDING CONTENTS

It drops objects (such as tables, indexes, and key constraints) that belong to the tablespace. If an index or key constraint references a table in the tablespace  but exists outside of it, that index or constraint will also be dropped.
If the INCLUDING CONTENTS clause is not used, then no objects should exist within the tablespace.

[ { AND | KEEP } DATAFILES ]

It specifies whether to drop the datafiles that make up the tablespace.
For memory temporary tablespaces, datafiles do not exist, so this clause is ignored.

drop behavior

When omitted, the default value is RESTRICT.

CASCADE and CASCADE CONSTRAINTS have the same meaning.

If a FOREIGN KEY in a different tablespace refers to a PRIMARY KEY or UNIQUE constraint that is being dropped together with the corresponding tablespace, the CASCADE CONSTRAINTS clause must be specified.

Description

Unlike other Data Definition Language (DDL) operations, the DROP TABLESPACE statement cannot be rolled back, and the transaction is automatically committed upon execution.
If the tablespace being dropped contains recycle bin objects, those objects are also permanently dropped.

Examples

The following is an example of dropping a tablespace along with all objects contained in it, as well as all datafiles that make up the tablespace:

gSQL> DROP TABLESPACE space1 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

Tablespace dropped.

The following is an example of how to avoid an error when the specified tablespace does not exist by using the IF EXISTS clause:

gSQL> DROP TABLESPACE IF EXISTS not_exist_tablespace;

Tablespace dropped.

Compatibility

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

For More Information

Refer to the following.

DROP USER

Function

It drops a database user.

Syntax

<drop user statement> ::=
    DROP USER [ IF EXISTS ] user_identifier [ <drop behavior> ]
    ;

<drop behavior> ::=
      RESTRICT
    | CASCADE

Invocation and Access Rules

The DROP USER ON DATABASE privilege is required to execute the <drop user statement>.

The schema owned by user_identifier must not exist.

For more information about dropping a schema, refer to the DROP SCHEMA statement.

Syntax Rules and Parameters

IF EXISTS

No error is raised even if the specified user does not exist.

user_identifier

It is the username of the database to be dropped.
However, the user which is automatically created when the database is created, such as "SYS", cannot be dropped.

The following types of objects are not dropped if they were created by user_identifier but are not owned by it.

<drop behavior>

User and Schema Relationships in Other DBMSs





Description

In GOLDILOCKS, the relationship between a user and schemas is 1:N.
In other words, a user may not own any schemas, or may own multiple schemas.
To drop a user, all schemas owned by the user must first be dropped.
During this process, any recycle bin objects belonging to the user are also removed.

Examples

The following is an example of dropping all schemas owned by a user, followed by dropping the user:

gSQL> DROP SCHEMA u1 CASCADE;

Schema dropped.

gSQL> DROP USER u1 CASCADE;

User dropped.

The following example uses the IF EXISTS clause to avoid raising an error if the specified user does not exist.

gSQL> DROP USER IF EXISTS not_exist_user;

User dropped.

Compatibility

The SQL standard defines the concept of a user but does not specify SQL statements for creating or dropping users.

For More Information

Refer to the following.

DROP VIEW

Function

It drops a view.

Syntax

<drop view statement> ::=
    DROP VIEW [ IF EXISTS ] view_name
    ;

Invocation and Access Rules

One of the following privileges is required to execute the <drop view statement>.

Syntax Rules and Parameters

IF EXISTS

No error is raised even if the specified view does not exist.

view_name

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

Description

Even Data Definition Language (DDL) statements such as DROP VIEW can be rolled back, as long as the transaction has not been committed.

Examples

The following is an example of dropping a view.

gSQL> DROP VIEW v1;

View dropped.

The following example uses the IF EXISTS clause to avoid raising an error if the specified view does not exist.

gSQL> DROP VIEW IF EXISTS not_exist_view;

View dropped.

Compatibility

The SQL standard does not define the IF EXISTS clause.

SQL standard compatibility

Feature ID

Description

Compatibility

F032

CASCADE drop behavior

X

For More Information

Refer to the following.

EXECUTE IMMEDIATE 'sql_string'

Function

It executes a dynamic SQL statement that was not defined at the time of writing the program.

Syntax

<execute immediate statement> ::=
    EXECUTE IMMEDIATE <SQL statement variable>
    ;

<SQL statement variable> ::=
      variable_name
    | 'sql statement'
    | "sql statement"
    | sql statement

Invocation and Access Rules

It can be used in embedded SQL.
The appropriate execution privileges for the type of dynamic SQL statement must be granted.

Syntax Rules and Parameters

<SQL statement variable>

A dynamic SQL statement referenced by a <SQL statement variable> cannot use host variables (:var) or parameter markers (?).
The following four types of <SQL statement variable> can be used.

To represent string data within a single-quoted string, two single quotes ('') must be used as follows.

{
    ...
    EXEC SQL EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES ( ''literal data'' )'; 
    ...
}

If the SQL statement is a query that produces a result set, it will execute successfully, but the result cannot be retrieved.

variable_name

The type corresponding to variable_name must be a character string.
The dynamic SQL statement defined in variable_name must be valid.

sql statement

The dynamic SQL statement defined in the sql statement must be valid.

Description

The EXECUTE IMMEDIATE 'sql_string' statement can be used for non-query SQL operations that do not include host variables in a dynamic embedded SQL application.
It is suitable for executing one-off DDL or DML statements, as it does not require a separate preparation step.
For more information, refer to  Embedded Dynamic SQL.

Example

The following is an example of using EXECUTE IMMEDIATE 'sql_string' in embedded SQL source code.

{
    ...
    sprintf(sSqlStmt, "INSERT INTO EMP_RND\n"
            "SELECT *\n"
            "FROM   EMP\n"
            "WHERE  JOB = 'RND'\n" );
    EXEC SQL EXECUTE IMMEDIATE :sSqlStmt;
    if(sqlca.sqlcode != 0)
    {
        goto fail_exit;
    }
    ...
}

The full source code that uses EXECUTE IMMEDIATE 'sql_string' can be found in the Dynamic Embedded SQL Example Program.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

B031

Basic Dynamic SQL

O

For More Information

Refer to the following.

EXECUTE statement_name

Function

It executes a prepared statement.

Syntax

<execute statement> ::=
    EXECUTE statement_name [ <parameter using clause> ] [ <result into clause> ]
    ;

<parameter using clause> ::=
      <using parameter arguments>

<using parameter arguments> ::=
    USING variable_name [, ...]

<result into clause> ::=
      <into result arguments>

<into result arguments> ::=
    INTO variable_name [, ...]

Invocation and Access Rules

It can be used in embedded SQL.
Appropriate execution privileges must be granted for the type of dynamic SQL statement.

Syntax Rules and Parameters

statement_name

It is the name of the prepared statement.
statement_name must be prepared in advance using the PREPARE statement_name syntax.
If the dynamic SQL statement referenced by statement_name includes dynamic parameters, a <parameter using clause> must be specified.
{
    ...
    EXEC SQL PREPARE stmt1 FROM 'DELETE FROM t1 WHERE c1 > ?';
    EXEC SQL EXECUTE stmt1 USING :sValue;
    ...
}
{

    ...
    EXEC SQL PREPARE stmt1 FROM 'SELECT COUNT(*) INTO :v1 FROM t1';
    EXEC SQL EXECUTE stmt1 USING :sValue;
    ...
}

If the dynamic SQL statement referenced by statement_name is a query or a stored function that returns a result, a <result into clause> must be specified.

{
    ...
    EXEC SQL PREPARE stmt1 FROM 'SELECT COUNT(*) FROM t1';
    EXEC SQL EXECUTE stmt1 INTO :sValue;
    ...
}
If multiple queries are executed, they run normally, but only the result of the first statement can be retrieved.
To retrieve multiple rows, cursor-related statements must be used as follows:

If the query produces no result, the operation completes with NO DATA.

[ <parameter using clause> ] [ <result into clause> ]

The <parameter using clause> and <result into clause> can be specified in any order, but must not be used more than once.

<parameter using clause>

If the dynamic SQL statement referenced by statement_name contains parameters, information about those parameters must be provided using the <using parameter arguments> clause.

<using parameter arguments>

When the <using parameter arguments> clause is used, the number of variable_name entries must match the number of parameters in the dynamic SQL statement referenced by statement_name.

The listed variable_names correspond to the dynamic parameters in the order in which they appear.

{

    ...
    EXEC SQL PREPARE stmt1 FROM 'DELETE FROM t1 WHERE c1 IN ( ?, ?, ? )';
    EXEC SQL EXECUTE stmt1 USING :sValue1, :sValue2, :sValue3;
    ... 
}

<result into clause>

If the dynamic SQL statement referenced by statement_name is a query, information about the result columns must be specified using the <into result arguments> clause.
If a result value is null and no INDICATOR variable is specified, a [DATA EXCEPTION, NULL VALUE, NO INDICATOR PARAMETER] error will occur.

<into result arguments>

When the <into result arguments> clause is used, the number of variable_names must match the number of result columns in the dynamic SQL statement referenced by statement_name.
The listed variable_name corresponds to the dynamic parameter in the order of its description.
{

    ...
    EXEC SQL PREPARE stmt1 FROM 'SELECT MIN(salary), MAX(salary), AVG(salary) FROM employee';
    EXEC SQL EXECUTE stmt1 INTO :sMinValue, :sMaxValue, :sAvgValue;
    ... 
}

Description

statement_name is an identifier that informs the precompiler of the statement in the embedded SQL source code.
A separate type or declaration is not required, as statement_name is not a host variable.
The EXECUTE statement_name must be written after the PREPARE statement_name.
For more information, refer to Embedded Dynamic SQL.

Example

The following is an example of using EXECUTE statement_name in embedded SQL source code.

{
    ...
    sprintf( sUpdateSql, "UPDATE EMP SET sal = sal * :v1 WHERE JOB = 'SALES'");
    EXEC SQL PREPARE UPDATE_STMT FROM :sUpdateSql;
    if(sqlca.sqlcode != 0)
    {
        goto fail_exit;
    }

    sRatio = 1.1;
    EXEC SQL EXECUTE UPDATE_STMT USING :sRatio;
    if(sqlca.sqlcode != 0)
    {
        goto fail_exit;
    }
    ...
}

The full source code that uses EXECUTE statement_name can be found in the Dynamic Embedded SQL Example Program.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

B031

Basic Dynamic SQL

O

B032

Extended dynamic SQL

X

For More Information

Refer to the following.

FETCH cursor_name

Function

It positions the cursor on a specific row of the result set and retrieves the values of that row into host variables.

Syntax

<fetch statement> ::=
    FETCH [ <fetch orientation> ] [ FROM ] cursor_name 
        <result into clause>
    ;

<fetch orientation> ::=
      NEXT
    | PRIOR
    | FIRST
    | LAST
    | CURRENT
    | ABSOLUTE position
    | RELATIVE position

<result into clause> ::=
      <into result arguments>

<into result arguments> ::=
    INTO variable_name [, ...]

Syntax Rules and Parameters

[ FROM ] cursor_name

It must be a cursor that is open within the session.
The FROM clause can be omitted.

<fetch orientation>

To use a <fetch orientation> other than FETCH NEXT, a scrollable cursor must be used.
If <fetch orientation> is omitted, the default is NEXT.
An open cursor maintains position information for the result set as shown below.

Cursor position information

Cursor position information

Cursor position

Position

Description

BEFORE THE FIRST ROW

The cursor is positioned before the first row of the result set. This is also the initial position when the cursor is opened.

ON A CERTAIN ROW

The cursor is positioned on a specific row of the result set by a FETCH operation.

AFTER THE LAST ROW

The cursor is positioned after the last row of the result set.

The behavior of <fetch orientation> based on the cursor position is as follows.

<result into clause>

The variables to receive the result columns are specified using <into result arguments>.
If a result value is null and no INDICATOR variable is specified, a [DATA EXCEPTION, NULL VALUE, NO INDICATOR PARAMETER] error will occur.

<into result arguments>

The number of variables specified in the INTO clause must be the same as the number of columns in the cursor's result set.

Description

If the cursor is positioned BEFORE THE FIRST ROW or AFTER THE LAST ROW after performing a FETCH, it remains at that position regardless of the value specified in <fetch orientation>.

Example

The following is an example of declaring a SCROLL cursor using interactive SQL (gsql) and demonstrating the behavior of various <fetch orientation> options.

gSQL> DECLARE cur_scroll SCROLL CURSOR FOR SELECT id, data FROM t1;

Cursor declared.

gSQL> OPEN cur_scroll;

Cursor is open.

gSQL> \var v_id   INTEGER
gSQL> \var v_data VARCHAR(128)

gSQL> FETCH NEXT cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   1 data_1

1 row fetched.

gSQL> FETCH NEXT cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   2 data_2

1 row fetched.

gSQL> FETCH PRIOR cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   1 data_1

1 row fetched.

gSQL> FETCH PRIOR cur_scroll INTO :v_id, :v_data;

no rows fetched.

gSQL> FETCH FIRST cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   1 data_1

1 row fetched.

gSQL> FETCH FIRST cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   1 data_1

1 row fetched.

gSQL> FETCH LAST cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   5 data_5

1 row fetched.

gSQL> FETCH LAST cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   5 data_5

1 row fetched.

gSQL> FETCH FIRST cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   1 data_1

1 row fetched.

gSQL> FETCH CURRENT cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   1 data_1

1 row fetched.

gSQL> FETCH LAST cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   5 data_5

1 row fetched.

gSQL> FETCH CURRENT cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   5 data_5

1 row fetched.

gSQL> FETCH ABSOLUTE 3 cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   3 data_3

1 row fetched.

gSQL> FETCH CURRENT cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   3 data_3

1 row fetched.

gSQL> FETCH ABSOLUTE 1 cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   1 data_1

1 row fetched.

gSQL> FETCH ABSOLUTE -1 cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   5 data_5

1 row fetched.

gSQL> FETCH ABSOLUTE 6 cur_scroll INTO :v_id, :v_data;

no rows fetched.

gSQL> FETCH ABSOLUTE -6 cur_scroll INTO :v_id, :v_data;

no rows fetched.

gSQL> FETCH ABSOLUTE 3 cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   3 data_3

1 row fetched.

gSQL> FETCH ABSOLUTE -3 cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   3 data_3

1 row fetched.

gSQL> FETCH RELATIVE 1 cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   4 data_4

1 row fetched.

gSQL> FETCH RELATIVE -1 cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   3 data_3

1 row fetched.

gSQL> FETCH RELATIVE 5 cur_scroll INTO :v_id, :v_data;

no rows fetched.

gSQL> FETCH RELATIVE -5 cur_scroll INTO :v_id, :v_data;

V_ID V_DATA
---- ------
   1 data_1

1 row fetched.

gSQL> CLOSE cur_scroll;

Cursor closed.

Compatibility

The SQL standard does not define CURRENT among <fetch orientation> options.

SQL standard compatibility

Feature ID

Description

Compatibility

F431

Read-only scrollable cursors

O

B031

Basic dynamic SQL

O

For More Information

Refer to the following.

FLASHBACK TABLE

Function

It restores the table object that was stored in the recycle bin.

Syntax

<flashback table statement> ::=
    FLASHBACK TABLE table_name
    TO BEFORE DROP [ RENAME TO new_table_name ]
    ;

Invocation and Access Rules

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

Syntax Rules and Parameters

table_name

It is the name of the object stored in the recycle bin, or the name of the dropped table.
The schema to which the dropped table belongs can be defined using the format schema_name.table_name. If schema_name is omitted, the default schema of the user executing the statement will be used.

new_table_name

This is the new name of the table to be restored.
There must not be another table with the same name within the schema.

Description

It restores a table object stored in the recycle bin using either the object name in the recycle bin or the original name of the dropped table. If multiple tables with the same name exist in the recycle bin, the most recently dropped table is restored.
If a table with the same name as the one being restored already exists in the schema, an error occurs. In this case, the table can be restored under a new name using the RENAME TO clause.
Constraints and indexes are restored with their original names. However, if a constraint or index with the original name already exists, they are restored with the names they had in the recycle bin.
Unlike other Data Definition Language (DDL) operations, the FLASHBACK TABLE statement cannot be rolled back, and the transaction is automatically committed upon execution.

Example

The following is an example of restoring a table using the object name stored in the recycle bin.

gSQL> SELECT SCHEMA_NAME, OBJECT_NAME, ORIGINAL_NAME, OBJECT_TYPE FROM USER_RECYCLEBIN;

SCHEMA_NAME OBJECT_NAME                          ORIGINAL_NAME OBJECT_TYPE
----------- ------------------------------------ ------------- -----------
PUBLIC      BIN$106A4F90165D11EA9C5C835D3E4BBBF7 T1            TABLE      

1 row selected.

gSQL> FLASHBACK TABLE "BIN$106A4F90165D11EA9C5C835D3E4BBBF7" TO BEFORE DROP;

Flashback complete.

The following is an example of restoring a table from the recycle bin using its original name before it was dropped.

gSQL> SELECT SCHEMA_NAME, OBJECT_NAME, ORIGINAL_NAME, OBJECT_TYPE FROM USER_RECYCLEBIN;

SCHEMA_NAME OBJECT_NAME                          ORIGINAL_NAME OBJECT_TYPE
----------- ------------------------------------ ------------- -----------
PUBLIC      BIN$106A4F90165D11EA9C5C835D3E4BBBF7 T1            TABLE      

gSQL> FLASHBACK TABLE T1 TO BEFORE DROP;

Flashback complete.

Compatibility

The SQL standard does not define the <flashback table statement>.

For More Information

Refer to the following.

GRANT privileges TO

Function

It grants privileges to a user or role.

Syntax

<grant privilege statement> ::=
    GRANT <privilege> TO <grantee> [, ...]
        [ WITH GRANT OPTION ]
    ;

<grantee> ::=
      PUBLIC
    | user_identifier
    | role_name
    
<privilege> ::=
      <database privilege>
    | <tablespace privilege>
    | <schema privilege>
    | <table privilege>
    | <sequence privilege>
    | <procedure privilege>
    | <package privilege>
    | <library privilege>

<database privilege> ::=
      ALL [ PRIVILEGES ] [ON DATABASE]
    | <database action> [, ...] [ON DATABASE]

<database action> ::=
      ADMINISTRATION
    | ANALYZE ANY 
    | ALTER DATABASE
    | ALTER SYSTEM
    | AUDIT SYSTEM
    | ACCESS CONTROL
    | CREATE SESSION
    | CREATE PROFILE
    | ALTER PROFILE
    | DROP PROFILE 
    | CREATE USER
    | ALTER USER
    | DROP USER 
    | CREATE ROLE
    | ALTER ROLE
    | DROP ROLE
    | GRANT ROLE
    | CREATE TABLESPACE
    | ALTER TABLESPACE
    | DROP TABLESPACE
    | USAGE TABLESPACE
    | CREATE SCHEMA
    | ALTER SCHEMA
    | DROP SCHEMA
    | CREATE PUBLIC SYNONYM
    | DROP PUBLIC SYNONYM
    | CREATE ANY TABLE
    | ALTER ANY TABLE
    | DROP ANY TABLE
    | SELECT ANY TABLE
    | INSERT ANY TABLE
    | DELETE ANY TABLE
    | UPDATE ANY TABLE
    | LOCK ANY TABLE
    | CREATE ANY VIEW
    | DROP ANY VIEW
    | CREATE ANY SEQUENCE
    | ALTER ANY SEQUENCE
    | DROP ANY SEQUENCE
    | USAGE ANY SEQUENCE
    | CREATE ANY INDEX
    | ALTER ANY INDEX
    | DROP ANY INDEX
    | CREATE ANY SYNONYM
    | DROP ANY SYNONYM
    | CREATE ANY PROCEDURE
    | ALTER ANY PROCEDURE
    | DROP ANY PROCEDURE
    | EXECUTE ANY PROCEDURE
    | CREATE ANY PACKAGE
    | ALTER ANY PACKAGE
    | DROP ANY PACKAGE
    | EXECUTE ANY PACKAGE
    | CREATE ANY LIBRARY
    | DROP ANY LIBRARY
    | EXECUTE ANY LIBRARY
    | CREATE ANY TRIGGER
    | ALTER ANY TRIGGER
    | DROP ANY TRIGGER
    | PURGE DBA_RECYCLEBIN

<tablespace privilege> ::=
      ALL [ PRIVILEGES ] ON TABLESPACE tablespace_name
    | <tablespace action> [, ...] ON TABLESPACE tablespace_name

<tablespace action> ::=
    CREATE OBJECT

<schema privilege> ::=
      ALL [ PRIVILEGES ] ON SCHEMA schema_name
    | <schema action> [, ...] [ON SCHEMA schema_name]

<schema action> ::=
      CONTROL SCHEMA
    | CREATE TABLE
    | ALTER TABLE 
    | DROP TABLE
    | SELECT TABLE
    | INSERT TABLE
    | DELETE TABLE
    | UPDATE TABLE
    | LOCK TABLE
    | CREATE VIEW
    | DROP VIEW
    | CREATE SEQUENCE
    | ALTER SEQUENCE
    | DROP SEQUENCE
    | USAGE SEQUENCE
    | CREATE INDEX
    | ALTER INDEX
    | DROP INDEX
    | CREATE SYNONYM
    | DROP SYNONYM
    | CREATE PROCEDURE
    | ALTER PROCEDURE
    | DROP PROCEDURE
    | EXECUTE PROCEDURE
    | CREATE PACKAGE
    | ALTER PACKAGE
    | DROP PACKAGE
    | EXECUTE PACKAGE
    | CREATE LIBRARY
    | DROP LIBRARY
    | EXECUTE LIBRARY
    | CREATE TRIGGER
    | ALTER TRIGGER
    | DROP TRIGGER

<table privilege> ::=
      ALL [ PRIVILEGES ] ON [TABLE] table_name
    | { <table action> | <column action> } [, ...] ON [TABLE] table_name

<table action> ::=
      CONTROL TABLE
    | SELECT
    | INSERT
    | UPDATE
    | DELETE
    | TRIGGER
    | REFERENCES
    | LOCK
    | INDEX
    | ALTER

<column action> ::=
      SELECT ( column_name [, ...] )
    | INSERT ( column_name [, ...] )
    | UPDATE ( column_name [, ...] )
    | REFERENCES ( column_name [, ...] )

<sequence privilege> ::=
      ALL [ PRIVILEGES ] ON SEQUENCE sequence_name
    | <sequence action> ON SEQUENCE sequence_name

<sequence action> ::=
    USAGE

<procedure privilege> ::=
      ALL [ PRIVILEGES ] ON PROCEDURE procedure_name
    | <procedure action> ON PROCEDURE procedure_name

<procedure action> ::=
    EXECUTE

<package privilege> ::=
      ALL [ PRIVILEGES ] ON PACKAGE package_name
    | <package action> ON PACKAGE package_name

<package action> ::=
    EXECUTE

<library privilege> ::=
      ALL [ PRIVILEGES ] ON LIBRARY library_name
    | <library action> ON LIBRARY library_name

<library action> ::=
      EXECUTE

Syntax Rules and Parameters

<grantee>

It is the user or role to which the privileges are to be granted.

WITH GRANT OPTION

It allows the grantee to grant the privilege to other users.
The WITH GRANT OPTION is permitted only when the grantee is a user.
When the same <privilege> is granted as follows, the WITH GRANT OPTION is retained.

<privilege>

It is a privilege to be granted to a grantee (the user or role receiving the privilege).
The grantor (the user executing the statement) must meet one of the following conditions:

<database privilege>

It is the privilege for database objects.
The [ON DATABASE] clause can be omitted.
The database actions that can be defined with a database privilege are as follows:
Database privilege

<database action>

Description

ADMINISTRATION

Privilege to start or shut down the server

ALTER DATABASE

Privilege to execute the ALTER DATABASE statement

ALTER SYSTEM

Privilege to execute the ALTER SYSTEM statement

AUDIT SYSTEM

Privilege to manage audit policies

ACCESS CONTROL

Privilege to control all database privileges

CREATE SESSION

Privilege to connect to the database

CREATE PROFILE

Privilege to create profiles in the database

ALTER PROFILE

Privilege to alter any profile in the database

DROP PROFILE

Privilege to drop any profile in the database

CREATE USER

Privilege to create users in the database

ALTER USER

Privilege to alter any user in the database

DROP USER

Privilege to drop any user in the database

CREATE ROLE

Privilege to create roles in the database

ALTER ROLE

Privilege to alter any role in the database

DROP ROLE

Privilege to drop any role in the database

GRANT ROLE

Privilege to grant any role in the database

CREATE TABLESPACE

Privilege to create tablespaces in the database

ALTER TABLESPACE

Privilege to alter any tablespace in the database

DROP TABLESPACE

Privilege to drop any tablespace in the database

USAGE TABLESPACE

Privilege to use any tablespace in the database

CREATE SCHEMA

Privilege to create schemas in the database

ALTER SCHEMA

Privilege to alter any schema in the database

DROP SCHEMA

Privilege to drop any schema in the database

CREATE PUBLIC SYNONYM

Privilege to create public synonyms in the database

DROP PUBLIC SYNONYM

Privilege to drop any public synonym in the database

CREATE ANY TABLE

Privilege to create tables in any schema of the database

ALTER ANY TABLE

Privilege to alter any table in the database

DROP ANY TABLE

Privilege to drop any table in the database

SELECT ANY TABLE

Privilege to query rows from any table in the database

INSERT ANY TABLE

Privilege to insert rows into any table in the database

DELETE ANY TABLE

Privilege to delete rows from any table in the database

UPDATE ANY TABLE

Privilege to update rows in any table in the database

LOCK ANY TABLE

Privilege to lock any table in the database

CREATE ANY VIEW

Privilege to create views in any schema of the database

DROP ANY VIEW

Privilege to drop any view in the database

CREATE ANY SEQUENCE

Privilege to create sequences in any schema of the database

ALTER ANY SEQUENCE

Privilege to alter any sequence in the database

DROP ANY SEQUENCE

Privilege to drop any sequence in the database

USAGE ANY SEQUENCE

Privilege to use any sequence in the database

CREATE ANY INDEX

Privilege to create indexes in any schema of the database

ALTER ANY INDEX

Privilege to alter any index in the database

DROP ANY INDEX

Privilege to drop any index in the database

CREATE ANY SYNONYM

Privilege to create synonyms in any schema of the database

DROP ANY SYNONYM

Privilege to drop any synonym in the database

CREATE ANY PROCEDURE

Privilege to create procedures or functions in any schema of the database

ALTER ANY PROCEDURE

Privilege to alter any procedure or function in the database

DROP ANY PROCEDURE

Privilege to drop any procedure or function in the database

EXECUTE ANY PROCEDURE

Privilege to execute any procedure or function in the database

CREATE ANY PACKAGE

Privilege to create packages in any schema of the database

ALTER ANY PACKAGE

Privilege to alter any package in the database

DROP ANY PACKAGE

Privilege to drop any package in the database

EXECUTE ANY PACKAGE

Privilege to execute any package of the database

CREATE ANY LIBRARY

Privilege to create libraries in any schema of the database

DROP ANY LIBRARY

Privilege to drop any library in the database

EXECUTE ANY LIBRARY

Privilege to execute any library in the database

CREATE ANY TRIGGER

Privilege to create triggers in any schema of the database

ALTER ANY TRIGGER

Privilege to alter any trigger in the database

DROP ANY TRIGGER

Privilege to drop any trigger in the database

PURGE DBA_RECYCLEBIN

Privilege to purge the entire recycle bin in the database

<tablespace privilege>

It is the privilege for tablespace objects.
The tablespace actions that can be defined with a tablespace privilege are as follows:
Tablespace privilege

<tablespace action>

Description

CREATE OBJECT

Privilege to create objects in the tablespace

<schema privilege>

It is the privilege for schema objects.

The schema actions that can be defined with a schema privilege are as follows:

Schema privilege

<schema action>

Description

CONTROL SCHEMA

All privileges on the schema

CREATE TABLE

Privilege to create tables in the schema

ALTER TABLE

Privilege to alter any table in the schema

DROP TABLE

Privilege to drop any table in the schema

SELECT TABLE

Privilege to query rows of any table in the schema

INSERT TABLE

Privilege to insert rows into any table in the schema

DELETE TABLE

Privilege to delete rows from any table in the schema

UPDATE TABLE

Privilege to update rows of any table in the schema

LOCK TABLE

Privilege to lock any table in the schema

CREATE VIEW

Privilege to create views in the schema

DROP VIEW

Privilege to drop any view in the schema

CREATE SEQUENCE

Privilege to create sequences in the schema

ALTER SEQUENCE

Privilege to alter any sequence in the schema

DROP SEQUENCE

Privilege to drop any sequence in the schema

USAGE SEQUENCE

Privilege to use any sequence in the schema

CREATE INDEX

Privilege to create indexes in the schema

ALTER INDEX

Privilege to alter any index in the schema

DROP INDEX

Privilege to drop any index in the schema

CREATE SYNONYM

Privilege to create synonyms in the schema

DROP SYNONYM

Privilege to drop any synonym in the schema

CREATE PROCEDURE

Privilege to create procedures/functions in the schema

ALTER PROCEDURE

Privilege to alter any procedure/function in the schema

DROP PROCEDURE

Privilege to drop any procedure/function in the schema

EXECUTE PROCEDURE

Privilege to execute any procedure/function in the schema

CREATE PACKAGE

Privilege to create packages in the schema

ALTER PACKAGE

Privilege to alter any package in the schema

DROP PACKAGE

Privilege to drop any package in the schema

EXECUTE PACKAGE

Privilege to execute any package in the schema

CREATE LIBRARY

Privilege to create libraries in the schema

DROP LIBRARY

Privilege to drop any library in the schema

EXECUTE LIBRARY

Privilege to execute any library in the schema

CREATE TRIGGER

Privilege to create triggers in the schema

ALTER TRIGGER

Privilege to alter any trigger in the schema

DROP TRIGGER

Privilege to drop any trigger in the schema

<table privilege>

It is the privilege for table objects or view objects.
The [TABLE] clause can be omitted.
The table actions that can be defined with the table privilege are as follows:
Table privilege

<table action>

Description

CONTROL TABLE

All privileges on the specified table

SELECT

Privilege to query rows from the table

INSERT

Privilege to insert rows into the table

UPDATE

Privilege to update rows in the table

DELETE

Privilege to delete rows from the table

TRIGGER

Privilege to create triggers on the table

REFERENCES

Privilege to create referential constraints that reference the table

LOCK

Privilege to lock the table

INDEX

Privilege to create indexes on the table

ALTER

Privilege to alter the table

For SELECT, INSERT, UPDATE, and REFERENCES, additional privileges are granted on all columns of the table.
The column actions that can be defined with the table privilege are as follows.
Note that column actions apply only to base tables.
Column privilege

<column action>

Description

SELECT (columns)

Privilege to query the specified columns

INSERT (columns)

Privilege to insert rows including the specified columns

UPDATE (columns)

Privilege to update the specified columns

REFERENCES (columns)

Privilege to create referential constraints that reference the specified columns

<sequence privilege>

It is the privilege for sequence objects.
The sequence actions that can be defined with the sequence privilege are as follows:
Sequence privilege

<sequence action>

Description

USAGE

Privilege to use the sequence

<procedure privilege>

It is the privilege for procedures/ function objects.
The actions that can be defined with the procedure privilege are as follows:
Procedure privilege

<procedure action>

Description

EXECUTE

Privilege to execute the procedure/function

<package privilege>

It is the privilege for package objects.
The actions that can be defined with the package privilege are as follows:
Package privilege

<package action>

Description

EXECUTE

Privilege to execute the package

<library privilege>

It is the privilege for library objects.

The actions that can be defined with the library privilege are as follows.

Library privilege

<package action>

Privilege for executing the package

EXECUTE

Privilege to execute the library

Description

Data Definition Language (DDL) such as GRANT privileges can be rolled back as long as the transaction has not been committed.
An owner who creates a SQL schema object—such as a table or sequence—automatically receives certain privileges on that object without requiring explicit privilege grants.
For more information, refer to the following CREATE statements:
However, for non-schema objects—such as schemas or tablespaces—the creator (owner) does not automatically receive any privileges on the object. Therefore, explicit privilege grants are required.
For more information, refer to the following CREATE statements:

Examples

The following is an example of granting the SELECT ON TABLE t1 privilege to user u1.

gSQL> GRANT SELECT ON t1 TO u1;

Grant succeeded.

The following is an example of granting the SELECT ON TABLE t1 privilege to the PUBLIC account, which refers to all authorizations (users and roles).

gSQL> GRANT SELECT ON t1 TO PUBLIC;

Grant succeeded.

The following is an example of user u1 granting this privilege to other users using the WITH GRANT OPTION.

gSQL> GRANT SELECT ON t1 TO u1 WITH GRANT OPTION;

Grant succeeded.

The following is an example of the user executing the statement granting all privileges they own on TABLE t1 to user u1 using the WITH GRANT OPTION.

gSQL> GRANT ALL PRIVILEGES ON TABLE t1 TO u1;

Grant succeeded.

The following is an example of granting the CREATE SESSION ON DATABASE privilege, which allows a user to connect to the database.

gSQL> GRANT CREATE SESSION ON DATABASE TO u1;

Grant succeeded.

The following is an example of granting multiple privileges to user u1 for creating objects such as tables, views, indexes, and sequences in SCHEMA s1.

gSQL> GRANT CREATE TABLE, CREATE VIEW, CREATE INDEX, CREATE SEQUENCE ON SCHEMA s1 TO u1;

Grant succeeded.

The following is an example of granting the privilege to create objects in TABLESPACE mem_data_tbs to user u1.

gSQL> GRANT CREATE OBJECT ON TABLESPACE mem_data_tbs TO u1;

Grant succeeded.

The following is an example of granting user u1 the privilege to query specific columns in TABLE t1.

gSQL> GRANT SELECT( id, name ) ON TABLE t1 TO u1;

Grant succeeded.

The following is an example of granting user u1 the privilege to use the NEXTVAL() and CURRVAL() functions on SEQUENCE seq1.

gSQL> GRANT USAGE ON SEQUENCE seq1 TO u1;
Grant succeeded.

The following is an example of granting the SELECT ON TABLE t1 privilege to role1.

gSQL> GRANT SELECT ON t1 TO role1;  

Grant succeeded.

Compatibility

The SQL standard does not define the following privileges.

SQL standard compatibility

Feature ID

Description

Compatibility

S023

Basic structured types

X

S024

Enhanced structured types

X

S081

Subtables

X

T211

Basic trigger capability

O

T281

SELECT privilege with column granularity

O

T332

Extended Roles

X

F731

INSERT column privileges

O

For More Information

Refer to the following.

GRANT role TO

Function

It grants the role to a user or another role.

Syntax

<grant role statement> ::=
    GRANT <role granted> [ , ... ] TO <grantee> [ , ... ]
        [ WITH ADMIN OPTION ]
    ;

<role granted> ::=
    <role_name>

<grantee> ::=
      PUBLIC
    | <user_identifier>
    | <role_name>

Invocation and Access Rules

One of the following conditions must be satisfied to execute a <grant role statement>.

Syntax Rules and Parameters

<role granted>

It is the name of the role to be granted.

<grantee>

It is the user or role that is to be granted the role.

WITH ADMIN OPTION

It allows the grantee (the user or role receiving the role) to grant the role to other users or roles.

When the same <role granted> is granted multiple times as shown below, the WITH ADMIN OPTION is preserved:

Description

It grants the role to another user or role.
Data Definition Language (DDL) statements such as GRANT role can be rolled back if the transaction has not yet been committed.
When a user creates a role, the role is automatically granted to that user with the WITH ADMIN OPTION, even if it is not explicitly granted. 
For more information, refer to the CREATE ROLE statement.

Examples

The following is an example of a user with the GRANT ROLE ON DATABASE privilege granting a role.

gSQL> GRANT GRANT ROLE ON DATABASE TO u1;

Grant succeeded.

gSQL> SELECT grantee, privilege
        FROM dba_sys_privs
       WHERE grantee = 'U1';

GRANTEE PRIVILEGE                  
------- ---------------------------
U1      CREATE SESSION ON DATABASE 
U1      GRANT ROLE ON DATABASE     

2 rows selected.

gSQL> SELECT grantee, granted_role, admin_option 
        FROM dba_role_privs 
       WHERE granted_role = 'ROLE1';

no rows selected.

gSQL> \connect u1 u1

gSQL> GRANT role1 TO role2;

Grant succeeded.

The following is an example of a user with the WITH ADMIN OPTION for a role granting that role.

gSQL> GRANT role1 TO u1 WITH ADMIN OPTION;

Grant succeeded.

gSQL> SELECT grantee, privilege
        FROM dba_sys_privs
       WHERE grantee = 'U1';

GRANTEE PRIVILEGE                  
------- ---------------------------
U1      CREATE SESSION ON DATABASE 

1 row selected.

gSQL> SELECT grantee, granted_role, admin_option 
        FROM dba_role_privs 
       WHERE granted_role = 'ROLE1';

GRANTEE GRANTED_ROLE ADMIN_OPTION
------- ------------ ------------
U1      ROLE1        YES         

1 row selected.

gSQL> \connect u1 u1

gSQL> GRANT role1 TO role2;

Grant succeeded.

Compatibility

SQL standard compatibility

Feature ID

Description

Compatibility

T331

Basic roles

O

T332

Extended roles

X

For More Information

Refer to the following.