ALTER TABLE

Purpose

This statement is used to modify the structure of an existing table, including modifying the table and its properties, adding columns, modifying columns and their properties, and deleting 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])]: Use this syntax to split a partition into two partitions based on the specified 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): Use this syntax to split a partition into multiple partitions. The value ranges defined for the split partitions must be the same as those of the source partition and must be in ascending order. You cannot specify the last split partition, which has the same value as the source partition.

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

dynamic_partition_policy_option

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

  • true: the default value. Dynamic partition management is enabled.
  • false: dynamic partition management is disabled.

• PRECREATE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}: specifies the time for precreating partitions. When you schedule dynamic partition management, partitions are precreated so that the upper bound of the maximum partition is greater than the current time plus the precreate time. Valid values:

  • -1: the default value. No partitions are precreated.
  • 0: only the current partition is precreated.
  • n {hour | day | week | month | year}: partitions are precreated for the specified time span. For example, 3 hour indicates that partitions are precreated for 3 hours.

  Note

  • When multiple partitions are precreated, the interval between the boundaries of adjacent partitions is TIME_UNIT.
  • The boundary of the first precreated partition is the smallest multiple of TIME_UNIT that is greater than or equal to the upper bound of the existing maximum partition.

• EXPIRE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}: specifies the expiration time of a partition. When you schedule dynamic partition management, all partitions whose upper bounds are earlier than the current time minus the expiration time are deleted. Valid values:

  • -1: the default value. Partitions do not expire.
  • 0: only the current partition is retained. All previous partitions are expired.
  • n {hour | day | week | month | year}: the expiration time of a partition. For example, 1 day indicates that a partition expires after 1 day.

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

Here is an example:

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

Examples

• Change the data type of the col1 column 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 the tbl2 table.

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

  • Add the col3 column 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 the key values in the parent table that match the rows in the child table, execute the 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 the p1_r4 subpartition under the p1 partition of the non-templated subpartitioned table tbl3.

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

• Drop the p3_r3 subpartition from the non-templated subpartitioned table tbl3.

  obclient> ALTER TABLE tbl3 DROP SUBPARTITION p2_r3;
  

• Add the p4 partition to the non-templated subpartitioned table tbl3, and specify the partition definition and the subpartition definitions under the partition.

  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 the p3 partition to the templated subpartitioned table tbl4. The subpartition definitions are automatically filled in 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)
       );
  
  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 tbl7_equal_check1 CHECK constraint 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 the cons_fk1 foreign key constraint 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 the tbl1_pk primary key constraint 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 the tbl9 table to the col2 column.

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

• Drop the primary key from the tbl9 table.

  obclient> ALTER TABLE tbl9 DROP PRIMARY KEY;
  

• Rename the partitions.

  /* Create the range-range partitioned table 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 the partition, but the modification does not affect the partition name of the local index. */
  

  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 the subpartition, but the modification does not affect the subpartition name of the local index. */
  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';
  

• Change the columnar attribute of the table.

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

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

  2. Change the storage type of the tbl1 table to a row-column redundant table and then delete the row-column redundant attributes.

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

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

  3. Change the storage type of the tbl1 table to a columnar table and then delete the columnar attributes.

     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 the table.

  1. Run the following SQL 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. Run the following statement to change the Skip Index attribute of the col2 column in the test_skidx table to the SUM Skip Index type.

     ALTER TABLE test_skidx MODIFY col2 FLOAT SKIP_INDEX(SUM);
     

  3. Add a 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. Delete the Skip Index attribute of the col1 column in the test_skidx table.

     ALTER TABLE test_skidx MODIFY col1 NUMBER SKIP_INDEX();
     

• Modify the table attributes and run the following command to disable the Bloom filter at the macroblock level for the tb table.

  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 use the CREATE TABLE statement to create READ ONLY and READ WRITE tables. You can also use 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 user the privileges to connect to and create tables in OceanBase Database:

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

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