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 deleting columns.

Syntax

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 {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]
    | MODIFY PARTITION partition_name ADD add_subpartition_list
    | DUPLICATE_SCOPE= 'none | cluster'
    | 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
    [GENERATED BY DEFAULT AS IDENTITY | GENERATED ALWAYS AS IDENTITY]
    [DEFAULT const_value]
    [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
    | comment
    | parallel_clause
    | TABLE_MODE [=] 'table_mode_value'

parallel_clause:
    {NOPARALLEL | PARALLEL integer}

table_mode_value:
    NORMAL
    | QUEUING
    | MODERATE
    | SUPER
    | EXTREME

add_subpartition_list:
    {SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr, ...}
    | {SUBPARTITION subpartition_name VALUES list_partition_expr, ...}

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 for subpartitioning*/
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-templated subpartitioning*/
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 applies to non-templated partitions*/]

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 subpartitions can be specified.*/]
    [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/*Non-template subpartitions can be specified*/]
    [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

Syntax

table_mode_value

• NORMAL: The default value. This mode indicates normal. In this mode, the probability of triggering a compaction after a data dump is very low.
• QUEUING: In this mode, the probability of triggering a compaction after a data dump is low.
• MODERATE: This mode indicates moderate. In this mode, the probability of triggering a compaction after a data dump is moderate.
• SUPER: This mode indicates super. In this mode, the probability of triggering a compaction after a data dump is high.
• EXTREME: This mode indicates extreme. In this mode, the probability of triggering a compaction after a data dump is very high.

For more information about compaction, see Adaptive compaction.

Examples

Change the data type of a column

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

Rename a column

• Rename 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 columns

• Create the tbl2 table.

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

Add a column

• 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 a column

• Drop a single column

  • 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
    

• Drop multiple columns

  • Add the col3, col4, and col5 columns to the tbl2 table.

    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
    

  • Drop the col4 and col5 columns from the tbl2 table.

    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
    

  • Drop the col2 and col3 columns from the tbl2 table.

  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
  

• Drop obsolete columns

  • Drop the obsolete col2, col3, col4, and col5 columns from the tbl2 table.

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

Index operations

• Create a unique index on the tbl2 table.

  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
  

Foreign key operations

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

Partition operations

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

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

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

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

• Add the p4 partition to the non-templated partitioned table tbl3. You must specify the definition of the partition and the definitions of the subpartitions 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)
  );
  Query OK, 0 rows affected
  

• Add the p3 partition to the templated partitioned table tbl4. You need only specify the definition of the partition. The definitions of the subpartitions 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)
       );
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl4 ADD PARTITION p3 VALUES LESS THAN (400);
  Query OK, 0 rows affected
  

Modify the parallelism

• Change the parallelism 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
  

Operations on column types

• 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
  

• 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)
  );
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl6 TRUNCATE PARTITION M202001, M202002 UPDATE GLOBAL INDEXES;
  Query OK, 0 rows affected
  

• 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);
  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 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);
  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 the tbl1_pk primary key constraint 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 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, but the change does not affect the partition names of the local index. */
  
  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, but the change does not affect the partition names of the local index. */
  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