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
    | RENAME { PARTITION | SUBPARITION } partition_name TO new_name
    | 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 {SET NULL | 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
    

• Specify a foreign key for the ref_t2 table. Specify to execute the SET NULL action when a DELETE operation affects a key value in the parent table that has matching rows in the child table.

  obclient> CREATE TABLE ref_t1(c1 INT PRIMARY KEY,C2 INT);
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE ref_t2(c1 INT PRIMARY KEY,C2 INT);
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE ref_t2 ADD CONSTRAINT fk1 FOREIGN KEY (c2) REFERENCES ref_t1(c1) ON DELETE SET NULL;
  Query OK, 0 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
  

• Rename a partition and a subpartition.

  /* Create a subpartitioned table named range_range_table and create a local index based on col1. */
  CREATE TABLE range_range_table(col1 INT, col2 INT, col3 INT)
      PARTITION BY RANGE(col1)
      SUBPARTITION BY RANGE(col2)
         (PARTITION p0 VALUES LESS THAN(100)
             (SUBPARTITION sp0 VALUES LESS THAN(100),
              SUBPARTITION sp1 VALUES LESS THAN(200)
             ),
          PARTITION p1 VALUES LESS THAN(200)
             (SUBPARTITION sp2 VALUES LESS THAN(100),
              SUBPARTITION sp3 VALUES LESS THAN(200),
              SUBPARTITION sp4 VALUES LESS THAN(300)
             )
          );
  
  CREATE INDEX local_idx_for_range_range_tb ON range_range_table (col1) LOCAL;
  
  /* Rename a partition. The partition name in the local index remains unchanged. */
  
  obclient> SELECT partition_name FROM sys.user_tab_partitions WHERE table_name = 'RANGE_RANGE_TABLE';
  +----------------+
  | PARTITION_NAME |
  +----------------+
  | P0             |
  | P1             |
  +----------------+
  2 rows in set
  
  obclient> ALTER TABLE range_range_table RENAME PARTITION p0 TO p10;
  Query OK, 0 rows affected
  
  obclient> SELECT partition_name FROM sys.user_tab_partitions WHERE table_name = 'RANGE_RANGE_TABLE';
  +----------------+
  | PARTITION_NAME |
  +----------------+
  | P10            |
  | P1             |
  +----------------+
  2 rows in set
  
  obclient> SELECT partition_name FROM sys.user_ind_partitions WHERE index_name = 'LOCAL_IDX_FOR_RANGE_RANGE_TB';
  +----------------+
  | PARTITION_NAME |
  +----------------+
  | P0             |
  | P1             |
  +----------------+
  2 rows in set
  
  /* Rename a subpartition. The subpartition name in the local index remains unchanged. */
  obclient> SELECT partition_name, subpartition_name FROM sys.user_tab_subpartitions WHERE table_name = 'RANGE_RANGE_TABLE';
  +----------------+-------------------+
  | PARTITION_NAME | SUBPARTITION_NAME |
  +----------------+-------------------+
  | P10            | SP0               |
  | P10            | SP1               |
  | P1             | SP2               |
  | P1             | SP3               |
  | P1             | SP4               |
  +----------------+-------------------+
  5 rows in set
  
  obclient> ALTER TABLE range_range_table RENAME SUBPARTITION sp0 TO sp10;
  Query OK, 0 rows affected
  
  obclient> SELECT partition_name, subpartition_name FROM sys.user_tab_subpartitions WHERE table_name = 'RANGE_RANGE_TABLE';
  +----------------+-------------------+
  | PARTITION_NAME | SUBPARTITION_NAME |
  +----------------+-------------------+
  | P10            | SP10              |
  | P10            | SP1               |
  | P1             | SP2               |
  | P1             | SP3               |
  | P1             | SP4               |
  +----------------+-------------------+
  5 rows in set
  
  obclient> SELECT partition_name, subpartition_name FROM sys.user_ind_subpartitions WHERE index_name = 'LOCAL_IDX_FOR_RANGE_RANGE_TB';
  +----------------+-------------------+
  | PARTITION_NAME | SUBPARTITION_NAME |
  +----------------+-------------------+
  | P0             | SP0               |
  | P0             | SP1               |
  | P1             | SP2               |
  | P1             | SP3               |
  | P1             | SP4               |
  +----------------+-------------------+
  5 rows in set