ALTER TABLE

描述

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

语法

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 | DELETING }
    | REMOVE TTL

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 */

语法说明

split_partition_option

• SPLIT PARTITION partition_name AT (value) [INTO (PARTITION split_partition_name1, PARTITION [split_partition_name2])]：使用此语法分区分裂时，将会以给定的 value 为界限，将源分区分裂为两个分区。也可以使用 INTO 子句给分裂出的分区设置名称。

• 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)：使用此语法分区分裂时，可以将一个分区分裂为多个分区，分区分裂所定义的 value 范围需要保证与源分区的 value 范围相同，且需要保证按升序定义 value（不允许填写最后一个分区分裂的定义，其 value 等同于源分区）。

更多有关手动分区分裂的信息，参见 手动分区分裂。

dynamic_partition_policy_option

• ENABLE = {true | false}：表示是否启用动态分区管理。取值如下：

  • true：默认值，表示启用动态分区管理。
  • false：表示禁用动态分区管理。

• PRECREATE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}：表示预创建时间。调度一次动态分区管理，会预创建分区使得 最大分区上界 > now() + precreate_time。取值如下：

  • -1：默认值，表示不预创建分区。
  • 0：表示仅预创建当前分区。
  • n {hour | day | week | month | year}：表示预创建对应时间跨度的分区。例如，3 hour 表示预创建 3 小时内的分区。

  说明

  • 需要预创建多个分区时，分区边界的间隔为 TIME_UNIT。
  • 首个预创建的分区边界为现存最大分区边界按 TIME_UNIT 向上取整。

• EXPIRE_TIME = {'-1' | '0' | 'n {hour | day | week | month | year}'}：可选项，表示分区过期时间。调度一次动态分区管理，会删除所有 分区上界 < now() - expire_time 的过期分区。可修改，取值如下：

  • -1：默认值，表示分区永不过期。
  • 0：表示除当前分区外，之前的所有分区均过期。
  • n {hour | day | week | month | year}：表示分区过期时间。例如，1 day 表示分区过期时间为 1 天。

更多有关修改动态分区表的信息，参见 修改动态分区表。

示例如下：

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

示例

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

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

• 修改表 tbl1 中列 col1 的名称为 col2。

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

• 增加和删除列。

  • 创建表 tbl2。

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

  • 对表 tbl2 增加 col3 列。

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

  • 删除表 tbl2 的 col3 列。

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

  • 为表 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');
    

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

  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;
  

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

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

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

  obclient> ALTER TABLE tbl3 DROP SUBPARTITION p2_r3;
  

• 为非模板化二级分区表 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)
  );
  

• 为模板化二级分区表 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)
       );
  
  obclient> ALTER TABLE tbl4 ADD PARTITION p3 VALUES LESS THAN (400);
  

• 修改表 tbl5 的并行度为 3。

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

  或者：

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

• 修改外键约束的状态。

  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%';
  

• 清空分区表 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)
  );
  
  obclient> ALTER TABLE tbl6 TRUNCATE PARTITION M202001, M202002 UPDATE GLOBAL INDEXES;
  

• 删除表 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);
  
  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%';
  

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

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

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

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

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

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

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

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

• 删除表 tbl9 的主键。

  obclient> ALTER TABLE tbl9 DROP PRIMARY KEY;
  

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

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

• 修改表的列存属性。

  1. 使用下面 SQL 语句创建表 tbl1。

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

  2. 将表 tbl1 的变更为行存列存冗余表，然后再删除行存列存冗余属性。

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

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

  3. 将表 tbl1 的变更为列存表，然后再删除列存属性。

     ALTER TABLE tbl1 ADD COLUMN GROUP(each column);
     

     ALTER TABLE tbl1 DROP COLUMN GROUP(each column);
     

• 修改表中列的 Skip Index 属性。

  1. 使用下面 SQL 语句创建表 test_skidx。

     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. 修改表 test_skidx 中列 col2 的 Skip Index 属性为 SUM Skip Index 类型。

     ALTER TABLE test_skidx MODIFY col2 FLOAT SKIP_INDEX(SUM);
     

  3. 建表后新增列的 Skip Index 属性。为表 test_skidx 中列 col4 增加的 MIN_MAX Skip Index 类型。

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

  4. 建表后删除列的 Skip Index 属性。删除表 test_skidx 中列 col1 的 Skip Index 属性。

     ALTER TABLE test_skidx MODIFY col1 NUMBER SKIP_INDEX();
     

• 修改表属性，执行如下命令关闭持久化宏块级别 bloom filter 的表 tb。

  ALTER TABLE tb SET enable_macro_block_bloom_filter = False;
  

• 为分区表启用或禁用行移动。

  -- 启用行移动，允许 UPDATE 导致的分区键变更时行跨分区移动
  ALTER TABLE partitioned_tbl ENABLE ROW MOVEMENT;
  
  -- 禁用行移动
  ALTER TABLE partitioned_tbl DISABLE ROW MOVEMENT;
  

• 修改表的 TTL 策略。例如：将数据过期时间修改为 1 小时。

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

• 删除表的 TTL 策略。

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

Oracle 租户下的只读和读写表

在 Oracle 租户下，您可以使用 CREATE TABLE 创建 READ ONLY 和 READ WRITE 类型的表。您也可以使用 ALTER TABLE 语句更改表的读写属性。

操作步骤：

1. 创建普通用户：

   CREATE USER test1 IDENTIFIED BY "12345";
   

2. 授予用户连接和建表权限：

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

3. 使用普通用户连接 OceanBase 数据库：

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

4. 创建只读表：

   CREATE TABLE tb_readonly1(id INT) READ ONLY;
   

5. 尝试向只读表插入数据（预期失败）：

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

6. 创建读写表：

   CREATE TABLE tb_readwrite1(id INT) READ WRITE;
   

7. 向读写表插入数据（预期成功）：

   INSERT INTO tb_readwrite1 VALUES (99),(98);
   -- 预期结果：Query OK, 2 rows affected (0.002 sec)
   --             Records: 2  Duplicates: 0  Warnings: 0
   

8. 将读写表转换为只读表：

   ALTER TABLE tb_readwrite1 READ ONLY;
   

9. 尝试向转换后的只读表插入数据（预期失败）：

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

相关文档

• 更改表
• 创建表
• 清空表
• 删除表