ALTER TABLE

Purpose

This statement is used to modify the structure of an existing table, including modifying the table and its attributes, adding columns, modifying columns and their attributes, and dropping columns.

Syntax

ALTER TABLE table_name alter_table_actions;
| ALTER TABLE EXTERNAL table_name alter_table_actions;
| ALTER TABLE table_name alter_column_group_option;
| ALTER TABLE EXTERNAL table_name ADD PARTITION '(' add_external_table_partition_actions ')' LOCATION STRING_VALUE;
| ALTER TABLE EXTERNAL table_name DROP PARTITION LOCATION STRING_VALUE;

alter_table_actions:
    alter_table_action
    | alter_table_actions ',' alter_table_action
    | exclude_alter_table_action

exclude_alter_table_action:
    alter_partition_option
    | modify_partition_info
    | auto_split_range_partition_option

alter_table_action:
    table_option_list_space_seperated
    | SET table_option_list_space_seperated
    | opt_alter_compress_option
    | alter_column_option
    | alter_tablegroup_option
    | RENAME relation_factor
    | RENAME TO relation_factor
    | alter_index_option
    | DROP CONSTRAINT constraint_name
    | enable_option ALL TRIGGERS
    | REFRESH
    | enable_macro_block_bloom_filter
    | DYNAMIC_PARTITION_POLICY [=] (dynamic_partition_policy_list)
    | SET INTERVAL(expr)
    | COLUMN_NAME_CASE_SENSITIVE [=] {True | False}
    | DELTA_FORMAT [=] 'flat | encoding'
    | [SET] TTL [=] col_name + INTERVAL interval_num ttl_unit BY COMPACTION
    | REMOVE TTL

dynamic_partition_policy_list:
    dynamic_partition_policy_option [, dynamic_partition_policy_option ...]

dynamic_partition_policy_option:
    ENABLE = {true | false}
    | TIME_UNIT = {'hour' | 'day' | 'week' | 'month' | 'year'}
    | PRECREATE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}
    | EXPIRE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}

alter_partition_option:
    DROP PARTITION drop_partition_name_list
    | DROP PARTITION drop_partition_name_list UPDATE GLOBAL INDEXES
    | RENAME PARTITION relation_name TO relation_name
    | add_range_or_list_partition
    | SPLIT PARTITION relation_factor split_actions
    | TRUNCATE PARTITION name_list
    | TRUNCATE PARTITION name_list UPDATE GLOBAL INDEXES
    | MODIFY PARTITION relation_factor add_range_or_list_subpartition
    | RENAME SUBPARTITION relation_name TO relation_name
    | DROP SUBPARTITION drop_partition_name_list
    | DROP SUBPARTITION drop_partition_name_list UPDATE GLOBAL INDEXES
    | TRUNCATE SUBPARTITION name_list
    | TRUNCATE SUBPARTITION name_list UPDATE GLOBAL INDEXES
    | EXCHANGE {PARTITION partition_name
               | SUBPARTITION subpartition_name} WITH TABLE origin_table_name INCLUDING INDEXES WITHOUT VALIDATION

alter_column_group_option:
    ADD COLUMN GROUP '(' column_group_list ')' alter_column_group_delayed_desc
    | DROP COLUMN GROUP '(' column_group_list ')' alter_column_group_delayed_desc

modify_partition_info:
    MODIFY hash_partition_option
    | MODIFY list_partition_option
    | MODIFY range_partition_option

auto_split_range_partition_option:
    PARTITION BY RANGE '(' ')'  opt_auto_split_tablet_size_option
    | PARTITION BY RANGE '(' column_name_list ')' subpartition_option opt_auto_split_range_partition_info
    | MODIFY PARTITION BY RANGE '(' ')'  opt_auto_split_tablet_size_option
    | MODIFY PARTITION BY RANGE '(' column_name_list ')' subpartition_option auto_split_tablet_size_option
    | MODIFY PARTITION BY RANGE '(' column_name_list ')' subpartition_option auto_split_tablet_size_option opt_range_partition_list
    | PARTITION BY RANDOM SIZE('size_value')

add_external_table_partition_actions:
    add_external_table_partition_action
    | add_external_table_partition_actions ',' add_external_table_partition_action
    | /* empty */

opt_auto_split_tablet_size_option:
    | AUTO_SPLIT_TABLET_SIZE '(' integer_value ')'
    | /* empty */

Syntax description

split_partition_option

• SPLIT PARTITION partition_name AT (value) [INTO (PARTITION split_partition_name1, PARTITION [split_partition_name2])]: When using this syntax for partition splitting, the source partition is split into two partitions at the given value. You can also use the INTO clause to name the split partitions.

• SPLIT PARTITION partition_name INTO (PARTITION split_partition_name VALUES LESS THAN (value) [, PARTITION split_partition_name VALUES LESS THAN (value) ...], PARTITION split_partition_name): When using this syntax for partition splitting, a single partition can be split into multiple partitions. The value ranges defined for the split must be the same as those of the source partition, and they must be defined in ascending order. (The definition for the last split partition is not required; its value is equivalent to that of the source partition.)

For more information about manual partition splitting, see Manual partition splitting.

dynamic_partition_policy_option

• ENABLE = {true | false}: Indicates whether to enable dynamic partition management. Valid values:

  • true: The default value, indicating to enable dynamic partition management.
  • false: Indicates to disable dynamic partition management.

• PRECREATE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}: Indicates the precreation time. When scheduling dynamic partition management, it will pre-create partitions so that max_partition_upper_bound > now() + precreate_time. Valid values:

  • -1: The default value, indicating not to pre-create partitions.
  • 0: Indicates to pre-create only the current partition.
  • n {hour | day | week | month | year}: Indicates to pre-create partitions for the corresponding time span. For example, 3 hour indicates to pre-create partitions within the next 3 hours.

  Note

  • When multiple partitions need to be pre-created, the interval between partition boundaries is TIME_UNIT.
  • The boundary of the first pre-created partition is the existing maximum partition boundary rounded up by TIME_UNIT.

• EXPIRE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}: Optional. Indicates the partition expiration time. When scheduling dynamic partition management, it will delete all expired partitions where **partition_upper_bound < now() - expire_time`. Modifiable. Valid values:

  • -1: The default value, indicating partitions never expire.
  • 0: Indicates all partitions except the current one expire.
  • n {hour | day | week | month | year}: Indicates the partition expiration time. For example, 1 day indicates the partition expires in 1 day.

For more information about modifying a dynamic partitioned table, see Modify a dynamic partitioned table.

Example:

ALTER TABLE tbl2 SET DYNAMIC_PARTITION_POLICY(
    ENABLE = true,
    PRECREATE_TIME = '1 day',
    EXPIRE_TIME = '-1'
);

Examples

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

  obclient> CREATE TABLE tbl1(col1 VARCHAR(3));
  
  obclient> ALTER TABLE tbl1 MODIFY col1 CHAR(10);
  
  obclient> DESCRIBE tbl1;
  

• Rename column col1 to col2 in table tbl1.

  obclient> ALTER TABLE tbl1 RENAME COLUMN col1 TO col2;
  
  obclient> DESCRIBE tbl1;
  

• Add and drop columns.

  • Create table tbl2.

    obclient> CREATE TABLE tbl2 (col1 NUMBER(30) PRIMARY KEY,col2 VARCHAR(50));
    

  • Add a col3 column to table tbl2.

    obclient> ALTER TABLE tbl2 ADD col3 NUMBER(30);
    
    obclient> DESCRIBE tbl2;
    

  • Drop the col3 column from table tbl2.

    obclient> ALTER TABLE tbl2 DROP COLUMN col3;
    
    obclient> DESCRIBE tbl2;
    

  • Create a unique index on table tbl2.

    obclient> CREATE TABLE tbl2 (col1 NUMBER(30) PRIMARY KEY,col2 VARCHAR(50), col3 INT);
    
    obclient> ALTER TABLE tbl2 ADD CONSTRAINT constraint_TBL2 UNIQUE (col2, col3);
    
    obclient [SYS]> DESC tbl2;
    
    obclient> INSERT INTO tbl2 VALUES('1','2','2');
    
    obclient> INSERT INTO tbl2 VALUES('2','2','2');
    
    obclient> INSERT INTO tbl2 VALUES('2','3','2');
    

• Add a foreign key for table ref_t2 and execute a SET NULL operation when a DELETE operation affects the key value in the parent table that matches a row in the subtable.

  obclient> CREATE TABLE ref_t1(c1 INT PRIMARY KEY,C2 INT);
  
  obclient> CREATE TABLE ref_t2(c1 INT PRIMARY KEY,C2 INT);
  
  obclient> ALTER TABLE ref_t2 ADD CONSTRAINT fk1 FOREIGN KEY (c2) REFERENCES ref_t1(c1) ON DELETE SET NULL;
  

• Add a subpartition p1_r4 to the partition p1 of the non-templated secondary partitioned table tbl3.

  obclient> ALTER TABLE tbl3 MODIFY PARTITION p1 ADD SUBPARTITION p1_r4 VALUES LESS THAN(2022);
  

• Drop the subpartition p3_r3 from the non-template-based secondary partitioned table tbl3.

  obclient> ALTER TABLE tbl3 DROP SUBPARTITION p2_r3;
  

• Add a partition p4 to the non-template-based secondary partitioned table tbl3. You must specify both the definition of the partition and the definition of the subpartition within it.

  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)
  );
  

• Add a partition p3 to the template-based secondary partitioned table tbl4. You only need to specify the definition of the partition; the definition of the secondary partitions will be automatically populated according to 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)
       );
  
  obclient> ALTER TABLE tbl4 ADD PARTITION p3 VALUES LESS THAN (400);
  

• Modify the parallelism of table tbl5 to 3.

  obclient> CREATE TABLE tbl5(col1 int primary key, col2 int) PARALLEL 5;
  
  obclient> ALTER TABLE tbl5 PARALLEL 3;
  

  or:

  obclient> CREATE TABLE tbl5(col1 int primary key, col2 int) PARALLEL 5;
  
  obclient> ALTER /*+ parallel(3) */ TABLE tbl5;
  

• 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
  );
  
  obclient> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS FROM user_constraints WHERE CONSTRAINT_NAME LIKE 'FK_MMS_GROUPUSE%';
  
  obclient> ALTER TABLE MMS_GROUPUSER ENABLE CONSTRAINT FK_MMS_GROUPUSER_02;
  
  obclient> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS FROM user_constraints WHERE CONSTRAINT_NAME LIKE 'FK_MMS_GROUPUSE%';
  

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

  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)
  );
  
  obclient> ALTER TABLE tbl6 TRUNCATE PARTITION M202001, M202002 UPDATE GLOBAL INDEXES;
  

• Drop the CHECK constraint tbl7_equal_check1 on table tbl7.

  obclient> CREATE TABLE tbl7 (col1 INT, col2 INT, col3 INT,CONSTRAINT tbl7_equal_check1 CHECK(col2 = col3 * 2) ENABLE VALIDATE);
  
  obclient> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS FROM user_constraints WHERE TABLE_NAME LIKE 'TBL%';
  
  obclient> ALTER TABLE tbl7 DROP CONSTRAINT tbl7_equal_check1;
  
  obclient> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS FROM user_constraints WHERE TABLE_NAME LIKE 'TBL%';
  

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

  obclient> SHOW TABLEGROUPS;
  
  obclient> ALTER TABLE tbl8 SET TABLEGROUP tblgroup2;
  
  obclient> SHOW TABLEGROUPS;
  

• Add a foreign key constraint cons_fk1 to the primary_table.

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

• Add a primary key constraint named tbl1_pk to the tbl9 table.

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

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

  obclient> ALTER TABLE tbl9 MODIFY PRIMARY KEY(col2);
  

• Drop the primary key of table tbl9.

  obclient> ALTER TABLE tbl9 DROP PRIMARY KEY;
  

• Rename a partition and subpartition.

  /* Create a RANGE-partitioned table named range_range_table and create a local index on column1 */
  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, but the modification does not affect the partition name in local indexes.*/
  

  obclient> SELECT partition_name FROM SYS.USER_TAB_PARTITIONS WHERE table_name = 'RANGE_RANGE_TABLE';
  
  obclient> ALTER TABLE range_range_table RENAME PARTITION p0 TO p10;
  
  obclient> SELECT partition_name FROM SYS.USER_TAB_PARTITIONS WHERE table_name = 'RANGE_RANGE_TABLE';
  
  obclient> SELECT partition_name FROM SYS.USER_IND_PARTITIONS WHERE index_name = 'LOCAL_IDX_FOR_RANGE_RANGE_TB';
  
  /*Rename a subpartition, but the modification does not affect the partition name of local indexes.*/
  obclient> SELECT partition_name, subpartition_name FROM SYS.USER_TAB_SUBPARTITIONS WHERE table_name = 'RANGE_RANGE_TABLE';
  
  obclient> ALTER TABLE range_range_table RENAME SUBPARTITION sp0 TO sp10;
  
  obclient> SELECT partition_name, subpartition_name FROM SYS.USER_TAB_SUBPARTITIONS WHERE table_name = 'RANGE_RANGE_TABLE';
  
  obclient> SELECT partition_name, subpartition_name FROM SYS.USER_IND_SUBPARTITIONS WHERE index_name = 'LOCAL_IDX_FOR_RANGE_RANGE_TB';
  

• Modify the columnar storage attributes of a table.

  1. Use the following SQL statement to create table tbl1.

     CREATE TABLE tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR(50));
     

  2. Change table tbl1 to a hybrid row-column storage redundant table, and then remove the hybrid row-column storage redundancy attribute.

     ALTER TABLE tbl1 ADD COLUMN GROUP(all columns, each column);
     

     ALTER TABLE tbl1 DROP COLUMN GROUP(all columns, each column);
     

  3. Change table tbl1 to a columnar storage table, and then drop the columnar storage attribute.

     ALTER TABLE tbl1 ADD COLUMN GROUP(each column);
     

     ALTER TABLE tbl1 DROP COLUMN GROUP(each column);
     

• Modify the skip index attribute of a column in a table.

  1. Execute the following SQL statement to create the test_skidx table.

     CREATE TABLE test_skidx(
         col1 NUMBER SKIP_INDEX(MIN_MAX, SUM),
         col2 FLOAT SKIP_INDEX(MIN_MAX),
         col3 VARCHAR2(1024) SKIP_INDEX(MIN_MAX),
         col4 CHAR(10)
       );
     

  2. Change the skip index property of column col2 in table test_skidx to SUM skip index type.

     ALTER TABLE test_skidx MODIFY col2 FLOAT SKIP_INDEX(SUM);
     

  3. The SKIP INDEX attribute for a new column after table creation. Added the MIN_MAX SKIP INDEX type for column col4 in table test_skidx.

     ALTER TABLE test_skidx MODIFY col4 CHAR(10) SKIP_INDEX(MIN_MAX);
     

  4. Drop a column with the SKIPINDEX attribute after creating the table. Drop the col1 column from the test_skidx table with the SKIPINDEX attribute.

     ALTER TABLE test_skidx MODIFY col1 NUMBER SKIP_INDEX();
     

• To modify table attributes, execute the following command to disable persistent macroblock-level Bloom filtering for table tb.

  ALTER TABLE tb SET enable_macro_block_bloom_filter = False;
  

• Enable or disable row movement for a partitioned table.

  -- Enable row movement to allow rows to cross partitions when an UPDATE operation changes the partitioning key.
  ALTER TABLE partitioned_tbl ENABLE ROW MOVEMENT;
  
  -- Disable Row Movement
  ALTER TABLE partitioned_tbl DISABLE ROW MOVEMENT;
  

• Modify the TTL attribute of a table, including adding a TTL attribute, changing a TTL column, modifying a TTL strategy, and deleting a TTL attribute.

  1. Create a non-TTL table.

     obclient(SYS@oracle001)[SYS]> CREATE TABLE TBL3(
        ORDER_ID INT,
        ORDER_TIME DATE NOT NULL,
        PAYMENT_TIME DATE NOT NULL DEFAULT sysdate,
        PRIMARY KEY(ORDER_ID, ORDER_TIME, PAYMENT_TIME));
     

  2. Added a TTL attribute for non-TTL tables.

     obclient(SYS@oracle001)[SYS]> ALTER TABLE TBL3 TTL ORDER_TIME + INTERVAL 7 DAY BY COMPACTION;
     

  3. Update the TTL column for the table. Change the table's TTL column from ORDER_TIME to PAYMENT_TIME.

     obclient(SYS@oracle001)[SYS]> ALTER TABLE TBL3 TTL PAYMENT_TIME + INTERVAL 7 DAY BY COMPACTION;
     

  4. Modify the table's TTL policy. For example, change the data expiration time to 1 hour.

     obclient(SYS@oracle001)[SYS]> ALTER TABLE TBL3 SET TTL PAYMENT_TIME + INTERVAL 1 HOUR BY COMPACTION;
     

  5. Delete the table's TTL attribute.

  obclient(SYS@oracle001)[SYS]> ALTER TABLE TBL3 REMOVE TTL;
  

Read-only and read/write tables in Oracle tenants

In an Oracle-compatible tenant, you can use the CREATE TABLE statement to create tables that are read-only or read/write. You can also use the ALTER TABLE statement to change the read/write attribute of a table.

Procedure:

1. Create a regular user:

   CREATE USER test1 IDENTIFIED BY "12345";
   

2. Grant the user connection and table creation privileges:

   GRANT CREATE SESSION TO test1;
   GRANT CREATE TABLE TO test1;
   

3. Connect to OceanBase Database as the regular user:

   obclient -hxxx.xx.xxx.xxx -P2881 -utest1@oracle001  -A
   

4. Create a read-only table:

   CREATE TABLE tb_readonly1(id INT) READ ONLY;
   

5. Attempt to insert data into the read-only table (expected to fail):

   INSERT INTO tb_readonly1 VALUES (1);
   -- Expected error: ORA-00600: internal error code, arguments: -5235, The table 'TEST1.TB_READONLY1' is read only so it cannot execute this statement
   

6. Create a read/write table:

   CREATE TABLE tb_readwrite1(id INT) READ WRITE;
   

7. Insert data into the read/write table (expected to succeed):

   INSERT INTO tb_readwrite1 VALUES (99),(98);
   -- Expected result: Query OK, 2 rows affected (0.002 sec)
   --             Records: 2  Duplicates: 0  Warnings: 0
   

8. Convert the read/write table to a read-only table:

   ALTER TABLE tb_readwrite1 READ ONLY;
   

9. Attempt to insert data into the converted read-only table (expected to fail):

   INSERT INTO tb_readwrite1 VALUES (96),(97);
   -- Expected error: ORA-00600: internal error code, arguments: -5235, The table 'TEST1.TB_READWRITE1' is read only so it cannot execute this statement
   

References

• Modify a table

• Create a table

• Truncate a table

• Delete a table

• Use the TTL (Time To Live) feature