ALTER TABLE

描述

该语句用来修改已存在的表的结构，包括修改表及表属性、新增列、修改列及属性、删除列等。

语法

ALTER TABLE [EXTERNAL] TABLE table_name
    [alter_table_action [, alter_table_action] ...]
    [partition_options]

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 {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]
    | FORCE


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
    [VISIBLE | INVISIBLE] [GENERATED BY DEFAULT AS IDENTITY | GENERATED ALWAYS AS IDENTITY]
    {
    [DEFAULT expression]
    [NULL | NOT NULL] [[PRIMARY] KEY] [UNIQUE [KEY]] [COMMENT string]
    }


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
    | comment
    | parallel_clause
    | TABLE_MODE [=] 'table_mode_value'

parallel_clause:
    {NOPARALLEL | PARALLEL integer}

table_mode_value:
    NORMAL
    | QUEUING
    | MODERATE
    | SUPER
    | EXTREME

partition_options:
    partition_option [partition_option] ...

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)

/*模板化二级分区*/
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)

/*非模板化二级分区*/
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/*仅非模板化二级分区可定义*/]

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/*仅非模板化二级分区可定义*/]
    [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/*仅非模板化二级分区可定义*/]
    [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

语法说明

table_mode_value

• NORMAL：默认值，表示正常。在该模式下，数据转储后触发合并的概率极低。
• QUEUING：在该模式下，数据转储后触发合并的概率低。
• MODERATE：表示适度。在该模式下，数据转储后触发合并的概率为中等。
• SUPER：表示超级。在该模式下，数据转储后触发合并的概率高。
• EXTREME：表示极端。在该模式下，转储后触发合并的概率较高。

更多有关合并的信息，请参见 自适应合并。

示例

修改字段类型

• 修改表 tbl1 中字段 col1 的字段类型。

  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
  

重命名列操作

• 修改表 tbl1 中列 col1 的名称为 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
  

增加和删除列的操作

• 创建表 tbl2。

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

增加列操作

• 对表 tbl2 增加 col3 列。

  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
  

删除列操作

• 删除单列

  • 删除表 tbl2 的 col3 列。

    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
    

• 删除多列

  • 先对表 tbl2 增加 col3、col4、col5 列。

    obclient> ALTER TABLE tbl2 ADD col3 NUMBER(30);
    Query OK, 0 rows affected
    
    obclient> ALTER TABLE tbl2 ADD col4 NUMBER(30);
    Query OK, 0 rows affected
    
    obclient ALTER TABLE tbl2 ADD col5 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  |
    | COL4  | NUMBER(30)   | YES  | NULL | NULL    | NULL  |
    | COL5  | NUMBER(30)   | YES  | NULL | NULL    | NULL  |
    +-------+--------------+------+------+---------+-------+
    5 rows in set
    

  • 删除表 tbl2 的 col4、col5 列。

    obclient> ALTER TABLE tbl2 DROP (col4, col5);
    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
    

  • 删除表 tbl2 的 col2、col3 列。

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

• 清除废弃列

  • 删除表 tbl2 废弃的 col2、col3、col4、col5 列。

  obclient> ALTER TABLE tbl2 FORCE;
  Query OK, 0 rows affected
  

索引操作

• 为表 tbl2 创建唯一性索引。

  obclient> DROP TABLE tbl2;
  Query OK, 0 rows affected
  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> 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
  

外键操作

• 为表 ref_t2 添加外键，当 DELETE 操作影响与子表相匹配行的父表中键值时，执行 SET NULL 操作。

  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
  

分区操作

• 为非模板化二级分区表 tbl3 的一级分区 p1 下添加二级分区 p1_r4。

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

• 删除非模板化二级分区表 tbl3 的二级分区 p3_r3。

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

• 为非模板化二级分区表 tbl3 添加一级分区 p4，需要同时指定一级分区的定义和该分区下的二级分区定义。

  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
  

• 为模板化二级分区表 tbl4 添加一级分区 p3，只需要指定一级分区的定义，二级分区的定义会自动按照模板填充。

  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
  

修改并行度

• 修改表 tbl5 的并行度为 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
  

列类型相关操作

• 修改外键约束的状态。

  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
  

• 清空分区表 tbl6 的分区 M202001 和 M202002 中的全部数据。

  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
  

• 删除表 tbl7 的 CHECK 约束 tbl7_equal_check1。

  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
  

• 把表 tbl8 从表组 tblgroup1 中移到表组 tblgroup2 中。

  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
  

• 为表 primary_table 添加外键约束 cons_fk1。

  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
  

• 为表 tbl9 添加主键约束 tbl1_pk。

  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
  

• 修改表 tbl9 的主键为 col2 列。

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

• 删除表 tbl9 的主键。

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

• 重命名一级分区和二级分区。

  /* 创建二级分区表 range_range_table 并基于 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;
  
  /* 重命名一级分区，但修改不会影响到局部索引的分区名 */
  
  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
  
  /* 重命名二级分区，但修改不会影响到局部索引的分区名 */
  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