3. Data Definition Language#
This chapter describes Data Definition Language (DDL), which are SQL statements used to create database objects.
ALTER DATABASE#
Syntax#
alter_database :: =
startup_clause ::=
rename_datafile_clause ::=
create_datafile_clause ::=
create_checkpoint_image_clause ::=
session_clause ::=
archivelog_option ::=
backup_clause ::=
incremental_backup_clause ::=
incremental_level_clause ::=
with_tag_clause ::=
recover_clause ::=
from_tag_clause ::=
until_option ::=
restore_clause ::=
restore_database_clause ::=
restore_tablespace_clause ::=
change_backup_directory_clause ::=
move_backup_clause ::=
delete_backup_clause ::=
change_tracking_clause ::=
snapshot_clause ::=
Prerequisites#
The ALTER DATABASE statement can only be executed in a startup phase preceding the SERVICE phase by the SYS user after connecting in SYSDBA mode. The exception is when using the SESSION CLOSE option, in which case it is not necessary to connect in SYSDBA mode in order to execute this statement.
Description#
The ALTER DATABASE statement modifies, maintains, or restores an existing database.
database_name
This element is used to specify the name of the database to be managed.
startup_clauses
This element is used to specify the name of the startup phase in which to start up Altibase.
CONTROL
This option is used to start the database in the CONTROL phase. When the database is started in this phase, database media recovery can be performed. Tablespaces can also be discarded in this phase. For more detailed information about the various Altibase startup phases, please refer to the Administrator’s Manual.
To proceed to the phase immediately following the CONTROL phase, the META phase, execute this statement as follows:
ALTER DATABASE dababase_name META;
META
This option is used to start the database in the META phase. While proceeding to this phase from the previous phase, the CONTROL phase, the database meta data are loaded. To proceed to the next phase, execute the following statement:
ALTER DATABASE dababase_name SERVICE;
SERVICE
This option is used to start the database in the SERVICE phase. When the database is started in this phase, all memory and disk tables are loaded, and extended services such as replication and SNMP can be started. If the database can be successfully started in this phase, it means that any required recovery has been performed, and that the system is in a state in which service is being provided normally.
UPGRADE
This option is used to start the database in the META UPGRADE phase. When the database is started up to this phase, the meta data are upgraded, meaning that all recovery-related tasks have been completed.
To proceed to the next phase, execute the following statement:
ALTER DATABASE dababase_name SERVICE;
RESETLOGS
This task is required to ensure normal startup of the server after incomplete recovery has been performed in the CONTROL phase. Logs that are no longer necessary once incomplete recovery has been performed are deleted while proceeding to this phase.
To proceed to the next phase, execute the following statement:
ALTER DATABASE dababase_name SERVICE;
META RESETUNDO
In this phase, the SYS_TBS_DISK_UNDO tablespace is initialized, but the size of the tablespace file is not changed. Before executing this statement, check the integrity of the database, ensure that disk garbage collection has been performed, and shut down the database normally
SHUTDOWN NORMAL
The server waits until all client connections have been disconnected normally before shutting down normally.
SHUTDOWN IMMEDIATE
The server forcibly disconnects all clients and then shuts down normally.
SHUTDOWN EXIT
This option is used to kill the Altibase server forcibly. When Altibase is shut down in this way, the contents of the database will likely be invalid, and thus the next time the server is executed, recovery tasks will have to be performed.
RENAME DATAFILE
This command is used to change a reference to a data file within Altibase so that it points to a data file that has a different name or is located in a different directory. The data file specified in TO 'datafile_path' must exist. This command can only be executed in the CONTROL phase. datafile_path must be an absolute path.
For reference, the ALTER TABLESPACE statement is used to move memory tablespace checkpoint image files.
CREATE DATAFILE
When a disk data file has been lost, this command is used to create a data file with reference to the log anchor data. After this statement is executed, complete media recovery is performed to restore the data file.
This statement is available only during the CONTROL phase.
datafile_path, which is where the data file will be created, must be an absolute path. For reference, the ALTER TABLESPACE statement is used to create memory tablespace checkpoint image files.
CREATE CHECKPOINT IMAGE
When a memory checkpoint image file has been lost, this command is used to create a checkpoint image file with reference to the log anchor data. After this statement is executed, complete media recovery can be performed in order to restore the memory checkpoint image file.
Because the checkpoint image file is created in the checkpoint path specified for memory tablespaces, it is not necessary to specify the path; only the name of the file to be created need be provided.
This statement is available only during the CONTROL phase.
<Query> Recreate the lost checkpoint image file MEM-TBS-1.
iSQL> ALTER DATABASE CREATE CHECKPOINT IMAGE 'MEM-TBS-1';
SESSION CLOSE
This statement is used to forcibly terminate a session.
This statement can specify the session ID and user to terminate a session, and all sessions can be terminated with ALL statement. However, the session accessed by the current user cannot be terminated. If this statement is executed, a transaction of the session is rolled back.
Note: It is impossible to terminate a session immediately if the session is waiting to obtain a lock.
archivelog_option
The database can be switched between archivelog mode and noarchivelog mode in the CONTROL phase.
BACKUP LOGANCHOR
When the database is operating in archivelog mode, this statement is used to back up log anchor files online without interrupting service.
BACKUP TABLESPACE
When the database is operating in archivelog mode, this statement is used to back up the specified tablespace to the backup directory without interrupting service.
BACKUP DATABASE
When the database is operating in archivelog mode, this statement is used to back up all memory and disk tablespaces and log anchor files without interrupting service.
incremental_backup_clause
This incrementally backs up the whole database or certain tablespaces.
incremental_level_clause
This specifies the incremental backup level.
WITH TAG tag_name
This specifies a tag name for a backup.
RECOVER DATABASE
This is used to perform complete media recovery. The log files in the archive log directory are read for use in recovering the data files that were affected by media errors to the current point in time.
FROM TAG tag_name
This specifies a database restoration or recovery from the backup with the tag name tag_name.
RECOVER DATABASE UNTIL TIME
This is used to perform incomplete media recovery to a specified point in time. The log files in the archive log directory are read for use in recovering the data files that were affected by media errors to the specified point in time.
RECOVER DATABASE UNTIL CANCEL
This is used to perform incomplete media recovery to the most recent point in time at which the logs in archive log files are valid. The log files in the archive log directory are read for use in recovering the data files that were affected by media errors to that point in time.
restore_database_clause
This performs a complete media restoration on a database, or an incomplete restoration to a specific point in time or a specific tag name.
restore_tablespace_clause
This performs a complete restoration on a tablespace.
change_backup_directory_clause
This specifies the location of backup files generated by incremental backups.
move_backup_clause [WITH CONTENTS]
This changes the incremental backup directory. If the WITH CONTENTS option is specified, previous backup files are transferred to the new directory.
delete_backup_clause
This deletes incremental backup files which have expired.
change_tracking_clause
This enables or disables page change tracking for incremental backups.
snapshot_clauses
This option configures SNAPSHOT SCN on the basis of the time when BEGIN SNAPSHOT is executed, and exports data with iLoader based upon the configured SCN.
Examples#
<Query> Start up a database called mydb to provide normal service.
iSQL> ALTER DATABASE mydb SERVICE;
\<Query> Switch the database to archievelog mode.
iSQL> ALTER DATABASE ARCHIVELOG;
\<Query> Start up the database normally after the incomplete recovery.
iSQL> ALTER DATABASE mydb META RESETLOGS;
\<Query> Back up the SYS_TBS_DISK_DATA tablespace to the /altibase backup directory.
iSQL> ALTER DATABASE TABLESPACE SYS_TBS_DISK_DATA TO ‘/altibase_backup/’;
\<Query> Restore the database to Feb. 16, 2009, 12:00 PM from a previous backup.
iSQL> ALTER DATABASE RECOVER DATABASE UNTIL TIME ‘2008-02-16:12:00:00’;
\<Query>Restore the database from a previous backup to reflect the entire contents of the log file #20000, which immediately precedes the log file #20001, which was lost.
iSQL> ALTER DATABASE RECOVER DATABASE UNTIL CANCEL;
ALTER DATABASE LINKER#
For information on Database Link, please refer to the Database Link User's Manual.
ALTER INDEX#
Syntax#
alter_index ::=
directkey_mod_clause ::=
rebuild_clause ::=
index_attribute ::=
alter_index_properties::=
alter_index_segment_attribute_clause::=
storage_clause::=
allocate_extent_clause::=
Prerequisites#
The SYS user, the owner of the schema containing the index, and users having the ALTER ANY INDEX system privilege can execute the ALTER INDEX statement.
Description#
The ALTER INDEX statement is used to change or rebuild an existing index.
user_name
This specifies the name of the owner of the index to be altered.
If omitted, Altibase will assume that the index belongs to the schema of the user connected via the current session.
index_name
This specifies the name of the index to be altered.
directkey_mod_clause
This specifies whether to use a direct key index or not. For more detailed information about direct key indexes, please refer to CREATE INDEX.
- MAXSIZE integer
This sets the maximum size of a direct index. - OFF
This changes a direct key index to a normal index.
rebuild_clause
This rebuilds an existing index or one of its partitions.
index_attribute
This specifies the tablespace in which the rebuilt index partition will be stored.
RENAME
This specifies the name of index to be changed.
AGING
This records a transaction commit SCN in an index page and deletes old versions of nodes. This statement is only available for disk-based indexes.
REORGANIZATION
The ALTER INDEX clause reorganizes the index space through integration of leaf nodes in memory B-tree index and neighboring nodes.
The REORGANIZATION clause ensures high efficiency of index space especially when it is used in a case the index range is relatively greater than that of the data, or there is an occurrence of index fragmentation on particular indexes. The memory based B-tree indexes are available for use.
alter_index_segment_attribute_clause
- INITRANS Clause
This changes the initial number of Touched Transaction Slots (TTS). - MAXTRANS Clause
This changes the maximum number of Touched Transaction Slots (TTS).
storage_clause
Sets parameters for managing extents in segments.
- INITEXTENTS Clause
This ignores the INITEXTENTS parameter in the ALTER INDEX statement. - NEXTEXTENTS Clause
This determines the number of extents that are added to the segment every time the segment is increased in size. - MINEXTENTS Clause
This Sets the minimum number of extents in a segment. - MAXEXTENTS Clause
This sets the maximum number of extents in a segment.
allocate_extent_clause
This explicitly allocates extents to the index segment. Set SIZE to the total size of extents that are to be added to the index segment. If the disk tablespace consists of several data files, extents are distributed equally between them,
Examples#
Changing the Direct Key Index#
\<Query> Rebuild the index partition IDX_P5 in the tablespace TBS1
iSQL> ALTER INDEX idx1 DIRECTKEY;
\<Query> Change the direct key index idx1 to a normal index.
iSQL> ALTER INDEX idx1 DIRECTKEY OFF;
<Query> Change the index idx3 to a direct key index and set the maximum
iSQL> ALTER INDEX idx3 DIRECTKEY MAXSIZE 10;
Rebuilding the Index Partition#
<Query> Rebuild the index partition idx_p5 in tablespace tbs1.
iSQL> ALTER INDEX IDX1 REBUILD PARTITION idx_p5 TABLESPACE tbs1;
Changing the Index Name#
<Query> Change the name of emp_idx1 to emp_idx2.
iSQL> ALTER INDEX emp_idx1 RENAME TO emp_idx2;
Allocating Extents to Indexes#
\<Query> Allocate extents totaling 10MB in size to the index LOCAL_IDX, which is located in a disk tablespace.
iSQL> ALTER INDEX felt_idx ALLOCATE EXTENT ( SIZE 10M );
Reorganization Indexes#
<Query> Execute reorganization of index idx1.
iSQL> ALTER INDEX idx1 REORGANIZATION;
ALTER JOB#
Syntax#
alter_job ::=
execute_procedure_statement ::=
Prerequisites#
Only the SYS user can change the JOB with this statement. In order to perform the created JOB, it should be activated with ENABLE when creating or after creating the JOB.
Description#
The definition of the JOB created with the CREATE JOB statement can be changed. The execution statement of the procedure registered for the JOB, the start time and end time of the JOB, the execution interval of the job and etc. can be changed with this statement.
job_name
This specifies the name of the JOB to be changed.
START
This specifies the time at which the JOB is to start.
END
This specifies the time at which the JOB is to end.
expr1
This accepts only a DATA type value or a DATE type expression.
INTERVAL number
This specifies the interval after which the JOB is to run again, after it has run for the first time. The unit of time is determined by the YEAR, MONTH, DAY, HOUR, MINUTE which is specified after the number.
expr2
This specifies the input argument value necessary for the execution of the defined procedure as a constant or an expression.
ENABLE/DISABLE
This is used to modify whether to execute each join in the job scheduler.
COMMENT
This allows users to modify the job description
Examples#
\<Query> Change job1 to the enabled state.
iSQL> ALTER JOB job2 SET ENABLE;
Alter success.
\<Query> Change job2 to the disabled state.
iSQL> ALTER JOB job2 SET DISABLE;
Alter success.
\<Query> Change the start time of job1 to ‘January 1st, 2013’.
iSQL> ALTER JOB job1 SET START to_date('20130101','YYYYMMDD');
Alter success.
\<Query> Change the procedure to execute in job2 to the proc1 procedure in usr1.
iSQL> alter job job2 set exec usr1.proc1;
Alter success.
\<Query>Change the start time of job2 to 'June 3rd, 2013'.
iSQL> alter job job2 set start to_date('2013/06/03 10:00:00','YYYY/MM/DD HH24:MI:SS');
Alter success.
\<Query> Change the end time of job2 to 'June 7th, 2013 10:00:00'.
iSQL> alter job job2 set end to_date('2013/06/07 10:00:00', 'YYYY/MM/DD HH24:MI:SS');
Alter success.
\<질의> Change the interval after which job2 is to run to 10 minutes.
iSQL> alter job job2 set interval 10 minute;
Alter success.
ALTER QUEUE#
Syntax#
alter_queue ::=
Description#
ALTER QUEUE alters the definition of a queue.
COMPACT
This returns empty pages to the tablespace in which the queue is located. No data is actually moved.
MSGID RESET
This resets the queue's MSGID
ALTER REPLICATION#
Syntax#
alter_replication ::=
replication_item ::=
alter_replication_set_clause ::=
offline_clause ::=
Prerequisites#
Only the SYS user can execute replication-related statements.
Description#
ALTER REPLICATION controls the operation of a replication object that has been created with the CREATE REPLICATION statement.
For more information about control statements such as replication termination, please refer to "Data Control Statements" > alter_replication_dcl.
For more information about replication, please refer to the Replication Manual.
replication_name
This specifies the name of the replication object.
SYNC
This sends all data in replication target tables on the local server to the corresponding tables on the remote server and starts replication.
SYNC ONLY
This sends all data in replication target tables on the local server to the corresponding tables on the remote server. It does not initiate a replication sender thread.
PARALLEL parallel_factor
The parallel_factor option does not need to be specified; on omission, the default value is 1. The maximum value of parallel_factor is (the number of CPUs * 2). This value cannot be exceeded, even if a higher maximum value is specified. If 0 (zero) or a negative value is specified, an error is returned.
TABLE replication_item
This specifies which of the replication tables or partitions on the local server to synchronize using the SYNC parameter. If this clause is specified, replication starts from the point in time up to which replication was last performed after the specified tables have been synchronized. If the TABLE clause is omitted, replication starts from the current position in the logs after all of the replication tables and partitions have been synchronized.
START
Replication starts from the point in time up to which replication was last performed.
QUICKSTART
Replication starts from the current point in time.
START/ QUICKSTART RETRY
When STARTing or QUICKSTARTing replication using the RETRY option, even if handshaking fails, a sender thread is created on the local server. When handshaking between the local server and the remote server subsequently succeeds, replication starts.
When this option is used, iSQL will report handshaking success even if the first handshake attempt fails. Therefore, the user has to check the result of this command by viewing trace logs or performance views.
When starting replication without the RETRY option, if the first handshaking attempt fails, an error is raised and execution stops. Note that the use of the RETRY option is not supported in EAGER mode.
STOP
Please refer to the ALTER REPLICATION caluse of the Data Control Statement.
RESET
This resets replication information (such as the restart SN). It can only be executed while replication is stopped. It is an alternative to executing the DROP REPLICATION and CREATE REPLICATION commands.
ADD TABLE
This is used to add a table to a replication object. A table can be added to a replication object only when replication is stopped.
TABLE FROM replication_item TO replication_item
This is used to specify the name of the replication target table or partition, together with the name of the table owner.
DROP TABLE
This is used to remove a table from a replication object. A table can be removed from a replication object only when replication is stopped.
FLUSH
Please refer to the ALTER REPLICATION clause of the Data Control Statement.
SET HOST
This sets a particular host as the current one. It can be changed while replication is stopped.
USING conn_type [ib_latency]
The communication method (TCP or InfiniBand) can be set with the remote server. The ib_latency value can be set only when using InfiniBand. To use InfiniBand, the IB_ENABLE property must have a value of 1.
alter_replication_set_clause
This clause allows the user to enable or disable the following options for replication in LAZY mode.
- RECOVERY: Enables or disables data recovery.
- GAPLESS: Enables or disables replication gap dissolution.
- GROUPING: Enables or disables replication transaction grouping.
- PARALLEL: Enables or disables parallel appliers. Also sets the number of parallel appliers.
offline_cluase
This clause allows the user to change the offline option or perform replication with a specified offline path.
Considerations#
There are several points that users working with replication must keep in mind before using replication. Before executing an ALTER REPLICATION command, please refer to the Replication Manual.
Example#
Start execution of the repl1 replication object#
\<Query> Send the data on the local server data to the remote server, and start replication.
iSQL> ALTER REPLICATION rep1 SYNC;
Alter success.
\<Query> Start the rep1 replication from the time point at which replication was most recently executed:
iSQL> ALTER REPLICATION rep1 START;
Alter success.
\<Query> Start the replication
iSQL> ALTER REPLICATION rep1 QUICKSTART;
Alter success.
Remove the replication target table employees from a replication object named rep1.#
iSQL> ALTER REPLICATION rep1 STOP;
Alter success.
iSQL> ALTER REPLICATION rep1 DROP TABLE FROM sys.employees TO sys.employees;
Alter success.
Add partition p2 of the partitioned table tbl_sales to the replication object of the name rep1.#
iSQL> ALTER REPLICATION rep1 STOP;
Alter success.
iSQL> ALTER REPLICATION rep1 ADD TABLE
FROM sys.tbl_sales PARTITION p2 TO sys.tbl_sales PARTITION p2;
Alter success.
Add the table employees to the replication object rep1.#
iSQL> ALTER REPLICATION rep1 STOP;
Alter success.
iSQL> ALTER REPLICATION rep1 ADD TABLE FROM sys.employees TO sys.employees;
Alter success.
ALTER SEQUENCE#
Syntax#
alter_sequence ::=
sequence_options ::=
sync_table_clause ::=
Prerequisites#
Only the SYS user, the owner of the schema to which the sequence belongs, users having the ALTER object privilege on the sequence, and users having the ALTER ANY SEQUENCE system privilege can execute this statement.
Description#
After a sequence has been created using the CREATE SEQUENCE statement, this statement is used to change the definition of the sequence. For more detailed information, please refer to the description of the CREATE SEQUENCE statement.
user_name
This is used to specify the name of the owner of the sequence to be changed. If omitted, Altibase will assume that the sequence belongs to the schema of the user connected via the current session.
seq_name
This is used to specify the name of the sequence to be altered.
INCREMENT BY
This is used to specify the interval between sequence numbers.
MAXVALUE
This is used to specify the maximum value that the sequence can generate.
MINVALUE
This is used to specify the minimum value of the sequence.
CYCLE
This is used to allow a sequence to continue to output values after it reaches the limit specified by MAXVALUE or MINVALUE. In the case of an ascending sequence, the minimum value will be output once its maximum value has been reached, whereas the opposite is true for a descending sequence: once it reaches its minimum value, it outputs its maximum value.
CACHE
This is used to specify the number of sequence values that are cached in memory so that they can be accessed more quickly. The first time the sequence is referred to, the cache is populated, and whenever values are subsequently requested from the sequence, they are retrieved from the cached values. After the last sequence value in the cache has been used, the next request for a key value from the sequence causes new sequence values to be created and cached in memory. The number of sequence values that are created and cached at this time is set using the CREATE SEQUENCE statement. If this option is omitted, the default value is 20.
FLUSH CACHE
This flushes the sequence values cached in memory. If the value of a sequence is requested after flushing the cache using this option, new sequence values are cached in memory.
ENABLE SYNC TABLE
This creates a custom table for sequence replication for the purpose of replicating sequence numbers. The custom table for sequence replication is automatically granted the name, "[sequence name]$seq".
DISABLE SYNC TABLE
This drops the custom table for sequence replication used for the purpose of replicating a sequence.
Restriction#
When changing the definition of an existing sequence, the START WITH clause cannot be used, because the sequence has already been created.
A custom table for sequence replication can be created only if the length of the sequence name is equal to or smaller than 36 bytes.
For detailed more information about sequences, please refer to the description of the CREATE SEQUENCE statement.
Examples#
\<Query> Change the sequence seq1 so that the minimum value is 0, the maximum value is 100, and increments by 1.
iSQL> ALTER SEQUENCE seq1
INCREMENT BY 1
MINVALUE 0
MAXVALUE 100;
Alter success.
\<Query> Change the minimum and maximum values of sequence seq2 to unlimited.
iSQL> ALTER SEQUENCE seq2
NOMAXVALUE
NOMINVALUE;
Alter success.
\<Query> Flush the sequence values cached in the sequence seq1.
iSQL> ALTER SEQUENCE seq1 FLUSH CACHE;
Alter success.
\<Query> Create a custom table for sequence replication for the purpose of replicating the sequence seq1.
iSQL> ALTER SEQUENCE seq1 ENABLE SYNC TABLE;
ALTER TABLE#
Syntax#
alter_table::=
log_compression_clause ::=
alter_table_properties::=
alter_table_tablespace::=
table_move_index_clause::=
table_lob_column_clause::=
logging_clause::=
parallel_clause::=
row_movement_clause::=
alter_table_segment_properties::=
alter_table_segment_attribute_clause::=
storage_clause::=
alter_table_partitioning::=
add_table_partition ::=
alter_partition ::=
partition_index_clause ::=
partition_lob_column_clause ::=
coalesce_table_partition ::=
drop_table_partition ::=
merge_table_partition ::=
rename_table_partition ::=
split_table_partition ::=
truncate_table_partition ::=
partition_spec ::=
table_partition_description ::=
index_partition_spec ::=
index_partition_description ::=
partition_access_mode ::=
access_mode_clause ::=
column_clauses::=
add_column_clauses::=
column_definition::=
partition_lob_storage_clause ::=
alter_column_clause ::=
modify_column_clause::=
modify_column_spec::=
drop_column_clause::=
rename_column_clause::=
reorganize_column_clause::=
constraints_clauses::=
add_table_constraint_clauses ::=
table_constraint_for_alter::=
constraint_state::=
modify_constraint_clause::=
rename_constraint_clauses ::=
drop_constraint_clause::=
aging_clause::=
compact_clause::=
allocate_extent_clause::=
Prerequisites#
Only the SYS user, the owner of the schema to which the table belongs, users having the ALTER object privilege for the table, and users having the ALTER ANY TABLE system privilege can alter table definitions.
Description#
The ALTER TABLE statement is a SQL statement which modifies the definition of a specified table. Execution of this statement alters the meta information of the table.
The ALTER TABLE statement can modify the attributes of partitioned tables. The clauses related to partitioned tables are the ALTER, ADD, COALESCE, DROP, MERGE, RENAME, SPLIT and TRUNCATE clauses.
The following table shows whether each statement can be used with range-, list- and hash-partitioned tables.
| Range-Partitioned Tables | List-Partitioned Tables | Hash-Partitioned Tables | |
|---|---|---|---|
| ALTER | ○ | ○ | ○ |
| ADD | X | X | ○ |
| COALESCE | X | X | ○ |
| DROP | ○ | ○ | X |
| MERGE | ○ | ○ | X |
| RENAME | ○ | ○ | ○ |
| SLIP | ○ | ○ | X |
| TRUNCATE | ○ | ○ | ○ |
[Table 3-1] Operations Supported for Use with Different Partitioning Methods
user_name
This is used to specify the name of the owner of the table to be altered. If omitted, Altibase will assume that the table belongs to the schema of the user connected via the current session.
tbl_name
This is used to specify the name of the table to alter.
parallel_clause
Please refer to the parallel clause description of CREATE TABLE.
alter_table_segment_attribute_clause
- PCTFREE Clause
This is used to change the percentage of free space that is reserved for future use when updating records that have already been saved in pages.
An ALTER TABLE statement containing the alter_table_segment_attribute_clause, which is used to change segment attributes, can be executed while Altibase is running. However, the changes will not be immediately applied in all of the pages in the segment; rather, each table page will be changed individually the next time the page is accessed.
-
PCTUSED Clause
This is used to change the threshold below which the amount of used space in a page must decrease in order for the page to return to the state in which records can be inserted. -
INITRANS Clause
This is used to change the initial number of TTS (Touched Transaction Slots). -
MAXTRANS Clause
This is used to change the maximum number of TTS (Touched Transaction Slots).
storage_clause
This is used to set parameters for managing extents in segments.
- INITEXTENTS Clause
The INITEXTENTS parameter in the ALTER TABLE statement is ignored. - NEXTEXTENTS Clause
This determines the number of extents that are added to the segment every time the segment is increased in size - MINEXTENTS Clause
This sets the minimum number of extents in a segment. - MAXEXTENTS Clause
This sets the maximum number of extents in a segment.
add_table_partition
This clause can add a partition into a partitioned table and it can be also used in hash-partitioned tables. The local indexes are automatically created in added partitions as well if the local indexes are already created in the existing partitions. At this point, names are automatically determined by the system, and the indexes are stored into tablespace where the newly added partitions located.
partition_spec
This is used to specify the name of the partition and the tablespace in which the partition will be stored. The name of the tablespace can be omitted. If it is omitted, the data pertaining the partition are stored in the tablespace in which the table is located. Furthermore, if an index has been defined for the table, the tablespace in which the index partition is stored can be specified.
alter_partition
This clause modifies the tablespace of partitions. It transfers not only the partition records which will be moved, but also transfers the local indexes and LOB columns that are created in the partitions.
However, the index can be transferred to the tablespace of an equivalent storage medium, and the LOB column can be transferred only to a different tablespace when a partition is moved to the disk tablespace.
partition_index_clause
This clause specifies the tablespace in which the local index of partition will be located when modifying the tablespace of partition.
partition_lob_column_clause
This clause specifies the tablespace in which LOB column of partition will be located when modifying the tablespace of partition.
table_partition_description
This is used to specify the tablespace in which each partition is stored and the attributes of LOB columns, if any.
If the tablespace clause is omitted, the data are stored in the default tablespace for the table. In the same way, if the tablespace statement for a LOB column is omitted, the LOB column data are stored in the tablespace in which the partition is stored.
For more detailed information on using tablespaces, please refer to the explanation of table_partition_description in CREATE TABLE.
index_partition_spec
When the ALTER TABLE statement is executed with the SPLIT PARTITION, MERGE PARTITION or ADD PARTITION clause, a new partition is created. At this time, this clause can be used to specify the tablespace in which to store the index partition that is created along with the table partition.
coalesce_table_partition
This can only be used with hash partitions. It is used to coalesce hash partitions and reorganize the data. When partitions are coalesced, the last partition is chosen, its contents are distributed among the remaining partitions, and it is dropped.
drop_table_partition
This is used to remove a partition. The data in the partition are deleted, together with any local indexes. In order to avoid deleting the data, MERGE the partition with another partition before executing DROP on it.
merge_table_partition
This is used to merge two partitions into one. Use the INTO clause to specify the name of the new partition. The name can be the same as the name of one of the two partitions being merged, or can be a new name not belonging to any existing table partitions.
When merging range partitions, the partitions are merged into the partition having the higher upper limit.
When merging list partitions, the partitions are merged into a partition having the union of the key values of the two partitions.
When a partition is merged with the default partition, the domain of the default partition is increased to encompass the domain of the merged partition, and only the default partition remains.
If any local indexes have been defined for the table, the local indexes of the merged partitions are deleted. If the table contains a LOB column, its attributes can be specified separately.
If no tablespace is specified, the new partition is stored in the default tablespace for the table, even if the original partition having the same name as the name of the newly created partition was stored in another tablespace.
rename_table_partition
This is used to rename a partition.
split_table_partition
This is used to split a partition into two partitions.
The AT clause can only be used with range partitions. It is used to specify a partition key value, on the basis of which a partition is split into two. This value must be larger than the partition key value for the partition immediately preceding it, and smaller than the partition key value for the partition before it was split.
The VALUES clause can only be used with list partitions. It is used to specify a list of values to separate from the list of values for the existing partition. The values specified using the VALUES clause must be present in the list of values for the existing partition; however, not all of the values for the existing partition can be specified in the VALUES list.
The INTO clause is used to specify the names and tablespaces for the two partitions resulting from the SPLIT operation.
If any local indexes have been defined for the table, the local index partition is also split, along with the data partition.
If the table contains a LOB column, the attributes for the LOB column can be set separately.
truncate_table_partition
This is used to delete all of the data in a partition.
partition_access_mode
This switches the data access mode for the partition to Read-Only, Read/Write or Read/Append mode.
add_column_clause
This is used to add a new column to the table.
partition_lob_storage_clause
When a LOB column is added to a partitioned table, this clause is used to set the tablespace in which each of the LOB column partitions is stored.
alter_column_clause
These clauses are used to change the default value for an existing column.
modify_column_clause
This is used to change the data type of an existing column.
SRID can be used an integer within the 4-byte range. If the SRID value is changed, only the value that matches the value entered in the table is selected.
The following table shows which data types can be changed into which data types.
△ means that the data type change might result in the loss of non-NULL data. To acknowledge the possibility of data loss and proceed with the data type change anyway, use the TOLERATE DATA LOSS option. When changing data into date type data, Altibase does so according to the DEFAULT_DATE_FORMAT property.
-
Prerequisites when changing data types#
-
Character data type → Numeric data type
Character data should consist only of numbers and decimal points
Character data must be within the range of numeric data types - Character data type → Character data type
The column size must be greater than or equal to the length of the data type before the change - Numeric data type → Character data type
The column size must be greater than or equal to the length of the data type before the change - Numeric data type → Numeric data type The data must be within the range of the numeric data type users want to change.
- Character data type → Data date type
The data before the change should stored as a date
The data format must match the DEFAULT_DATE_FORMAT property - Date data type → Character data type When converted to the character type, it is changed to DEFAULT_DATE_FORMAT
| After Modification / Before Modifaciton | char | var char | nchar | nvarchar | clob | big int | dou ble | float | int eger | num ber | num eric | real | small int | date | blob | byte | nibble | bit | varbit | geometry |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| char | o | o | o | o | △ | △ | △ | △ | △ | △ | △ | △ | △ | |||||||
| varchar | o | o | o | o | △ | △ | △ | △ | △ | △ | △ | △ | △ | |||||||
| nchar | o | o | o | o | △ | △ | △ | △ | △ | △ | △ | △ | △ | |||||||
| nvarchar | o | o | o | o | △ | △ | △ | △ | △ | △ | △ | △ | △ | |||||||
| clob | ||||||||||||||||||||
| bigint | o | o | o | o | o | △ | △ | o | △ | △ | △ | o | ||||||||
| double | o | o | o | o | △ | o | △ | △ | △ | △ | △ | △ | ||||||||
| float | o | o | o | o | △ | △ | △ | △ | △ | △ | △ | △ | ||||||||
| integer | o | o | o | o | o | △ | △ | o | △ | △ | △ | o | ||||||||
| number | o | o | o | o | △ | △ | △ | △ | △ | △ | △ | △ | ||||||||
| numeric | o | o | o | o | △ | △ | △ | △ | △ | △ | △ | △ | ||||||||
| real | o | o | o | o | △ | △ | △ | △ | △ | △ | o | △ | ||||||||
| smallint | o | o | o | o | o | △ | △ | o | △ | △ | △ | o | ||||||||
| date | △ | △ | △ | △ | o | |||||||||||||||
| blob | ||||||||||||||||||||
| byte | o | |||||||||||||||||||
| nibble | o | |||||||||||||||||||
| bit | o | o | ||||||||||||||||||
| varbit | o | o | o | |||||||||||||||||
| geometry | o |
O: If the data type change condition is satisfied, the data type of the existing column can be changed without specifying the TOLERATE DATA LOSS option
△: The data type of existing column can be changed only when the data type change condition is satisfied and the TOLERATE DATA LOSS option is specified.
drop_column_clause
This is used to delete one or more columns.
rename_column_clause
This is used to rename a column.
reorganize_column_clause
This specifies the column which is to reorganize data of the dictionary table in which data of the column is actually stored.
column_definition
- DEFAULT
When a new column is added, if the DEFAULT option is not specified, the value for each row in the new column will be NULL. However, if the DEFAULT option is specified, when the column is added to a table containing existing rows, it will be populated with the DEFAULT value. - TIMESTAMP
This is used to add a timestamp column.
column_constraint
Specifies constraints for the column.
- NULL/NOT NULL
This specifies whether NULL values are allowed in the column. If NULL values are to be disallowed for a column, then the column can be added using the ALTER TABLE command only if a default value is also specified. In other words, in order to add a new column to a table, the column must either allow NULL values or have a default value specified. - CHECK condition
This specifies an integrity rule that applies to the target column. Only the target column can be referenced within the condition of the column_constraint clause. - USING INDEX TABLESPACE tablespace_name
This specifies the tablespace in which to store the index for the constraint.
Many of the clauses in the ALTER TABLE statement have the same function as those in the CREATE TABLE statement. For more information about those clauses, please also refer to CREATE TABLE.
constraints_clauses
This clause is used to add a constraint to a table, delete an existing constraint, or change the name of an existing constraint.
- add_table_constraints_clause
This clause is used to add a constraint to a table. - rename_table_constraints_clause
This clause is used to change the name of an existing constraint. - drop_table_constraints_clause
This clause is used to delete an existing constraint. - DROP CONSTRAINT
This is used to remove a constraint. - DROP PRIMARY KEY
This is used to remove the primary key from the table. - DROP UNIQUE
This is used to drop a UNIQUE constraint from one or more columns. - DROP LOCALUNIQUE
RENAME TO
This is used to change the name of the table.
MAXROWS
This is used to change the maximum number of records that the table can contain, which was set when the table was created. For more information, please refer to CREATE TABLE.
ENABLE/DISABLE
This clause is used to activate or deactivate all of the indexes in the table specified using tbl_name. The performance of the server can be improved by minimizing the time taken to build indexes, either when the database is started1or while the database is providing service.
For example, when using iLoader to load large amounts of data into a database (or relocate them to a new table), if many indexes exist in the table in which the data are to be saved, it will take a lot of time2 to load the data due to the operations that must be performed on the indexes. In such cases, disabling indexes and then enabling them again after inserting a large amount of records can minimize the time required to build indexes, leading to improved performance.
aging_clause
This is used to physically eliminate previous versions of records that have already been logically deleted from the table. It can be executed by specifying a partition.
compact_clause
This is used to retrieve empty pages in which the queue is located. Even if when executing compression, Altibase does not actually transfer the data. This statement is only used and supported in the memory tables and volatile tables
allocate_extent_clause
This is used to explicitly allocate extents to table segments. The SIZE attribute determines the total size of extents to be allocated to the table segment. If the size specified here is not an exact multiple of the size of one extent, then the number of extents that are allocated is rounded up. If the disk tablespace consists of several data files, the extents are distributed among them equally.
ACCESS access_mode_clause
This switches the data access mode for the table to Read-Only, Read/Write or Read/Append mode.
alter_table_tablespace
This clause can modify the tablespace of a table, and transfer indexes and LOB columns created in the existing tables. At this time, depending on whether the table is partitioned, record movement and column attribute changes can be performed implicitly.
-
If case of non-partitioned table:#
-
Move records in the table.
- When changing a tablespace from a disk tablespace to memory or volatile, VARIABLE-enabled columns are chaged to VARIABLE columns.
-
When changing a table space from disk to disk in a memory or volatile tablespace, all columns are chaged to FIXED
-
In case of partitioned table:#
-
Only the tablespace of the partitioned table are changed. At this time, the tablespace of the partition is not changed, and the records of the partition are not moved.
- To change the table space of a partition, see the alter_partition section.
table_move_index_clause
The tablespace that can store the relevant table index can be specified when modifying tablespace.
However, it can be transferred into the tablespace identical to the table type (memory, volatile, and disk).
table_lob_column_clause
The LOB columns the relevant table can specify the tablespace to store.
However, the tablespace storing the LOB columns can specify different disk tablespace only in the case of transferring to the partitioned tablespace.
TOUCH
This clause intentionally manipulates the optimizer by increasing the SCN(System Commit Number) so that the optimizer can recognize that a table has been modified. Then, the query execution plan including a table is re-created.
Precautions#
- The definition of a table that has been designated as a replication target table cannot be altered. This means that it is impossible to change the data type of a column in a replication target table because changing the data type would mean altering the table definition.
-
If the table has only one partition, COALESCE/DROP TABLE PARTITION cannot be used.
-
The DROP PARTITION and MERGE PARTITION clauses cannot be used with hash-partitioned tables. Instead, the COALESCE PARTITION clause should be used. Moreover, the SPLIT PARTITION clause cannot be used with hash-partitioned tables.
-
For range-partitioned tables, the partitions to merge must be adjacent to each other.
-
If the table contains a primary key or unique key, called the referenced key, that is referenced by any foreign key constraints in other tables, the definition of the table cannot be changed.
-
Columns cannot be added to or removed from a table such that the total number of columns in the table is reduced to 0 (zero) or increased beyond 1024, which is the maximum allowable number of columns in a table. If a table contains one or more columns with the VARIABLE option, the maximum allowable number of columns in the table might be less than 1024, depending on the value specified in IN ROW SIZE clause.
-
A table can have only one primary key defined for it.
-
For a foreign key constraint, the foreign key and the unique or primary key being referred to in another table must have the same number of columns, and respective columns must have the same data type.
-
The data type of a column that is the basis of a foreign key constraint cannot be changed. This is not permitted, either for foreign keys or for unique or primary keys that are referred to by foreign keys in other tables, because data values might be changed when the data type is changed.
-
The maximum number of indexes that can be created in one table is 64. The maximum number of primary keys and unique keys combined that can exist in one table is 64.
-
The record of each partition is not transferred when modifying to the tablespace of partitioned table.
-
Modifying from memory or volatile tablespace to disk tablespace changes all the LOB columns to be FIXED.
Constraints#
- A timestamp constraint cannot be added to or removed from an existing column using the ADD/DROP CONSTRAINT clause.
- When an INSERT or UPDATE statement is executed on a table containing a column with the TIMESTAMP constraint, the system time is inserted into that column by default. Therefore, the DEFAULT value cannot be changed or dropped using the ALTER TABLE SET/DROP DEFAULT statement. For more information, please refer to the description of the CREATE TABLE statement.
- When the SRID of a GEOMETRY column is changed, values that match the values entered in the table can only be selected. For example, if a value with a SRID of 100, 101, or 102 is entered in a table, it cannot be changed to any value.
Example#
Adding and Dropping Columns#
\<query> Add the column shown below to the table book
isbn: CHAR(10) PRIMARY KEY
edition: INTEGER DEFAULT 1
iSQL> ALTER TABLE books
ADD COLUMN (isbn CHAR(10) PRIMARY KEY,
edition INTEGER DEFAULT 1);
Alter success.
Or
iSQL> ALTER TABLE books
ADD COLUMN (isbn CHAR(10) CONSTRAINT const1
PRIMARY KEY, edition INTEGER DEFAULT 1);
Alter success.
\<Query> Drop the isbn column from the table books.
iSQL> ALTER TABLE books
DROP COLUMN isbn;
Alter success.
\<Query> Add a TIMESTAMP colum to the table books
iSQL> ALTER TABLE books
ADD COLUMN (due_date TIMESTAMP);
Alter success.
\<Query> Drop the isbn and due_date columns from the table books, due_date.
iSQL> ALTER TABLE books
DROP COLUMN (isbn, due_date);
Alter success.
Adding and Dropping Constraints for Existing Columns#
\<Query> Add the UNIQUE constraint to the existing bno (book number) column in the table books.
iSQL> ALTER TABLE books
ADD UNIQUE(bno);
Alter success.
Or
iSQL> ALTER TABLE books
ADD CONSTRAINT const1 UNIQUE(bno);
Alter success
\<Query> Change the name of the constraint const1 in the table books.
iSQL> ALTER TABLE books
RENAME CONSTRAINT const1 TO const_unique;
Alter success
\<Query> Drop the UNIQUE constraint from the bno column in the table books.
iSQL> ALTER TABLE books
DROP UNIQUE(bno);
Alter success.
Or
iSQL> ALTER TABLE books
DROP CONSTRAINT const_unique;
Alter success
\<Query> While adding a column to the table inventory, place the FOREIGN KEY constraint fk_isbn, which refers to the isbn column in the table books, on the new column.
isbn: CHAR(10)
iSQL> ALTER TABLE inventory
ADD COLUMN(isbn CHAR(10) CONSTRAINT fk_isbn REFERENCES books(isbn));
Alter success.
\<Query> Drop the constraint fk_isbn from the table inventory.
iSQL> ALTER TABLE inventory
DROP CONSTRAINT fk_isbn;
Alter success.
\<Query> Drop the primary key constraint from the table books.
iSQL> ALTER TABLE books
DROP PRIMARY KEY;
Alter success.
\<Query> Add a primary key constraint to the existing bno (book number) column in the table books, and ensure that the index can be used even if a system or media fault occurs (LOGGING).
iSQL> ALTER TABLE books
ADD PRIMARY KEY (bno) USING INDEX PARALLEL 4;
Alter success.
Or
iSQL> ALTER TABLE books
ADD PRIMARY KEY (bno) USING INDEX LOGGING
PARALLEL 4;
Alter success.
\<Query> Add a primary key constraint to the existing bno (book number) column in the table books. Create an index using the NOLOGGING option. Use the FORCE option so that the index is available even if the server dies.
iSQL> ALTER TABLE books
ADD PRIMARY KEY (bno) USING INDEX NOLOGGING PARALLEL 4;
Alter success.
Or
iSQL> ALTER TABLE books
ADD PRIMARY KEY (bno) USING INDEX NOLOGGING FORCE PARALLEL 4;
Alter success.
\<Query> Add a primary key constraint to the existing bno (book number) column in the table books. Create an index using the NOLOGGING option. Use the NOFORCE option to prevent the index from being written to disk.
iSQL> ALTER TABLE books
ADD PRIMARY KEY (bno) USING INDEX NOLOGGING NOFORCE PARALLEL 4;
Alter success.
\<Query> Add the CHECK constraint that values for column bno(book number) must be between 1 and 1000000 for the table books.
iSQL> ALTER TABLE books ADD CHECK ( bno >= 1 AND bno <= 1000000 );
Alter success.
Specifying Tablespaces for Individual Index Partitions#
\<Query> Add the i2 column, having the LOCALUNIQUE constraints, to the partitioned table T1.
iSQL> ALTER TABLE T1 ADD COLUMN
(I2 INTEGER LOCALUNIQUE USING INDEX LOCAL
(
PARTITION P1_LOCALUNIQUE ON P1 TABLESPACE TBS3,
PARTITION P2_LOCALUNIQUE ON P2 TABLESPACE TBS2,
PARTITION P3_LOCALUNIQUE ON P3 TABLESPACE TBS1
)
);
\<Query> Add column i7 with the LOCALUNIQUE constraint to the partitioned table t3; however, specify the tablespace where each partition of the partitioned index to be created is to be stored.
iSQL> ALTER TABLE t3
ADD COLUMN ( i7 INTEGER LOCALUNIQUE USING INDEX LOCAL
(
PARTITION p1_localunique ON p1 TABLESPACE PMT_TBS,
PARTITION p2_localunique ON p2 TABLESPACE PMT_TBS,
PARTITION p3_localunique ON p3 TABLESPACE PMT_TBS2,
PARTITION p4_localunique ON p4 TABLESPACE PMT_TBS3,
PARTITION pd_localunique ON pd TABLESPACE PMT_TBS4
)
);
Alter success.
Renaming Columns#
This is used to change the name of one of the columns in a table. The new column name must not be the same as the name of any of the other columns in the table. When a column is renamed, the new column inherits all of the indexes and constraints that were originally defined for the column.
After renaming a column, any stored procedures that reference the column by its previous name become invalid. In order to be able to use such a stored procedure again, it will be necessary to rewrite the name of the column in the stored procedure.
\<Query> Change the name of a column in the table departments from dno to dcode.
iSQL> ALTER TABLE departments
RENAME COLUMN dno TO dcode;
Alter success.
Setting/Dropping Default Values#
\<Query> Set the default value for the gender (SEX column) in the table employees to "M".
iSQL> ALTER TABLE employees
ALTER (sex SET DEFAULT 'M');
Alter success.
\<Query> Change the gender (SEX) column in the table employees so that it has no default value.
iSQL> ALTER TABLE employees
ALTER (sex DROP DEFAULT);
Alter success.
Change Column Data Type#
\<Query> Set the data type of the isbn column in the books table to CHAR(20) and that of the edition column to BIGINT.
iSQL> ALTER TABLE books MODIFY COLUMN (isbn CHAR(20), edition BIGINT);
Alter success.
\<Query> Change the data type of the isbn column in the table books from CHAR(20) to BIGINT and the edition column to FLOAT.
iSQL> ALTER TABLE books MODIFY COLUMN (isbn BIGINT TOLERATE DATA LOSS, edition FLOAT TOLERATE DATA LOSS);
Alter success.
\<Query> Change the data type of column i1 in the t1 table from CHAR(20) to DATE type.
iSQL> CREATE TABLE t1 (i1 CHAR(20));
insert into t1 values (sysdate);
Create success.
ALTER TABLE t1 MODIFY COLUMN (i1 DATE TOLERATE DATA LOSS);
Alter success.
\<Query> Change the data type of column c4 in table t from CHAR (14) to DATE type.
iSQL> ALTER TABLE t ADD COLUMN (c4 CHAR(14));
Alter success.
iSQL> INSERT INTO t(c4) VALUES('20161123112119');
1 row inserted.
iSQL>ALTER SESSION SET DEFAULT_DATE_FORMAT = 'YYYYMMDDHHMISS';
Alter success.
iSQL> ALTER TABLE t MODIFY COLUMN (c4 DATE TOLERATE DATA LOSS);
Alter success.
Changing Table Names#
\<Query> Change the name of the table from books to ebooks.
iSQL> RENAME books TO ebooks;
Rename success.
Or
iSQL> ALTER TABLE books
RENAME TO ebooks;
Alter success.
Changing the Maximum Number of Rows for Tables#
\<Query> Set the maximum number of records for the table departments to 6000000.
iSQL> ALTER TABLE departments MAXROWS 6000000;
Alter success.
Activating and Deactivating Indexes#
\<Query> Disable all indexes in the table orders.
iSQL> ALTER TABLE orders ALL INDEX DISABLE;
Alter success.
Creating Partitioned Tables#
\<Query> Create range-, list-, and hash-partitioned tables.
CREATE TABLE T1
(
I1 INTEGER,
I2 INTEGER
)
PARTITION BY RANGE(I1)
(
PARTITION P1 VALUES LESS THAN (100),
PARTITION P2 VALUES LESS THAN (200),
PARTITION P3 VALUES DEFAULT
) TABLESPACE SYS_TBS_DISK_DATA;
CREATE TABLE T2
(
I1 INTEGER,
I2 INTEGER
)
PARTITION BY LIST (I1)
(
PARTITION P1 VALUES (1,2,3,4),
PARTITION P2 VALUES (5,6,7,8),
PARTITION P3 VALUES DEFAULT
) TABLESPACE SYS_TBS_DISK_DATA;
CREATE TABLE T3
(
I1 INTEGER
)
PARTITION BY HASH (I1)
(
PARTITION P1,
PARTITION P2
) TABLESPACE SYS_TBS_DISK_DATA;
CREATE TABLE T4
(
I1 INTEGER,
I2 INTEGER
)
PARTITION BY RANGE(I1)
(
PARTITION P1 VALUES LESS THAN (100),
PARTITION P2 VALUES DEFAULT
);
CREATE TABLE T5
(
I1 INTEGER,
I2 INTEGER
)
PARTITION BY LIST (I1)
(
PARTITION P1 VALUES DEFAULT
);
CREATE TABLE T6
(
I1 INTEGER
)
PARTITION BY HASH (I1)
(
PARTITION P1
);
Adding Partitions#
\<Query> Add a new partition to a hash-partitioned table
ALTER TABLE T3 ADD PARTITION P3;
Coalescing Partitions#
\<Query> Coalesce the partitions in the hash-partitioned table T3 so that only two hash partitions remain.
ALTER TABLE T3 COALESCE PARTITION;
Dropping Partitions#
\<Query> Delete partition P2 from table T1
ALTER TABLE T1 DROP PARTITION P2;
Merging Partitions#
\<Query> Merge the remaining partitions P1 and P3 in table T1 to form a new partition named P_1_3.
ALTER TABLE T1 MERGE PARTITIONS P1, P3 INTO PARTITION P_1_3;
Renaming Partitions#
\<Query> Change the name of a partition from P1 to P1_LIST.
ALTER TABLE T2 RENAME PARTITION P1 TO P1_LIST;
Splitting Partitions#
\<Query> Split the default partition P3 in the range-partitioned table T1 on the basis of the value 350. This will create a partition named P_200_350, which holds values from 200 - 350, and change the name of the default partition to P_OVER_350.
ALTER TABLE T1 SPLIT PARTITION P3
AT ( 350 ) INTO ( PARTITION P_200_350, PARTITION P_OVER_350 );
\<Query> Use a VALUES clause instead of an AT clause to split a list-partitioned table.
ALTER TABLE T2
SPLIT PARTITION P1_LIST VALUES ( 2, 4 )
INTO
(
PARTITION P_2_4 TABLESPACE TBS1,
PARTITION P_1_3 TABLESPACE TBS2
);
Truncating Partitions#
\<Query> Delete all data in partition P5
ALTER TABLE T1 TRUNCATE PARTITION P5;
Creating Partitioned Table Indexes#
CREATE INDEX T4_IDX ON T4 ( I1 )
LOCAL
(
PARTITION T4_P1_IDX ON P1,
PARTITION T4_P2_IDX ON P2
);
CREATE INDEX T5_IDX ON T5 ( I1 )
LOCAL
(
PARTITION T5_P1_IDX ON P1
);
CREATE INDEX T6_IDX ON T6 ( I1 )
LOCAL
(
PARTITION T6_P1_IDX ON P1
);
Specifying Index Partition Names#
\<Query> When adding a new partition to the has partitioned table, specify the indexes partition name.
ALTER TABLE T6 ADD PARTITION P2 INDEX ( T6_IDX PARTITION T6_P2_IDX );
\<Query> Merge the partitions P1 and P2 remaining in table T4 into P1 and specify the index partition name.
ALTER TABLE T4 MERGE PARTITIONS P1, P2 INTO PARTITION P1 INDEX ( T4_IDX
PARTITION T4_P1_IDX );
\<Query> In the range partitioned table T4, the primary partition P1 is separated based on 100. Since P1 is in-place detached, an index partition name cannot be specified.
ALTER TABLE T4 SPLIT PARTITION P1 AT ( 100 ) INTO
(
PARTITION P1,
PARTITION P2 INDEX ( T4_IDX PARTITION T4_P2_IDX )
);
\<Query> In the case of list-partitioned tables, use VALUES instead of AT to separate them. Since P1 is in-place detached, an index partition name cannot be specified.
ALTER TABLE T5 SPLIT PARTITION P1 VALUES ( 2, 4 ) INTO
(
PARTITION P1,
PARTITION P2 INDEX ( T5_IDX PARTITION T5_P2_IDX )
);
Using row_movement_clause#
<Query> Table T1 must be a partitioned table. If it is a non-partitioned table, an error will occur. ALTER TABLE T1 ENABLE ROW MOVEMENT;
ALTER TABLE T1 ENABLE ROW MOVEMENT;
Allocating Extents to Tables#
<Query> Table T1 must be a partitioned table. If it is a non-partitioned table, an error will occur. ALTER TABLE T1 ENABLE ROW MOVEMENT;
iSQL> ALTER TABLE LOCAL_TBL ALLOCATE EXTENT ( SIZE 10M );
Alter success.
Modifying of Tablespace of a Table#
<Query> Create non-partitioned table and change it to a different tablespace of a different storage.
iSQL> CREATE MEMORY TABLESPACE mem_tbs_0 SIZE 32M AUTOEXTEND ON;
Create success.
iSQL> CREATE VOLATILE TABLESPACE vol_tbs_0 SIZE 32M AUTOEXTEND ON;
Create success.
iSQL> CREATE TABLE sales_table
(
sales_date DATE,
sales_id NUMBER,
sales_city VARCHAR(20)
)
TABLESPACE SYS_TBS_DISK_DATA UNCOMPRESSED LOGGING;
Create success.
iSQL> -- Disk -> Memory
iSQL> ALTER TABLE sales_table ALTER TABLESPACE SYS_TBS_MEM_DATA;
Alter success.
iSQL> -- Memory -> Memory
iSQL> ALTER TABLE sales_table ALTER TABLESPACE mem_tbs_0;
Alter success.
iSQL> -- Memory -> Volatile
iSQL> ALTER TABLE sales_table ALTER TABLESPACE vol_tbs_0;
Alter success.
iSQL> -- Volatile -> Disk
iSQL> ALTER TABLE sales_table ALTER TABLESPACE SYS_TBS_DISK_DATA;
Alter success.
iSQL> -- Disk -> Volatile
iSQL> ALTER TABLE sales_table ALTER TABLESPACE vol_tbs_0;
Alter success.
iSQL> -- Volatile -> Memory
iSQL> ALTER TABLE sales_table ALTER TABLESPACE mem_tbs_0;
Alter success.
iSQL> -- Memory -> Disk
iSQL> ALTER TABLE sales_table ALTER TABLESPACE SYS_TBS_DISK_DATA;
Alter success.
\<Query> Create a partitioned table and change it to a different tablespace storage.
iSQL> CREATE TABLE part_table
(
sales_date DATE,
sales_id NUMBER,
sales_city VARCHAR(20)
)
PARTITION BY LIST(sales_city)
(
PARTITION part_1 VALUES ( 'SEOUL' , 'INCHEON' ),
PARTITION part_2 VALUES ( 'PUSAN' , 'JUNJU' ),
PARTITION part_3 VALUES ( 'CHUNGJU' , 'DAEJUN' ),
PARTITION part_def VALUES DEFAULT
)
TABLESPACE SYS_TBS_DISK_DATA UNCOMPRESSED LOGGING;
Create success.
iSQL> -- Disk -> Memory
iSQL> ALTER TABLE part_table ALTER TABLESPACE SYS_TBS_MEM_DATA;
Alter success.
iSQL> -- Memory -> Memory
iSQL> ALTER TABLE part_table ALTER TABLESPACE mem_tbs_0;
Alter success.
iSQL> -- Memory -> Volatile
iSQL> ALTER TABLE part_table ALTER TABLESPACE vol_tbs_0;
Alter success.
iSQL> -- Volatile -> Disk
iSQL> ALTER TABLE part_table ALTER TABLESPACE SYS_TBS_DISK_DATA;
Alter success.
iSQL> -- Disk -> Volatile
iSQL> ALTER TABLE part_table ALTER TABLESPACE vol_tbs_0;
Alter success.
iSQL> -- Volatile -> Memory
iSQL> ALTER TABLE part_table ALTER TABLESPACE mem_tbs_0;
Alter success.
iSQL> -- Memory -> Disk
iSQL> ALTER TABLE part_table ALTER TABLESPACE SYS_TBS_DISK_DATA;
Alter success.
<Query> Change the partitioned table to another tablespace, and chance the tablespace to store the indexes of the partitioned table.
iSQL> CREATE DISK TABLESPACE disk_tbs_0 DATAFILE '/tmp/tbs.user.0';
Create success.
iSQL> CREATE DISK TABLESPACE disk_tbs_1 DATAFILE '/tmp/tbs.user.1';
Create success.
iSQL> CREATE MEMORY TABLESPACE mem_tbs_0 SIZE 32M AUTOEXTEND ON;
Create success.
iSQL> CREATE MEMORY TABLESPACE mem_tbs_1 SIZE 32M AUTOEXTEND ON;
Create success.
iSQL> CREATE VOLATILE TABLESPACE vol_tbs_0 SIZE 32M AUTOEXTEND ON;
Create success.
iSQL> CREATE VOLATILE TABLESPACE vol_tbs_1 SIZE 32M AUTOEXTEND ON;
Create success.
iSQL> CREATE TABLE text_table
(
id NUMBER,
date DATE,
text VARCHAR(500)
)
PARTITION BY RANGE ( id )
(
PARTITION part_1 VALUES LESS THAN ( 100 ),
PARTITION part_2 VALUES LESS THAN ( 200 ),
PARTITION part_def VALUES DEFAULT
)
TABLESPACE disk_tbs_0 UNCOMPRESSED LOGGING;
Create success.
iSQL> CREATE INDEX text_table_idx on text_table ( date ) LOCAL;
Create success.
iSQL> -- Disk -> Memory
iSQL> ALTER TABLE text_table ALTER TABLESPACE mem_tbs_0 INDEX ( text_table_idx TABLESPACE mem_tbs_1 );
Alter success.
iSQL> -- Memory -> Volatile
iSQL> ALTER TABLE text_table ALTER TABLESPACE vol_tbs_0 INDEX ( text_table_idx TABLESPACE vol_tbs_1 );
Alter success.
iSQL> -- Volatile -> Disk
iSQL> ALTER TABLE text_table ALTER TABLESPACE disk_tbs_0 INDEX ( text_table_idx TABLESPACE disk_tbs_1 );
Alter success.
iSQL> -- Disk -> Volatile
iSQL> ALTER TABLE text_table ALTER TABLESPACE vol_tbs_1 INDEX ( text_table_idx TABLESPACE vol_tbs_0 );
Alter success.
iSQL> -- Volatile -> Memory
iSQL> ALTER TABLE text_table ALTER TABLESPACE mem_tbs_1 INDEX ( text_table_idx TABLESPACE mem_tbs_0 );
Alter success.
iSQL> -- Memory -> Disk
iSQL> ALTER TABLE text_table ALTER TABLESPACE disk_tbs_1 INDEX ( text_table_idx TABLESPACE disk_tbs_0 );
Alter success.
\<Query> Change memory partitioned tables to disk tablespaces, and Lob columns to disk tablespaces.
iSQL> CREATE TABLE clob_table
(
id NUMBER,
date DATE,
text CLOB
)
PARTITION BY RANGE ( id )
(
PARTITION part_1 VALUES LESS THAN ( 100 ),
PARTITION part_2 VALUES LESS THAN ( 200 ),
PARTITION part_def VALUES DEFAULT
)
TABLESPACE mem_tbs_0 UNCOMPRESSED LOGGING;
Create success.
iSQL> -- Memory -> Disk
iSQL> ALTER TABLE clob_table ALTER TABLESPACE disk_tbs_0 LOB ( text TABLESPACE disk_tbs_1 );
Alter success.
\<Query> Create a partitioned table and move the partition to a tablespace, which is another storage medium.
iSQL> CREATE TABLE data_table
(
id NUMBER,
date DATE,
data VARCHAR(500)
)
PARTITION BY RANGE ( id )
(
PARTITION part_1 VALUES LESS THAN ( 100 ),
PARTITION part_2 VALUES LESS THAN ( 200 ),
PARTITION part_def VALUES DEFAULT
)
TABLESPACE disk_tbs_0 UNCOMPRESSED LOGGING;
Create success.
iSQL> -- Disk -> Memory
iSQL> ALTER TABLE data_table ALTER PARTITION part_1 TABLESPACE mem_tbs_0;
Alter success.
iSQL> -- Memory -> Volatile
iSQL> ALTER TABLE data_table ALTER PARTITION part_1 TABLESPACE vol_tbs_0;
Alter success.
iSQL> -- Volatile -> Disk
iSQL> ALTER TABLE data_table ALTER PARTITION part_1 TABLESPACE disk_tbs_0;
Alter success.
iSQL> -- Disk -> Volatile
iSQL> ALTER TABLE data_table ALTER PARTITION part_1 TABLESPACE vol_tbs_1;
Alter success.
iSQL> -- Volatile -> Memory
iSQL> ALTER TABLE data_table ALTER PARTITION part_1 TABLESPACE mem_tbs_1;
Alter success.
iSQL> -- Memory -> Disk
iSQL> ALTER TABLE data_table ALTER PARTITION part_1 TABLESPACE disk_tbs_1;
Alter success.
\<Query> Create a partitioned table and move the partitions to a tablespace on another storage medium, and move the index to another tablespace.
iSQL> CREATE TABLE data_table
(
id NUMBER,
date DATE,
data VARCHAR(500)
)
PARTITION BY RANGE ( id )
(
PARTITION part_1 VALUES LESS THAN ( 100 ),
PARTITION part_2 VALUES LESS THAN ( 200 ),
PARTITION part_def VALUES DEFAULT
)
TABLESPACE disk_tbs_0 UNCOMPRESSED LOGGING;
Create success.
iSQL> CREATE INDEX data_table_idx on data_table ( date ) LOCAL;
Create success.
iSQL> -- Disk -> Memory
iSQL> ALTER TABLE data_table ALTER PARTITION part_1 TABLESPACE mem_tbs_0 INDEX ( data_table_idx TABLESPACE mem_tbs_1 );
Alter success.
iSQL> -- Memory -> Volatile
iSQL> ALTER TABLE data_table ALTER PARTITION part_1 TABLESPACE vol_tbs_0 INDEX ( data_table_idx TABLESPACE vol_tbs_1 );
Alter success.
iSQL> -- Volatile -> Disk
iSQL> ALTER TABLE data_table ALTER PARTITION part_1 TABLESPACE disk_tbs_0 INDEX ( data_table_idx TABLESPACE disk_tbs_1 );
Alter success.
iSQL> -- Disk -> Volatile
iSQL> ALTER TABLE data_table ALTER PARTITION part_1 TABLESPACE vol_tbs_1 INDEX ( data_table_idx TABLESPACE vol_tbs_0 );
Alter success.
iSQL> -- Volatile -> Memory
iSQL> ALTER TABLE data_table ALTER PARTITION part_1 TABLESPACE mem_tbs_1 INDEX ( data_table_idx TABLESPACE mem_tbs_0 );
Alter success.
iSQL> -- Memory -> Disk
iSQL> ALTER TABLE data_table ALTER PARTITION part_1 TABLESPACE disk_tbs_1 INDEX ( data_table_idx TABLESPACE disk_tbs_0 );
Alter success.
\<Query> Create a memory partitioned table, and disk memory partition part_1 to the tablespace, and change the Lob column to another disk tablespace disk_tbs_1.
iSQL> CREATE TABLE blob_table
(
id NUMBER,
date DATE,
data blob
)
PARTITION BY RANGE ( id )
(
PARTITION part_1 VALUES LESS THAN ( 100 ),
PARTITION part_2 VALUES LESS THAN ( 200 ),
PARTITION part_def VALUES DEFAULT
)
TABLESPACE mem_tbs_0 UNCOMPRESSED LOGGING;
Create success.
iSQL> -- Memory -> Disk
iSQL> ALTER TABLE blob_table ALTER PARTITION part_1 TABLESPACE disk_tbs_0 LOB ( data TABLESPACE disk_tbs_1 );
Alter success.
ALTER TABLESPACE#
Syntax#
alter_tablespace ::=
datafile_tempfile_clause ::=
datafile_spec ::=
autoextend_clause ::=
maxsize_clause ::=
modify_datafile_clause ::=
modify_autoextend_clause ::=
modify_checkpoint_path_clause ::=
status_clause ::=
backup_clause ::=
Prerequisites#
The SYS user and users to whom the ALTER TABLESPACE system privilege has been granted can use all of the functionality of the ALTER TABLESPACE statement.
Description#
The ALTER TABLESPACE statement is used to change the definition of a disk, temporary, memory or volatile tablespace. This statement is also used to change other tablespace attributes, including the data and temporary files associated with the tablespace, the checkpoint paths, settings related to automatic extension, and the status of the tablespace.
tablespace_name
This is used to specify the name of the tablespace to change.
datafile_tempfile_clause
This is used to add, delete or change a data file or temporary file.
For more detailed information on the datafile_spec, maxsize_clause and autoextend_clause clauses, please refer to the description of the CREATE TABLESPACE statement.
ADD DATAFILE | TEMPFILE
This is used to add data or temporary files to the corresponding tablespace.
RENAME DATAFILE | TEMPFILE
This is used to change the name of the data or temporary files in a tablespace. More than one file can be renamed at one time. A file having the same name as TO file_name must have already been created.
modify_datafile_clause
This is used to change the size or the autoextend attribute of a data or temporary file in a disk tablespace.
modify_autoextend_clause
This is used to change the autoextension-related attributes for a memory or volatile tablespace, including whether it is automatically extended in size, the unit (increment) by which it is extended, and its maximum size.
DROP DATAFILE | TEMPFILE
This is used to remove data or temporary files from a tablespace. More than one file can be removed at one time. Because this statement does not actually delete the physical files from the file system, they will need to be deleted (or otherwise managed) manually by the user.
modify_checkpoint_path_clause
This is used to add, change or delete a checkpoint image path. Operations related to checkpoint image paths can only be performed during the CONTROL phase
ADD CHECKPOINT PATH Clause
This is used to add a new checkpoint path to a memory tablespace. The DBA can move any checkpoint image files from the existing checkpoint paths to the new checkpoint path as desired. Because Altibase looks for checkpoint image files in all checkpoint paths when a memory tablespace is loaded, checkpoint image files can be stored in any of the checkpoint paths for the tablespace
When checkpointing takes place after a new checkpoint path has been added, the new checkpoint image files are distributed evenly among all of the checkpoint paths, including the new checkpoint path.
If the specified checkpoint path does not exist, or if the user who started up the Altibase server does not have write permissions for the checkpoint path, an error will be raised. Therefore, the DBA must manually create the checkpoint path to be added in the file system and add write permissions to the path for the user.
RENAME CHECKPOINT PATH clause
This is used to change an existing checkpoint path for a memory tablespace to the path specified in the clause following “TO“. The DBA must manually create or rename the actual checkpoint path in the file system. If the specified checkpoint path does not exist, or if the user who started up the Altibase server does not have suitable permissions for the checkpoint path, an error will be raised.
DROP CHECKPOINT PATH clause
This is used to delete an existing checkpoint path from a memory tablespace. It is the DBA's responsibility to physically move the checkpoint image files in the directory corresponding to the deleted checkpoint path to the remaining checkpoint paths in the tablespace. Because Altibase looks for checkpoint image files in all checkpoint paths when a memory tablespace is loaded, checkpoint image files can be stored in any of the valid checkpoint paths for the tablespace.
Deleting the actual checkpoint path on the file system must be done by the DBA.
The physical path corresponding to the dropped checkpoint path must be manually deleted from the file system. A memory tablespace must have at least one checkpoint path. If an attempt is made to delete the only checkpoint path remaining for a memory space, an error will be raised.
status_clause
This is used to change the status of a disk or memory tablespace to ONLINE, OFFLINE or DISCARD.
OFFLINE
When a disk tablespace is taken offline, the contents of all data pages in the buffer corresponding the tablespace are written to data files, and the pages in the buffer pool are invalidated.
In the case of a memory tablespace, the contents of the data pages are written to checkpoint image files, and the page memory is released.
All memory that has been allocated to indexes in the tablespace is released, and the indexes for the tables in the tablespace become unavailable. In addition, the tables in the tablespace become unavailable until the tablespace is brought back online.
ONLINE
When a disk tablespace is brought online, all of its data files become accessible, and the tables in the tablespace become available again.
When a memory tablespace is brought online, memory is assigned to all data pages again, and the contents of checkpoint image files are loaded back into the memory pages.
If a referenced tablespace is OFFLINE, an attempt to bring the tablespace ONLINE will succeed, but it may be impossible to access the tables that are stored in the tablespace.
A so-called “referenced tablespace“ is, in the case of a disk tablespace, a tablespace containing a table that is associated with indexes, BLOB/CLOB columns, etc. that are stored in one or more other tablespaces, or containing a partitioned table having some of its partitions stored in one or more other tablespaces.
DISCARD
This is used during the CONTROL phase to switch the status of a disk or memory tablespace to DISCARD.
The tables, indexes and BLOB/CLOB columns in a discarded tablespace are unusable. Furthermore, discarded tablespaces are ignored when RESTART RECOVERY is executed and when unused data are removed from the database while the database is being started up.
Once a tablespace has been discarded, the only command that can be executed on it is DROP TABLESPACE. It cannot be brought back ONLINE.
backup clause
This statement is used to indicate that online backup (“hot backup”), in which the data files for a disk or memory tablespace are copied, is to be performed, or is complete.
BEGIN BACKUP
This is used to indicate that online backup is to be performed on all of the data files that constitute a tablespace. During the tablespace backup operation, which takes place between this statement and the END BACKUP statement, transactions are not prevented from accessing the tablespace.
Users must execute BEGIN BACKUP before performing online backup. In addition, it is possible to indicate that online backup is to be performed for multiple tablespaces at the same time. However, disk temporary tablespaces cannot be backed up online.
END BACKUP
This is used to indicate that online backup of a disk or memory tablespace is complete. The user must execute the END BACKUP statement immediately after online backup is completed.
Consideration#
- The ALTER TABLESPACE statement can be used to add data files and change tablespace attributes only when in online mode, and can be used to rename data files only during the CONTROL phase (STARTUP CONTROL).
- status_clause cannot be used with temporary or volatile tablespaces.
Examples#
<Query> Add the data file tbs2.user, which is 64 MB in size, to the user_disk_tbs tablespace. When more space is needed, automatically increase the size of the file in 512 kB increments.
iSQL> ALTER TABLESPACE user_disk_tbs
ADD DATAFILE '/tmp/tbs2.user' SIZE 64M
AUTOEXTEND ON NEXT 512K;
Alter success.
<Query 2> To distribute disk I/O when checkpointing, add the '/home/path' checkpoint path to the user_memory_tbs tablespace. Additionally, change the tablespace attributes such that it increases in size in 256 MB increments and cannot grow larger than 1 GB. (Note that although table attributes can be changed during the SERVICE phase, checkpoint paths can be added only during the CONTROL phase.)
iSQL(sysdba)>>startup control;
iSQL(sysdba)>>ALTER TABLESPACE user_memory_tbs ADD CHECKPOINT PATH '/home/path';
Alter success.
iSQL> ALTER TABLESPACE user_memory_tbs ALTER AUTOEXTEND ON NEXT 256M MAXSIZE 1G;
Alter success.
\<Query 3> Change the attributes of the user_volatile_tbs tablespace such that it increases in size in 256 MB increments and cannot grow larger than 1 GB.
iSQL> ALTER TABLESPACE user_volatile_tbs ALTER AUTOEXTEND ON NEXT 256M MAXSIZE
1G;
Alter success.
ALTER TRIGGER#
Syntax#
alter_trigger ::=
Prerequisites#
Only the SYS user, the owner of the schema containing the trigger, and users having the ALTER ANY TRIGGER system privilege can execute the ALTER TRIGGER statement.
Description#
This statement is used to enable, disable, or compile a specified database trigger.
user_name
This is used to specify the name of the owner of the trigger to be altered. If it is omitted, Altibase will assume that the trigger to be altered belongs to the schema of the user connected via the current session.
trigger_name
This is used to specify the name of the trigger to alter.
ENABLE
This is used to enable the trigger.
DISABLE
This is used to disable the trigger.
COMPILE
This is used to explicitly compile the trigger, regardless of whether or not it is valid. Explicit recompiling helps reduce the load on the system incurred by automatically compiling invalid triggers when they need to fire.
Example#
\<Query> This is used to disable a trigger so that it does not fire automatically. More examples involving the del_trigger trigger can be found in the explanation of the CREATE TRIGGER statement.
iSQL> ALTER TRIGGER del_trigger DISABLE;
Alter success.
ALTER USER#
Syntax#
alter_user ::=
Prerequisites#
Only the SYS user and users having the ALTER USER system privilege can execute the ALTER USER statement. However, individual users can change their own passwords without this privilege.
Description#
This statement is used to change a user's password and the tablespaces associated with the user.
IDENTIFIED clause
This is used to specify a new password for the user. The new password is specified after the BY element.
Because the other commands are the same as the corresponding commands executed using the CREATE USER statement, please refer to the description of the CREATE USER statement.
LIMIT clause
This modifies password management policies for accounts. This clause can only be executed by the SYS user. When a password management policy is changed, an existing policy that is not specified is initialized.
ACCOUNT LOCK/UNLOCK
This explicitly locks or unlocks accounts.
ENABLE/ DISABLE
The user can restrict TCP connections. Only the SYS user can execute this clause.
Precautions#
- When changing the password for the SYS user, who can log on in SYSDBA mode, after the password is changed using the ALTER USER statement, it must be changed one more time by running the altipasswd utility in a console window of the operating system (Unix shell or DOS terminal window). For more information on the altipasswd utility, please refer to the Utilities Manual.
- After the password is changed using the ALTER USER statement, it must also be changed in the shell scripts files containing the password such as: server, is, and il to ensure that the script files will run without error.
Examples#
\<Query> Change the password of the user Tom to ab1rose.
iSQL> ALTER USER tom
IDENTIFIED BY ab1rose;
Alter success.
\<Query> Change a user's default tablespace.
iSQL> ALTER USER tom
DEFAULT TABLESPACE uare_data;
Alter success.
\<Query> This statement modifies password management policies for user rose2.
iSQL> ALTER USER rose2 LIMIT (FAILED_LOGIN_ATTEMPTS 7, PASSWORD_LOCK_TIME 7);
\<Query> Modify the managing policy of user rose2's password. Do not put a limit on the number of times for logging in, and maintain the account locked for 10 days.
iSQL> ALTER USER rose2 LIMIT (FAILED_LOGIN_ATTEMPTS UNLIMITED,
PASSWORD_LOCK_TIME 10);
\<Query> Login fails for user rose2 after locking the account.
ALTER USER rose2 ACCOUNT LOCK;
iSQL> CONNECT rose2/rose2;
[ERR-31370 : The account is locked.]
ALTER VIEW#
Syntax#
alter_view ::=
Prerequisites#
Only the SYS user, the owner of the schema containing the view, and users having the ALTER ANY TABLE system privilege can execute the ALTER VIEW statement.
Description#
This is used to explicitly recompile a view that has become invalid. As an example, when one of the tables on which the view is based has been changed using the ALTER TABLE statement, it will be necessary to explicitly recompile the view in order to use it.
user_name
This is used to specify the name of the owner of the view to recompile. If omitted, Altibase will assume that the view belongs to the schema of the user connected via the current session.
view_name
This is used to specify the name of the view to recompile. The name of the view must follow the "Rules of Object Name" in Chapter 1.
When recompiling the view, Altibase reads the view creation statement and compiles the view again, so any errors that arose when the view was originally created will be raised again when the ALTER VIEW statement is executed. If the FORCE option was used when creating the view originally, the view may still be in an invalid state after the ALTER VIEW statement has executed successfully.
The ALTER VIEW statement cannot be used to change the definition of an existing view. To change a view's definition, use the CREATE VIEW OR REPLACE VIEW statement.
Example#
\<Query> After changing the definition of the table employees, on which the view avg_sal is based, recompile the view. (The definition of avg_sal can be found in the explanation of the CREATE VIEW statement.)
iSQL> ALTER TABLE employees
ADD COLUMN (email VARCHAR(20));
Alter success.
iSQL> ALTER VIEW avg_sal COMPILE;
Alter success.
iSQL> SELECT * FROM avg_sal;
DNO EMP_AVG_SAL
---------------------
..
6 rows selected.
ALTER MATERIALIZED VIEW#
Syntax#
alter_materialized_view ::=
Prerequisites#
Only the following users can alter the properties of the materialized view with this statement.
- The SYS user
- The ownder of the materialized view
- The user with the system privilege of ALTER ANY MATERIALIZED VIEW
Description#
By using the ALTER MATERIALIZED VIEW statement, the method and time for refreshing for the existing materialized view can be altered. For more detailed information on each keyword, refer to the description on the CREATE MATERIALIZED VIEW statement.
Examples#
\<Query> Alter the refresh method of the materialized view.
ALTER MATERIALIZED VIEW mv1 REFRESH COMPLETE ON DEMAND;
COMMENT#
Syntax#
comment_on ::=
Prerequisites#
Only the SYS user, the owner of the schema to which the table (or view) belongs, users having the ALTER object privilege for the table (or view), and users having the ALTER ANY TABLE system privilege can use the COMMENT statement to write comments.
Description#
This statement is used to write or modify comments for a specified table, view or column.
user_name
This is the name of the owner of the object on which the COMMENT statement is executed. If it is omitted, Altibase will assume that the object being commented on belongs to the schema of the user connected via the current session.
table_name, view_name
This is used to specify the name of the table or view being commented on.
column_name
This is used to specify the name of the column being commented on.
comment
This is the actual content of the comment. The comment can be up to 4000 bytes long. To delete an existing comment, execute the COMMENT statement with nothing between the single quotation marks (’’).
Example#
\<Query> Add comments to the books table, which is owned by the user library1, and to one of its columns.
iSQL> COMMENT ON TABLE library1.books IS 'Table of Book Info';
Comment Created.
iSQL> COMMENT ON COLUMN library1.books.title IS 'Title of Book';
Comment Created.
<Query> Read the table books of the user library 1 and comments for that column.
iSQL> SET VERTICAL ON;
iSQL> SELECT * FROM system_.sys_comments_ WHERE user_name = ‘LIBRARY1’ AND table_name = ‘BOOKS’;
SYS_COMMENTS_.USER_NAME : LIBRARY1
SYS_COMMENTS_.TABLE_NAME : BOOKS
SYS_COMMENTS_.COLUMN_NAME : TITLE
SYS_COMMENTS_.COMMENTS : title of book
SYS_COMMENTS_.USER_NAME : LIBRARY1
SYS_COMMENTS_.TABLE_NAME : BOOKS
SYS_COMMENTS_.COLUMN_NAME :
SYS_COMMENTS_.COMMENTS : table of book info
2 rows selected.
<Query> Delete the comments from the books table and the title column in that table.
iSQL> COMMENT ON TABLE library1.books IS ’’;
Comment created.
iSQL> COMMENT ON COLUMN library1.books.title IS ’’;
Comment created.
CONJOIN TABLE#
Syntax#
conjoin_table ::=
row_movement_clause ::=, tablespace_clause ::=, physical_attributes_clause ::=, logging_clause ::=, lob_column_properties ::=
conjoin_range_clause ::=
range_table_to_partition_clause ::=
conjoin_list_clause ::=
list_table_to_partition_clause ::=
Prerequisites#
It is necessary to be satisfied one or more of the following conditions in order to create table:
- The SYS user
- The user has the CREATE TABLE or CREATE ANY TABLE system privilege in own schema
- The user has the CREATE ANY TABLE system privilege in another user's schema.
It is necessary to be satisfied one or more of the following conditions in order to drop table:
- The SYS user
- Table's owner
- The user has the DROP ANY TABLE system privilege.
Description#
More than one non-partitioned tables are grouped to create a partitioned and its associated partitions. A new partitioned table is created in the user's default tablespace unless the tablespace option is specified. The data located in existing non-partitioned table is transferred to a partition belonging to a new partitioned table, and the non-partitioned tables are removed.
conjoin_range_clause
This clause enables to create a range partitioned table with multiple non-partitioned tables through the range partitioning.
conjoin_list_clause
This clause enables to create a list partitioned table and multiple partitions are created through list partitioning method.
range_table_to_partition
This clause specifies the name of the nonpartitioned table that is the target of the range partitioned table, the name of the partition to be created, and the range of values for the partitioning key.
list_table_to_partition
This clause specifies the name of the nonpartitioned table that is the target of the list partitioned table, the name of the partition to be created, and the set of partitioning key values to be the base.
Precautions#
- Do not specify the user name in front of the target table and partitioned table which will be converting.
- After the CONJOIN, data that does not match the key value of the partition may exist on the partition.
- A metatable related to a newly created partitioned table is created, and all the metatable in relation to the target table converted into a partitioned table is deleted.
- PMS, packages, and views that are related to the target table cannot be used.
- The hash partitioned table is not supported.
- The schema created in partitioned tables must be identical with that of the target table. The target table must be identical with all the elements, such as the number of columns, name, sequence and data type, and in row and compressed logging option, as well as constraints of CHECK and NOT NULL.
- There must not be hidden columns, security columns, and compressed columns in the target table.
- Triggers operating with events must not exist in the target table.
Example#
\<Query> Convert table t1 and t2 into a list partitioned table t3.
iSQL> conjoin table t3
partition by list(i1)
(
table t1 to partition p1 values (1,2,3,4,5),
table t2 to partition p2 values default
)
tablespace tbs3;
Conjoin success.
CREATE DATABASE#
Syntax#
create_database ::=
Prerequisites#
Because this statement can only be executed during the PROCESS phase, the only user who can execute this SQL statement is the SYS user, and only when running in -sysdba mode.
Description#
This statement is used to create a database. When a database is created, various system tablespaces, including the dictionary tablespace, the undo tablespace, and temporary tablespaces are created. The names of these system tablespaces are defined by the system.
Please note that user-defined tablespaces cannot be created during database creation; rather, they are subsequently added by users.
The database character set and national character set must be specified when the database is created.
database_name
This is used to specify the name of the database to be created. The database name specified here must be the same as that specified using the DB_NAME property in the properties file. If they are not the same, an error will be raised.
INITSIZE clause
The initial size of a memory database is specified here, using an expression such as “128M“ (i.e. 128 megabytes) or “4G“ (i.e. 4 gigabytes). If only a number is specified here, the size unit is assumed to be megabytes by default.
The system tablespaces related to a disk database are automatically created when the CREATE DATABASE statement is executed.
The default values for system tablespaces are determined by reading the following properties from the altibase.properties file.
- SYS_DATA_TBS_EXTENT_SIZE, SYS_TEMP_TBS_EXTENT_SIZE, SYS_UNDO_TBS_EXTENT_SIZE
- SYS_DATA_FILE_INIT_SIZE, SYS_TEMP_FILE_INIT_SIZE, SYS_UNDO_FILE_INIT_SIZE
- SYS_DATA_FILE_MAX_SIZE, SYS_TEMP_FILE_MAX_SIZE, SYS_UNDO_FILE_MAX_SIZE
- SYS_DATA_FILE_NEXT_SIZE, SYS_TEMP_FILE_NEXT_SIZE, SYS_UNDO_FILE_NEXT_SIZE
ARCHIVELOG | NOARCHIVELOG
This is used to specify whether the database will initially run in archivelog mode or noarchivelog mode. Archivelog mode is used to prepare for media recovery, whereas noarchivelog mode does not support media recovery.
For more information about Altibase backup and recovery, please refer to the Administrator's Manual.
charset
This is used to specify the database character set and national character set.
- Available Database Character Sets
US7ASCII
KO16KSC5601
MS949
BIG5
GB231280
MS936
UTF8
SHIFTJIS
MS932
EUCJP - Available National Character Sets
UTF8
UTF16
Example#
\<Query> Create a database named mydb that is initially 10MB in size, and for which the database character set is KSC5601 and the national character set is UTF16.
$ isql -s localhost -u sys -p manager -sysdba
..
iSQL> STARTUP PROCESS;
Trying Connect to Altibase.. Connected with Altibase.
TRANSITION TO PHASE: PROCESS
Command execute success.
iSQL> CREATE DATABASE mydb INITSIZE=10M
NOARCHIVELOG
CHARACTER SET KSC5601
NATIONAL CHARACTER SET UTF16;
.
.
Create success.
CREATE DATABASE LINK#
For information on Database Link, please refer to the Database Link User’s Manual.
CREATE DIRECTORY#
Syntax#
Prerequisites#
Only the SYS user and users having the CREATE ANY DIRECTORY system privilege can execute this statement.
Description#
The fact that stored procedures are able to manipulate files means that it is possible to use SQL to read from and write to text files in the host's file system. This feature makes it possible to perform a wide variety of tasks, including leaving messages in files about the execution of stored procedures, writing reports and query results in files, and inserting data read from files into database tables.
The CREATE DIRECTORY statement is used to create a database object that corresponds to a directory that contains files that are manipulated by stored procedures in this way.
The owner of a DIRECTORY object created using the CREATE DIRECTORY statement is always the SYS user. The user who actually created the DIRECTORY object is only granted read and write privileges, including the WITH GRANT OPTION, for the object.
The CREATE DIRECTORY statement records directory data in the SYS_DIRECTORIES_ meta table, but does not actually create the new directory in the file system. Therefore, the user must explicitly create the directory in the actual file system.
OR REPLACE
This option allows an existing DIRECTORY object to be replaced when a new DIRECTORY object is created with the same name. Note that the actual directory in the file system is not deleted.
directory_name
This is used to specify the name of the database object representing the directory. Refer to "Rules for Object Names" for more information on specifying names
path_name
This is used to specify the absolute path of the directory in the file system as a character string.
Examples#
\<Query> Create a directory object named alti_dir1 in the folder /home/altibase/altibase_home/psm_msg.
iSQL> create directory alti_dir1 as '/home/altibase/altibase_home/psm_msg';
Create success.
\<Query>> Create a directory object named alti_dir1 in the folder /home/altibase/altibase_home/psm_result. If a DIRECTORY object named alti_dir1 already exists in the database, replace it with this new one.
iSQL> create or replace directory alti_dir1 as '/home/altibase/altibase_home/psm_result';
Create success.
CREATE INDEX#
Syntax#
create_index ::=
table_index_clause ::=
memory_index_clause ::=
disk_index_clause::=
domain_index_clause ::=
directkey_clause ::=
memory_index_attributes ::=
storage_clause ::=
index_partitioning_clause ::=
index_partition_definition ::=
disk_index_attributes::=
parallel_clause ::=
logging_clause ::=
physical_attributes_clause ::=
Prerequisites#
Only the SYS user, users having the CREATE INDEX system privilege, and users having sufficient privileges to modify index objects in the table to which the index is to be added can execute this statement.
In order to create function-based indexes, the same privileges as conventional indexes are required. However, if a user-defined function is included in the expression, the function must be marked DETERMINISTIC. The user must also have the EXECUTE object privilege on user-defined functions used in the function-based index owned by another user.
Description#
This statement is used to create an index on the basis of one or more of the columns or expressions in a table. When a partitioned index (i.e. local index) is created, the LOCALUNIQUE keyword can be optionally specified. If the LOCALUNIQUE option or local keyword is not used, a non-partitioned index is created when creating an index on a disk partitioned table.
A partitioned index is classified as either a prefixed index or a non-prefixed index, depending on the relationship between the partition key and the index columns. If the leftmost index partition key is the same column as the leftmost index column, it is a prefixed index. If they are different columns, it is a non-prefixed index.
Function-based indexes are based on expressions. Expressions can include built-in SQL functions or user-defined functions.
user_name
This is used to specify the name of the owner of the index to create. If omitted, Altibase will create the index in the schema of the user connected via the current session.
index_name
This is used to specify the name of the index to create. Refer to "Rules for Object Names" for more information on specifying names.
UNIQUE
This keyword indicates that duplicate values are not allowed.
LOCALUNIQUE
This keyword is useful when creating a partitioned index. It indicates that the UNIQUE constraint must be satisfied within each index partition of a partitioned (local) index.
index_expr
This specifies the column or expression to be indexed.
Table columns, constants, SQL functions and user-defined functions can be included in expressions. When specifying an expression instead of a column, a function-based index is created. Limitations of function-based indexes:
- Only the columns of the table to be indexed can be included in expressions. Schema and table names cannot be specified in front of column names.
- Constants (character strings or integers) can be included in expressions.
- Aggregate functions like SUM cannot be included in expressions.
- Cursors and cursor properties(%ISOPEN, SQLCODE, SQLERRM, etc.) cannot be used for stored procedures included in expressions.
- For global partitioned indexes, expressions cannot be partitioning keys.
- Regardless of the absence of function arguments, all functions must be specified with parentheses. On omission, database servers perceive them to be column names.
- DATE constants not fully specified are permitted in expressions. If the year/month is not specified in the DATE type value, the current year/month is assumed as the default value. The time zone value cannot be specified for the DATE type of Altibase.
- Built-in SQL functions that always return identical values can be included in expressions; for example, the SYSDATE function cannot be included.
- User-defined functions included in expressions must be marked DETERMINISTIC.
- Subqueries cannot be included in expressions.
- Sequences cannot be included in expressions.
- Pseudo columns of all sorts cannot be included in expressions.
- The PRIOR operator cannot be included in expressions.
- LOB data cannot be included in expressions.
- A function-based index can be selected during query optimization only if the QUERY_REWRITE_ENABLE property is set to 1.
ASC/DESC
The use of the ASC or DESC keywords respectively specifies that each column of the index is to be sorted in ascending or descending order.
index_partitioning_clause
This is used to specify that the index to be created is a partitioned index. If omitted, index partitions will be stored in the table's default tablespace.
The easiest way to create a partitioned index is simply to specify the LOCAL keyword when creating the index. Alternatively, the attributes of the index partition to be created for each table partition can be specified in greater detail.
If only the LOCAL keyword is specified, an index partition is created for each table partition, and the names of the partitions are automatically determined by the system. Index partitions are successively named SYS_IDX1, SYS_IDX2, etc.
The attributes of index partitions can be expressly specified for some or all table partitions. When the attributes of only some index partitions are specified, the attributes of the index partitions for the remaining table partitions are determined automatically as outlined above.
If no tablespace is specified when creating a partitioned index, the process for determining the tablespace in which each of the index partitions is stored is as shown in the following diagram:
Note: Even if the tablespace in which each parititon is to be stored is specified, it is ignored, as memory indexes are not stored in tablespaces.
BTREE
This is used to specify that the index is a B+ -tree index, which is useful in situations where ranges are often searched. An index can be either a B+ -tree or an R-tree index. If the INDEXTYPE IS clause is omitted, the index will be a B+ -tree index by default.
RTREE
This is used to specify that the index is an R-tree index, which is useful for processing multidimensional data.
directkey_clause
Creates direct key indexes. A direct key is a record stored in the index node; a direct key index can reduce index scan cost since it stores the actual record and the record pointer.
- MAXSIZE integer
Sets the maximum size of a direct key; on omission, the default value is 8 bytes. Please refer to the table below for supported data types.
If a value larger than MAXSIZE is set for a data type that supports partial keys, it is stored as a prefix that corresponds to MAXSIZE and a direct key index is successfully created.
If a value larger than MAXSIZE is set for a data type that does not support partial keys, a direct key index fails to be created.
- Direct Key Index Restrictions
- If a direct key index is created on composite index, the first column is set as the direct key.
- A direct key index cannot be created on compressed or encrypted column.
- A direct key index cannot be created for indexes residing on disk.
The following data types support direct key indexes.
|
Category |
Data Type |
MAXSIZE (Supports Full Keys) |
Support Partial Keys) |
|---|---|---|---|
|
Native Numeric Data Type |
BINGINT |
8 |
X |
|
DOUBLE |
8 |
X |
|
|
INTEGER |
4 |
X |
|
|
REAL |
4 |
X |
|
|
SMALLINT |
2 |
X |
|
|
Non-Native Numeric Data Type |
FLOAT FLOAT(p) |
23 3 + ( ( p + 2 ) / 2 ) |
X |
|
NUMBER NUMBER(p, s) NUMBER(p) |
23 3 + ( ( p + 2 ) / 2 ) 3 + ( ( p + 2 ) / 2 ) |
X |
|
|
NUMERIC NUMERIC(p, s) NUMERIC(p) |
23 3 + ( ( p + 2 ) / 2 ) 3 + ( ( p + 2 ) / 2 ) |
X |
|
|
Character Data Type |
CHAR(M) |
M + 2 |
O |
|
VARCHAR(M) |
M + 2 |
O |
|
|
NCHAR(M) |
(M * 2) + 2 <UTF16> (M * 3) + 2 <UTF8 |
O |
|
|
NVARCHAR(M) |
(M * 2) + 2 <UTF16> (M * 3) + 2 <UTF8> |
O |
|
|
Date Data Type |
DATE |
8 |
X |
|
Binary Data Type |
BIT(M) |
(M / 8) + 4 |
X |
|
VARBIT(M) |
(M / 8) + 4 |
|
|
|
BYTE(M) |
M + 2 |
X |
|
|
NIBBLE(M) |
(M / 2) + 1 |
|
physical_attributes_clause
- INITRANS Clause
This is used to set the initial number of TTS (Touched Transaction Slots). The default is 8. - MAXTRANS Clause
This is used to set the maximum number of TTS (Touched Transaction Slots). The default is 50.
TABLESPACE Clause
This is used to specify the name of the tablespace in which the index is to be stored. If this clause is omitted, Altibase stores the index in the default tablespace for the owner of the schema to which the index belongs. However, when an index is created for a memory table, even if the tablespace is specified, this clause is ignored because memory indexes are not stored in any tablespace.
parallel_clause
This is a hint for setting the number of threads used to create an index, with the aim of realizing a performance improvement. Altibase determines the optimal number of index creation threads in consideration of the user-defined parallel_factor, which is specified using a hint, the size of the tables for which the indexes are being created, and the amount of available memory at the time the index is created.
The value of parallel_factor can be set within the range from 0 to 512. The default is the number of CPUs in the host on which Altibase is running. Because the number of index creation threads is determined using the above optimized determination method, it is safe to omit parallel_factor.
If parallel_factor is not set, or is set to 0, the value of the INDEX_BUILD_THREAD_COUNT property in the altibase.properties file, which has the same meaning as parallel_factor, is used instead. If the INDEX_BUILD_THREAD_COUNT property has not been set either, the number of CPUs is used as a hint to set the optimum number of index creation threads.
If parallel_factor is set to a value that is greater than the number of CPUs, or is set to a value greater than 512, the user-defined value is ignored and the number of CPUs is used as a hint for setting the optimum number of threads.
logging_clause
The LOGGING or NOLOGGING clause can be used to enable or disable logging when an index is created for a disk table. Logging is enabled by default, meaning that information about the creation of the index is logged when an index is created.
The FORCE and NOFORCE options are used to determine whether to forcibly store a disk index to disk immediately after the index is created.
For more details about logging_clause, please refer to the section pertaining to indexes in the “Objects and Privileges“ chapter of the Administrator's Manual.
storage_clause
This is used to set parameters for managing extents in segments.
- INITEXTENTS Clause
This sets the number of extents that are allocated by default when a segment is created. The default is 1. - NEXTEXTENTS Clause
This sets the number of extents that are added to the segment every time the segment is increased in size. The default is 1. - MINEXTENTS Clause
This sets the minimum number of extents in a segment. The default is 1. - MAXEXTENTS Clause
This set the maximum number of extents in a segment. If this isn't specified, there is no maximum limit on the number of extents in a segment.
Considerations#
- In the case of an index for a partitioned table, i.e. a partitioned index, the tablespace in which each local index is stored is defined separately in index_partitioning_clause. disk_index_attributes cannot be used to specify the tablespace for an entire partitioned index. Additionally, a local index can only be a B+ -tree index.
-
In the event of a system or media fault, the consistency of an index that was created using the NOLOGGING (FORCE/NOFORCE) option cannot be guaranteed. In this case, the error “The index is inconsistent” will be raised. To fix this error, locate the inconsistent index, drop it, and create it again. The consistency of an index can be checked using the V$DISK_BTREE_HEADER performance view.
-
An index cannot be created on the basis of a LOB column.
Example#
\<Query 1> Create the index emp_idx2 on the column eno in ascending order and on the column dno in descending order.
iSQL> CREATE INDEX emp_idx2
ON employees (eno ASC, dno DESC);
Create success.
\<Query 2> Create a unique index named emp_idx2 for the dno column in the employees table in descending order. (This is possible when there are no records in the employees table, or when there are only unique values in the column dno.)
iSQL> CREATE UNIQUE INDEX emp_idx2
ON employees (dno DESC);
Create success.
\<Query 3> Create the B+ -tree index emp_idx3 in ascending order for the eno column in the employees table. Because a primary key already exists for the eno column of the employees table, it must first be deleted before the index emp_idx3 can be created. If it is not deleted first, the following error will be raised:
ERR-3104C: Duplicate key columns in an index
iSQL> ALTER TABLE employees
DROP PRIMARY KEY;
Alter success.
iSQL> CREATE INDEX emp_idx3
ON employees (eno ASC)
INDEXTYPE IS BTREE;
Create success.
\<Query 4> Create the index idx1 in the user_data tablespace on the basis of the i1 column in the table_user table.
iSQL> CREATE INDEX idx1
ON table_user (i1)
TABLESPACE user_data;
Create success.
\<Query 5> Create the index idx2 in the user_data tablespace on the basis of the i1 column in the table_user table using the parallel option.
iSQL> CREATE INDEX idx1
ON table_user (i1)
TABLESPACE user_data PARALLEL 4;
Create success.
\<Query 6> Create a local index, that is, an index in which the partitions correspond to respective table partitions, based on product_id. Allow the partition names to be determined automatically
CREATE INDEX prod_idx ON products(product_id) LOCAL;
\<Query 7> Create a local index, specifying the attributes for each index partition.
CREATE INDEX prod_idx ON products(product_id)
LOCAL
(
PARTITION p_idx1 ON p1 TABLESPACE tbs_disk1,
PARTITION p_idx2 ON p2 TABLESPACE tbs_disk2,
PARTITION p_idx3 ON p3 TABLESPACE tbs_disk3
);
\<Query 8> Create a local index, specifying the attributes for only some of the index partitions. The attributes for the other partitions are determined automatically.
CREATE INDEX prod_idx ON products(product_id)
LOCAL
(
PARTITION p_idx1 ON p1 TABLESPACE tbs_disk1,
PARTITION p_idx3 ON p3 TABLESPACE tbs_disk3
);
\<Query 9> Create the index idx1 in the table employees based on the employee number column (eno). Enable logging to ensure the availability of the index in the event of a system or media fault. Assume that the table employees is in a disk tablespace.
iSQL> CREATE INDEX idx1
ON employees (eno);
Create success.
Or
iSQL> CREATE INDEX idx1
ON employees (eno) LOGGING ;
Create success.
\<Query 10> Create the index idx1 in the table employees with the NOLOGGING option using the employee number column (eno) in ascending order and the department number column (dno) in ascending order. Ensure that the index is available in the event of a system fault after the index is created (FORCE). Assume that the table employees is in a disk tablespace.
iSQL> CREATE INDEX idx1
ON employees (eno ASC, dno ASC)
NOLOGGING;
Create success.
Or
employee number(eno): ASC
department number(dno): ASC
iSQL> CREATE INDEX idx1
ON employees (eno ASC, dno ASC)
NOLOGGING FORCE;
Create success.
\<Query 11> Create the index idx1 in the table employees with the NOLOGGING option using the employee number column (eno) in ascending order and the department number column (dno) in ascending order. Specify that the index is not to be written to disk (NOFORCE). Assume that the table employees is in a disk tablespace.
iSQL> CREATE INDEX idx1
ON employees (eno ASC, dno ASC)
NOLOGGING NOFORCE;
Create success.
\<Query 12> Create the index LOCAL_IDX in the disk tablespace USERTBS for the table LOCAL_TBL. Allocate one extent to the index when it is created and specify that 2 extents are to be added whenever it is necessary to increase the size of the index segment, and also that there is no upper limit to the total number of extents in the index segment.
iSQL> CREATE INDEX LOCAL_IDX ON LOCAL_TBL ( I1 )
TABLESPACE USERTBS
STORAGE ( INITEXTENTS 1 NEXTEXTENTS 2 MAXEXTENTS UNLIMITED );
Create success.
\<Query 13> Create a function-based index on salary using the salary column of the employees table.
iSQL> CREATE INDEX income_idx ON employees (salary*12);
Create success.
\<Query 14> Create the same index as
CREATE OR REPLACE FUNCTION get_annual_salary
(salary in integer)
RETURN integer
DETERMINISTIC
AS
BEGIN
RETURN salary*12;
END;
/
iSQL> CREATE INDEX income_idx ON employees(sys.get_annual_salary(salary));
Create success.
\<Query 15> Create a direct key index on the eno column in the table employees.
iSQL> CREATE INDEX direct_idx ON employees ( eno ) DIRECTKEY ;
Create success.
\<Query 16> Create a direct key index that can store 32 byte records on the name column (VARCHAR(100)) in the tab1 table.
iSQL> CREATE INDEX idx1 ON tab1 ( name ) DIRECTKEY MAXSIZE 32;
Create success.
CREATE JOB#
Syntax#
create_job ::=
execute_procedure_statement ::=
start_end_clause ::=
interval_clause ::=
Prerequisites#
Only the SYS user can create a JOB with this statement. In order to perform the created JOB, it should be activated with ENABLE when creating or after creating the JOB.
Description#
A JOB to be managed by the job scheduler can be created with the CREATE JOB statement. The procedure to be executed, the start time, the end time, the interval after which it is to be executed and etc. can be set for each JOB to be created.
execute_procedure_statement
This specifies the execution statement of the procedure to be registered for the JOB. Only one procedure can be registered per JOB and the user name of the procedure can be omitted; on omission, the SYS user is assumed by default. For expr2, the input argument value for the execution of the defined procedure is specified as a constant or an expression.
For more detailed information on procedures, please refer to the Stored Procedures Manual.
start_end_clause
This sets the start time and the end time at which the JOB starts to run and ends running. Expr1 only accepts a DATE type value or a DATE type expression.
interval_clause
This specifies the interval after which the JOB is to run repeatedly after it has run for the first time. The unit of time is determined by the YEAR, MONTH, DAY, HOUR, MINUTE which is specified. after the number.
ENABLE/DISABLE
This statement is used for a user to set whether or not to execute each job through selecting ENABLE/DIABLE in the job scheduler. The default value is set to DISABLE if omitted.
COMMENT
This statement is used for a user to set whether or not to execute each job through selecting ENABLE/DIABLE in the job scheduler. The default value is set to DISABLE if omitted.
Precautions#
The following are issues which the DBA should keep in mind when using a JOB:
- A stored procedure which takes either an OUT type or an INOUT type argument cannot be registered for a JOB.
- For a JOB to be run by the job scheduler, the value of the JOB_SCHEDULER_ENABLE property must be 1 and the value of the JOB_THREAD_COUNT property must be larger than 0.
- If an error occurs within the procedure being executed by a JOB, logs - such as error messages - are stored to the trace log file (default: $ALTIBASE_HOME/trc/altibase_qp.log) set for the QP_MSGLOG_FILE property.
- If an output function, like SYSTEM_.PRINTLN, is used within a procedure, the output content is written to the trace log file (default: $ALTIBASE_HOME/trc/altibase_qp.log) set for the QP_MSGLOG_FILE property
Examples#
\<Query> Create a JOB which executes the procedure proc1 once per month, starting from the current point in time.
iSQL> CREATE JOB job1 EXEC proc1 START sysdate INTERVAL 1 MONTH;
Create success.
\<Quey> Create job2 executing the proc procedure everyday from August 1, 2016 to August 31, 2016, and get the job2 activated (ENABLE).
iSQL> CREATE JOB job2 EXEC proc
START to_date('2016/08/01 00:00:00', 'YYYY-MM-DD HH:MI:SS')
END to_date('2016/08/31 00:00:00', 'YYYY-MM-DD HH:MI:SS')
INTERVAL 1 DAY
ENABLE;
Create success.
CREATE QUEUE#
Syntax#
create_queue ::=
Prerequisites#
It is necessary to be satisfied one or more of the following conditions in order to create table:
- The SYS user
- The user has the CREATE TABLE or CREATE ANY TABLE system privilege in own schema.
- The user has the CREATE ANY TABLE system privilege in another user's schema.
Description#
This syntax either can configure the maximum length of messages that are inserted into a queue or allows the user to directly define a column in order to create a queue. It can also specifies the number of maximum records that can be stored in a queue table.
queue_name
This is used to specify the name of the queue. The maximum possible length of the queue name is 28 bytes.
size
This is used to set the maximum size (in bytes) of a message to be stored in the queue. This value can be set within the range from 1 to 32,000 bytes.
FIXED|variable_clause
This is used to specify how messages are saved. (For more information, please refer to the General Reference).
column_definition
This clause specifies the user-defined column. It refers to CREATE TABLE statement's column_definition and does not support column_constraint, crypt_clause and timestamp.
MAXROWS count
This is used to set the maximum number of records that can be stored in a queue table. This value can be set within the range from 1 to 4294967295 (or (232) -1). When not specified, it defaults to the maximum value of 4294967295.
Considerations#
- When a queue is created, an object having the name queue_name + "_NEXT_MSG_ID" is created in the database. Therefore, if any existing table, view, sequence, synonym or stored procedure has the same name as the queue to be created, or has the name queue_name + "_NEXT_MSG_ID", the CREATE QUEUE statement will return an error.
Examples#
\<Query> >Create the queue Q1, stipulating that the maximum message length is 40 bytes and that the maximum number of records is 1,000,000.
iSQL> CREATE QUEUE Q1(40) MAXROWS 1000000;
Create success.
\<Query> Create 2 columns that can store values for numeric(5,2) type when creating a queue named Q1.
iSQL> CREATE QUEUE Q1(c1 numeric(5,2), c2 numeric(5,2));
Create success.
\<Query> Create a queue named Q2 defining the user-defined column and delete it after writing messages into the pertinent column.
iSQL> CREATE QUEUE Q2(V1 VARCHAR(10), V2 INTEGER, V3 NUMERIC(5,3));
Create success.
iSQL> ENQUEUE INTO Q2(V1, V2, V3) VALUES ('abc', 1, 99.999);
1 row inserted.
iSQL> DEQUEUE V1, V2, V3 FROM Q2;
V1 V2 V3
----------------------------------------
abc 1 99.999
1 row selected.
CREATE REPLICATION#
Syntax#
create_replication ::=
option_clause ::=
replication_item ::=
Prerequisites#
Only the SYS user can execute replication-related statements.
Description#
This statement is used to create a replication object, set the connection between a local server and one or more remote servers, and establish replication there between. Replication takes place between tables on a 1:1 basis; that is, a table is matched to only one corresponding table.
In order to resolve conflicts, the AS MASTER or AS SLAVE clause can be specified in the statement. Doing so stipulates that a master-slave scheme is to be used to resolve conflicts. For more detailed information about replication conflict resolution, please refer to Chapter 2 of the Replication Manual.
replication_name
This is used to specify the name of the replication object. The replication object name must be the same on both the local server and the remote server. Refer to "Rules for Object Names" for more information on specifying names.
FOR ANALYSIS | FOR ANALYSIS PROPAGATION
This creates the Xlog Sender. For more detailed information about properties, please refer to the Log Analyzer User's Manual.
FOR PROPAGABLE LOGGING
This is used to write logs received by the propagable receiver from the local server in order to replicate into other server. However, this function cannot be used with the recovery option.
FOR PROPAGATION
The propagable logs are transmitted to other target server.
option_clause
This is used to optionally specify the RECOVERY, OFFLINE, GROUPING, PARALLEL, and GAPLESS functions for the replication object. These features are for use in data recovery and when performing offline replication, respectively. For more information, please refer to Chapter 3 of the Replication Manual.
replication_host_ip
This is used to specify the IP address of the remote server.
replication_host_port_no
This is used to specify the port number used by the Receiver thread on the remote server. It is identical to the REPLICATION _PORT_NO property in the altibase.properties file on the remote server.
USING conn_type [ib_latency]
The communication method (TCP or InfiniBand) can be set with the remote server. The ib_latency value can be set only when using InfiniBand. To use InfiniBand, the IB_ENABLE property must have a value of 1.
user_name
This is used to specify the name of the owner of the table to be replicated.
tbl_name
This is used to specify the name of the table to be replicated.
partition_name
This is used to specify the name of the partition to be replicated.
Examples#
\<Query> Create replication rep1 according to the following conditions:
-
The IP address of the local server is 192.168.1.60.
-
The replication port number on the local server is 25524.
-
The IP address of the remote server is 192.168.1.12.
-
The replication port number on the remote server is 35524.
Create the replication object to replicate the employees and departments tables between the servers.
On the local server (IP: 192.168.60) :
iSQL> CREATE REPLICATION rep1
WITH '192.168.1.12',35524
FROM sys.employees TO sys.employees,
FROM sys.departments TO sys.departments;
Create success.
On the remote server (IP: 192.168.1.12) :
iSQL> CREATE REPLICATION rep1
WITH '192.168.1.60',25524
FROM sys.employees TO sys.employees,
FROM sys.departments TO sys.departments;
Create success.
\<Query> After the remote server received the log in replication Rep 1, create replication Rep2 for the propagable logs and sends them to target server.
iSQL> CREATE REPLICATION rep1
FOR PROPAGABLE LOGGING WITH '192.168.1.12',35524
FROM sys.t1 TO sys.t1;
iSQL> SELECT replication_name, role
FROM system_.sys_replications_;
REPLICATION_NAME ROLE
---------------------------------------------------------
REP1 2
1 row selected.
iSQL> CREATE REPLICATION rep2
FOR PROPAGATION WITH '192.168.1.60',25524
FROM sys.t1 TO sys.t1;
Create success.
iSQL> SELECT replication_name, role
FROM system_.sys_replications_;
REPLICATION_NAME ROLE
---------------------------------------------------------
REP2 3
1 row selected.
CREATE ROLE#
Syntax#
create_role ::=
Prerequisites#
Only the SYS user and users with the CREATE ROLE system privilege can create a role.
Description#
A new role is created with the specified name.
A set of privileges which can be granted to a user is called a role.
If a role is created for the first time, it does not have any privilege. Add privileges to a role by using the GRANT statement, then grant the role to a user.
In order for a user to use a privilege which is granted by a role, the user should re-access to the database. The privilege which can be granted to a role is a system privilege and object privilege. Further information on how to use them is delineated in examples of GRANT and REVOKE statements as well.
role_name
This is used to specify the name of the role to be created. This name must be unique within the database.
Example#
\<Query> Create a role with the name alti_role.
iSQL> CREATE ROLE alti_role;
Create success.
CREATE SEQUENCE#
Syntax#
create_sequence ::=
sequence_options ::=
sync_table_clause ::=
Prerequisites#
Only the SYS user and users having the CREATE SEQUENCE system privilege can execute this statement. In addition, it is necessary to be the SYS user or have the CREATE ANY SEQUENCE system privilege in order to create a sequence in another user's schema
Description#
This statement is used to define a new sequence having the specified name and automatically populate the sequence.
user_name
This is used to specify the name of the owner of the sequence to be created. If it is omitted, Altibase will create the sequence in the schema of the user who is connected via the current session.
seq_name
This is used to specify the name of the sequence to be created. Refer to "Rules for Object Names" for more information on specifying names.
START WITH
This is the initial value of the sequence. This can be set to any value between MINVALUE and MAXVALUE inclusive. If this value is omitted and the value for INCREMENT BY is more than 0, the default value is the same as the minimum value of the sequence. If this value is omitted and the value for INCREMENT BY is less than 0, the default value is the same as the maximum value of the sequence.
INCREMENT BY
This is the value by which the sequence increments. The default value is 1. The absolute of this value must be less than the difference between MAXVALUE and MINVALUE.
MAXVALUE
This is the maximum value of the sequence. This can be set to any value between -9223372036854775805 and 9223372036854775806. If the value for INCREMENT BY is more than 0, the default value is 9223372036854775806. If the value for INCREMENT BY is less than 0, the default value is -1.
MINVALUE
This is the minimum value of the sequence. This can be set to any value between -9223372036854775806 and 9223372036854775805. If the value for INCREMENT BY is more than 0, the default value is 1. If the value for INCREMENT BY is less than 0, the default value is -9223372036854775806.
CYCLE
This clause is used to ensure that the sequence will continue to generate values when it reaches the value specified using MAXVALUE or MINVALUE. The sequence cycles again from the minimum value in the case of an ascending sequence, or from the maximum value in the case of a descending sequence.
CACHE
A specified number of sequence values can be created in advance and cached in memory so that they can be accessed more quickly. The cache is populated when a key value is first requested from a new sequence, and is accessed every time another key value is requested from the sequence. After the last sequence value in the cache has been used, the next request for a key value from the sequence causes new sequence values to be created and cached in memory. When a sequence is created, the default CACHE value is 20.
ENABLE | DISABLE SYNC TABLE
This specifies whether or not to create a custom table for sequence replication for the purpose of replicating a sequence.
- ENABLE: Creates a custom table for sequence replication. The table is automatically granted the name, [sequence name]$seq.
- DISABLE: Does not create a custom table for sequence replication.
If this option is not specified, a custom table for sequence replication is not created by default.
Consideration#
- Please note that the sequence_name.CURRVAL value of a newly created sequence cannot be accessed. In order to access the sequence_name.CURRVAL value for a newly created sequence, it is first necessary to access the sequence_name.NEXTVAL value.
- A custom table for sequence replication can be created only if the length of the sequence name is equal to or smaller than 36 bytes.
Examples#
The following SQL statements show how to define new sequences and check sequence values and information.
iSQL> CREATE TABLE seqtbl(i1 INTEGER);
Create success.
iSQL> CREATE OR REPLACE PROCEDURE proc1
AS
BEGIN
FOR i IN 1 .. 10 LOOP
INSERT INTO seqtbl VALUES(i);
END LOOP;
END;
/
Create success.
iSQL> EXEC proc1;
Execute success.
\<Query> Use the following statements to check information on sequence objects.
iSQL> select * from v$seq;
This command displays information about all sequence objects that have been created. Unlike Select * from seq, querying the performance view allows information about other users' sequences to be viewed. For more information on the performance view V$SEQ, please refer to the section of the Data Dictionary that explains performance views in the General Reference.
\<Query> Create a sequence named seq1 that begins at 13, increments by 3, and has a minimum value of 0 and no maximum value.
iSQL> CREATE SEQUENCE seq1
START WITH 13
INCREMENT BY 3
MINVALUE 0 NOMAXVALUE;
Create success.
iSQL> INSERT INTO seqtbl VALUES(seq1.NEXTVAL);
1 row inserted.
iSQL> INSERT INTO seqtbl VALUES(seq1.NEXTVAL);
1 row inserted.
iSQL> SELECT * FROM seqtbl;
SEQTBL.I1
--------------
1
2
3
4
5
6
7
8
9
10
13
16
12 rows selected.
\<Query> Change seq1 so that it increments by 50 and starts over at the minimum value if it reaches the maximum value of 100.
iSQL> ALTER SEQUENCE sys.seq1
INCREMENT BY 50
MAXVALUE 100
CYCLE;
Alter success.
iSQL> INSERT INTO sys.seqtbl VALUES(seq1.NEXTVAL);
1 row inserted.
iSQL> INSERT INTO sys.seqtbl VALUES(seq1.NEXTVAL);
1 row inserted.
iSQL> INSERT INTO sys.seqtbl VALUES(seq1.NEXTVAL);
1 row inserted.
iSQL> INSERT INTO sys.seqtbl VALUES(seq1.NEXTVAL);
1 row inserted.
iSQL> SELECT * FROM sys.seqtbl;
SEQTBL.I1
--------------
1
2
3
4
5
6
7
8
9
10
13
16
66
0
50
100
16 rows selected.
\<Query> Check the current value of seq1, which will cause a new value to be generated.
iSQL> SELECT seq1.CURRVAL FROM dual;
SEQ1.CURRVAL
-----------------------
100
1 row selected.
\<Query> Change the value in column i1 to the next value of the sequence, which is 0.
iSQL> UPDATE SEQTBL SET i1 = seq1.NEXTVAL;
16 rows updated.
\<Query> Check the current value of seq1.
iSQL> SELECT seq1.CURRVAL FROM dual;
SEQ1.CURRVAL
-----------------------
0
1 row selected.
\<Query> Change seq1 so that the specified number of sequence values (25) are cached for the faster access.
iSQL> ALTER SEQUENCE seq1
INCREMENT BY 2
MAXVALUE 200
CACHE 25;
Alter success.
iSQL> CREATE OR REPLACE PROCEDURE proc2
AS
BEGIN
FOR i IN 1 .. 30 LOOP
INSERT INTO sqqtbl VALUES(seq1.NEXTVAL);
END LOOP;
END;
/
Create success.
iSQL> EXEC proc2;
Execute success.
iSQL> SELECT * FROM seqtbl;
SEQTBL.I1
--------------
0
50
100
0
50
100
0
50
100
0
50
100
0
50
100
0
2
4
6
8
10
12
14
.
.
.
58
60
46 rows selected.
<Query> When connected to a database as the SYS user, output information on all sequences.
iSQL> SELECT * FROM SEQ;
USER_NAME
--------------------------------------------
SEQUENCE_NAME CURRENT_VALUE INCREMENT_BY
------------------------------------------------
MIN_VALUE MAX_VALUE CYCLE CACHE_SIZE
------------------------------------------------
SYS
SEQ1 60 2
0 200 YES 25
1 row selected.
<Query> The following SQL statements show how to define sequences and view sequence values and information using multiple user accounts.
iSQL> CONNECT sys/manager;
Connect success.
iSQL> CREATE USER user1 IDENTIFIED BY user1;
Create success.
iSQL> CREATE USER user2 IDENTIFIED BY user2;
Create success.
iSQL> CONNECT user1/user1;
Connect success.
iSQL> CREATE SEQUENCE seq1 MAXVALUE 100 CYCLE;
Create success.
iSQL> CREATE SEQUENCE seq2;
Create success.
\<Query> Output information on all sequences created by user1.
iSQL> SELECT * FROM SEQ;
SEQUENCE_NAME CURRENT_VALUE INCREMENT_BY
------------------------------------------------
MIN_VALUE MAX_VALUE CYCLE CACHE_SIZE
------------------------------------------------
SEQ1 1 1
1 100 YES 20
SEQ2 1 1
1 9223372036854775806 NO 20
2 rows selected.
iSQL> CONNECT user2/user2;
Connect success.
iSQL> CREATE SEQUENCE seq1 INCREMENT BY -30;
Create success.
iSQL> CREATE SEQUENCE seq2 INCREMENT BY -10 MINVALUE -100;
Create success.
iSQL> CONNECT sys/manager;
Connect success.
iSQL> CREATE SEQUENCE seq2 START WITH 20 INCREMENT BY 30;
Create success.
iSQL> CREATE SEQUENCE seq3 CACHE 40;
Create success.
\<Query> When connected to the database as the SYS user, output information on all sequences.
iSQL> SELECT * FROM SEQ;
USER_NAME
--------------------------------------------
SEQUENCE_NAME CURRENT_VALUE INCREMENT_BY
------------------------------------------------
MIN_VALUE MAX_VALUE CYCLE CACHE_SIZE
------------------------------------------------
SYS
SEQ1 60 2
0 200 YES 25
SYS
SEQ2 20 30
1 9223372036854775806 NO 20
SYS
SEQ3 1 1
1 9223372036854775806 NO 40
USER1
SEQ1 1 1
1 100 YES 20
USER1
SEQ2 1 1
1 9223372036854775806 NO 20
USER2
SEQ1 -1 -30
-9223372036854775806 -1 NO 20
USER2
SEQ2 -1 -10
-100 -1 NO 20
7 rows selected.
\<Query> Create the sequence seq1 with the cache size 100, which creates a custom table for sequence replication.
CREATE SEQUENCE seq1 CACHE 100 ENABLE SYNC TABLE;
CREATE SYNONYM#
Syntax#
create_synonym ::=
Prerequisites#
At least one of the following conditions must be met:
- The SYS user
- Users having the CREATE SYNONYM or CREATE ANY SYNONYM system privilege in order to create a synonym in their own schema can execute this statement.
- Additionally, it is necessary to be the SYS user or have the CREATE ANY SYNONYM system privilege in order to create a synonym in another user's schema, and it is necessary to be the SYS user or have the CREATE PUBLIC SYNONYM system privilege in order to create a public synonym
Description#
This statement is a SQL statement that generates synonyms. Synonyms are alternative names for the following objects:
- Table
- View
- Sequence
- Stored procedure and function
- Another synonym
Synonyms can be referred to using the following types of SQL statements:
| DML Statement | DDL statement |
|---|---|
| SELECT INSERT UPDATE DELETE MOVE LOCK TABLE MERGE ENQUEUE DEQUEUE |
GRANT REVOKE |
OR REPLACE
This clause specifies the re-creation of a synonym, if it already exists. Using this clause, you can alter the definition of an existing synonym, without deleting it first.
PUBLIC Synonyms vs. PRIVATE Synonyms
A PUBLIC synonym is accessible by all users, whereas a PRIVATE synonym can only be accessed by its owner.
To create a public synonym, specify PUBLIC in the statement. If this keyword is not provided, a PRIVATE synonym will be created by default.
user_name
If a user name is provided in front of the synonym name, that user will be the owner of the synonym.
When creating a PUBLIC synonym, do not specify the name of the owner.
When creating a PRIVATE synonym, it is possible to specify the name of the owner. If no user name is provided, the synonym will be created in the schema of the user connected via the current session.
synonym_name
If there is a table, view, sequence, stored procedure, stored function,or another synonym that has the same name as the synonym to be created, an error will be raised. Because synonyms occupy the same namespace as these object types, the name for the synonym must be unique within the schema in which it is created. Refer to "Rules for Object Names" for more information of specifying names.
FOR clause
This clause is used to specify the object for which the synonym will serve as an alias.
user_name
This is used to specify the owner of the object for which the synonym will function as an alias. If no user name is specified, Altibase will assume that the object belongs to the schema of the user connected via the current session.
object_name
This is used to specify the name of the object for which the synonym will serve as an alias.
If this object does not exist in the database, no error will be raised, and the synonym will be created successfully regardless. In other words, the schema object need not currently exist, and it is not necessary to have privileges for the object for which the synonym will function as an alias.
Privileges and Synonyms#
To execute DML statements on synonyms, it is necessary to have DML execution privileges for the underlying object.
When DML execution privileges on a synonym are granted or revoked, the privileges are actually granted or revoked on the underlying objects.
Therefore, when the execution of a DML statement on a synonym results in an error, check the SYS_GRANT_SYSTEM_ or SYS_GRANT_OBJECT_ meta table to determine whether the user has been granted suitable privileges for the underlying object.
If the user has not been granted suitable privileges, grant the privileges to the user. When granting privileges to the user, they can be granted either by referencing the object itself, or by referencing the synonym. If the user already has appropriate privileges for the underlying object itself, it is necessary only to create the synonym. No additional privileges need to be granted for the synonym.
Privileges granted for an object by referencing a synonym for the object are not revoked even if the synonym is subsequently dropped. This is because the privileges are actually granted for the object underlying the synonym, not the synonym itself, even though the synonym was referenced when the privileges were granted.
Object Name Search Order#
In order to determine which objects match the objects referenced in a SQL statement, any tables, views, sequences, stored procedures or stored functions having corresponding names are first searched for. If they don't exist, other synonym objects having the names are searched for.
PRIVATE synonyms are examined before PUBLIC synonyms.
For example, whether objects having the names referenced in a SQL statement exist in the database would be determined in the order shown below.
-
SELECT * FROM NAME
-
Any tables or views having the name “NAME” are searched for.
- If no tables or views having the name “NAME” exist, any PRIVATE synonym objects having the name are searched for in the schema of the user connected via the current session.
-
If no PRIVATE synonyms having the name exist, any PUBLIC synonym objects are searched for.
-
SELECT * FROM USER.NAME
-
Any tables or views having the name “NAME” are searched for in the “USER” schema.
- If no tables or views having the name “NAME” exist, any PRIVATE synonym objects having the name are searched for in the “USER” schema.
- If no PRIVATE synonyms having the name exist, no PUBLIC synonym objects are searched for. Instead, an error is returned.
Examples#
\<Query> >Create a synonym called my_dept for the table dept, which is owned by the user altibase, in the current user's schema and execute some DML statements using the synonym.
iSQL> CONNECT altibase/altibase;
Connect success.
iSQL> CREATE TABLE dept
(
id integer,
name char(10),
location varchar(40),
member integer
);
Create success.
iSQL> GRANT INSERT ON dept TO mylee;
Grant success.
iSQL> GRANT SELECT ON dept TO mylee;
Grant success.
iSQL> CONNECT mylee/mylee;
Connect success.
iSQL> CREATE SYNONYM mylee.my_dept FOR altibase.dept;
Create success.
iSQL> INSERT INTO my_dept VALUES (1,'rndn1',NULL,4);
1 row inserted.
iSQL> SELECT * FROM my_dept;
MY_DEPT.ID MY_DEPT.NAME MY_DEPT.LOCATION
-------------------------------------------------------
MY_DEPT.MEMBER
-----------------
1 rndn1
4
1 row selected.
CREATE TABLE#
Syntax#
create_table ::=
table_constraint ::=, temporary_attributes_clause ::=, table_partitioning_clause, access_mode_clause ::=, physical_attributes_clause ::=, log_compression_clause ::=, logging_clause ::=, parallel_clause::=, table_compression_clause ::=, lob_column_properties ::=
column_definition ::=
encrypt_clause::=
variable_clause::=
in_row_clause::=
default_clause::=
column_constraint ::=
unique_clause ::=
unique_specification ::=
sort_order_clause ::=
directkey_clause ::=
using_index_clause ::=
index_attribute_clause ::=
memory_index_attributes ::=, disk_index_attributes ::=
references_clause::=
check_clause ::=
table_constraint ::=
table_unique_clause ::=
referential_constraint ::=
temporary_attributes_clause ::=
table_partitioning_clause ::=
range_partitioning ::=
partition_default_clause ::=
table_partition_description ::=
lob_column_properties ::=, access_mode_clause ::=
partition_range_clause ::=
table_partition_description ::=
hash_partitioning ::=
table_partition_description ::=
list_partitioning ::=
partition_list_clause ::=
range_partitioning_using_hash ::=
row_movement_clause ::=
access_mode_clause ::=
tablespace_clause ::=
physical_attributes_clause ::=
storage_clause ::=
log_compression_clause ::=
logging_clause ::=
parallel_clause::=
table_compression_clause ::=
lob_column_properties ::=
LOB_storage_clause ::=
lob_attributes ::=
Prerequisites#
At least one of the following conditions must be met:
- The SYS user
- Users have the CREATE TABLE or CREATE ANY TABLE system privilege in order to create table in their own schema
- Users have the CREATE ANY TABLE system privilege in order to create table in another user's schema.
Description#
This command is used to create a new table with the specified name.
[GLOBAL] TEMPORARY
[GLOBAL] TEMPORARY specifies the table as a temporary table. There is no difference whether or not GLOBAL is specified; it can be omitted. Table definitions created in this manner are visible to all sessions; however, temporary table data is private to the session that inserts data into the table.
When the user first creates a temporary table, only meta data of the table is stored in the data dictionary and space for table data is not allocated; table segment space is allocated when the first DML statement is operated on the table. Temporary table definitions persist in the database as do regular table definitions; however, table segments for temporary tables and all temporary table data are session-specific or transaction-specific. With the ON COMMIT keyword, the user can specify whether table segments and data are session-level or transaction-level. For more detailed information, refer to the temporary_attributes_clause below.
For session-specific temporary tables, DDL operations(ALTER TABLE, DROP TABLE, CREATE INDEX, etc) are permitted only if a session is not bound to the temporary table.
For transaction-specific temporary tables, DDL operations are permitted, regardless of whether or not a transaction is bound to the temporary table. However, Altibase internally commits before operating DDL statements; therefore, table data disappears after DDL statements are operated on temporary tables.
- Restrictions on temporary tables:
- Partitioning is impossible for temporary tables.
- Foreign keys cannot be specified for temporary tables.
- Only volatile tablespace can be specified for TABLESPACE of the lob_storage_clause for temporary tables.
- Temporary tables can only be stored on volatile tablespace.
- Distributed transactions are not supported for temporary tables.
user_name
This is used to set the owner of the table. If it is omitted, Altibase will create the table in the schema of the user connected via the current session.
tbl_name
This is used to specify the name of the table to be created. Refer to “Rules for Object Names” for more information on specifying names.
column_definition
- DEFAULT
If no DEFAULT clause is specified for a column, the initial value for each row in the column is NULL. - TIMESTAMP
A TIMESTAMP column is handled like other data types in many respects. When the data type of a column is specified as TIMESTAMP in a CREATE TABLE statement, a TIMESTAMP value having a size of 8 bytes is generated internally. However, because the value of a TIMESTAMP column is determined by the system, no DEFAULT value can be expressly specified. Furthermore, only one TIMESTAMP column can be created for one table
column_constraint
This is used to specify the constraint for a column when a new table is created. A constraint name can be expressly specified by the user. The LOCALUNIQUE constraint is intended for use with partitioned tables.
- PRIMARY KEY
The value(s) on which a primary key is based must be unique in the table. Additionally, none of the columns which a primary key is based can contain NULL values. Only one primary key can be defined in each table. A primary key can be created on the basis of up to 32 columns.
- UNIQUE
A UNIQUE constraint prohibits multiple rows from having the same value in the same column (or combination of columns). However, NULL values are allowed.
A unique constraint and a primary key constraint cannot both be defined for the same column or combination of columns in one table. Additionally, only one unique constraint can be defined for a column or combination of columns. However, these limitations do not pertain to other columns or combinations of columns within the same table. A unique constraint can be created for a combination of up to 32 columns.
- LOCALUNIQUE
This keyword specifies that each local index must satisfy the UNIQUE constraint.
- (NOT) NULL
This keyword specifies that the corresponding column can (cannot) contain NULL values.
- CHECK
This clause specifies an integrity rule that applies to the target column. Only the target column can be referenced within the condition of the column_constraint clause.
The following limitations concern the conditions of CHECK constraints:
- Subqueries, sequences, all pseudo columns, such as LEVEL, ROWNUM, etc., and non-deterministic SQL functions, such as SYSDATE, USER_ID, etc., cannot be included.
- The PRIOR operator cannot be used.
- LOB type data cannot be used.
- Referential integrity
- TIMESTAMP
directkey_clause
Creates a direct key index. For more detailed information about direct key indexes, please refer to CREATE INDEX.
check_clause
This clause specifies the condition that the value for each record of the table must satisfy. The results of the condition must be evaluated to either TRUE, FALSE or NULL.
This clause is valid as the column constraint or the table constraint.
table_constraint
This is used to specify the constraint for combination of columns or one column. The following table constraints exist:
- PRIMARY KEY
- UNIQUE
- LOCALUNIQUE
- CHECK
- Referential integrity
using_index_clause
This is used to specify the tablespace in which to store an index that is created to support a constraint.
If any of the PRIMARY KEY, UNIQUE or LOCALUNIQUE constraints are specified, the tablespace in which to store the index of the local index for each index partition can be specified. For more information, please refer to index_partitioning_clause in the description of the CREATE INDEX statement
references_clause
This clause is used to define a foreign key. The referenced key, that is, the key that resides in another table and is referenced by a foreign key, must either have the UNIQUE constraint applied to it, or be the PRIMARY KEY for the table in which it resides. If the columns of a referenced key are not specified, the primary key for that table is automatically taken as the referenced key.
- NO ACTION
This is the default behavior for checking referential integrity.
Normally, when an INSERT, UPDATE, or DELETE operation is performed on a so-called “parent table”, that is, a table that contains a referenced key, the operation is performed only after an integrity check is performed on any so-called “child tables”, that is, tables containing foreign keys that reference the referenced key. The NO ACTION option prevents parent rows from being altered if integrity checking fails, and outputs an error instead.
In this example, when an attempt is made to delete a department from the departments table, if the department code is referenced by a record in the employees table, the delete attempt will fail and an error will be raised.
CREATE TABLE employees (
ENO INTEGER PRIMARY KEY,
DNO INTEGER,
NAME CHAR(10),
FOREIGN KEY(DNO) REFERENCES
departments(DNO) ON DELETE NO ACTION );
- ON DELETE CASCADE
This option stipulates that if a row in the parent table is deleted, all rows in child tables that have foreign keys that reference this row will also be deleted.
For example, if the table employees is created as follows, when a department from the table departments is tried to be deleted, all rows referencing this department number in the table employees are also deleted.
CREATE TABLE employees (
ENO INTEGER PRIMARY KEY,
DNO INTEGER,
NAME CHAR(10),
FOREIGN KEY(DNO) REFERENCES
departments (DNO) ON DELETE CASCADE );
- ON DELETE SET NULL
This option specifies for every foreign key column value in the child table referencing a row in the parent table to be set to NULL, if the given row is deleted.
In order to enforce the referential integrity of this option, the target column must be nullable.
For example, consider the case where the user creates the table employees that references the table departments and then deletes a certain department from the table departments. All the column values of the table employees referencing the deleted department number will be modified to NULL.
CREATE TABLE employees (
ENO INTEGER PRIMARY KEY,
DNO SMALLINT,
NAME CHAR(10),
CONSTRAINT dno_fk FOREIGN KEY (dno) REFERENCES
departments (dno) ON DELETE SET NULL );
MAXROWS
This is used to specify the maximum number of records that can be entered into a table. If an attempt is made to insert records such that the total number of records would be more than that specified using MAXROWS, the insert attempt will fail and an error will be returned. The MAXROWS clause cannot be specified with the table_partitioning_clause clause.
temporary_attributes_clause
This clause specifies whether the temporary table is transaction-specific or session-specific. The following two options are available:
ON COMMIT DELETE ROWS
This creates a transaction-specific temporary table. The transaction that first inserts data into the temporary table is bound to the temporary table. Transaction-level binding is unbound with the execution of the COMMIT or ROLLBACK statement. If the transaction is committed, Altibase truncates the temporary table.
ON COMMIT PRESERVE ROWS
This creates a session-specific temporary table. A session is bound to the temporary table when data is first inserted into the temporary table in the session. This binding is unbound when the session is terminated or TRUNCATE is operated on the table in the session. If the user terminates the session, Altibase truncates the session bound temporary table.
table_partitioning_clause
This is used to create a partitioned table. A partitioned table can be range-partitioned, hash-partitioned or list-partitioned. row_movement_clause can also be specified when a partitioned table is created.
range_partitioning
This specifies that the table will be partitioned based on ranges of partition key values. It is primarily used with the DATE data type. Because the table is partitioned based on user-specified values, there is no guarantee that the data will be uniformly distributed among the partitions. The range of each partition is determined by setting the maximum value of its range.
Any values exceeding all of the specified ranges, along with any NULL values, will be saved in the default partition. The default partition clause cannot be omitted. A partition key can be defined on the basis of multiple columns.
table_partition_description
The tablespace for a partition can be specified. Additionally, if the table contains one or more LOB columns, the attributes for each LOB column can be specified separately. The data access mode for the partition can also be set.
If the tablespace statement is omitted, the partition will be stored in the default tablespace for the table.
Additionally, if the tablespace in which to store a LOB column is not specified, the LOB data will be stored in the tablespace for the partition.
In the following example, the default tablespace for the user is tbs_05.
CREATE TABLE print_media_demo
(
product_id INTEGER,
ad_photo BLOB,
ad_print BLOB,
ad_composite BLOB
)
PARTITION BY RANGE (product_id)
(
PARTITION p1 VALUES LESS THAN (3000) TABLESPACE tbs_01
LOB (ad_photo) STORE AS (TABLESPACE tbs_02 ),
PARTITION p2 VALUES DEFAULT
LOB (ad_composite) STORE AS (TABLESPACE tbs_03)
) TABLESPACE tbs_04;
Partition p1 will be stored in the tbs_01 tablespace because this was expressly specified. However, the ad_photo column for this partition will be stored in the tbs_02 tablespace. Because no tablespace was specified for partition p2, which is the default partition, it will be stored in tablespace tbs_04, where table T1 resides. If no tablespace for the table is specified either, it will be stored in the default tablespace, which is tbs_05.
This is illustrated in the following diagram:
partition_range_clause
This is used to specify the noninclusive upper limit for a range partition. This value must not be set to the same value as that of any other partition.
hash_partitioning
This specifies that the table will be partitioned based on hash values corresponding to partition key values. This partitioning scheme is suitable for situations in which the data must be distributed uniformly among the partitions. A partition key can be defined on the basis of multiple columns.
list_partitioning
This specifies that the table will be partitioned based on sets of values. The default partition cannot be omitted because any values not specified as belonging to another partition are automatically included in this partition.
When a new partition is defined, the values specified as belonging to that partition are removed from the default partition. This is because values cannot be specified as belonging to more than one partition. Additionally, the partition key for a list-partitioned table can be defined only on the basis of a single column.
partition_list_clause
The list that defines each list partition must comprise at least one value. A value in one list must not be present in any other list.
range_partitioning_using_hash
This specifies the range based on the hash values corresponding to the partition key values. Partition keys can be defined with single column and the range is specified by the value of the remainder(mod) of the hash value when divided by 1000. 1000 is a fixed value. It is a partitioning method combining the advantage of hash partitioning which distributes data evenly and the advantage of range partitioning which can merge and split data.
row_movement_clause
When a record in a partitioned table is updated in a way that changes the data in a column on which the partition key is defined such that the record (row) must be moved to another partition, this clause determines whether to move the record automatically or raise an error. If this clause is omitted, the DISABLE ROW MOVEMENT option (i.e. raise an error) is set by default.
CREATE TABLE … AS SELECT
When creating a table, to copy column attributes and data from other tables into the new table, use the CREATE TABLE ... AS SELECT statement. The number of columns in the new table cannot be set differently from the number of columns retrieved by the AS SELECT clause. Additionally, the data types of the new columns cannot be expressly set, as they are set the same as the original columns from which the data are retrieved.
If no column names are specified for the new table, the names of the original columns will be used as the column names for the new table. If the name of the search target is in the form of an expression, an alias must be provided. This alias will becomes the name of the column in the new table.
access_mode_clause
This sets the data access mode. A mode can be chosen among Read-Only, Read/Write and Read/Append modes and on omission, the Read/Write mode is set by default.
Note: Even if the data access mode for the table or partition is set to ‘Read-Only’ or ‘Read/Append’, copying data through replication, TRUNCATE statement execution, and LOB column alteration are allowed
tablespace_clause
This clause is used to set the tablespace in which to save the table.
If this clause is omitted, the table will be saved in the DEFAULT TABLESPACE of the user in whose schema the table is being created. The user's DEFAULT TABLESPACE was specified when the user was created. If no DEFAULT TABLESPACE has been specified for the user, the table will be created in the SYSTEM MEMORY DEFAULT TABLESPACE.
If a UNIQUE or PRIMARY KEY constraint is specified in the CREATE TABLE statement, the index supporting the constraint will be saved in the tablespace in which the table is saved.
physical_attributes_clause
This clause is used to specify the PCTFREE, PCTUSED, INITRANS, and MAXTRANS. If this clause is specified for a partitioned table, the PCTFREE and PCTUSED values will apply to all of the partitions in the table.
- PCTFREE Clause
This is used to specify the amount of free space that is reserved for use in updating records that have already been saved in a page. Additional records can only be saved into the portion of the page that is not reserved in this way. This value represents the percentage of free space in the page.
For example, for a table in which PCTFREE is set to 20, records can only be inserted into 80% of the space in each page, and the remaining 20% of the page is reserved for use in updating existing records. This value is only meaningful for disk-based tables.
This option must be set to an integer value ranging from 0 to 99, representing the percentage. If this value is not set, the default PCTFREE value is 10. This option only applies to pages that have been assigned to tables - PCTUSED Clause
This is the threshold below which the amount of used space in a page must decrease in order for the page to return to the state in which records can be saved in it again. When the amount of free space in a page falls below the percentage specified in PCTFREE, it becomes impossible to save new records in the page. At this time it is permissible only to update and delete existing records. Once subsequent update or delete operations reduce the percentage of used space in the page below the threshold specified by PCTUSED, it becomes possible to save new records in the page again.
For example, assuming that PCTUSED has been set to 40, once the percentage of unused space in a page has decreased below the limit specified using PCTFREE (i.e. when the percentage of used space increases beyond 100 - PCTFREE), no more records are saved in that page until the percentage of used space falls to 39%. In other words, new records can be saved in the page only after the percentage of used space falls below 40%. This option only applies to disk-based tables.
This option must be set to an integer value ranging from 0 to 99, representing the percentage. If this value is not set, the default PCTUSED value is 40. This option only applies to pages that have been assigned to tables. - INITRANS Clause
This clause is used to set the initial number of TTS (Touched Transaction Slots). The default value is 2. - MAXTRANS Clause
This clause is used to set the maximum number of TTS (Touched Transaction Slots), to which the number of TTS can increase. The default value is 120.
Note:
PCTFREE and PCTUSED are used together to optimize performance as follows. In this example, assume that PCTFREE has been set to 20 and PCTUSED to 40.
20% of each page that is allocated to a table is reserved for use in updating existing records. New records can only be saved in the page until the remaining 80% of the space in the page has been filled.
At this point, no more new records can be saved in the page. The only operations that can be performed are update and delete operations on records that already exist in the page. 20% of the page has been reserved for update operation. If enough records are deleted for the amount of used space in the page to fall below 40%, it becomes possible to save new records in the page again.
The values of PCTFREE and PCTUSED are used in this way to determine in a cyclical manner how the space in pages is used.
storage_clause
This clause is used to set storage parameters for managing extents in segments.
- INITEXTENTS Clause
This is used to set the number of extents that are initially allocated when a segment is created. If this is not specified, one extent is allocated by default. - NEXTEXTENTS Clause
This is used to set the number of extents that are added to a segment every time the segment is increased in size. If this is not specified, the default value is 1. - MINEXTENTS Clause
This is used to set the minimum number of extents in a segment. If this is not specified, the default value is 1. - MAXEXTENTS Clause
This is used to set the maximum number of extents in a segment. If this is not specified, there is no upper limit.
LOB_storage_clause
n a disk table, LOB column data can be stored in a tablespace other than that in which the table containing the LOB column is stored. However, in a memory table, LOB column data cannot be stored separately from the rest of the table; that is, they can only be stored in the same tablespace as the table.
parallel_clause
This is used to specify the number of threads which execute parallel queries. Omitting this clause is equivalent to specifying NOPARALLEL.
- NOPARALLEL : Does not execute queries in parallel.
- PARALLEL integer : Specification of integer indicates the number of threads to execute queries in parallel. A value from 1~65535 is valid for specification. PARALLEL 1 is equivalent to NOPARALLEL.
Currently, Altibase only support the following parallel queries:
- Parallel queries which scan partitioned tables.
- Parallel queries with HASH, SORT, GRAG nodes in their execution plans. For such nodes, however, only one parallel worker thread is created per node.
table_compression_clause
This specifies each of the names of the columns to be compressed with the use of commas. The maximum number of rows that can be inserted to the dictionary table, which is automatically generated for each compressed column, is specified in the MAXROWS clause. On omission, the default value is the same as for normal tables, 2664-1.
The execution of table creation and data insertion in one statement by specifying this clause and subquery in the CREATE TABLE statement is not supported.
The following table shows the data types valid for compression and the minimum size for each type.
| Data Type | Minimum Size |
|---|---|
| CHAR, VARCHAR, BYTE | 6 |
| NCHAR, NVARCHAR (UTF-8) | 6 |
| NCHAR, NVARCHAR (UTF-16) | 3 |
| NIBBLE | 13 |
| BIT, VARBIT | 25 |
| DATE |
Consideration#
Here are some considerations to keep in mind when creating a table:
- If columns are created larger than their maximum allowable size or smaller than their minimum allowable size, an error occurs. The maximum and minimum sizes vary depending on the data type.
- The maximum number of columns in one table is 1024.
- A maximum of one primary key can be defined for a table.
- For a foreign key constraint, the foreign key and the referenced key must have the same number of columns. For a foreign key constraint, corresponding columns in the foreign key and the referenced key must have the same data types.
- The total number of indexes, primary keys and unique keys cannot exceed 1024.
- When executing a CREATE TABLE ... AS SELECT statement, if the names of the columns to create are specified, the number column names must be the same as the number of columns retrieved using the AS SELECT clause.
- When executing a CREATE TABLE ... AS SELECT statement, when the column name is not specified in the CREATE TABLE statement and the name of the column to be retrieved is provided in the form of an expression, an alias name must be specified for the purpose of determining the name of the column in the new table.
- The MAXROWS clause is not supported for use with partitioned tables.
- For range- and hash-partitioned tables, up to 32 columns can be specified as partition key columns. (This is the same as the upper limit on the number of index columns when an index is created.)
- In the event of a system or media fault, the consistency of an index that was created using the NOLOGGING (FORCE/NOFORCE) option cannot be guaranteed. After an index becomes inconsistent, the error message indicating that the index is inconsistent will be raised when the index is accessed. To fix this error, locate the inconsistent index, drop it, and create it again. The consistency of an index can be checked using the V$DISK_BTREE_HEADER performance view.
- Just as when executing the CREATE INDEX statement, the tablespace in which a local partitioned index is saved cannot be specified.
- The CHECK constraint cannot be specified for the CREATE TABLE … AS SELECT statement. • Columns with the PRIMARY KEY, UNIQUE and TIME STAMP constraints cannot be compressed.
Examples#
Creating Tables#
Create the following tables.
-
Table name: employees Columns: employee number, employee first and last name, position, telephone number, department number, salary, gender, birthday, hiring date, and status.
iSQL> CREATE TABLE employees( eno INTEGER PRIMARY KEY, e_lastname CHAR(20) NOT NULL, e_firstname CHAR(20) NOT NULL, emp_job VARCHAR(15), emp_tel CHAR(15), dno SMALLINT, salary NUMBER(10,2) DEFAULT 0, sex CHAR(1) CHECK(sex IN ('M', 'F')), birth CHAR(6), join_date DATE, status CHAR(1) DEFAULT 'H'); Create success. -
Table name: orders Columns: order number, order date, salesperson, customer number, product number, quantity, estimated delivery date, and status.
iSQL> CREATE TABLE orders( ono BIGINT, order_date DATE, eno INTEGER NOT NULL, cno BIGINT NOT NULL, gno CHAR(10) NOT NULL, qty INTEGER DEFAULT 1, arrival_date DATE, processing CHAR(1) DEFAULT '0', PRIMARY KEY(ono, order_date)); Create success. -
Using CREATE TABLE ... AS SELECT Create a new table called dept_1002 and copy the column attributes and data that meet the condition shown from the employees table.
iSQL> CREATE TABLE dept_1002 AS SELECT * FROM employees WHERE dno = 1002; Create success. -
Create a table that has a TIMESTAMP type column.
iSQL> CREATE TABLE tbl_timestamp(
i1 TIMESTAMP CONSTRAINT const2 PRIMARY KEY,
i2 INTEGER,
i3 DATE,
i4 Byte(8));
Create success.
The attributes of the table tbl_timestamp are as shown below.
[ TABLESPACE : SYS_TBS_MEM_DATA ]
[ ATTRIBUTE ]
------------------------------------------------------------------------------
NAME TYPE IS NULL
------------------------------------------------------------------------------
I1 TIMESTAMP FIXED NOT NULL
I2 INTEGER FIXED
I3 DATE FIXED
I4 BYTE(8) FIXED
[ INDEX ]
------------------------------------------------------------------------------
NAME TYPE IS UNIQUE COLUMN
------------------------------------------------------------------------------
CONST2 BTREE UNIQUE I1 ASC
[ PRIMARY KEY ]
------------------------------------------------------------------------------
I1
The way to distinguish between i4, which explicitly declares the Byte (8) data type, and i1, which is a TIMESTAMP data type column, is to check whether the column type is TIMESTAMP by querying the SYS_CONSTRAINTS_ and SYS_CONSTRAINT_COLUMNS_ meta tables.
Note: If the DEFAULT keyword is used for a timestamp column when performing an INSERT or UPDATE operation, the system time at which the operation is performed will be written to the TIMESTAMP column.
iSQL> INSERT INTO tbl_timestamp VALUES(DEFAULT, 2, '02-FEB-01', Byte'A1111002');
1 row inserted.
iSQL> UPDATE tbl_timestamp SET i1 = DEFAULT, i2 = 102, i3 = '02-FEB-02', i4 = Byte'B1111002' WHERE i2 = 2;
1 row updated.
iSQL> SELECT * FROM tbl_timestamp;
I1 I2 I3 I4
------------------------------------------------------------------
4E3778C900037AE9 102 02-FEB-2002 B111100200000000
1 row selected.
Similarly, if the user does not specify a TIMESTAMP value when performing an INSERT or UPDATE operation on a TIMESTAMP column, the system time at which the operation is performed will be used to perform the INSERT or UPDATE operation.
iSQL> INSERT INTO tbl_timestamp(i2, i3, i4) VALUES(4, '02-APR-01', Byte'C1111002');
1 row inserted.
iSQL> UPDATE tbl_timestamp SET i2=104, i3='02-APR-02', i4=BYTE'D1111002' WHERE i2=4;
1 row updated.
iSQL> SELECT * FROM tbl_timestamp;
I1 I2 I3 I4
------------------------------------------------------------------
4E3778C900037AE9 102 02-FEB-2002 B111100200000000
4E37794900083702 104 02-APR-2002 D111100200000000
2 rows selected
- Using and Specifying Temporary Tables
\<Query> After creating a temporary table and inserting data in a session, the data is retrieved in that session and no data is retrieved in the other sessions.
iSQL> create volatile tablespace my_vol_tbs size 12M autoextend on maxsize 1G;
Create success.
iSQL> create temporary table t1(i1 integer, i2 varchar(10)) on commit delete rows tablespace my_vol_tbs;
Create success.
iSQL> create temporary table t2(i1 integer, i2 varchar(10)) on commit preserve rows tablespace my_vol_tbs;
Create success.
iSQL> desc t2;
[ TABLESPACE : MY_VOL_TBS ]
[ ATTRIBUTE ]
------------------------------------------------------------------------------
NAME TYPE IS NULL
------------------------------------------------------------------------------
I1 INTEGER FIXED
I2 VARCHAR(10) FIXED
T2 has no index
T2 has no primary key
iSQL> alter table t2 add constraint t2_pk primary key (i1);
Alter success.
iSQL> insert into t2 values (1, 'abc');
1 row inserted.
iSQL> insert into t2 values (2, 'def');
1 row inserted.
iSQL> select * from t2;
I1 I2
---------------------------
1 abc
2 def
2 rows selected.
iSQL> connect sys/manager;
Connect success.
iSQL> select * from t2;
I1 I2
---------------------------
No rows selected.
- Specifying Tablespaces for Index Partitions
\<Query>Create table tbl1 in the user uare1's schema. (Assume that no default tablespace was specified when this user was created.)
iSQL> CONNECT uare1/rose1;
Connect success.
iSQL> CREATE TABLE tbl1(
i1 INTEGER,
i2 VARCHAR(3));
Create success.
Note: The table will be created in the system memory default tablespace when no default tablespace has been defined for the user.
\<Query> Create the books and inventory tables in the user_data tablespace, which is the default tablespace for the user.
books columns: book number, book name, author, edition, publication year, price, and publication code (can contain a maximum of two rows).
inventory Columns: subscription number, book number, store code, purchase date, quantity, and a character to indicate whether the item has been paid for.
``` iSQL> CREATE TABLE books( isbn CHAR(10) CONSTRAINT const1 PRIMARY KEY, title VARCHAR(50), author VARCHAR(30), edition INTEGER DEFAULT 1, publishingyear INTEGER, price NUMBER(10,2), pubcode CHAR(4)) MAXROWS 2 TABLESPACE user_data; Create success.
iSQL> CREATE TABLE inventory( subscriptionid CHAR(10) PRIMARY KEY, isbn CHAR(10) CONSTRAINT fk_isbn REFERENCES books (isbn), storecode CHAR(4), purchasedate DATE, quantity INTEGER, paid CHAR(1)) TABLESPACE user_data; Create success.
Or
- Creating a Table with a Direct Key Index
\<Query> Create an id(INTEGER) column in the tab1 table with the UNIQUE constraint and create a direct key index on it.
iSQL> CREATE TABLE tab1 (id INTEGER UNIQUE DIRECTKEY );
Create success.
- Specifying Tablespaces for Index Partitions
\<Query>Create the partitioned table T1 having the UNIQUE constraint on column I1.
CREATE TABLE T1
(
I1 INTEGER UNIQUE USING INDEX LOCAL
(
PARTITION P1_UNIQUE ON P1 TABLESPACE TBS3,
PARTITION P2_UNIQUE ON P2 TABLESPACE TBS2,
PARTITION P3_UNIQUE ON P3 TABLESPACE TBS1
)
)
PARTITION BY RANGE (I1)
(
PARTITION P1 VALUES LESS THAN (100),
PARTITION P2 VALUES LESS THAN (200) TABLESPACE MEM_TBS1,
PARTITION P3 VALUES DEFAULT TABLESPACE MEM_TBS2
) TABLESPACE SYS_TBS_DISK_DATA;
- Range Partitioning
\<Query 1> Create the table range_sales, partitioning the year 2006 into respective quarters as shown below.
CREATE TABLE range_sales
(
prod_id NUMBER(6),
cust_id NUMBER,
time_id DATE
)
PARTITION BY RANGE (time_id)
(
PARTITION Q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006')),
PARTITION Q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006')),
PARTITION Q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006')),
PARTITION Q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007')),
PARTITION DEF VALUES DEFAULT
) TABLESPACE SYS_TBS_DISK_DATA;
\<Query 2> Create a partitioned table, specifying the tablespace for some of the partitions.
CREATE TABLE T1
(
I1 INTEGER,
I2 INTEGER
)
PARTITION BY RANGE (I1)
(
PARTITION P1 VALUES LESS THAN (100),
PARTITION P2 VALUES LESS THAN (200) TABLESPACE TBS1,
PARTITION P3 VALUES DEFAULT TABLESPACE TBS2
) TABLESPACE SYS_TBS_DISK_DATA
\<Query 3> Create a partitioned table in which multiple columns are used as the partition key.
CREATE TABLE T1
(
I1 DATE,
I2 INTEGER
)
PARTITION BY RANGE (I1, I2)
(
PARTITION P1 VALUES LESS THAN (TO_DATE('01-JUL-2006'), 100),
PARTITION P2 VALUES LESS THAN (TO_DATE('01-JAN-2007'), 200),
PARTITION P3 VALUES DEFAULT
) TABLESPACE SYS_TBS_DISK_DATA;
\<Query 4> Create a partitioned table in which the data is moved automatically when required.
CREATE TABLE T1
(
I1 INTEGER,
I2 INTEGER
)
PARTITION BY LIST (I1)
(
PARTITION P1 VALUES (100, 200),
PARTITION P2 VALUES (150, 250),
PARTITION P3 VALUES DEFAULT
) ENABLE ROW MOVEMENT TABLESPACE SYS_TBS_DISK_DATA;
- List Partitioning
\<Query> Create the table list_customers, which is list-partitioned on the basis of the nls_territory column into the asia partition for the values 'CHINA' and 'THAILAND', the Europe partition for the values 'GERMANY', 'ITALY' and 'SWITZERLAND', the west partition for the value 'AMERICA', the east partition for the value 'INDIA', and the default partition for any other values.
CREATE TABLE list_customers
(
customer_id NUMBER(6),
cust_first_name VARCHAR(20),
cust_last_name VARCHAR(20),
nls_territory VARCHAR(30),
cust_email VARCHAR(30)
)
PARTITION BY LIST (nls_territory)
(
PARTITION asia VALUES ('CHINA', 'THAILAND'),
PARTITION europe VALUES ('GERMANY', 'ITALY', 'SWITZERLAND'),
PARTITION west VALUES ('AMERICA'),
PARTITION east VALUES ('INDIA'),
PARTITION rest VALUES DEFAULT
) TABLESPACE SYS_TBS_DISK_DATA;
- Hash Partitioning
\<Query> Create a table that is hash-partitioned into 4 partitions based on product_id.
CREATE TABLE hash_products
(
product_id NUMBER(6),
product_name VARCHAR(50),
product_description VARCHAR(2000)
)
PARTITION BY HASH (product_id)
(
PARTITION p1,
PARTITION p2,
PARTITION p3,
PARTITION p4
) TABLESPACE SYS_TBS_DISK_DATA;
\<Query> Create a table in which the LOB data is stored in separate tablespaces; specifically, in which the LOB data in the image1 column is stored in the lob_data1 tablespace and the LOB data in the image2 column is stored in the lob_data2 tablespace.
CREATE TABLE lob_products
(
product_id integer,
image1 BLOB,
image2 BLOB
) TABLESPACE SYS_TBS_DISK_DATA
LOB(image1) STORE AS ( TABLESPACE lob_data1 )
LOB(image2) STORE AS ( TABLESPACE lob_data2 );
- Creating a Table in which the Extents in the Segment are Managed
<Query> Create the table local_tbl in the usertbs disk tablespace. Allocate 10 extents to the table when it is created and specify that 1 extent is to be added whenever the size of the table needs to be increased.
iSQL> CREATE TABLE local_tbl (i1 INTEGER, i2 VARCHAR(32) )
TABLESPACE usertbs
STORAGE ( INITEXTENTS 10 NEXTEXTENTS 1 );
Create success.
\<Query> Create the table local_tbl in the usertbs disk tablespace. Specify that the minimum number of extents in the table is 3, which is the same number that are allocated to the table when it is created, and limit the maximum number of extents to 100.
iSQL> CREATE TABLE local_tbl ( i1 INTEGER, i2 VARCHAR(32) )
TABLESPACE usertbs
STORAGE ( INITEXTENTS 3 MINEXTENTS 3 MAXEXTENTS 100 );
Create success.
CREATE DISK TABLESPACE#
Syntax#
create_disk_tablespace ::=
datafile_spec ::=
autoextend_clause ::=
maxsize_clause ::=
Prerequisites#
Only the SYS user and users having the CREATE TABLESPACE system privilege can create tablespace
Description#
The CREATE DISK TABLESPACE statement is used to create a disk tablespace, in which database objects can be permanently stored within the database. Tablespaces created using this command can be used to hold tables and indexes.
DISK
This keyword is used to specify that the tablespace to be created will be a disk tablespace. A disk tablespace is created even when the CREATE TABLESPACE statement is executed without the DISK keyword.
DATA
This keyword is used to specify that the tablespace to be created will be used to store user data. A data tablespace is created even when the CREATE TABLESPACE statement is executed without the DATA keyword.
tablespace_name
This is used to specify the name of the tablespace to be created. Refer to “Rules for Object Names” in Chapter 2 for more information on specifying names.
datafile_spec
This is used to specify the attributes of the data file constituting the tablespace.
EXTENTSIZE Clause
This is used to specify the size of an extent, which is a collection of pages. This cannot be changed after the tablespace is created. The default unit used to specify the size of an extent is kB (kilobytes, expressed as “K“), but it is also permissible to use MB (megabytes, expressed as “M“) or GB (gigabytes, expressed as “G“).
If size of the extents in the tablespace is not specified, the extents will be the default size, which is 64 times the size of a single page. When specifying the size of the extents in the tablespace, the extent size must be set to a multiple of the size of a single page. If the extent size is set to a value other than a multiple of the page size, the extent size will be rounded to the closest value internally so that it is a multiple of the page size.
Additionally, the size of an extent must be at least five times the size of a single page. In other words, because the size of a single page is 8kB, the extent size must be set to at least 40kB.
SEGMENT MANAGEMENT Clause
This is used to specify how segments are to be managed in the disk tablespace to be created. This clause is optional. If this option is not specified, segments in the newly created disk space will be managed according to the setting of the DEFAULT_SEGMENT_MANAGEMENT_TYPE property in the altibase.properties file. (The default value for this property is AUTO.)
- MANUAL : This specifies that segments are created on the basis of a so-called “free list“ method of managing available space in the user tablespace.
- AUTO : This specifies that segments are created on the basis of a so-called “bitmap index“ method of managing available space in the user tablespace.
file_name
This is used to specify the absolute path and name of the data file to be created.
SIZE Clause
This is used to specify the size of the data file. If this clause is omitted, the data file will be the default size, which is 100 megabytes. This default file size can be changed by setting the USER_DATA_FILE_INIT_SIZE property as desired.
The size is specified by providing an integer followed by one of the following units: kB (kilobytes, expressed as “K”), MB (megabytes, expressed as “M”) or GB (gigabytes, expressed as “G”). If no units are specified, the default unit is kilobytes.
REUSE
This is used to specify whether or not to reuse an existing data file. If a file with the name specified in file_name exists, the REUSE option must be specified. Note however that if an existing file is reused, the original contents of the file will be lost, so care must be taken in order to prevent data loss.
If the REUSE option is specified but no file with the name specified in file_name exists, this option will be ignored, and a new file will be created.
autoextend_clause
This is used to specify whether to automatically increase the size of the data file when it fills up, and the maximum size to which it can increase. If this clause is omitted, AUTOEXTEND is disabled by default.
ON
This enables the AUTOEXTEND option for the file.
OFF
This disables the AUTOEXTEND option for the file.
NEXT
This is used to specify the amount by which the size of the file will increase when it is automatically increased in size.
If AUTOEXTEND is enabled but this value is not set, the default NEXT value is the value set in the USER_DATA_FILE_NEXT_SIZE property in the altibase.properties file.
The size can be specified in kB (kilobytes, expressed as “K”), MB (megabytes, expressed as “M”) or GB (gigabytes, expressed as “G”). If no units are specified, the default unit is kilobytes.
maxsize_clause
This is used to specify the maximum size to which the data file can increase. If AUTOEXTEND is enabled but this value is not set, the default is the value set using the USER_DATA_FILE_MAX_SIZE property in the altibase.properties file.
It can be expressed in kB (kilobytes, expressed as “K”), MB (megabytes, expressed as “M”) or GB (gigabytes, expressed as “G”). If no units are specified, the default unit is kilobytes.
UNLIMITED
This is used to indicate that there is no upper limit to the size to which the file can increase. If this option is used, the actual maximum size of the file will be determined by the operating system or by the amount of available space in the file system.
Examples#
\<Query> Create the user_data tablespace, comprising three data files. Specify that segments are to be managed using the “free list” method.
iSQL> CREATE TABLESPACE user_data
DATAFILE '/tmp/tbs1.user' SIZE 10M,
'/tmp/tbs2.user' SIZE 10M,
'/tmp/tbs3.user' SIZE 10M
SEGMENT MANAGEMENT MANUAL;
Create success.
\<Query> Create the user_data tablespace, which has an initial size of 10MB, consists of the tbs.user data file (in which the tables and indexes in this tablespace will be stored), and extends automatically.
iSQL> CREATE TABLESPACE user_data DATAFILE '/tmp/tbs.user' SIZE 10M AUTOEXTEND
ON;
Create success.
\<Query> Create the user_data tablespace, which can increase in size up to 100MB in 500kB increments.
iSQL> CREATE TABLESPACE user_data
DATAFILE '/tmp/tbs.user' SIZE 500K REUSE
AUTOEXTEND ON NEXT 500K MAXSIZE 100M;
Create success.
\<Query> Create the user_data tablespace comprising the tbs.user data file, which does not automatically increase in size.
iSQL> CREATE TABLESPACE user_data
DATAFILE '/tmp/tbs.user' AUTOEXTEND OFF;
Create success.
CREATE MEMORY TABLESPACE#
Syntax#
create_memory_tablespace ::=
initsize_clause ::=
autoextend_clause ::=
maxsize_clause ::=
checkpoint_path_clause ::=
splitsize_clause ::=
Prerequisites#
Only the SYS user and users having the CREATE TABLESPACE system privilege can create tablespaces.
Description#
The CREATE MEMORY TABLESPACE statement is used to create a memory data tablespace, in which database objects can be stored within the database. Tablespaces created using this command can be used to hold memory tables.
MEMORY
This keyword is used to specify that the tablespace to be created will be a memory tablespace.
DATA
This keyword is used to specify that the tablespace to be created will be used to store user data. A data tablespace is created even when the CREATE TABLESPACE statement is executed without the DATA keyword.
tablespace_name
This is used to specify the name of the tablespace to create. Refer to “Rules for Object Names” in Chapter 2 for more information on specifying names.
initsize_clause
This is used to specify the initial size of the tablespace to create.
SIZE
This is used to specify the initial size of the tablespace. The initial size of a memory tablespace must be a multiple of the default allocation size. (i.e. the number of pages specified in the EXPAND_CHUNK_PAGE_COUNT property * the size of one page in memory tablespace (32kB)).
For example, if the EXPAND_CHUNK_PAGE_COUNT property is set to 128, the default allocation size would be 128 * 32 = 4MB. Therefore, the initial size must be set to a multiple of 4MB.
The size can be specified in kB (kilobytes, expressed as “K”), MB (megabytes, expressed as “M”) or GB (gigabytes, expressed as “G”). If no units are specified, the default unit is kilobytes.
autoextend_clause
This is used to specify whether the tablespace automatically increases in size when it fills up, and the maximum size to which it can increase. If this clause is omitted, AUTOEXTEND is disabled by default.
ON
This enables the AUTOEXTEND option.
OFF
This disables the AUTOEXTEND option. This is the default.
NEXT
This is used to specify the increment by which the tablespace increases in size when it is automatically increased in size.
Note that this size must be a multiple of the default allocation size (the number of pages specified in the EXPAND_CHUNK_PAGE_COUNT property * the size of one page in memory tablespace (32kB)).
If AUTOEXTEND is enabled but this value is not set, the default is the value set using the EXPAND_CHUNK_PAGE_COUNT property in the altibase.properties file
If AUTOEXTEND is OFF, this value is irrelevant.
The size can be specified in kB (kilobytes, expressed as “K”), MB (megabytes, expressed as “M”) or GB (gigabytes, expressed as “G”). If no units are specified, the default unit is kilobytes.
maxsize_clause
This is used to specify the maximum size to which a tablespace can increase when it automatically increases in size. If AUTOEXTEND is enabled but this value is not set, the default value is UNLIMITED.
If AUTOEXTEND is OFF, this value is meaningless.
The size can be specified in kB (kilobytes, expressed as “K”), MB (megabytes, expressed as “M”) or GB (gigabytes, expressed as “G”). If no units are specified, the default unit is kilobytes.
UNLIMITED
This is used to indicate that there is no upper limit to the size to which the tablespace can increase.
If this option is used, the tablespace will automatically increase in size up to the point at which the total size of all memory tablespaces and all volatile tablespaces in the system reaches the size specified in the MEM_MAX_DB_SIZE property in the altibase.properties file.
checkpoint_path_clause
To ensure the durability of the data in memory tablespaces, the data must be saved in files. These memory tablespace data storage files are known as “checkpoint images”.
The checkpoint_path clause is used to specify the checkpoint path, that is, the path and directory where these image files are stored.
If no checkpoint path is provided, the path specified in the MEM_DB_DIR property is used as the default path.
checkpoint_path
This is used to specify the location at which a checkpoint image is stored when checkpointing is performed for the memory tablespace. It is permissible to specify more than one path, which is helpful in distributing the disk I/O costs incurred when checkpointing is performed and when the contents of tablespaces are read from disk at startup.
split_each_clause
This clause is used to split checkpoint files into smaller files. This is useful when the size of the memory tablespace exceeds the maximum file size supported by the operating system, or in order to distribute I/O costs. The size of the resulting files can be specified by the user. If this size is not specified, the default split size specified in the DEFAULT_MEM_DB_FILE_SIZE property is used.
The size can be specified in kB (kilobytes, expressed as “K”), MB (megabytes, expressed as “M”) or GB (gigabytes, expressed as “G”). If no units are specified, the default unit is kilobytes.
Examples#
\<Query 1> Create a user-defined memory data tablespace that is initially 512MB in size and does not automatically increase in size. (The checkpoint image is stored in the path specified in the MEM_DB_DIR property. If the checkpoint image file is split into multiple files, the size of those files will be the same as the value set in the DEFAULT_MEM_DB_FILE_SIZE property.)
iSQL> CREATE MEMORY DATA TABLESPACE user_data SIZE 512M;
Create success.
\<Query 2> Create a user-defined memory data tablespace that is initially 512MB in size and increases in size in 128MB increments[4] (The checkpoint image is stored in the path specified in the MEM_DB_DIR property. If the checkpoint image file is split into multiple files, the size of those files will be the same as the value set in the DEFAULT_MEM_DB_FILE_SIZE property.)
[4] If the maximum size of the tablespace is not specified using the MAXSIZE clause, it defaults to UNLIMITED. In this case, the tablespace can increase in size as long as the combined size of all of the memory tablespaces and volatile tablespaces that exist in the system does not exceed the amount of memory specified in the MEM_MAX_DB_SIZE property.
iSQL> CREATE MEMORY DATA TABLESPACE user_data
SIZE 512M
AUTOEXTEND ON NEXT 128M;
Create success.
\<Query 3> Create a user-defined memory data tablespace that is initially 512MB in size and increases in size in 128MB increments up to a maximum size of 1GB. (The checkpoint image is stored in 3 directories, and the size of each of the checkpoint image files is 256MB).
iSQL> CREATE MEMORY DATA TABLESPACE user_data
SIZE 512M AUTOEXTEND ON NEXT 128M MAXSIZE 1G
CHECKPOINT PATH ‘/dbs/path1’, ‘/dbs/path2’, ‘/dbs/path3’
SPLIT EACH 256M;
Create success.
CREATE VOLATILE TABLESPACE#
Syntax#
create_volatile_tablespace ::=
initsize_clause ::=
autoextend_clause ::=
maxsize_clause ::=
Prerequisites#
Only the SYS user and users having the CREATE TABLESPACE system privilege can create tablespaces.
Description#
The CREATE VOLATILE TABLESPACE statement is used to create a volatile tablespace for storing database objects in the database. Tablespaces created using this command are used to hold volatile tables.
VOLATILE
This keyword is used to specify that the tablespace to be created will be a volatile tablespace.
DATA
This keyword is used to specify that the tablespace to be created will be used to store user data. A data tablespace is created even when the CREATE TABLESPACE statement is executed without the DATA keyword.
tablespace_name
This is used to specify the name of the tablespace to create. Refer to “Rules for Object Names” in Chapter 2 for more information on specifying names.
initsize_clause
This is used to specify the initial size of the tablespace to create.
SIZE
This is used to specify the initial size of a tablespace. The size can be specified in kB (kilobytes, expressed as “K”), MB (megabytes, expressed as “M”) or GB (gigabytes, expressed as “G”). If no units are specified, the default unit is kilobytes.
For example, if the EXPAND_CHUNK_PAGE_COUNT property is set to 128, the default allocation size would be 128 * 32 = 4MB. Therefore, the initial size must be set to a multiple of 4MB.
This value can be specified in units of Kilobytes (K), Megabytes (M), or Gigabytes (G). If no unit is specified, the default unit is in kilobytes.
autoextend_clause
This is used to specify whether the tablespace automatically increases in size when necessary, and the maximum size to which it can increase. If this clause is omitted, AUTOEXTEND is disabled by default.
ON
This enables the AUTOEXTEND option.
OFF
This disables the AUTOEXTEND option.
NEXT
This is used to specify the increment by which the tablespace increases in size when it is automatically increased in size.
Note that the initial size of the memory tablespace must be a multiple of this increment.
If AUTOEXTEND is enabled but this value is not set, the default is the value set using the EXPAND_CHUNK_PAGE_COUNT property in the altibase.properties file. If AUTOEXTEND is OFF, this value is meaningless.
The size can be specified in kB (kilobytes, expressed as “K”), MB (megabytes, expressed as “M”) or GB (gigabytes, expressed as “G”). If no units are specified, the default unit is kilobytes.
maxsize_clause
This is used to specify the maximum size to which a tablespace can increase when it automatically increases in size.
If AUTOEXTEND is enabled but this value is not set, the default value is UNLIMITED. If AUTOEXTEND is OFF, this value is meaningless.
The size can be specified in kB (kilobytes, expressed as “K”), MB (megabytes, expressed as “M”) or GB (gigabytes, expressed as “G”). If no units are specified, the default unit is kilobytes.
UNLIMITED
This is used to indicate that there is no upper limit to the size to which the tablespace can increase.
If this option is used, the tablespace will automatically increase in size up to the point at which the total size of all memory tablespaces and all volatile tablespaces in the system reaches the size specified in the MEM_MAX_DB_SIZE property in the altibase.properties file.
Examples#
\<Query 1> Create a user-defined volatile data tablespace that is initially 512MB in size and does not automatically increase in size.
iSQL> CREATE VOLATILE DATA TABLESPACE user_data SIZE 512M;
Create success.
\<Query 2> Create a user-defined volatile data tablespace that is initially 512MB in size and increases in size in 128MB increments.
iSQL> CREATE VOLATILE DATA TABLESPACE user_data SIZE 512M AUTOEXTEND ON NEXT
128M;
Create success.
CREATE TEMPORARY TABLESPACE#
Syntax#
create_temporary_tablespace ::=
datafile_spec ::=
autoexetend_clause ::=
Prerequisites#
Only the SYS user and users having the CREATE TABLESPACE system privilege can create temporary tablespaces.
Description#
This command is used to create a temporary tablespace for storing temporary results that are used only for the duration of a session. The temporary tablespace will be created in disk space. Data in temporary tablespaces are stored in data files.
To create a tablespace in which to store database objects permanently, use the CREATE DISK TABLESPACE statement.
tablespace_name
This is used to specify the name of the temporary tablespace to be created. Refer to “Rules for Object Names” in Chapter 2 for more information on specifying names.
TEMPFILE datafile_space
This clause is used to specify the temporary file(s) constituting the temporary tablespace.
Example#
\<Query> Create the temporary tablespace temp_data, which is 5 MB in size and is constituted by the tbs.temp data file.
iSQL> CREATE TEMPORARY TABLESPACE temp_data
TEMPFILE '/tmp/tbs.temp' SIZE 5M
AUTOEXTEND ON;
Create success.
CREATE TRIGGER#
Syntax#
create_trigger ::=
simple_dml_trigger ::=
trigger_event ::=
referencing_clause ::=
trigger_action::=
psm_body::=
instead_of_dml_trigger::=
Prerequisites#
At least one of the following conditions must be met:
- The SYS user
- Users have the CREATE TRIGGER or CREATE ANY TRIGGER system privilege to create a trigger in their own schema
- Users have the CREATE ANY TRIGGER system privilege to create a trigger in another user's schema
Description#
This command is used to create a trigger having the specified name.
OR REPLACE
This is used to replace a trigger of the same name if a trigger already exists. That is, this changes the definition of an existing trigger instead of removing and recreating an existing trigger.
user_name
This is used to specify the name of the owner of the trigger to be created. If this value is omitted, Altibase will assume that the trigger is to be created in the current user's schema.
trigger_name
This is used to specify the name of the trigger to be created. Refer to “Rules for Object Names” in Chapter 2 for more information on specifying names.
AFTER
Choose the AFTER option to fire the trigger after the execution of trigger_event, that is, the event that caused the trigger to fire.
BEFORE
Choose the BEFORE option to fire the trigger before the execution of trigger_event, that is, the event that caused the trigger to fire.
INSTEAD OF
Choose the INSTEAD OF option to fire the trigger, instead of performing the triggering DML statement. INSTEAD OF triggers can only be created on views. If the view has a LOB column, a trigger can be created with the INSTEAD OF option; however, an error is raised when the triggering DML statement is executed.
trigger_event
This is an event that changes the data in a table and causes the trigger to fire. Note that in order to preserve database integrity, DML operations that change the data and are initiated by the replication Receiver thread in a replication target table (i.e. a table that is cited in an active replication object) are not treated as a trigger event (i.e. do not fire the trigger on the table). Multiple trigger events can be applied to one trigger. trigger_event can specify the three following DML statements.
- DELETE
DELETE option is used to fire the trigger when data in the table are deleted by executing a DELETE statement. - INSERT
INSERT option is used to fire the trigger when data are inserted into the table by executing an INSERT statement. Tables with LOB columns can create a trigger with the ‘BEFORE INSERT … FOR EACH ROW’ statement. However, an error occurs when the DML statement firing the trigger is executed. - UPDATE
UPDATE option is used to fire the trigger when data in the table are updated by executing an UPDATE statement. The OF clause specifies that the trigger will only be fired when an UPDATE statement changes one of the columns specified therein. Tables with LOB columns can create a trigger with the ‘BEFORE UPDATE … FOR EACH ROW’ statement. However, an error occurs when the DML statement firing the trigger is executed.
ON table_name
This is used to specify the table that is referenced when determining whether the trigger will fire. The trigger will fire in response to a change made to the table specified in table_name.
Triggers can only reference regular tables. They cannot be created on the basis of objects such as views, sequences, or stored procedures.
Triggers cannot be created on the basis of tables that are referenced in replications. Likewise, any attempt to create a replication object that references a table that is already referenced by a trigger will fail.
If the user_name is omitted, Altibase will assume that the trigger is to be created for a table in the current user's schema.
REFERENCING Clause
One characteristic of triggers is the concept of old and new rows. When the data in the table referenced by a trigger are changed, an individual row that was changed will consequently have both old and new values. The REFERENCING clause makes it possible to refer to either the old value or the new value as desired.
The REFERENCING clause has the following restrictions:
- The REFERENCING clause must be used together with the FOR EACH ROW option.
- The REFERENCING clause must have the following structures so that it can be referred to in the trigger_action clause.
- {OLD|OLD ROW|OLD ROW AS|OLD AS} alias_name
This indicates the data contained in a record before it is modified. Old values can be referenced in the WHEN clause or in psm_body in trigger_action. It has NULL value when the trigger event is an INSERT trigger event, because there is no old value. - {NEW|NEW ROW|NEW ROW AS|NEW AS} alias_name
This indicates the data contained in a record after it is modified. Note that when the trigger is a BEFORE trigger, it is possible to change these data in the body of the trigger. It has NULL value when the trigger event is a DELETE trigger event and does not affect it, because there is no new value.
trigger_action
The trigger action clause consists of the following three parts:
- Action granularity: Determines the unit (row or statement) by which the trigger operates.
- Action WHEN condition: Optionally used to set an additional condition to determine whether the trigger will fire.
- Action body: Determines what the trigger actually does.
FOR EACH {ROW|STATEMENT}
This is used to specify the unit of operation of the trigger. The changes to the data in the table take place according to this unit. The default is FOR EACH STATEMENT.
- FOR EACH ROW: The operations specified in action body are conducted once for each row that is affected by trigger_event and satisfies the WHEN clause. The FOR EACH ROW clause must be used when either the REFERENCING clause or the WHEN clause is used.
- FOR EACH STATEMENT: The trigger will be fired only once, either after or before execution of the DML statement that caused the trigger to fire.
WHEN search_condition
This is used to specify the conditions that are used to determine whether to fire the trigger. The action body of the trigger is executed only if the search_condition in the WHEN clause evaluates to TRUE. If the search_condition in the WHEN clause evaluates to FALSE, the action body of the trigger will not be executed. If no WHEN clause is specified, the action body of the trigger will be executed every time the trigger event occurs.
To use a condition in the WHEN clause, the following constraints must be satisfied:
- The WHEN clause can only be used with the FOR EACH ROW clause.
- Only the alias_name defined in the REFERENCING clause can be used in the WHEN search_condition.
- Subqueries cannot be used in the WHEN search_condition.
- Stored procedures cannot be used in the WHEN search_condition.
psm_body
This is the so-called “action body” of the trigger, and is used to specify the actual operations that are to be carried out by the trigger. psm_body can be specified in the same way as a block statement in a stored procedure.
psm_body must satisfy the following constraints:
Due to the nature and concept of a trigger, the following SQL statement syntax for the action body cannot be used.
- Transaction-related statements such as COMMIT and ROLLBACK cannot be used.
- Session-related statements such as CONNECT cannot be used.
- Schema-related statements such as CREATE TABLE cannot be used.
- Stored procedures cannot be called.
- Recursive triggers, that is, triggers that perform the operation specified in trigger_event, cannot be created.
ENABLE | DISABLE
The user can select an enable or a disable database trigger when creating a trigger. The default value is set to enable.
- If the status is set to disable, it does not operate when creating a trigger, but the trigger status can be modified by the ALTER TRIGGER statement.
For more detailed information about the use of block statements in stored procedures, please refer to the Stored Procedures Manual.
Considerations#
- Order of Trigger Execution
It is possible to create multiple triggers that reference the same table. In such cases, the order in which the triggers fire is not fixed. If it is important to control the order in which the actions of multiple triggers occur, rewrite the triggers as a single trigger. - Trigger Execution Failure
If an error occurs while a trigger is executing, the DML statement that caused the trigger to fire will also fail. - Execution of DDL on Tables Referenced in Triggers
When a table that causes triggers to fire is deleted using the DROP TABLE statement, the triggers that fire in response to changes made to the table are also deleted. However, when a table that is referred to by the action body of a trigger is altered or dropped, the trigger is not dropped. In the case where the table is dropped, when it becomes impossible to perform the operations in the action body of such a trigger, any DML statements that cause the trigger to fire will fail. In the case where the table is altered, the trigger will be internally recompiled and executed at the time it is fired. - Triggers and Replication
DML statements that are executed in the course of replication do not cause triggers to fire.
Examples#
\<Query> The following example shows how to use a trigger to track the deletion of rows. In this example, the trigger operates on a FOR EACH ROW basis, and references the original values in the ono, cno, qty and arrival_date columns of the orders table when data about completed (processing='D') delivery orders are deleted from the table. The trigger creates a record of the deleted rows by inserting rows into the log_tbl table.
iSQL> CREATE TABLE log_tbl(
ono BIGINT,
cno BIGINT,
qty INTEGER,
arrival_date DATE,
sysdate DATE);
Create success.
iSQL> CREATE TRIGGER del_trigger
AFTER DELETE ON orders
REFERENCING OLD ROW old_row
FOR EACH ROW
AS BEGIN
INSERT INTO log_tbl VALUES(old_row.ono, old_row.cno, old_row.qty, old_row.arrival_date, sysdate);
END;
/
Create success.
iSQL> DELETE FROM orders WHERE processing = 'D';
2 rows deleted.
iSQL> SELECT * FROM log_tbl;
ONO CNO QTY ARRIVAL_DATE
------------------------------------------------------------------------
SYSDATE
---------------
11290011 17 1000 05-DEC-2011
25-APR-2012
11290100 11 500 07-DEC-2011
25-APR-2012
2 rows selected.
\<Query> In the following example, when a record is inserted into the SCORES table, a value of 0 is set for the SCORE column if NULL is specified. This is accomplished using a BEFORE INSERT trigger that fires FOR EACH ROW.
iSQL> CREATE TABLE scores( id INTEGER, score INTEGER );
Create success.
iSQL> CREATE TRIGGER scores_trigger
BEFORE INSERT ON scores
REFERENCING NEW ROW NEW_ROW
FOR EACH ROW
AS BEGIN
IF NEW_ROW.SCORE IS NULL THEN
NEW_ROW.SCORE := 0;
END IF;
END;
/
Create success.
iSQL> INSERT INTO scores VALUES( 1, 20 );
1 row inserted.
iSQL> INSERT INTO scores VALUES( 5, NULL );
1 row inserted.
iSQL> INSERT INTO scores VALUES( 17, 75 );
1 row inserted.
iSQL> SELECT * FROM SCORES;
ID SCORE
---------------------------
1 20
5 0
17 75
3 rows selected.
\<Query> Confirm the operating status by creating a disable trigger, and modifies it to enable to check the status again.
iSQL> CREATE TABLE scores( id INTEGER, score INTEGER );
Create success.
iSQL> CREATE TRIGGER scores_trigger
BEFORE INSERT ON scores
REFERENCING NEW ROW NEW_ROW
FOR EACH ROW
DISABLE
AS BEGIN
IF NEW_ROW.SCORE IS NULL THEN
NEW_ROW.SCORE := 0;
END IF;
END;
/
Create success.
iSQL> INSERT INTO scores VALUES( 1, 20 );
1 row inserted.
iSQL> INSERT INTO scores VALUES( 5, NULL );
1 row inserted.
iSQL> INSERT INTO scores VALUES( 17, 75 );
1 row inserted.
iSQL> SELECT * FROM SCORES;
ID SCORE
---------------------------
1 20
5
17 75
3 rows selected.
iSQL> ALTER TRIGGER scores_trigger ENABLE;
Alter success.
iSQL> INSERT INTO scores VALUES( 100, NULL );
1 row inserted.
iSQL> SELECT * FROM SCORES;
ID SCORE
---------------------------
1 20
5
17 75
100 0
4 rows selected.
CREATE USER#
Syntax#
create_user ::=
password_parameters ::=
Prerequisites#
Only the SYS user and users to whom the CREATE USER system privilege has been granted can create users.
Description#
This statement is used to create a database user and specify the user's name, password, and tablespace access privileges.
user_name
This is used to specify the name of the user to create. The user name must be unique in the database. Refer to “Rules for Object Names” in Chapter 2 for more information on specifying names.
IDENTIFIED BY password
Altibase uses password authentication. The maximum length of a user password can be 16 to 22 bytes, depending on the operating system. An error is raised if you try to create a user with password that exceeds this limit. Altibase automatically converts lowercase passwords to uppercase by default. However, you can create a case sensitive password by setting the value of CASE_SENSITIVE_PASSWORD to 1, and then enclosing the password in quotation marks, when creating a user with the CREATE USER statement
User passwords must comply with Rules for Object Names in Chapter 2.
TEMPORARY TABLESPACE Clause
This clause is used to specify the default temporary tablespace for the user, and will be used to store intermediate results when the user performs operations on tables.
If no tablespace is specified, the system temporary tablespace[5] will be used as the temporary tablespace for the user.
[5]: The system temporary tablespace is used for the temporary storage of data that are generated while a query is being executed. It is not logged, and thus the data stored therein cannot be recovered in the event of a media error.
A temporary tablespace is normally used to store intermediate results when the user performs operations on disk-based tables.
In the case where all of the tables being accessed by a query are memory tables, all query operations would take place in memory space, and thus no temporary tablespace would be used unless coerced using a query hint.
Only one temporary tablespace can be assigned to a user.
DEFAULT TABLESPACE Clause
This is used to specify the default tablespace in which to store user-created objects. If this clause is omitted, the default tablespace for the user is the system memory default tablespace.
Only one default tablespace can be specified for a user.
ACCESS Clause
This is used to specify whether or not the user has access to the specified tablespace. If this clause takes the form ACCESS tablespace_name ON, the user is permitted to access the specified tablespace, whereas if it takes the form ACCESS tablespace_name OFF, the user is not authorized to access the specified tablespace.
Users are also able to access tablespaces if the ALTER TABLESPACE system privilege has been granted to them.
ENABLE/ DISABLE
Specifies whether to enable or disable the user’s TCP connection. Only the SYS user can execute this clause.
FAILED_LOGIN_ATTEMPTS
If the number of times login fails equals the number set for this value, the account is locked and login is impossible until it is unlocked.
If PASSWORD_LOCK_TIME is set, the account is automatically unlocked after the specified time elapses
PASSWORD_LOCK_TIME
This specifies the date(unit: days) required to elapse for a locked account to become unlocked. For example, if the number 5 is set for this value and an account is locked, the account is unlocked after 5 days and login is possible.
PASSWORD_LIFE_TIME
This specifies the period of validity (unit: days) of the account password. PASSWORD_LIFE_TIME is applied in compliance with the last time the password was modified.
PASSWORD_GRACE_TIME
This specifies the grace period (unit: days) during which the password can be modified after the expiry date. After expiration, the password must be modified by logging in the account within the given grace period. After grace period expiration, the password must be modified by logging in as SYS user.
PASSWORD_REUSE_TIME
This specifies the period of time (unit: days) needed to elapse for the reuse of identical passwords. Thus, identical passwords can be reused after the period of time specified for this option elapses.
PASSWORD_REUSE_MAX
This specifies the number of times passwords can be altered for the reuse of identical passwords. Thus, identical passwords can be reused after they have been altered for the number of times specified for this option.
Note: If either PASSWORD_REUSE_MAX or PASSWORD_REUSE_TIME is specified exclusively, the password cannot be reused.
PASSWORD_VERIFY_FUNCTION
Here the user can register user-defined CALLBACK functions to verify passwords. User-defined CALLBACK functions must return ‘TRUE’.
CALLBACK functions for verifying passwords must have the following input parameters and return types:
CREATE OR REPLACE FUNCTION pwd_verify_function (
username varchar(20),
password varchar(20))
RETURN varchar(100)
AS
result varchar(100);
...
BEGIN
...
result := 'TRUE';
RETURN result;
END;
Restrictions#
A single user can use multiple data tablespaces. However, a single user can use only one temporary tablespace.
It is not possible for a user to expressly access the system undo tablespace or create tables, indexes, or other objects therein. Additionally, because there is one, and only one, system undo tablespace in the system, users cannot delete the system undo tablespace or create other system undo tablespaces.
Examples#
\<Query> Create a user whose name is uare1 and password is rose1.
iSQL> CREATE USER uare1 IDENTIFIED BY rose1;
Create success.
\<Query> Create a user named uare4 with the password rose4. Specify user_data as the default tablespace and temp_data as the temporary tablespace for the user, and grant the user privileges to access the SYS_TBS_MEMORY tablespace.
iSQL> CREATE USER uare4
IDENTIFIED BY rose4
DEFAULT TABLESPACE user_data
TEMPORARY TABLESPACE temp_data
ACCESS SYS_TBS_MEMORY ON;
Create success.
\<Query> Create user rose2; the account is to be locked if login fails 5 times and unlocked after 5 days.
iSQL> CREATE USER rose2 IDENTIFIED BY rose2
LIMIT (FAILED_LOGIN_ATTEMPTS 5, PASSWORD_LOCK_TIME 5);
\<Query> Create user rose3; the password is to expire after 5 days and a grace period of 5 days is to follow.
iSQL> CREATE USER rose3 IDENTIFIED BY rose3
LIMIT (PASSWORD_LIFE_TIME 5, PASSWORD_GRACE_TIME 5);
\<Query> Create user rose4; the password is to be available for reuse after being altered 3 times, and 10 days have elapsed.
iSQL> CREATE USER rose4 IDENTIFIED BY rose4
LIMIT (PASSWORD_REUSE_MAX 3, PASSWORD_REUSE_TIME 10);
CREATE VIEW#
Syntax#
create_view ::=
query_restriction_clause ::=
Prerequisites#
At least one of the following conditions must be met:
- The SYS user
- Users have the CREATE VIEW or CREATE ANY VIEW system privilege to create a view in their own schema
- Users have the CREATE ANY VIEW system privilege to create a view in another user's schema
Description#
This statement is used to create a view having the specified name. A view is a logical table based on one or more tables or views. A view contains no data itself. The tables upon which a view is based are called base tables.
OR REPLACE
Use the OR REPLACE clause to replace a view having the same name if such a view already exists. This clause is used to change the definition of an existing view without having to drop the old view, create the new view, and grant previously granted privileges for the view.
FORCE
The FORCE clause is used to specify that the view is to be created even if the objects on which the view is based don't exist, and even if the owner of the schema containing the view does not have sufficient privileges to access the view.
This means that it is possible to use the FORCE option to create views that are semantically erroneous and thus invalid. In such cases, because an error will occur when a SELECT statement is executed on the view, it is advisable to test the view after creating it by executing a SELECT statement on it immediately, or to query the SYS_VIEWS_ meta table to verify that no errors were raised, which can be inferred to mean that the view is error-free.
NO FORCE
Use the NO FORCE clause to specify that the view is to be created only if the underlying objects exist and the owner of the schema in which the view is to be created has access privileges for them. This is the default view creation behavior.
user_name
This is used to specify the name of the owner of the schema in which the view is to be created. If this value is omitted, Altibase will create the view in the schema of the user who is connected via the current session.
view_name
This is used to specify the name of the view to create. Refer to “Rules for Object Names” in Chapter 2 for more information on specifying names.
alias_name
If the query on which a view is based contains an expression without an alias, an alias for the expression must be specified. This alias will becomes the name of the corresponding expression in the view. The number of aliases must be same as the number of expressions and columns in the query.
subquery
This is used to specify the text of a query that identifies rows and columns in the base table(s) to display in the view.
WITH READ ONLY
This is used to specify that the view will be a read-only view. If you do not specify this option, the updatable view is created that can perform INSERT, UPDATE, and DELETE operations on views.
Limitations on the Use of Queries in Views#
- The owner of the schema where the view is stored must have the necessary privileges to execute the SELECT statement from the table or view on which the view is based.
- A maximum of 1024 expressions can be specified in the SELECT statement on which a view is based.
- The CURRVAL and NEXTVAL pseudocolumns cannot be used in the SELECT statement on which a view is based.
Examples#
Creating a view#
\<Query> In the following example, a view called avg_sal will be created on the basis of the employees table. The purpose of the view is to display the average salary for each department.
iSQL> CREATE VIEW avg_sal AS
SELECT dno, AVG(salary) emp_avg_sal
FROM employees
GROUP BY dno;
Create success.
iSQL> SELECT * FROM avg_sal;
AVG_SAL.DNO AVG_SAL.EMP_AVG_SAL
------------------------------------
A001 2066.66667
C001 1576.66667
C002 1660
D001 2075.75
F001 1845
6 rows selected.
Since emp_avg_sal is provided as an alias for the expression AVG (salary) within the subquery, the alias for the column of the view does not need to be specified.
Creating a Join View[6]#
[6]:A join view is a view in which the underlying query contains a join.
\<Query> The following view shows the names of the employees responsible for the ordered products and the names of the customers who ordered the products.
iSQL> CREATE VIEW emp_cus AS
SELECT DISTINCT e.e_firstname, e.e_lastname,
c.c_firstname, c.c_lastname
FROM employees e, customers c, orders o
WHERE e.eno = o.eno AND o.cno = c.cno;
Create success.
iSQL> SELECT * FROM emp_cus;
E_FIRSTNAME E_LASTNAME C_FIRSTNAME C_LASTNAME
---------------------------------------------------------------------------------------------
Alvar Marquez Estevan Sanchez
Sandra Hammond Pierre Martin
.
.
.
William Blake Saeed Pahlavi
Sandra Hammond Saeed Pahlavi
22 rows selected.
CREATE MATERIALIZED VIEW#
Syntax#
create_materialized_view ::=
table_partitioning_clause ::=, tablespace_clause ::=, logging_clause ::=, lob_column_properties ::=
physical_attributes_clause ::=
build_clause ::=
refresh_clause ::=
Prerequisites#
At least one of the following conditions must met:
- The SYS user
- The CREATE MATERIALIZED VIEW system privilege are required to create a materialized view in the user’s schema. The SELECT object privilege for each base table that the user does not own or the SELECT ANY TABLE system privilege is also required.
- The CREATE ANY MATERIALIZED VIEW system privilege is required to create a materialized view in another user’s schema. And the SELECT object privilege for each base table that the user does not own or the SELECT ANY TABLE system privilege as well.
- Once the materialized view is created, an internal view and an internal table for the database are automatically created in the materialized view schema. These additionally created objects are used to maintain the materialized view data. The user requires the privilege to create such objects in order to create a Material View.
Description#
A new materialized view is created as the specified name. A materialized view is a database object that stores query results. A table, a view or another materialized view can constitute the FROM clause of the query. Such objects are defined as “base tables”.
Like other tables, a materialized view stores the results of query statements in a tablespace and is mostly used for data warehouse purposes. The performance time for query execution can be significantly reduced by creating query statements that are frequently used or include time demanding JOIN or aggregate functions as materialized views.
Altibase provides only read-only materialized views.
user_name
This specifies the owner name of the materialized view to be created. On omission, Altibase creates a materialized view in the user schema connected to the current session.
mview_name
This specifies the name of the materialized view to be created. Refer to “Rules for Object Names” in Chapter 2 for more information on specifying names. Altibase automatically creates a table to maintain the data of the materialized view under the same name as the specified materialized view.
c_alias
If the queried target from the base table is an expression, an alias must be specified for that expression. This alias becomes the column name for the materialized view. The number of aliases must equal the total number of queried targets(expressions and columns).
table_partitioning_clause
Please refer to table_partitioning_clause of the CREATE TABLE statement.
segment_attributes_clause
Please refer to segment_attributes_clause of the CREATE TABLE statement.
lob_column_properties
Please refer to lob_column_properties of the CREATE TABLE statement.
phsical_attributes_clause
Please refer to physical_attributes_clause of the CREATE TABLE statement.
build_clause clause
This specifies the initial time point at which data of the materialized view is built. On omission, the default value is IMMEDIATE.
- IMMEDIATE: Data is built at the time point that the materialized view is created.
- DEFERRED: Data is built during refresh execution after the materialized view is created.
refresh_clause clause
If the base table of the materialized view is altered, the data of the materialized view must be updated. This clause specifies the method and time that the materialized view is refreshed. On omission, the default values are FORCE and ON DEMAND.
After the REFRESH keyword, one of the following must be specified: COMPLETE, FAST, FORCE REFRESH or ON DEMAND, ON COMMIT
- COMPLETE: specifies data to be built by executing the sub query defined when the materialized view was created.
- FAST: currently not supported.
- FORCE: when refresh occurs, if possible, fast refresh is executed; if not, the database is commanded to execute complete refresh. Altibase does not currently support FAST; specifying FORCE is equivalent to COMPLETE.
- ON DEMAND: specifies refresh only on user’s request.
- ON COMMIT: currently not supported.
- NEVER REFRESH: currently not supported.
Note: The user can manually request materialized view refresh by calling the REFRESH_MATERIALIZED_VIEW stored procedure. For further information on the REFRESH_MATERIALIZED_VIEW stored procedure, refer to the Chapter 10 in the Stored Procedures Manual.
subquery clause
This specifies the query statement of the materialized view. When the user creates a materialized view, the subquery specified in the clause is executed and its result is stored in the materialized view.
Example#
\<Query> Create a materialized view of the name mv1 that takes the employees table as its base table. Since the build clause and refresh clause are not specified, refresh is possible only on the user’s request and complete refresh is executed.
CREATE MATERIALIZED VIEW mv1 AS
SELECT * FROM employees;
DISJOIN TABLE#
Syntax#
disjoin_table ::=
partition_to_table_clause ::=
Prerequisites#
At lease one of the following conditions must be met in order to create a table:
- The SYS user
- The user have the CREATE TABLE or CREATE ANY TABLE system privilege in their own schema
- The user have the CREATE ANY TABLE system privilege in another user's schema
At least one of the following conditions must be met:
- The SYS user
- The table's owner
- The user has the DROP ANY TABLE system privilege.
Description#
Partitions in a partitioned table are converted into one or more tables. Partitioned tables are dropped and non-partitioned tables are created. The partitions are each converted to the specified table and the data is moved. If a table space option is not specified, a new table will be created in the default table space.
partition_to_table
This clause specifies the name of the table to be used after dismounting all partitions belonging to the partitioned table.
Considerations#
The following statements should be taken into consideration when using DISJOIN TABLE syntax.
- Do not specify the owner name in the target table and the partitioned table that are created.
- The metatable associated with the newly created non-partitioned table is created, and the metatable associated with the partitioned table is deleted.
- PMS, packages, and views that are related to the target table cannot be used.
- Hash partitioned tables are not supported.
- The target partitioned table have the same condition with partition's attribute, constraints, and schema.
Example#
\<Query> Convert each partition p1, p2, and p3 in table t1 into t2, t3, and t4, respectively.
iSQL> disjoin table t1
(
partition p1 to table t2,
partition p2 to table t3,
partition p3 to table t4
);
Disjoin success.
DROP DATABASE#
Syntax#
drop_database ::=
Prerequisites#
This SQL statement can only be executed by the SYS user in -sysdba administrator mode, and can only be executed during the PROCESS phase.
Description#
This statement is used to delete a database from the system.
database_name
This is used to specify the name of the database to delete.
When this command is executed, all of the data, log files and log anchor files that were used by the database are also deleted.
Example#
\<Query> Delate a database named mydb.
iSQL(sysdba)> DROP DATABASE mydb;
Checking Log Anchor files
[Ok] /home /altibase_home/logs/loganchor0 Exist.
[Ok] /home /altibase_home/logs/loganchor1 Exist.
[Ok] /home /altibase_home/logs/loganchor2 Exist.
Removing DB files
Removing Log files
Removing Log Anchor files
Drop success.
DROP DATABASE LINK#
For information on Database Link, please refer to the Database Link User’s Manual.
DROP DIRECTORY#
Syntax#
drop_directory ::=
Prerequisites#
Only the SYS user and users to whom the DROP ANY DIRECTORY system privilege has been granted can execute this statement.
Description#
This statement is used to remove a directory. Note that only the reference to the directory in the database is removed; the actual directory is not removed from the file system.
directory_name
This is used to specify the name of the directory to drop.
Example#
\<Query> Drop the directory named anti_dir1.
iSQL> DROP DIRECTORY alti_dir1;
Drop success.
DROP INDEX#
Syntax#
drop_index ::=
Prerequisites#
Only the SYS user, the owner of the schema containing the index, users having the INDEX object privilege on table, and users having the DROP ANY INDEX system privilege can execute the DROP INDEX statement.
Description#
This statement is used to remove an index from the database.
user_name
This is used to specify the name of the owner of the index to be dropped. If omitted, Altibase will assume that the index belongs to the schema of the user connected via the current session.
index_name
This is used to specify the name of the index to drop.
Example#
\<Query> Delete the index emp_idx1.
iSQL> DROP INDEX emp_idx1;
Drop success.
DROP JOB#
Syntax#
drop_job ::=
Prerequisites#
Only the SYS user can use this statement.
Description#
This drops the JOB from the database.
job_name
This specifies the name of the JOB to be dropped.
Examples#
\<Query> Drop the JOB job1.
iSQL> DROP JOB job1;
Drop success.
DROP QUEUE#
Syntax#
drop_queue ::=
Prerequisites#
Only the SYS user, the owner of the schema containing the queue, and users having the DROP ANY TABLE system privilege can execute the DROP QUEUE statement.
Description#
This statement is used to delete the specified queue. The queue table, the index for the queue table and the sequence used to generate MSGID values in the queue table are deleted along with the queue.
Example#
\<Query> Delete the message queue Q1 and its associated objects.
iSQL> DROP QUEUE Q1;
DROP REPLICATION#
Syntax#
drop_replication ::=
Prerequisites#
Only the SYS user can execute replication-related statements.
Description#
This statement is used to drop a replication.
replication_name
This is used to specify the name of the replication to drop.
Limitation#
A replication that is currently active cannot be dropped. That is, a replication cannot be dropped if the ALTER REPLICATION START command has been executed for the replication; the ALTER REPLICATION STOP command must first be executed, after which it will be possible to drop the replication.
Example#
\<Query> Drop the replication rep1.
iSQL> DROP REPLICATION rep1;
DROP ROLE#
Syntax#
drop_role ::=
Prerequisites#
Only the SYS user and users with the DROP ANY ROLE system privilege can drop a role.
Description#
This statement drops the specified role.
role_name
This is used to specify the name of the role to be dropped.
Example#
\<Query> Drop the role with the name alti_role.
iSQL> DROP ROLE alti_role;
Drop success.
DROP SEQUENCE#
Syntax#
drop_sequence ::=
Prerequisites#
Only the SYS user, the owner of the schema containing the sequence, and users with the DROP ANY SEQUENCE system privilege can execute the DROP SEQUENCE statement.
Description#
This statement is used to remove a sequence from the database.
user_name
This is used to specify the name of the owner of the sequence to be dropped. If omitted, Altibase will assume that the sequence belongs to the schema of the user connected via the current session.
seq_name
This is used to specify the name of the sequence to drop.
Example#
\<Query> Delete the sequence seq1.
iSQL> DROP SEQUENCE seq1;
Drop success.
DROP SYNONYM#
Syntax#
drop_synonym ::=
Prerequisites#
Only the SYS user, the owner of the schema containing the , and the users with the DROP ANY SYNONYM privilege can execute the DROP SYNONYM statement.
In addition, only the SYS user and users with the DROP PUBLIC SYNONYM system privilege can drop public synonyms.
Description#
This statement is used to remove a synonym from the database.
PUBLIC
To remove a public synonym, use the PUBLIC keyword, If the PUBLIC keyword is not used, a PRIVATE synonym having the specified name will be removed.
When the PUBLIC keyword is used, user_name cannot be specified.
user_name
This is used to specify the name of the owner of the synonym to drop. If omitted, Altibase will assume that the synonym belongs to the schema of the user connected via the current session.
synonym_name
This is used to specify the name of the synonym to drop.
Examples#
\<Query> Drop the synonym my_dept.
iSQL> DROP SYNONYM my_dept;
Drop success.
\<Query> Drop the PUBLIC synonym dept.
iSQL> DROP PUBLIC SYNONYM dept;
Drop success.
DROP TABLE#
Syntax#
drop_table ::=
Prerequisites#
Only the SYS user, the owner of the schema containing the table, and users with the DROP ANY TABLE system privilege can execute the DROP TABLE statement.
Description#
This statement is used to remove a table and all of its data from a database.
Set the value of the RECYCLEBIN_ENABLE property to 1 to move a table to the recycle bin, instead of dropping it directly from the database. Tables of the same name can be dropped multiple times; only as many tables as the recycle bin can contain can be moved to the recycle bin.
user_name
This is used to specify the name of the owner of the table to be dropped. If omitted, Altibase will assume that the table belongs to the schema of the user connected via the current session.
table_name
This is used to specify the name of the table to be dropped.
{CASCADE | CASCADE CONSTRAINTS}
These options are used to delete referential integrity constraints in other tables that reference the primary key or unique keys in the table being dropped.
Example#
\<Query> Drop the table employees.
iSQL> DROP TABLE employees;
Drop success.
DROP TABLESPACE#
Syntax#
drop_tablespace ::=
Prerequisites#
Only the SYS user and users with the DROP TABLESPACE system privilege can execute the DROP TABLESPACE statement.
Description#
This statement is used to remove a tablespace from the database.
tablespace_name
This is used to specify the name of the tablespace to drop.
INCLUDING CONTENTS
This is used to specify that all of the contents of the tablespace are to be deleted. If one or more objects exist in the tablespace, this clause must be specified in order to remove the tablespace. If this clause is not specified, Altibase will return an error and the DROP TABLESPACE statement will fail.
AND DATAFILES
If the AND DATAFILES clause is additionally specified along with the INCLUDING CONTENTS clause, all of the files related to the tablespace are deleted from the file system.
When a disk tablespace is dropped, all of the data files in the disk tablespace are deleted from the file system.
When a memory tablespace is dropped, all the checkpoint image files for the memory tablespace are deleted from the file system. However, the checkpoint paths are not deleted.
The AND DATAFILES clause cannot be used when dropping a volatile tablespace.
CASCADE CONSTRAINTS
The CASCADE CONSTRAINTS clause must be specified in order to drop all referential integrity constraints in tables that are stored in tablespaces other than the tablespace being dropped but that refer to primary and unique keys in tables in the tablespace being dropped. If this clause is omitted when such referential integrity constraints exist, an error will be returned and the attempt to drop the tablespace will fail.
Limitation#
The following tablespaces are system tablespaces, and thus cannot be removed using the DROP TABLESPACE statement:
-
SYS_TBS_MEM_DIC
-
SYS_TBS_MEM_DATA
-
SYS_TBS_DISK_DATA
-
SYS_TBS_DISK_UNDO
-
SYS_TBS_DISK_TEMP
Examples#
\<Query 1> Drop the tablespace user_data.
iSQL> DROP TABLESPACE user_data;
Drop success.
\<Query 2> Delete the disk tablespace user_data along with all associated objects and data files.
iSQL> DROP TABLESPACE user_data INCLUDING CONTENTS AND DATAFILES;
Drop success.
\<Query 3> Delete the memory tablespace user_data along with all associated objects and data files.
iSQL> DROP TABLESPACE user_memory_tbs INCLUDING CONTENTS AND DATAFILES;
Drop success.
\<Query 4> Delete the tablespace user_data along with all objects stored therein and all referential integrity constraints that refer to primary and unique keys in all tables in the tablespace.
iSQL> DROP TABLESPACE user_data INCLUDING CONTENTS CASCADE CONSTRAINTS;
Drop success.
DROP TRIGGER#
Syntax#
drop_trigger ::=
Prerequisites#
Only the SYS user, the owner of the schema containing the trigger, and users with the DROP ANY TRIGGER system privilege can execute the DROP TRIGGER statement.
Description#
This statement is used to drop the specified trigger from the database.
user_name
This is used to specify the name of the owner of the trigger to be dropped. If omitted, Altibase will assume that the trigger belongs to the schema of the user connected via the current session.
trigger_name
This is used to specify the name of the trigger to be dropped.
Example#
\<Query> Drop the trigger del_trigger from a table.
iSQL> DROP TRIGGER del_trigger;
Drop success.
DROP USER#
Syntax#
drop_user ::=
Prerequisites#
Only the SYS user and users with the DROP USER system privilege can execute the DROP USER statement.
Description#
This statement is used to drop the specified user from the database.
user_name
This is used to specify the name of the user to drop.
CASCADE
This is used to specify that not only the database user but also all objects in the user's schema will be dropped. Additionally, any referential integrity constraints that refer to primary and unique keys in tables belonging to the user's schema will also be dropped.
If there is an object in the user schema to be deleted and omit CASCADE, an error will be returend and the DROP USER statement will fail.
Examples#
\<Query> Drop the user uare1.
iSQL> DROP USER uare1;
Drop success.
\<Query> Drop the user uare4 and all of the user's objects.
iSQL> DROP USER uare4 CASCADE;
Drop success.
DROP VIEW#
Syntax#
drop_view ::=
Prerequisites#
Only the SYS user, the owner of the schema containing the view, and users with the DROP ANY VIEW system privilege can execute the DROP VIEW statement.
Description#
This statement is used to drop the specified view from the database.
user_name
This is used to specify the name of the owner of the view to be dropped. If omitted, Altibase will assume that the view belongs to the schema of the user connected via the current session.
view_name
This is used to specify the name of the view to be dropped.
Example#
\<Query> Drop the view avg_sal.
iSQL> DROP VIEW avg_sal;
Drop success.
DROP MATERIALIZED VIEW#
Syntax#
drop_mview ::=
Prerequisites#
Only the following users can delete the materialized view using this statement:
-
The SYS user
-
The owner of the Materialized view
-
The user with the system privilege of DROP ANY MATERIALIZED VIEW
Description#
This statement deletes the specified materialized view from the database.
user_name
This specifies the owner name of the materialized view to be deleted. On omission, Altibase deletes the materialized view of the current user’s schema.
mview_name
This specifies the name of the materialized view to be deleted.
Example#
\<Query> Delete the materialized view with the name mv1.
DROP MATERIALIZED VIEW mv1;
FLASHBACK TABLE#
Syntax#
flashback_table::=
Prerequisites#
At least one or more of the following conditions must be met:
-
The SYS user
-
The users with the CREATE TABLE or CREATE ANY TABLE system privilege in their own schema.
-
The users with the CREATE ANY TABLE system privilege in another user's schema.
Description#
FLASHBACK TABLE recovers tables from the recycle bin. If there are several tables with the same name, the first table that was dropped is recovered to the database.
table_name
This specifies the name of the table to be recovered from the recycle bin. The table takes the name it had before it was moved, or a new name generated by the system when it was moved to the recycle bin. If there are several tables with the same name, the first table that was moved to the recycle bin is recovered to the database.
RENAME TO table_name
The user can specify a new name for the table when it is recovered; if the same name exists in the user's schema, the name can be altered.
GRANT#
Syntax#
grant ::=
grant_system_privilege ::=
grant_object_privilege ::=
Prerequisites#
Only the SYS user and users with the GRANT ANY ROLE system privilege can grant system privileges to a role. In addition, an object privilege can be granted to other users only by the owner of the object or who has been granted object privilege with WITH GRANT OPTION.
Description#
This statement is used to grant privileges to access the database or specified objects to one or more specified users.
Access privileges are classified as either system privileges or object privileges.
grant_system_privilege
System privileges are usually managed by the SYS user. The SYS user can grant limited system privileges to allow users to perform specific database tasks. System privileges can be seen as broad privileges that control objects in any schema.
System privileges are required in order to execute DDL statements and DCL statements.
grant_object_privilege
Once a user has been granted privileges for a particular object, the user can access and/or manipulate the object. Object access privileges are typically managed by the owner of the object.
If system privileges have not been granted, object access privileges are required in order to execute DML statements.
System Privileges#
system_privilege
This is used to specify the name of the system access privilege that will be granted.
role
This is used to specify the name of the role to be granted.
-
A role cannot be granted to another role or PUBLIC.
-
A maximum number of 126 roles can be granted per user.
-
A user cannot immediately use a role which has been granted. The privileges of a role are enabled after the user has connected to a database.
ALL PRIVILEGES
This is used to grant all system privileges to the specified user or users.
TO user
This is used to specify name of the user or users to whom the system privilege(s) will be granted.
TO role
This is used to specify the name of the role to which system privileges are to be granted.
TO PUBLIC
This is used to specify that the system privilege(s) is/are to be granted to all users.
Notes:#
- Just like the SYS user, any user to whom the GRANT ANY PRIVILEGES system is granted can grant all system access privileges to other users.
- The SYS user has all system access privileges.
- The presence of the ANY keyword in the name of a system privilege indicates that the privilege pertains to all schema. For example, the SELECT ANY TABLE privilege will allow the user to whom it is granted to run a SELECT statement on any table in the database.
- The CREATE privilege is granted to allow users to create objects, and includes permission to DROP (i.e. remove) the objects they have created.
- The CREATE TABLE object privilege allows users to create indexes as well as tables. The authority to create indexes is an object privilege, not a system privilege.
- When a new user is created, the following privileges are typically granted to the user: CREATE DATABASE LINK, CREATE LIBRARY, CREATE MATERIALIZED_VIEW, CREATE PROCEDURE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE TRIGGER, CREATE VIEW.
The following query can be used to display the list of system privileges supported in Altibase:
iSQL> SELECT * FROM SYSTEM_.SYS_PRIVILEGES_ where PRIV_TYPE = 2;
Altibase supports the following system privileges.
| PrivID | System privilege | Name | Purpose |
|---|---|---|---|
| 1 | ALL | Even if this privilege is granted to a user, the user will still not have the ALTER DATABASE, DROP DATABASE, or MANAGE TABLESPACE privileges. | |
| 201 | DATABASE | ALTER SYSTEM | For changing Altibase property settings using ALTER SYSTEM statements when Altibase is online |
| 233 | ALTER DATABASE | Cannot be granted to any users other than the SYS user | |
| 234 | DROP DATABASE | Cannot be granted to any users other than the SYS user | |
| 202 | INDEX | CREATE ANY INDEX | For creating indexes not only in the user's own schema but also in other users' schema |
| 203 | ALTER ANY INDEX | For altering the definition of any index in the database. | |
| 204 | DROP ANY INDEX | For dropping any index from the database | |
| 205 | PROCEDURE | CREATE PROCEDURE | For creating stored procedures and stored functions in the user's own schema |
| 206 | CREATE ANY PROCEDURE | For creating stored procedures and stored functions not only in the user's own schema but also in other users' schema | |
| 207 | ALTER ANY PROCEDURE | For recompiling any stored procedure or function in the database | |
| 208 | DROP ANY PROCEDURE | For dropping any stored procedure or function in the database | |
| 209 | EXECUTE ANY PROCEDURE | For executing any stored procedure or function in the database | |
| 210 | SEQUENCE | CREATE SEQUENCE | For creating sequences in the user's own schema |
| 211 | CREATE ANY SEQUENCE | For creating sequences not only in the user's own schema but also in other users' schema | |
| 212 | ALTER ANY SEQUENCE | For changing the definition of any sequence in the database | |
| 213 | DROP ANY SEQUENCE | For deleting any sequence in the database | |
| 214 | SELECT ANY SEQUENCE | For querying any sequence in the database | |
| 215 | SESSION | CREATE SESSION | For connecting to the database |
| 216 | ALTER SESSION | Granted automatically to every user | |
| 217 | TABLE | CREATE TABLE | For creating tables in the user's own schema |
| 218 | CREATE ANY TABLE | For creating tables not only in the user's own schema but also in other users' schema | |
| 219 | ALTER ANY TABLE | For truncating all records from any table or changing the definition of any table in the database | |
| 220 | DELETE ANY TABLE | For deleting any table from the database | |
| 221 | DROP ANY TABLE | For dropping any table in the database | |
| 222 | INSERT ANY TABLE | For inserting new records into any table in the database | |
| 223 | LOCK ANY TABLE | For locking any table in the database | |
| 224 | SELECT ANY TABLE | For querying any table in the database | |
| 225 | UPDATE ANY TABLE | For changing the data in any table in the database | |
| 226 | USER | CREATE USER | For creating new users |
| 227 | ALTER USER | For changing the definition of any user in the database | |
| 228 | DROP USER | For dropping users | |
| 229 | VIEW | CREATE VIEW | For creating views in the user's own schema |
| 230 | CREATE ANY VIEW | For creating views not only in the user's own schema but also in other users' schema | |
| 231 | DROP ANY VIEW | For deleting any view in the database | |
| 232 | MISCELLANEOUS | GRANT ANY PRIVILEGES | For granting any system privilege to other users |
| 235 | TABLESPACES | CREATE TABLESPACE | For creating tablespaces |
| 236 | ALTER TABLESPACE | For changing the definition of a tablespace | |
| 237 | DROP TABLESPACE | For deleting tablespaces | |
| 238 | MANAGE TABLESPACE | Cannot be granted to any users other than the SYS user | |
| 240 | SYSDBA | Cannot be granted to any users other than the SYS user | |
| 241 | TRIGGER | CREATE TRIGGER | For creating new triggers |
| 242 | CREATE ANY TRIGGER | For creating triggers not only in the user's own schema but also in other users' schema | |
| 243 | ALTER ANY TRIGGER | For changing the definition of any trigger in the database | |
| 244 | DROP ANY TRIGGER | For deleting any trigger in the database | |
| 245 | SYNONYM | CREATE SYNONYM | For creating new triggers |
| 246 | CREATE PUBLIC SYNONYM | For creating a PUBLIC synonym | |
| 247 | CREATE ANY SYNONYM | For creating private synonyms not only in the user's own schema but also in other users' schema | |
| 248 | DROP ANY SYNONYM | For dropping any private synonym | |
| 249 | DROP PUBLIC SYNONYM | For dropping public synonyms | |
| 250 | DIRECTORY | CREATE ANY DIRECTORY | A directory object can be created to control a file within the stored procedure. |
| 251 | DROP ANY DIRECTORY | This privilege can drop a directory object. | |
| 252 | MATERIALIZED VIEW | CREATE MATERIALIZED VIEW | This privilege can create a new materialized view in the user's own schema |
| 253 | CREATE ANY MATERIALIZED VIEW | This privilege can create a materialized view not only in the user's own schema, but also in other user's schemas. | |
| 254 | ALTER ANY MATERIALIZED VIEW | This privilege can be modified any materialized view in the database. | |
| 255 | DROP ANY MATERIALIZED VIEW | Any materialized view in the database can be dropped with this privilege. | |
| 256 | LIBRARY | CREATE LIBRARY | For creating new library objects in the user's own schema |
| 257 | CREATE ANY LIBRARY | For creating library objects not only in the user's own schema but also in other users' schema | |
| 258 | ALTER ANY LIBRARY | For recompiling any library object in the database | |
| 259 | DROP ANY LIBRARY | For dropping any library object in the database | |
| 260 | DATABASE LINK | CREATE DATABASE LINK | New database link can be created. |
| 261 | CREATE PUBLIC_DATABASE LINK | Public database link can be created. | |
| 262 | DROP PUBLIC DATABASE LINK | Public database link can be dropped. | |
| 263 | ROLE | CREATE ROLE | For creating a new role |
| 264 | DROP ANY ROLE | For dropping all roles existing in the database | |
| 265 | GRANT ANY ROLE | For granting all roles to other users | |
| 266 | JOB | CREATE ANY JOB | This privilege can create a new job not only in the user's own schema, but also in other user's schemas. |
| 268 | ALTER ANY JOB | Any job in the database can be modified with this privilege. | |
| 267 | DROP ANY JOB | Any JOB in the database can be dropped with this privilege. |
Object Privileges#
object_privilege
This clause is used when it is desired to grant only particular privileges for the object (the table later in this section shows which privileges are supported for which objects).
ALL [PRIVILEGES]
This clause is used to grant all possible privileges for the object.
ON object
This is used to specify the object, such as a table, sequence, or stored procedure, for which to grant privileges.
ON DIRECTORY directory_name
This clause is used to specify the name of the directory object, which is used in stored procedures to manipulate directories and files in the file system, for which to grant privileges.
TO user
This is used to specify the name of the user or users to whom the object privilege(s) will be granted.
TO PUBLIC
This is used to specify that the object privilege(s) is/are to be granted to all users.
TO role
This is used to specify the name of the role to which object privileges are to be granted.
WITH GRANT OPTION
The WITH GRANT OPTION is used to enable the grantee to grant the object privileges to other users. However, this option cannot be used to grant object privileges to a role.
Summary#
The term "object owner" refers to the user who created an object.
In order to grant object access privileges, it is necessary to be the SYS user, the owner of the object, or a user to whom the relevant object access privileges have been granted with the WITH GRANT OPTION.
The owner of an object automatically has all privileges for the object.
The following query can be used to display all of the object privileges supported in Altibase.
SELECT * FROM SYSTEM_.SYS_PRIVILEGES_ where PRIV_TYPE = 1;
Altibase supports the following object privileges:
| Priv ID | Object privileges | Table | Sequence | PSM/ External Procedure | View | directory | External Library |
|---|---|---|---|---|---|---|---|
| 101 | ALTER | O | O | ||||
| 102 | DELETE | O | |||||
| 103 | EXECUTE | O | O | ||||
| 104 | INDEX | O | |||||
| 105 | INSERT | O | |||||
| 106 | REFERENCES | O | |||||
| 107 | SELECT | O | O | O | |||
| 108 | UPDATE | O | |||||
| 109 | READ | O | |||||
| 110 | WRITE | O |
All users automatically have SELECT privileges for meta tables.
Examples#
System Privileges#
\<Query 1> In the following example, the EXECUTE ANY PROCEDURE, SELECT ANY TABLE, ALTER ANY SEQUENCE, INSERT ANY TABLE, and SELECT ANY SEQUENCE system privileges are granted to the user user5.
iSQL> CREATE TABLE seqtbl(i1 INTEGER);
Create success.
iSQL> CREATE OR REPLACE PROCEDURE proc1
AS
BEGIN
FOR i IN 1 .. 10 LOOP
INSERT INTO seqtbl VALUES(i);
END LOOP;
END;
/
Create success.
iSQL> CREATE USER uare5 IDENTIFIED BY rose5;
Create success.
iSQL> GRANT EXECUTE ANY PROCEDURE, SELECT ANY TABLE TO uare5;
Grant success.
iSQL> CONNECT uare5/rose5;
Connect success.
iSQL> EXEC sys.proc1;
Execute success.
iSQL> SELECT * FROM sys.seqtbl;
SEQTBL.I1
--------------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
iSQL> CONNECT sys/manager;
Connect success.
iSQL> CREATE SEQUENCE seq1
START WITH 13
INCREMENT BY 3
MINVALUE 0 NOMAXVALUE;
Create success.
iSQL> INSERT INTO seqtbl VALUES(seq1.NEXTVAL);
1 row inserted.
iSQL> INSERT INTO seqtbl VALUES(seq1.NEXTVAL);
1 row inserted.
iSQL> SELECT * FROM seqtbl;
SEQTBL.I1
--------------
1
2
3
4
5
6
7
8
9
10
13
16
12 rows selected.
iSQL> GRANT ALTER ANY SEQUENCE, INSERT ANY TABLE, SELECT ANY SEQUENCE TO uare5;
Grant success.
iSQL> CONNECT uare5/rose5;
Connect success.
iSQL> ALTER SEQUENCE sys.seq1
INCREMENT BY 50
MAXVALUE 100
CYCLE;
Alter success.
iSQL> INSERT INTO sys.seqtbl VALUES(sys.seq1.NEXTVAL);
1 row inserted.
iSQL> INSERT INTO sys.seqtbl VALUES(sys.seq1.NEXTVAL);
1 row inserted.
iSQL> INSERT INTO sys.seqtbl VALUES(sys.seq1.NEXTVAL);
1 row inserted.
iSQL> INSERT INTO sys.seqtbl VALUES(sys.seq1.NEXTVAL);
1 row inserted.
iSQL> SELECT * FROM sys.seqtbl;
SEQTBL.I1
--------------
1
2
3
4
5
6
7
8
9
10
13
16
66
0
50
100
16 rows selected.
\<Query 2> Create a role with the name alti_role, and then grant system privileges to it, such as CREATE USER, DROP USER, etc.
iSQL> create role alti_role;
Create success.
iSQL> grant create user, drop user to alti_role;
Grant success.
iSQL> create user user01 identified by user01;
Create success.
iSQL> grant alti_role to user01;
Grant success.
iSQL> connect user01/user01
Connect success.
iSQL> create user user02 identified by user02;
Create success.
iSQL> drop user user02;
Drop success.
Object Privileges#
\<Query 1> In the following example, the SELECT and DELETE object privileges on the table employees are granted to the user uare6 with the WITH GRANT OPTION. This user then passes these privileges on to the uare7 and uare8 users.
iSQL> CREATE USER uare6 IDENTIFIED BY rose6;
Create success.
iSQL> GRANT CREATE USER TO uare6;
Grant success.
iSQL> @ ?/sample/APRE/schema/schema
iSQL> CONNECT sys/manager;
iSQL> GRANT SELECT, DELETE ON employees TO uare6 WITH GRANT OPTION;
Grant success.
iSQL> CONNECT uare6/rose6;
Connect success.
iSQL> CREATE USER uare7 IDENTIFIED BY rose7;
Create success.
iSQL> GRANT SELECT, DELETE ON sys.employees TO uare7;
Grant success.
iSQL> CONNECT uare7/rose7;
Connect success.
iSQL> DELETE FROM SYS.employees WHERE eno = 12;
1 row deleted.
iSQL> SELECT eno, e_lastname FROM sys.employees WHERE eno = 12;
ENO E_LASTNAME
-------------------------------------
No rows selected.
iSQL> CONNECT sys/manager;
Connect success.
iSQL> CREATE USER uare8 IDENTIFIED BY rose8;
Create success.
iSQL> CONNECT uare6/rose6;
Connect success.
iSQL> GRANT SELECT, DELETE ON sys.employees TO uare8;
Grant success.
Because the uare6 user was granted object access privileges using the WITH GRANT OPTION, this user can grant these privileges not only to the user uare7, who was created by uare6, but also to the user uare8, who was created by the original grantor (the SYS user).
iSQL> CONNECT uare8/rose8;
Connect success.
iSQL> DELETE FROM sys.employees WHERE eno = 13;
1 row deleted.
iSQL> SELECT eno, e_lastname FROM sys.employees WHERE eno = 13;
ENO E_LASTNAME
-------------------------------------
No rows selected.
\<Query 2> The following is an exmple of granting system and object privileges to a user and then revoking each privilege.
- The SYS user has granted all system privileges to uare9.
iSQL> CONNECT sys/manager;
Connect success.
iSQL> CREATE TABLE book(
isbn CHAR(10) PRIMARY KEY,
title VARCHAR(50),
author VARCHAR(30),
edition INTEGER DEFAULT 1,
publishingyear INTEGER,
price NUMBER(10,2),
pubcode CHAR(4));
Create success.
iSQL> CREATE TABLE inventory(
subscriptionid CHAR(10) PRIMARY KEY,
storecode CHAR(4),
purchasedate DATE,
quantity INTEGER,
paid CHAR(1));
Create success.
iSQL> CREATE USER uare9 IDENTIFIED BY rose9;
Create success.
iSQL> GRANT ALL PRIVILEGES TO uare9;
Grant success.
- The SYS user is granting user uare9 the REFERENCES privilege on the object book as the WITH GRANT OPTION.
iSQL> GRANT REFERENCES ON book TO uare9 WITH GRANT OPTION;
Grant success.
The user uare9 receives the REFERENCES object privilege for the book object from the SYS user with the WITH GRANT OPTION, and thus uare9 is able to grant another user (uare10) the REFERENCES object privilege for the book object.
- The user uare9 inputs data into the book table, which is owned by the SYS user.
iSQL> CONNECT uare9/rose9;
Connect success.
iSQL> INSERT INTO sys.book VALUES ('0070521824', 'Software Engineering', 'Roger S. Pressman', 4, 1982, 100000, 'CHAU');
1 row inserted.
iSQL> INSERT INTO sys.book VALUES ('0137378424', 'Database Processing', 'David M. Kroenke', 6, 1972, 80000, 'PREN');
1 row inserted.
The user uare9 inputs data into the inventory table, which is owned by the SYS user.
```sql
iSQL> INSERT INTO sys.inventory VALUES('BORD000002', 'BORD', '12-Jun-2003', 6, 'N'); 1 row inserted. iSQL> INSERT INTO sys.inventory VALUES('MICR000001', 'MICR', '07-Jun-2003', 7, 'N'); 1 row inserted. ```
- The user uare9 queries the book table, which is owned by the SYS user.
iSQL> SELECT * FROM sys.book;
ISBN TITLE AUTHOR EDITION PUBLISHINGYEAR PRICE PUBCODE
----------------------------------------------------------------------------------------------------------------
0070521824 Software Engineering Roger S. Pressman 4 1982 100000 CHAU
0137378424 Database Processing David M. Kroenke 6 1972 80000 PREN
2 rows selected.
The user uare9 queries the inventory table, which is owned by the SYS user.
iSQL> SELECT * FROM sys.inventory;
SUBSCRIPTIONID STORECODE PURCHASEDATE QUANTITY PAID
-------------------------------------------------------------------------------------------------
BORD000002 BORD 12-JUN-2003 6 N
MICR000001 MICR 07-JUN-2003 7 N
2 rows selected.
iSQL> CREATE TABLE book(
isbn CHAR(10) PRIMARY KEY,
title VARCHAR(50),
author VARCHAR(30),
edition INTEGER DEFAULT 1,
publishingyear INTEGER,
price NUMBER(10,2),
pubcode CHAR(4));
Create success.
iSQL> CREATE TABLE inventory(
subscriptionid CHAR(10) PRIMARY KEY,
isbn CHAR(10) CONSTRAINT fk_isbn REFERENCES book(isbn),
storecode CHAR(4),
purchasedate DATE,
quantity INTEGER,
paid CHAR(1));
Create success.
- Because the SYS user granted ALL PRIVILEGES to the user uare9, uare9 can create other users.
iSQL> CREATE USER uare10 IDENTIFIED BY rose10;
Create success.
- Because the SYS user granted the REFERENCES privilege to the user uare9 with the WITH GRANT OPTION, uare9 can pass this privilege on to other users.
iSQL> GRANT REFERENCES ON sys.book TO uare10;
Grant success.
- Because the SYS user granted the GRANT ANY PRIVILEGES privilege to the user uare9, uare9 can grant system privileges to other users.
iSQL> GRANT ALTER ANY TABLE, INSERT ANY TABLE, SELECT ANY TABLE, DELETE ANY
TABLE TO uare10;
Grant success.
- Because the user uare10 has the ALTER ANY TABLE and REFERENCES privileges, uare10 can create a constraint in a table belonging to another user.
iSQL> CONNECT uare10/rose10;
Connect success.
iSQL> ALTER TABLE sys.inventory
ADD COLUMN (isbn CHAR(10) CONSTRAINT fk_isbn REFERENCES sys.book(isbn));
Alter success.
- Because the user uare10 has the INSERT ANY TABLE privilege, uare10 can enter data into a table belonging to uare9.
iSQL> INSERT INTO uare9.book VALUES('0471316156', 'JAVA and CORBA', 'Robert Orfali', 2, 1998, 50000, 'PREN');
1 row inserted.
iSQL> INSERT INTO uare9.inventory VALUES('TOWE000001', '0471316156', 'TOWE', '01-Jun-2003', 5, 'N');
1 row inserted.
Because the user uare10 has the INSERT ANY TABLE privilege, uare10 can enter data into a table belonging to the SYS user.
iSQL> INSERT INTO sys.book VALUES('053494566X', 'Working Classes', 'Robert Orfali', 1, 1999, 80000, 'WILE');
1 row inserted.
iSQL> INSERT INTO sys.inventory VALUES('MICR000005', 'WILE', '28-JUN-1999', 8, 'N', '053494566X');
1 row inserted.
-
Because the user uare10 has the SELECT ANY TABLE privilege, uare10 can query a table belonging to uare9.
iSQL> SELECT * FROM uare9.book; ISBN TITLE AUTHOR EDITION PUBLISHINGYEAR PRICE PUBCODE ------------------------------------------------------------------------------------------------------------------------ 0471316156 JAVA and CORBA Robert Orfali 2 1998 50000 PREN 1 row selected. iSQL> SELECT * FROM uare9.inventory; SUBSCRIPTIONID ISBN STORECODE PURCHASEDATE QUANTITY PAID ------------------------------------------------------------------------------------------------------------------------ TOWE000001 0471316156 TOWE 01-JUN-2003 5 N 1 row selected.Because the user uare10 has the SELECT ANY TABLE privilege, uare10 can query a table belonging to the SYS user.
iSQL> SELECT * FROM sys.book; ISBN TITLE AUTHOR EDITION PUBLISHINGYEAR PRICE PUBCODE ------------------------------------------------------------------------------------------------------------------ 0070521824 Software Engineering Roger S. Pressman 4 1982 100000 CHAU 0137378424 Database Processing David M. Kroenke 6 1972 80000 PREN 053494566X Working Classes Robert Orfali 1 1999 80000 WILE 3 rows selected. iSQL> SELECT * FROM sys.inventory; SUBSCRIPTIONID STORECODE PURCHASEDATE QUANTITY PAID ISBN ------------------------------------------------------------------------------------------------------------------------ BORD000002 BORD 12-JUN-2003 6 N MICR000001 MICR 07-JUN-2003 7 N MICR000005 WILE 28-JUN-1999 8 N 053494566X 3 rows selected. -
Because the user uare10 has the DELETE ANY TABLE privilege, uare10 can delete data from a table belonging to the SYS user.
iSQL> DELETE FROM uare9.inventory WHERE subscriptionid = 'TOWE000001'; 1 row deleted. iSQL> SELECT * FROM uare9.inventory; SUBSCRIPTIONID ISBN STORECODE PURCHASEDATE QUANTITY PAID ------------------------------------------------------------------------------------------------------------------------ No rows selected. iSQL> DELETE FROM sys.inventory WHERE subscriptionid = 'MICR000005'; 1 row deleted. iSQL> SELECT * FROM sys.inventory; SUBSCRIPTIONID STORECODE PURCHASEDATE QUANTITY PAID ISBN ------------------------------------------------------------------------------------------------------------------------ BORD000002 BORD 12-JUN-2003 6 N MICR000001 MICR 07-JUN-2003 7 N 2 rows selected. -
The user uare9 revokes all privileges that uare9 granted to uare10 without executing the REVOKE ALL statement.
iSQL> CONNECT uare9/rose9; Connect success. iSQL> REVOKE ALTER ANY TABLE, INSERT ANY TABLE, SELECT ANY TABLE, DELETE ANY TABLE FROM uare10; Revoke success. -
When uare10's REFERENCES privilege is revoked, referential integrity constraints that refer to primary key or unique keys in the sys.book table, which belongs to uare10's schema, are also dropped.
iSQL> REVOKE REFERENCES ON sys.book FROM uare10 CASCADE CONSTRAINTS; Revoke success. -
All of uare9's system privileges are revoked.
iSQL> CONNECT sys/manager; Connect success. iSQL> REVOKE ALL PRIVILEGES FROM uare9; Revoke success. -
The GRANT ANY PRIVILEGES privilege is revoked from uare9.
iSQL> REVOKE GRANT ANY PRIVILEGES FROM uare9; Revoke success. -
The REFERENCES privilege on the book table is revoked from uare9.
iSQL> REVOKE REFERENCES ON book FROM uare9; Revoke success.
\<Query 3> Grant the SELECT, UPDATE, INSER, and DELETE object privileges on the T1 table of user01 to the alti_role. Then, grant the alti_role role to the user user02.
iSQL> create role alti_role;
Create success.
iSQL> create user user01 identified by user01;
Create success.
iSQL> create user user02 identified by user02;
Create success.
iSQL> connect user01/user01
Connect success.
iSQL> create table t1 (i1 integer);
Create success.
iSQL> grant select,insert,update,delete on t1 to alti_role;
Grant success.
iSQL> connect sys/manager
Connect success.
iSQL> grant alti_role to user02;
Grant success.
iSQL> connect user02/user02;
Connect success.
iSQL> insert into user01.t1 values (1);
1 row inserted.
iSQL> insert into user01.t1 values (2);
1 row inserted.
iSQL> select * from user01.t1;
T1.I1
--------------
1
2
2 rows selected.
iSQL> update user01.t1 set i1=3 where i1=1;
1 row updated.
iSQL> select * from user01.t1;
T1.I1
--------------
2
3
2 rows selected.
iSQL> delete from user01.t1 where i1=2;
1 row deleted.
iSQL> select * from user01.t1;
T1.I1
--------------
3
1 row selected.
PURGE TABLE#
Syntax#
purge_table::=
Prerequisites#
The SYS user, table's owner, and the user with the DROP ANY TABLE system privilege can execute PURGE TABLE.
Description#
PURGE TABLE drops the specified table from the recycle bin. If there are several tables with the same name, the first table to be dropped is dropped from the database.
user_name
This is used to specifie the name of the table owner.
table_name
This is used specify the name of the table to be dropped from the recycle bin. The table takes the name it had before it was moved to the recycle bin, or a new name generated by the system when it was moved to the recycle bin.
Examples#
\<Query> Purge the table t1 in which DROP was executed from the recyle bin.
iSQL> alter session set recyclebin_enable = 1;
Alter success.
iSQL> create table t1 (i1 integer);
Create success.
iSQL> drop table t1;
Drop success.
iSQL> purge table t1;
Purge success.
RENAME TABLE#
Syntax#
rename ::=
Prerequisites#
The SYS user, the owner of the schema containing the table, and users having the ALTER ANY TABLE system privilege can execute the RENAME TABLE statement.
Description#
This statement is used to change the name of the specified table. Only the table name is altered; none of the data stored therein are changed.
user_name
This is used to specify the name of the owner of the table to be renamed. If this is omitted, Altibase will assume that the table belongs to the schema of the user connected via the current session.
old_name
This is used to specify the current name of the table.
new_name
This is used to specify the new name for the table.
Consideration#
The name of a replication target table cannot be changed.
Example#
\<Query> Rename the table employees to emp1
iSQL> RENAME employees TO emp1;
Rename success.
Or
iSQL> ALTER TABLE employees
RENAME TO emp1;
Alter success.
REVOKE#
Syntax#
revoke ::=
revoke_system_privilege ::=
revoke_object_privilege ::=
Prerequisites#
The SYS user or the user who originally granted the privilege to be revoked can revoke privileges.
Description#
This statement is used to revokes system privileges, object privileges on particular objects or roles from a specified user. This statement can also revoke system privileges or object privileges from roles.
This statement can only revoke system privileges, object privileges and roles that were granted with the GRANT command.
System Privileges#
role
This is used to specify the role to be revoked.
system_privilege
This is used to specify the system privilege(s) to be revoked. Please refer to the description of the GRANT statement for the complete list of system privileges.
ALL PRIVILEGES
This is used to specify that all system privileges that have been granted by the user executing this revoke statement are to be revoked.
System privileges that were granted using the ALL PRIVILEGES clause can be rocked using the ALL PRIVILEGES clause.
FROM user
This is used to identify the user from whom the privilege(s) will be revoked.
FROM role
This is used to specify the role from which system privileges are to be revoked.
FROM PUBLIC
Use the PUBLIC keyword to revoke the privilege(s) from all users.
Note: System privileges granted using the PUBLIC keyword can be revoked using the PUBLIC keyword.
Object Privileges#
role
This is used to specify the role to be revoked.
object_privilege
This is used to specify the object privilege that is to be revoked. Please refer to the table in the description of the GRANT statement for more information about object privileges.
ALL [PRIVILEGES]
The ALL PRIVILEGES (or merely ALL) clause is used to revoke all object privileges that have been granted to the user by the user executing this revoke statement.
When revoking privileges using the ALL [PRIVILEGES] clause, all object access privileges granted to the user are revoked. This even includes object privileges that were not granted using the ALL [PRIVILEGES] clause. For example, an object privilege granted to a user in this way:
GRANT SELECT ON object TO user;
can of course be explicitly revoked in this way:
REVOKE SELECT ON object FROM user;
It can also be revoked together with all other privileges in this way:
REVOKE ALL ON object FROM user;
ON object
This is used to specify the object (table, sequence, stored procedure, etc.) for which the permissions are to be revoked.
ON DIRECTORY directory_name
This clause is used to revoke privileges from the specified directory object.
FROM user
This is used to identify the user from whom the privilege(s) will be revoked.
FROM role
This clause is used to identify the user(s) from whom the privilege(s) will be revoked.
FROM PUBLIC
The PUBLIC keyword is used to revoke the privilege(s) from all users.
CASCADE CONSTRAINTS
This clause is relevant only when revoking the REFERENCES privilege or using the ALL [PRIVILEGES] clause. It is used to specify that any related referential integrity constraints are also to be dropped. These were granted either explicitly or implicitly using the ALL [PRIVILEGES] clause.
Examples#
\<Query 1> Revoke object privileges.
iSQL> CONNECT uare6/rose6;
Connect success.
iSQL> REVOKE SELECT, DELETE ON sys.employees
FROM uare7, uare8;
Revoke success.
iSQL> CONNECT uare7/rose7;
Connect success.
iSQL> SELECT eno, e_lastname FROM sys.employees WHERE eno = 15;
[ERR-311B1: The user must have the SELECT_ANY_TABLE privilege(s) to execute this statement.]
After the SELECT and DELETE privileges for the employees table have been revoked, an error message is displayed when an attempt is made to execute a SELECT statement on that table.
\<Query 2> Among the CREATE USER and DROP USER system privileges granted to the role, revoke the CREATE USER privilege.
iSQL> create role alti_role;
Create success.
iSQL> grant create user, drop user to alti_role;
Grant success.
iSQL> create user user01 identified by user01;
Create success.
iSQL> grant alti_role to user01;
Grant success.
iSQL> connect user01/user01
Connect success.
iSQL> create user user02 identified by user02;
Create success.
iSQL> drop user user02;
Drop success.
iSQL> connect sys/manager
Connect success.
iSQL> revoke create user from alti_role;
Revoke success.
iSQL> connect user01/user01
Connect success.
iSQL> create user user02 identified by user02;
[ERR-311B1 : The user must have CREATE_USER privilege(s) to execute this statement.]
\<Query 3> Revoke the DELETE object privilege on table t1 of user01 from the alti_role role.
iSQL> create role alti_role;
Create success.
iSQL> create user user01 identified by user01;
Create success.
iSQL> create user user02 identified by user02;
Create success.
iSQL> connect user01/user01
Connect success.
iSQL> create table t1 (i1 integer);
Create success.
iSQL> grant select,insert,update,delete on t1 to alti_role;
Grant success.
iSQL> connect sys/manager
Connect success.
iSQL> grant alti_role to user02;
Grant success.
iSQL> connect user02/user02;
Connect success.
iSQL> insert into user01.t1 values (1);
1 row inserted.
iSQL> insert into user01.t1 values (2);
1 row inserted.
iSQL> select * from user01.t1;
I1
--------------
1
2
2 rows selected.
iSQL> update user01.t1 set i1=3 where i1=1;
1 row updated.
iSQL> select * from user01.t1;
I1
--------------
2
3
2 rows selected.
iSQL> delete from user01.t1 where i1=2;
1 row deleted.
iSQL> select * from user01.t1;
I1
--------------
3
1 row selected.
iSQL> connect user01/user01
Connect success.
iSQL> revoke delete on t1 from alti_role;
Revoke success.
iSQL> connect user02/user02
Connect success.
iSQL> delete from user01.t1 where i1=3;
[ERR-311B1 : The user must have DELETE_ANY_TABLE privilege(s) to execute this statement.]
TRUNCATE TABLE#
Syntax#
truncate ::=
Prerequisites#
The SYS user, the owner of the schema containing the table, and users having the ALTER ANY TABLE system privilege can execute the TRUNCATE TABLE statement.
Description#
The TRUNCATE TABLE statement is used to remove all records from the specified table.
user_name
This is used to specify the name of the owner of the table to be truncated. If omitted, Altibase will assume that the table belongs to the schema of the user connected via the current session.
tbl_name
This is used to specify the name of the table to be truncated.
If the name of a queue table is specified in tbl_name, all ENQUEUE messages are also deleted at the same time.
TRUNCATE vs. DELETE#
When the TRUNCATE statement is executed, all of the pages in the table are returned to the database as free pages. Therefore, these pages are available for use by other tables. In contrast, when the DELETE statement is used to remove all of the rows from a table, any pages that are emptied are not returned to the database, but remain in a state in which they are reserved for future use by the same table, meaning that memory usage is not reduced.
Because the TRUNCATE statement is a DDL statement, it cannot be rolled back once it has executed successfully.
Consideration#
Once the records have been successfully deleted, they cannot be recovered. However, if an error occurs before the completion of execution of the statement, or in the event of a server error, the statement can be rolled back.
Example#
\<Query> Use the TRUNCATE statement to remove all data from the employees table.
iSQL> TRUNCATE TABLE employee;
Truncate success.
-
To maximize performance when the system is restarted, a command specifying that indexes are built using parallel processing can be used. ↩
-
The time required to build an index for a table that contains a large amount of data is proportional to the number of indexes that have been defined for the table. Although it is not possible to build multiple indexes for the same table simultaneously, the index building time can be minimized by building them one by one using parallel processing. ↩