ALTER TABLE

Purpose

You can use this statement to modify the structure of an existing table. For example, you can modify a table and its attributes, add columns, modify columns and their attributes, and drop columns.

Syntax

alter_table_stmt:
      ALTER TABLE table_name  alter_table_action_list;


alter_table_action_list:
    alter_table_action [, alter_table_action ...]

alter_table_action:
      ADD {column_definition | (column_definition_list)}
    | ADD [CONSTRAINT [constraint_name]] UNIQUE (column_name [, column_name ]...)
    | ADD [CONSTRAINT [constraint_name]] FOREIGN KEY (column_name_list) references_clause
    | ADD [CONSTRAINT [constraint_name]] CHECK (expr)
    | ADD CONSTRAINT constraint_name PRIMARY KEY (column_name)
    | ADD CONSTRAINT constraint_name FOREIGN KEY(foreign_col_name) REFERENCES              
        reference_tbl_name(column_name);
    | ALTER INDEX index_name [VISIBLE | INVISIBLE]

    | ADD range_partition_list
    | DROP {INDEX | KEY} index_name
    | DROP {PARTITION | SUBPARTITION} partition_name_list [UPDATE GLOBAL INDEXES]
    | DROP TABLEGROUP
    | DROP CONSTRAINT constraint_name
    | DROP PRIMARY KEY
    | DROP [COLUMN] column_name
    | {ENABLE | DISABLE} CONSTRAINT constraint_name
    | MODIFY [COLUMN] column_definition
    | MODIFY CONSTRAINT constraint_name { ENABLE | DISABLE }
    | MODIFY PRIMARY KEY (column_name_list)
    | RENAME [TO] table_name
    | RENAME COLUMN old_col_name TO new_col_name
    | REORGANIZE PARTITION name_list INTO partition_range_or_list
    | SET TABLEGROUP tablegroup_name
    | [SET] table_option_list
    | TRUNCATE {PARTITION | SUBPARTITION} partition_name_list [UPDATE GLOBAL INDEXES]


rename_table_action_list:
    rename_table_action [, rename_table_action ...]

rename_table_action:
    table_name TO table_name

column_definition_list:
    column_definition [, column_definition ...]

column_definition:
    column_name data_type
    [DEFAULT const_value] [AUTO_INCREMENT]
    [NULL | NOT NULL] [[PRIMARY] KEY] [UNIQUE [KEY]] comment


column_desc_list:
    column_desc [, column_desc ...]

column_desc:
     column_name [(length)] [ASC | DESC]

references_clause:
     REFERENCES table_name [ (column_name, column_name ...) ] [ON DELETE {CASCADE}]


index_option:
      [GLOBAL | LOCAL]
    | block_size
    | compression
    | STORING(column_name_list)
    | comment

table_option_list:
    table_option [ table_option ...]

table_option:
      table_group
    | block_size
    | compression
    | AUTO_INCREMENT [=] INT_VALUE
    | comment
    | parallel_clause

parallel_clause:
    {NOPARALLEL | PARALLEL integer}

partition_option:
      PARTITION BY HASH(column_name_list)
      [subpartition_option] hash_partition_define
    | PARTITION BY RANGE (column_name_list)
      [subpartition_option] (range_partition_list)
    | PARTITION BY LIST (column_name_list)
      [subpartition_option] (list_partition_list)

/*Template-based subpartition*/
subpartition_option:
      SUBPARTITION BY HASH (column_name_list) hash_subpartition_define
    | SUBPARTITION BY RANGE (column_name_list) SUBPARTITION TEMPLATE
      (range_subpartition_list)
    | SUBPARTITION BY LIST (column_name_list) SUBPARTITION TEMPLATE
      (list_subpartition_list)

/*Non-template-based subpartition*/
subpartition_option:
      SUBPARTITION BY HASH (column_name_list)
    | SUBPARTITION BY RANGE (column_name_list)
    | SUBPARTITION BY LIST (column_name_list)

subpartition_list:
      (hash_subpartition_list)
    | (range_subpartition_list)
    | (list_subpartition_list)

hash_partition_define:
      PARTITIONS partition_count [TABLESPACE tablespace] [compression]
    | (hash_partition_list)

hash_partition_list:
    hash_partition [, hash_partition, ...]

hash_partition:
    partition [partition_name] [subpartition_list/*Only non-template-based subpartitions can be defined.*/]

hash_subpartition_define:
      SUBPARTITIONS subpartition_count
    | SUBPARTITION TEMPLATE (hash_subpartition_list)

hash_subpartition_list:
    hash_subpartition [, hash_subpartition, ...]

hash_subpartition:
    subpartition [subpartition_name]

range_partition_list:
    range_partition [, range_partition ...]

range_partition:
    PARTITION [partition_name]
    VALUES LESS THAN {(expression_list) | (MAXVALUE)}
    [subpartition_list/*Only non-template-based subpartitions can be defined.*/]
    [ID = num] [physical_attribute_list] [compression]

range_subpartition_list:
    range_subpartition [, range_subpartition ...]

range_subpartition:
    SUBPARTITION subpartition_name
    VALUES LESS THAN {(expression_list) | MAXVALUE} [physical_attribute_list]

list_partition_list:
    list_partition [, list_partition] ...

list_partition:
    PARTITION [partition_name]
    VALUES (DEFAULT|expression_list)
    [subpartition_list/*Only non-template-based subpartitions can be defined.*/]
    [ID num] [physical_attribute_list] [compression]

list_subpartition_list:
    list_subpartition [, list_subpartition] ...

list_subpartition:
    SUBPARTITION [partition_name] VALUES (DEFAULT|expression_list) [physical_attribute_list]

expression_list:
    expression [, expression ...]

column_name_list:
    column_name [, column_name ...]

partition_name_list:
    partition_name [, partition_name ...]

partition_count | subpartition_count:
    INT_VALUE

Parameters

Examples

• Modify the data type of col1 in the tbl1 table.

  obclient> CREATE TABLE tbl1(col1 VARCHAR(3));
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl1 MODIFY col1 CHAR(10);
  Query OK, 0 rows affected
  
  obclient> DESCRIBE tbl1;
  +-------+----------+------+-----+---------+-------+
  | FIELD | TYPE     | NULL | KEY | DEFAULT | EXTRA |
  +-------+----------+------+-----+---------+-------+
  | COL1  | CHAR(10) | YES  | NULL | NULL    | NULL  |
  +-------+----------+------+-----+---------+-------+
  1 row in set
  

• Change the name of the col1 column in the tbl1 table to col2.

  obclient> ALTER TABLE tbl1 RENAME COLUMN col1 TO col2;
  Query OK, 0 rows affected
  
  obclient> DESCRIBE tbl1;
  +-------+-------------+------+-----+---------+-------+
  | FIELD | TYPE        | NULL | KEY | DEFAULT | EXTRA |
  +-------+-------------+------+-----+---------+-------+
  | COL2  | VARCHAR2(10) | YES  | NULL | NULL    | NULL  |
  +-------+-------------+------+-----+---------+-------+
  1 row in set
  

• Add and drop a column.

  • Create a table named tbl2.

    obclient> CREATE TABLE tbl2 (col1 NUMBER(30) PRIMARY KEY,col2 VARCHAR(50));
    Query OK, 0 rows affected
    

  • Add the col3 column to the tbl2 table.

    obclient> ALTER TABLE tbl2 ADD col3 NUMBER(30);
    Query OK, 0 rows affected
    
    obclient> DESCRIBE tbl2;
    +-------+--------------+------+-----+---------+-------+
    | FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
    +-------+--------------+------+-----+---------+-------+
    | COL1  | NUMBER(30)   | NO   | PRI | NULL    | NULL  |
    | COL2  | VARCHAR2(50) | YES  | NULL | NULL    | NULL  |
    | COL3  | NUMBER(30)   | YES  | NULL | NULL    | NULL  |
    +-------+--------------+------+-----+---------+-------+
    3 rows in set
    

  • Drop the col3 column from the tbl2 table.

    obclient> ALTER TABLE tbl2 DROP COLUMN col3;
    Query OK, 0 rows affected
    
    obclient> DESCRIBE tbl2;
    +-------+--------------+------+-----+---------+-------+
    | FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
    +-------+--------------+------+-----+---------+-------+
    | COL1  | NUMBER(30)   | NO   | PRI | NULL    | NULL  |
    | COL2  | VARCHAR2(50) | YES  | NULL | NULL    | NULL  |
    +-------+--------------+------+-----+---------+-------+
    2 rows in set
    

  • Create a unique index for the table tbl2.

    obclient> CREATE TABLE tbl2 (col1 NUMBER(30) PRIMARY KEY,col2 VARCHAR(50), col3 INT);
    Query OK, 0 rows affected
    
    obclient> ALTER TABLE tbl2 ADD CONSTRAINT constraint_TBL2 UNIQUE (col2, col3);
    Query OK, 0 rows affected
    
    obclient [SYS]> DESC tbl2;
    +-------+--------------+------+-----+---------+-------+
    | FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
    +-------+--------------+------+-----+---------+-------+
    | COL1  | NUMBER(30)   | NO   | PRI | NULL    | NULL  |
    | COL2  | VARCHAR2(50) | YES  | MUL | NULL    | NULL  |
    | COL3  | NUMBER(38)   | YES  | NULL | NULL    | NULL  |
    +-------+--------------+------+-----+---------+-------+
    3 rows in set
    
    obclient> INSERT INTO tbl2 VALUES('1','2','2');
    Query OK, 1 row affected
    
    obclient> INSERT INTO tbl2 VALUES('2','2','2');
    ORA-00001: unique constraint '2-2' for key 'CONSTRAINT_TBL2' violated
    
    obclient> INSERT INTO tbl2 VALUES('2','3','2');
    Query OK, 1 row affected
    

• Add the p1_r4 subpartition to the p1 partition in a non-template-based subpartitioned table named tbl3.

  obclient> ALTER TABLE tbl3 MODIFY PARTITION p1 ADD SUBPARTITION p1_r4 VALUES LESS THAN(2022);
  Query OK, 0 rows affected
  

• Drop the p2_r3 subpartition from a non-template-based subpartitioned table named tbl3.

  obclient> ALTER TABLE tbl3 DROP SUBPARTITION p2_r3;
  Query OK, 0 rows affected
  

• Add the p4 partition to a non-template-based subpartitioned table named tbl3. You need to specify both the partition definition and the subpartition definition.

  obclient> ALTER TABLE tbl3 ADD PARTITION p4 VALUES LESS THAN (400)
  (
    SUBPARTITION p4_r1 VALUES LESS THAN (2019),
    SUBPARTITION p4_r2 VALUES LESS THAN (2020),
    SUBPARTITION p4_r3 VALUES LESS THAN (2021)
  );
  Query OK, 0 rows affected
  

• Add the p3 partition to a template-based subpartitioned table named tbl4. You need to specify only the partition definition. The subpartition definition is filled in automatically based on the template.

  obclient> CREATE TABLE tbl4(col1 INT, col2 INT, PRIMARY KEY(col1,col2))
       PARTITION BY RANGE(col1)
       SUBPARTITION BY RANGE(col2)
       SUBPARTITION TEMPLATE
       (
       SUBPARTITION p0 VALUES LESS THAN (50),
       SUBPARTITION p1 VALUES LESS THAN (100)
       )
       (
       PARTITION p0 VALUES LESS THAN (100),
       PARTITION p1 VALUES LESS THAN (200),
       PARTITION p2 VALUES LESS THAN (300)
       );
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl4 ADD PARTITION p3 VALUES LESS THAN (400);
  Query OK, 0 rows affected
  

• Change the DOP of the tbl5 table to 3.

  obclient> CREATE TABLE tbl5(col1 int primary key, col2 int) PARALLEL 5;
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl5 PARALLEL 3;
  Query OK, 0 rows affected
  

• Modify the status of a FOREIGN KEY constraint.

  obclient> CREATE TABLE MMS_GROUPUSER (
    "ID" VARCHAR2(254 BYTE) NOT NULL,
    "GROUPID" VARCHAR2(254 BYTE),
    "USERID" VARCHAR2(254 BYTE),
    CONSTRAINT "PK_MMS_GROUPUSER" PRIMARY KEY ("ID"),
    CONSTRAINT "FK_MMS_GROUPUSER_02" FOREIGN KEY ("GROUPID") REFERENCES MMS_GROUPUSER ("ID") ON DELETE CASCADE DISABLE
  );
  Query OK, 0 rows affected
  
  obclient> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS FROM user_constraints WHERE CONSTRAINT_NAME LIKE 'FK_MMS_GROUPUSE%';
  +---------------------+-----------------+---------------+----------+
  | CONSTRAINT_NAME     | CONSTRAINT_TYPE | TABLE_NAME    | STATUS   |
  +---------------------+-----------------+---------------+----------+
  | FK_MMS_GROUPUSER_02 | R               | MMS_GROUPUSER | DISABLED |
  +---------------------+-----------------+---------------+----------+
  1 row in set
  
  obclient> ALTER TABLE MMS_GROUPUSER ENABLE CONSTRAINT FK_MMS_GROUPUSER_02;
  Query OK, 0 rows affected
  
  obclient> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS FROM user_constraints WHERE CONSTRAINT_NAME LIKE 'FK_MMS_GROUPUSE%';
  +---------------------+-----------------+---------------+---------+
  | CONSTRAINT_NAME     | CONSTRAINT_TYPE | TABLE_NAME    | STATUS  |
  +---------------------+-----------------+---------------+---------+
  | FK_MMS_GROUPUSER_02 | R               | MMS_GROUPUSER | ENABLED |
  +---------------------+-----------------+---------------+---------+
  1 row in set
  

• Truncate the M202001 and M202002 partitions of the tbl6 partitioned table.

  obclient> CREATE TABLE tbl6 (log_id number NOT NULL,log_value varchar2(50),log_date date NOT NULL DEFAULT sysdate)
  PARTITION BY RANGE(log_date)
  (
      PARTITION M202001 VALUES LESS THAN(TO_DATE('2020/02/01','YYYY/MM/DD'))
      , PARTITION M202002 VALUES LESS THAN(TO_DATE('2020/03/01','YYYY/MM/DD'))
      , PARTITION M202003 VALUES LESS THAN(TO_DATE('2020/04/01','YYYY/MM/DD'))
      , PARTITION M202004 VALUES LESS THAN(TO_DATE('2020/05/01','YYYY/MM/DD'))
      , PARTITION M202005 VALUES LESS THAN(TO_DATE('2020/06/01','YYYY/MM/DD'))
      , PARTITION MMAX VALUES LESS THAN (MAXVALUE)
  );
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl6 TRUNCATE PARTITION M202001, M202002 UPDATE GLOBAL INDEXES;
  Query OK, 0 rows affected
  

• Drop the CHECK constraint named tbl7_equal_check1 on the tbl7 table.

  obclient> CREATE TABLE tbl7 (col1 INT, col2 INT, col3 INT,CONSTRAINT tbl7_equal_check1 CHECK(col2 = col3 * 2) ENABLE VALIDATE);
  Query OK, 0 rows affected
  
  obclient> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS FROM user_constraints WHERE TABLE_NAME LIKE 'TBL%';
  +-------------------+-----------------+------------+---------+
  | CONSTRAINT_NAME   | CONSTRAINT_TYPE | TABLE_NAME | STATUS  |
  +-------------------+-----------------+------------+---------+
  | TBL8_EQUAL_CHECK1 | C               | TBL8       | ENABLED |
  | TBL7_EQUAL_CHECK1 | C               | TBL7       | ENABLED |
  +-------------------+-----------------+------------+---------+
  2 rows in set
  
  obclient> ALTER TABLE tbl7 DROP CONSTRAINT tbl7_equal_check1;
  Query OK, 0 rows affected
  
  obclient> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS FROM user_constraints WHERE TABLE_NAME LIKE 'TBL%';
  +-------------------+-----------------+------------+---------+
  | CONSTRAINT_NAME   | CONSTRAINT_TYPE | TABLE_NAME | STATUS  |
  +-------------------+-----------------+------------+---------+
  | TBL8_EQUAL_CHECK1 | C               | TBL8       | ENABLED |
  +-------------------+-----------------+------------+---------+
  1 row in set
  

• Move the tbl8 table from the tblgroup1 table group to the tblgroup2 table group.

  obclient> SHOW TABLEGROUPS;
  +-----------------+------------+---------------+
  | TABLEGROUP_NAME | TABLE_NAME | DATABASE_NAME |
  +-----------------+------------+---------------+
  | TBLGROUP1             | TBL8         | SYS           |
  | TBLGROUP2             | NULL       | NULL          |
  | oceanbase       | NULL       | NULL          |
  +-----------------+------------+---------------+
  3 rows in set
  
  obclient> ALTER TABLE tbl8 SET TABLEGROUP tblgroup2;
  Query OK, 0 rows affected
  
  obclient> SHOW TABLEGROUPS;
  +-----------------+------------+---------------+
  | TABLEGROUP_NAME | TABLE_NAME | DATABASE_NAME |
  +-----------------+------------+---------------+
  | TBLGROUP1             | NULL       | NULL          |
  | TBLGROUP2             | TBL8         | SYS           |
  | oceanbase       | NULL       | NULL          |
  +-----------------+------------+---------------+
  3 rows in set
  

• Add a FOREIGN KEY constraint named cons_fk1 to the primary_table table.

  obclient> CREATE TABLE primary_table (id NUMBER PRIMARY KEY, names VARCHAR(100) NOT NULL, foreign_col NUMBER);
  Query OK, 0 rows affected
  obclient> CREATE TABLE reference_table (id NUMBER PRIMARY key, comments VARCHAR2(100) NOT NULL);
  Query OK, 0 rows affected
  obclient> ALTER TABLE primary_table ADD CONSTRAINT cons_fk1 FOREIGN KEY(foreign_col) REFERENCES reference_table(id);
  Query OK, 0 rows affected
  

• Add a PRIMARY KEY constraint named tbl1_pk to the tbl9 table.

  obclient> CREATE TABLE tbl9 (col1 NUMBER, col2 INT,col3 VARCHAR2(100));
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl9 ADD CONSTRAINT tbl1_pk PRIMARY KEY (col1);
  Query OK, 0 rows affected
  

• Change the primary key of the tbl9 table to the col2 column.

  obclient> ALTER TABLE tbl9 MODIFY PRIMARY KEY(col2);
  Query OK, 0 rows affected
  

• Drop the primary key of the tbl9 table.

  obclient> ALTER TABLE tbl9 DROP PRIMARY KEY;
  Query OK, 0 rows affected