ALTER TABLE

ALTER TABLE#

Syntax#

alter_table ::=#

log_compression_clause ::=, alter_table_properties ::=, alter_table_segment_properties ::=, alter_table_partitioning ::=, alter_table_tablespace ::=, column_clauses ::=, constraints_clauses ::=, aging_clause ::=, compact_clause ::=, allocate_extent_clause ::=, access_mode_clause ::=

log_compression_clause ::=#

log_compression_clause

alter_table_properties ::=#

image52_alter_table_properties

logging_clause ::=, parallel_clause ::=, row_movement_clause ::=

logging_clause ::=#

logging_clause

alter_table_segment_properties ::=#

image53_alter_table_segment_properties

alter_table_segment_attribute_clause ::=#

image54_alter_table_segment_attribute_clause

alter_table_partitioning ::=#

ALTER_TABLE_PARTITIONING

add_table_partition ::=, alter_partition ::=, coalesce_table_partition ::=, drop_table_partition ::=, merge_table_partition ::=, rename_table_partition ::=, split_table_partition ::=, truncate_table_partition ::=, partition_access_mode ::=

add_table_partition ::=#

image56_add_table_partition

partition_spec ::=, index_partition_spec ::=

alter_partition ::=#

alter_partition

partition_lob_column_clause ::=#

partition_lob_column_clause

merge_table_partition ::=#

image62_merge_table_partition

partition_spec ::=

split_table_partition ::=#

image64_split_table_partition

partition_spec ::=

table_partition_description ::=#

table_partition_description

lob_column_properties ::=

index_partition_description ::=#

index_partition_description

column_clauses ::=#

image66_column_clauses

add_column_clauses ::=, alter_column_clause ::=, modify_column_clause ::=, drop_column_clause ::=, rename_column_clause ::=, reorganize_column_clause ::=

column_definition ::=#

column_definition

encrypt_clause ::=, variable_clause ::=, in_row_clause ::=, default_clause ::=, column_constraint ::=

partition_lob_storage_clause ::=#

image68_partition_lob_storage_clause

LOB_storage_clause ::=

modify_column_spec ::=#

image71_modify_column_spec

constraints_clauses ::=#

constraints_clauses

add_table_constraint_clauses ::=, modify_constraint_clause ::=, rename_constraint_clauses ::=, drop_constraint_clauses ::=

add_table_constraint_clauses ::=#

add_table_constraint_clauses

table_constraint_for_alter ::=#

table_constraint_for_alter

using_index_clause ::=, referential_constraint ::=, constraint_state ::=, check_clause ::=

constraint_state ::=#

constraint_state

modify_constraint_clause ::=#

modify_constraint_clause

constraint_state ::=

rename_constraint_clauses ::=#

rename_constraint_clauses

drop_constraint_clause ::=#

drop_constraint_clause

column_constraint ::=

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	big int	dou ble	float	int eger	num ber	num eric	real	small int	date	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 started¹or 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 time² 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 1: 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.

Example 2: 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.

Example 3: 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.

Example 4: 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.

Example 5: 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.

Example 6: 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.

Example 7: 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.

Example 8: 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.

Example 9: Activating and Deactivating Indexes#

<Query> Disable all indexes in the table orders.

iSQL> ALTER TABLE orders ALL INDEX DISABLE;
Alter success.

Example 10: 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
);

Example 11: Partition Operation#

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;

Example 12: 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
);

Example 13: 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 )
);

Example 14: 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;

Example 15: 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.

Example 16: 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.

Disk -> Memory#

iSQL> ALTER TABLE sales_table ALTER TABLESPACE SYS_TBS_MEM_DATA;
Alter success.

Memory -> Memory#

iSQL> ALTER TABLE sales_table ALTER TABLESPACE mem_tbs_0;
Alter success.

Memory -> Volatile#

iSQL> ALTER TABLE sales_table ALTER TABLESPACE vol_tbs_0;
Alter success.

Volatile -> Disk#

iSQL> ALTER TABLE sales_table ALTER TABLESPACE SYS_TBS_DISK_DATA;
Alter success.

Disk -> Volatile#

iSQL> ALTER TABLE sales_table ALTER TABLESPACE vol_tbs_0;
Alter success.

Volatile -> Memory#

iSQL> ALTER TABLE sales_table ALTER TABLESPACE mem_tbs_0;
Alter success.

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.

Disk -> Memory#

iSQL> ALTER TABLE part_table ALTER TABLESPACE SYS_TBS_MEM_DATA;
Alter success.

Memory -> Memory#

iSQL> ALTER TABLE part_table ALTER TABLESPACE mem_tbs_0;
Alter success.

Memory -> Volatile#

iSQL> ALTER TABLE part_table ALTER TABLESPACE vol_tbs_0;
Alter success.

Volatile -> Disk#

iSQL> ALTER TABLE part_table ALTER TABLESPACE SYS_TBS_DISK_DATA;
Alter success.

Disk -> Volatile#

iSQL> ALTER TABLE part_table ALTER TABLESPACE vol_tbs_0;
Alter success.

Volatile -> Memory#

iSQL> ALTER TABLE part_table ALTER TABLESPACE mem_tbs_0;
Alter success.

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.

Disk -> Memory#

iSQL> ALTER TABLE text_table ALTER TABLESPACE mem_tbs_0 INDEX ( text_table_idx TABLESPACE mem_tbs_1 );
Alter success.

Memory -> Volatile#

iSQL> ALTER TABLE text_table ALTER TABLESPACE vol_tbs_0 INDEX ( text_table_idx TABLESPACE vol_tbs_1 );
Alter success.

Volatile -> Disk#

iSQL> ALTER TABLE text_table ALTER TABLESPACE disk_tbs_0 INDEX ( text_table_idx TABLESPACE disk_tbs_1 );
Alter success.

Disk -> Volatile#

iSQL> ALTER TABLE text_table ALTER TABLESPACE vol_tbs_1 INDEX ( text_table_idx TABLESPACE vol_tbs_0 );
Alter success.

Volatile -> Memory#

iSQL> ALTER TABLE text_table ALTER TABLESPACE mem_tbs_1 INDEX ( text_table_idx TABLESPACE mem_tbs_0 );
Alter success.

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.

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.

Disk -> Memory#

iSQL> ALTER TABLE data_table ALTER PARTITION part_1 TABLESPACE mem_tbs_0;
Alter success.

Memory -> Volatile#

iSQL> ALTER TABLE data_table ALTER PARTITION part_1 TABLESPACE vol_tbs_0;
Alter success.

Volatile -> Disk#

iSQL> ALTER TABLE data_table ALTER PARTITION part_1 TABLESPACE disk_tbs_0;
Alter success.

Disk -> Volatile#

iSQL> ALTER TABLE data_table ALTER PARTITION part_1 TABLESPACE vol_tbs_1;
Alter success.

Volatile -> Memory#

iSQL> ALTER TABLE data_table ALTER PARTITION part_1 TABLESPACE mem_tbs_1;
Alter success.

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.

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.

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.

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.

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.

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.

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.

Memory -> Disk#

iSQL> ALTER TABLE blob_table ALTER PARTITION part_1 TABLESPACE disk_tbs_0 LOB ( data TABLESPACE disk_tbs_1 );
Alter 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. ↩