ALTER TABLE

Purpose

This statement is used to modify the structure of an existing table, including modifying the table name and table attributes, adding columns, modifying the column name and 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)

dynamic_partition_policy_list:
    dynamic_partition_policy_option [, dynamic_partition_policy_option ...]

dynamic_partition_policy_option:
    ENABLE = {true | false}
    | 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

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

split_partition_option

• SPLIT PARTITION partition_name AT (value) [INTO (PARTITION split_partition_name1, PARTITION [split_partition_name2])]: When you split a partition by using this syntax, the system splits the source partition into two partitions based on the given value. You can also use the INTO clause to specify the names of 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 you split a partition by using this syntax, you can split a partition into multiple partitions. The value range specified for partition splitting must be the same as that of the source partition and the value must be defined in ascending order (the definition of the last partition cannot be left empty, and its value is the same as that of the source partition).

For more information about manual partition splitting, see Manually split a partition.

dynamic_partition_policy_option

• ENABLE = {true | false}: specifies whether to enable dynamic partition management. The values are as follows:

  • true: default value, enables dynamic partition management.
  • false: disables dynamic partition management.

• PRECREATE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}: the pre-creation time. Each time dynamic partition management is scheduled, partitions will be pre-created so that the upper bound of the largest partition > now() + precreate_time. The values are as follows:

  • -1: default value, no partitions will be pre-created.
  • 0: only the current partition will be pre-created.
  • n {hour | day | week | month | year}: partitions will be pre-created for the specified time span. For example, 3 hour means pre-create partitions for the next 3 hours.

  Note

  • When multiple partitions need to be pre-created, the interval between partition boundaries is determined by TIME_UNIT.
  • The boundary of the first pre-created partition is the smallest multiple of TIME_UNIT that is greater than or equal to the current maximum partition boundary.

• EXPIRE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}: optional. Specifies the partition expiration time. Each time dynamic partition management is scheduled, all partitions with upper bound < now() - expire_time will be deleted as expired partitions. This value can be modified. The values are as follows:

  • -1: default value, partitions never expire.
  • 0: all partitions prior to the current partition are considered expired.
  • n {hour | day | week | month | year}: specifies the partition expiration time. For example, 1 day means the partition expires after 1 day.

For more information about modifying dynamic partitioned tables, see Modify dynamic partitioned tables.

Here is an example:

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

Examples

• Change the type of the col1 field in the tbl1 table.

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

• Rename the col1 column in the tbl1 table to col2.

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

• Add and drop columns.

  • Create a tbl2 table.

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

  • Add a column named col3 to the tbl2 table.

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

  • Drop the col3 column from the tbl2 table.

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

  • Create a unique index on the tbl2 table.

    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 to the ref_t2 table. When a DELETE operation affects a row in the parent table that matches a row in the child table, the foreign key constraint performs a SET NULL operation.

  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 non-template subpartition to the tbl3 table and specify the partitioning key and subpartitioning key of the partition.

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

• Drop a non-template subpartition from the tbl3 table.

  obclient> ALTER TABLE tbl3 DROP SUBPARTITION p2_r3;
  

• Add a partition to the tbl3 table and specify the partitioning key and subpartitioning key of the partition and subpartition.

  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 to the tbl4 table and specify the partitioning key and subpartitioning key of the partition and subpartition.

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

• Change the parallelism of the tbl5 table 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;
  

• Change the status of the 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%';
  

• Clear all data in the M202001 and M202002 partitions 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 named tbl7_equal_check1 from the tbl7 table.

  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 the tbl8 table from the tblgroup1 table group to the tblgroup2 table group.

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

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

• Modify the primary key of the tbl9 table to the col2 field.

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

• Drop the primary key constraint from the tbl9 table.

  obclient> ALTER TABLE tbl9 DROP PRIMARY KEY;
  

• Rename partitions and subpartitions.

  /* Create a subpartitioned table named range_range_table and create a local index on this table based on the col1 column. */
  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 without changing the name of the local index partition. */
  

  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 without changing the name of the local index partition. */
  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 attribute of a table.

  1. Execute the following statement to create a tbl1 table.

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

  2. Change the tbl1 table to a rowstore redundant table and then drop the rowstore redundant attribute.

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

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

  3. Change the tbl1 table to a columnstore table and then drop the columnstore 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 statement to create a 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 attribute of the col2 column in the test_skidx table to SUM.

     ALTER TABLE test_skidx MODIFY col2 FLOAT SKIP_INDEX(SUM);
     

  3. Add a Skip Index attribute to a new column when creating a table. Add the MIN_MAX Skip Index attribute to the col4 column in the test_skidx table.

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

  4. Drop the Skip Index attribute from a column when creating a table. Drop the Skip Index attribute from the col1 column in the test_skidx table.

     ALTER TABLE test_skidx MODIFY col1 NUMBER SKIP_INDEX();
     

• To modify the table property and disable the persistent macro block-level bloom filter for table tb, execute the following command:

  ALTER TABLE tb SET enable_macro_block_bloom_filter = False;
  

Read-only and read/write tables in an Oracle tenant

In an Oracle tenant, you can execute the CREATE TABLE statement to create tables with the READ ONLY or READ WRITE attribute. You can also execute the ALTER TABLE statement to change the read/write attribute of a table.

Procedure:

1. Create a normal user:

   CREATE USER test1 IDENTIFIED BY "12345";
   

2. Grant the connection and table creation privileges to the user:

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

3. Connect to OceanBase Database as the 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. 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. 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

• Change a table
• Create a table
• Truncate a table
• Drop a table