修改表

📄字数 9.1K

👁️阅读量加载中...

表创建完成后，用户可根据需求变化，对表定义进行相应修改。

警告

修改表是一种比较危险的做法，可能造成数据损坏，在某些情况下，修改表列还会触发自动重整表，对于含有大量数据的表，重整表操作会耗费大量时间，严重阻塞业务正常运行，请务必谨慎操作！有关重整表更多信息请参阅重整表。

在执行修改表操作时，还有以下事项需要注意：

注意

修改表操作会对表对象加排他锁，此时需保证无其他用户操作该对象
对表添加唯一值索引后，会自动在表上创建对应的唯一值约束，此时若需删除该索引，只能通过删除对应约束的方式进行删除，无法直接删除该唯一值索引
对于分区表，删除与截断分区会导致全局索引失效，需重建索引，因此在分区表上不建议使用全局索引，而应尽量使用局部索引，从而避免分区操作带来的重建索引开销；若删除或截断分区时需重建索引，可指定REBUILD GLOBAL INDEX参数
非空表不允许进行非兼容类型间的数据类型转换；定长字符数据类型CHAR进行精度扩展将自动触发重整表，建议创建表或更改列数据类型时，使用变长字符数据类型VARCHAR，可以避免此情况。
调整表约束时，CASCADE关键字用于对依赖项执行迭代调整，可能导致依赖对象被禁用或删除，所以对存在依赖项的表对象调整约束时，需确认各依赖项，确保操作影响范围符合预期，操作完成后应检查依赖项状态。

一、主要语法结构

1.1 语法格式

1.2 参数说明

table_name：待修改表名称
add_columns：为表添加新列，请参阅添加列
alter_columns：修改表现有列，请参阅修改列
drop_columns：删除表现有列，请参阅删除列
OWNER TO username：更改表的属主为 username 用户，此用户将拥有该表的所有权
TRUNCATE PARTITION ...：截断分区数据
- parti_name：操作分区名称
- opt_rebuild_idx：指定分区数据截断后，是否执行全局索引重建
  - 未指定：分区数据截断后，不重建全局索引
  - REBUILD GLOBAL INDEX：分区数据截断后，重建表上所有全局索引
REBUILD HEAP：重整表，请参阅重整表
REOPEN：重开表，请参阅重开表
( ENABLE | DISABLE ) CONSTRAINT ...：启用或禁用约束
- cons_name：操作约束名称
- keep_index_opt：指定启用或禁用约束后，如何操作约束对应的索引，仅支持主键约束与唯一值约束
  - 未指定：保留索引
  - KEEP INDEX：保留索引
  - DROP INDEX：删除索引
- alter_behavior：指定禁用约束的迭代性
  - 未指定：限制迭代，等同于 RESTRICT
  - CASCADE：级联迭代，等同于 RESTRICT CONSTRAINTS
  - RESTRICT：限制迭代，若存在依赖项则报告错误
  - CASCADE CONSTRAINTS：级联迭代，若存在依赖项，则禁用每一个依赖项
( ENABLE | DISABLE ) operation_commalist：启用或禁用表操作
- operation_commalist：对象操作权限列表，请参阅权限管理-对象级权限标志
  - SELECT：数据读取权限ACL_READ
  - INSERT：数据插入权限ACL_INSERT
  - UPDATE：数据更新权限ACL_UPDATE
  - DELETE：数据删除权限ACL_DELETE
  - EXECUTE：存储过程执行权限ACL_EXECUTE
  - REFERENCES：对象引用权限ACL_REF
  - ALTER：对象修改权限ACL_ALTER
  - DROP：对象删除权限ACL_DROP
  - INDEX：索引创建权限ACL_INDEX
  - TRIGGER：触发器创建权限ACL_TRIG
DROP CONSTRAINT ...：删除约束
- cons_name：待删除约束名称
- keep_drop_opt：指定删除约束后，如何操作约束对应的索引
  - 未指定：删除索引
  - KEEP INDEX：保留索引
  - DROP INDEX：删除索引
- alter_behavior：指定删除约束行为的迭代性
  - 未指定：限制迭代，等同于 RESTRICT
  - CASCADE：级联迭代，等同于 RESTRICT CONSTRAINTS
  - RESTRICT：限制迭代，若存在依赖项则报告错误
  - CASCADE CONSTRAINTS：级联迭代，若存在依赖项，则删除每一个依赖项
DROP PARTITION ...：删除分区
- parti_name：待删除分区名称
- opt_rebuild_idx：指定删除分区后，是否执行全局索引重建
  - 未指定：删除分区后，不重建全局索引
  - REBUILD GLOBAL INDEX：删除分区后，重建表上所有全局索引
ADD PARTITION parti_name VALUES ( parti_values )：添加列表分区
- parti_name：待添加分区名称
- parti_values：分区条件值列表，值个数必须与分区键个数相等
ADD PARTITION parti_name VALUES LESS THAN ( parti_values )：添加范围分区
- parti_name：待添加分区名称
- parti_values：分区条件值列表，值个数必须与分区键个数相等
SET ( ONLINE | OFFLINE )：设置表在线或离线
SET PARTITION parti_name ( ONLINE | OFFLINE )：设置 parti_name 分区在线或离线
SET CACHE ...：启用或禁用表数据缓存
- BY ( column_name ( , column_name )* )：启用表数据缓存，并设置缓存哈希键为 column_name 列名列表，使用,逗号分隔
- OFF：禁用表数据缓存
opt_wait：指定当所需资源锁被占用时，锁获取超时时间，请参阅超时限制

1.3 示例

示例1
为一级列表分区表增加一个新分区。

sql

-- 创建表
SQL> CREATE TABLE tab_add_parti(c1 DATETIME)
     PARTITION BY RANGE (c1)
     PARTITIONS (
         part1 VALUES LESS THAN ('2000-01-01 00:00:00'),
         part2 VALUES LESS THAN ('2010-01-01 00:00:00'),
         part3 VALUES LESS THAN ('2020-01-01 00:00:00')
     );

-- 添加分区
SQL> ALTER TABLE tab_add_parti ADD PARTITION part4 VALUES LESS THAN('2030-01-01 00:00:00');

-- 查询表结构
 SQL> SELECT ut.table_name,uc.col_name,uc.not_null,uc.def_val,uc.on_null,ut.parti_key
      FROM SYS_COLUMNS uc JOIN SYS_TABLES ut ON uc.table_id=ut.table_id
      WHERE ut.table_name='tab_add_parti';

+---------------+----------+----------+---------+---------+-----------+
|  TABLE_NAME   | COL_NAME | NOT_NULL | DEF_VAL | ON_NULL | PARTI_KEY |
+---------------+----------+----------+---------+---------+-----------+
| TAB_ADD_PARTI | C1       | F        | <NULL>  | 0       | "C1"      |
+---------------+----------+----------+---------+---------+-----------+

示例2
从一级列表分区表删除一个分区，并指定删除分区后重建全局索引。

sql

-- 创建表
SQL> CREATE TABLE tab_drop_parti(c1 DATETIME)
      PARTITION BY RANGE (c1)
      PARTITIONS (
          part1 VALUES LESS THAN ('2000-01-01 00:00:00'),
          part2 VALUES LESS THAN ('2010-01-01 00:00:00'),
          part3 VALUES LESS THAN ('2020-01-01 00:00:00')
      );

-- 删除分区并重建全局索引
SQL> ALTER TABLE tab_drop_parti DROP PARTITION part3 REBUILD GLOBAL INDEX;

示例3
在一张表上添加一个唯一值约束。

sql

-- 创建表
SQL> CREATE TABLE tab_add_cons(c1 INTEGER);

-- 添加约束
SQL> ALTER TABLE tab_add_cons ADD CONSTRAINT uk UNIQUE (c1);

示例4
从一张表上删除一个唯一值约束。

sql

-- 创建表以及约束
SQL> CREATE TABLE tab_drop_cons(c1 INTEGER, CONSTRAINT uk UNIQUE (c1));

-- 删除约束
SQL> ALTER TABLE tab_drop_cons DROP CONSTRAINT uk;

示例5
设置表禁用插入操作，即该表将拒绝插入数据。

sql

-- 创建表
SQL> CREATE TABLE tab_disable_insert(c1 INTEGER);

-- 禁用插入
SQL> ALTER TABLE tab_disable_insert DISABLE INSERT;

示例6
重命名一张表。

sql

-- 创建表
SQL> CREATE TABLE tab_rename(c1 INTEGER);

-- 重命名表
SQL> ALTER TABLE tab_rename RENAME TO tab_rename_new;

示例7
更改一张表的属主为访客用户。

sql

-- 创建表
SQL> CREATE TABLE tab_owner_to(c1 INTEGER);

-- 更改属主
SQL> ALTER TABLE tab_owner_to OWNER TO guest;

二、添加列

2.1 语法结构

(table_elements::=)

2.2 示例

示例1
为一张表增加一个新列，并修改一个现有列。

sql

-- 创建表
SQL> CREATE TABLE tab_add_column(c1 INTEGER,c2 VARCHAR);

-- 添加c1时间类型列，并设置c2列不为NULL
SQL> ALTER TABLE tab_add_column ADD COLUMN c3 DATETIME ALTER COLUMN c2 SET NOT NULL;

-- 查询表结构
SQL> SELECT ut.table_name,uc.col_name,uc.not_null,uc.def_val,uc.on_null,uc.type_name FROM SYS_COLUMNS uc JOIN SYS_TABLES ut ON uc.table_id=ut.table_id WHERE ut.table_name='tab_add_column'; 

+----------------+----------+----------+---------+---------+-----------+
|   TABLE_NAME   | COL_NAME | NOT_NULL | DEF_VAL | ON_NULL | TYPE_NAME |
+----------------+----------+----------+---------+---------+-----------+
| TAB_ADD_COLUMN | C1       | F        | <NULL>  | 0       | INTEGER   |
| TAB_ADD_COLUMN | C2       | T        | <NULL>  | 0       | CHAR      |
| TAB_ADD_COLUMN | C3       | F        | <NULL>  | 0       | DATETIME  |
+----------------+----------+----------+---------+---------+-----------+

三、修改列

3.1 语法结构

3.2 参数说明

column_name：待修改列名称
default_on_null：指定当值为NULL时填充默认值
- 未指定：当值为NULL时填充为NULL值
- ON NULL：当值为NULL时填充为默认值
- ON NULL FOR INSERT ONLY：当值为NULL时，仅插入语句填充默认值，其他语句填充NULL值
- ON NULL FOR INSERT AND UPDATE：当值为NULL时，仅插入与更新语句填充默认值，其他语句填充NULL值
b_expr：指定填充默认值
columnDef：修改列定义，请参阅表元素
alter_column_type：
- DROP DEFAULT:删除默认值
- DROP NOT NULL:删除不为NULL限制
- DROP NOTNULL:删除不为NULL限制
- SET NULL:设置可为NULL
- SET NOT NULL:设置不为NULL限制
- SET NOTNULL:设置不为NULL限制

3.3 示例

示例1
为表列设置默认值，并删除已有列。

sql

-- 创建表
SQL> CREATE TABLE tab_alter_column(c1 INTEGER, c2 VARCHAR);

-- 设置默认值
SQL> ALTER TABLE tab_alter_column ALTER COLUMN c1 DEFAULT 1024 DROP COLUMN c2;

-- 查询修改后的表结构
SQL> SELECT ut.table_name,uc.col_name,uc.not_null,uc.def_val,uc.on_null,uc.type_name FROM SYS_COLUMNS uc  JOIN SYS_TABLES ut ON uc.table_id=ut.table_id WHERE ut.table_name='tab_alter_column';

+------------------+----------+----------+---------+---------+-----------+
|    TABLE_NAME    | COL_NAME | NOT_NULL | DEF_VAL | ON_NULL | TYPE_NAME |
+------------------+----------+----------+---------+---------+-----------+
| TAB_ALTER_COLUMN | C1       | F        | 1024    | 0       | INTEGER   |
+------------------+----------+----------+---------+---------+-----------+

四、删除列

4.1 语法结构

4.2 参数说明

column_name：待删除列名

4.3 示例

示例1
从表中删除一列。

sql

-- 创建表
SQL> CREATE TABLE tab_drop_column(c1 INTEGER, c2 VARCHAR);

-- 设置默认值
SQL> ALTER TABLE tab_drop_column ALTER COLUMN c1 DEFAULT 1024 DROP COLUMN c2;

SQL> SELECT ut.table_name,uc.col_name,uc.not_null,uc.def_val,uc.on_null FROM SYS_COLUMNS uc JOIN SYS_TABLES ut ON uc.table_id=ut.table_id WHERE ut.table_name='tab_drop_column';

+-----------------+----------+----------+---------+---------+
|   TABLE_NAME    | COL_NAME | NOT_NULL | DEF_VAL | ON_NULL |
+-----------------+----------+----------+---------+---------+
| TAB_DROP_COLUMN | C1       | F        | 1024    | 0       |
+-----------------+----------+----------+---------+---------+

五、重整表

重建表的堆结构，用于优化存储或解决碎片问题。当表数据通过 DELETE 操作删除数据后，可能会产生无法再利用的无效存储，此部分存储只有通过重整表功能回收后，才能再次使用。重整表主要原理为：

创建一张挂载了原表存储的临时表
原表重新创建并挂载新分配的存储
将数据从临时表重新导入原表

由于重整表涉及数据全量导入操作，当表数据量很大时，耗时会很长，因此请务必谨慎操作！

注意

自v12.7版本起，重整表数据支持了BLOB、CLOB等大对象类型存储。
修改表列时，在以下情况会自动触发重整表，也需特别注意：
- 新增列，且指定了默认值，会触发自动重整表
- 新旧数据类型不同时：
  - 旧数据类型为CHAR，新数据类型不为VARCHAR，会触发自动重整表
  - 旧数据类型为VARCHAR，新数据类型为CHAR，且精度不同，会触发自动重整表
- 新旧数据类型相同时：
  - 数据类型为变长类型，且精标度不同，会触发自动重整表
  - 数据类型为NUMERIC或CHAR，且精标度不同，会触发自动重整表

5.1 语法结构

5.2 参数说明

table_name：待重整表名称
opt_wait：指定当所需资源锁被占用时，锁获取超时时间，请参阅超时限制

5.3 示例

示例1
对表新增一列，且设置默认值，将自动触发重整表操作。

sql

-- 创建表
SQL> CREATE TABLE tab_auto_rebuild_heap(c1 INTEGER);

-- 增加列并设置默认值
SQL> ALTER TABLE tab_auto_rebuild_heap ADD COLUMN c2 VARCHAR DEFAULT 'abc';

SQL> SELECT ut.table_name,uc.col_name,uc.not_null,uc.def_val,uc.on_null FROM SYS_COLUMNS uc JOIN SYS_TABLES ut ON uc.table_id=ut.table_id WHERE ut.table_name='tab_auto_rebuild_heap';

+-----------------------+----------+----------+---------+---------+
|      TABLE_NAME       | COL_NAME | NOT_NULL | DEF_VAL | ON_NULL |
+-----------------------+----------+----------+---------+---------+
| TAB_AUTO_REBUILD_HEAP | C1       | F        | <NULL>  | 0       |
| TAB_AUTO_REBUILD_HEAP | C2       | F        | 'abc'   | 0       |
+-----------------------+----------+----------+---------+---------+

示例2
数据库运维人员可以通过下列语句查询无效存储比例，评估是否需要执行重整表，当表存储碎片率超过 0.5（50%）时，建议执行重整表操作，以提升数据查询效率与存储利用率。

sql

-- 创建查询表中存储碎片率的存储函数
SQL> CREATE OR REPLACE FUNCTION func_get_table_fragmentation_rate(
        arg_db_name VARCHAR,
        arg_schema_name VARCHAR,
        arg_table_name VARCHAR
     ) RETURN DOUBLE AS
        TYPE ret_type IS RECORD(del_rows BIGINT, all_rows BIGINT);
        ret ret_type;
     BEGIN
        SELECT
        sum(del_num) del_rows,
        sum(row_num)+del_rows all_rows
        INTO ret
        FROM sys_databases db
        INNER JOIN sys_schemas scm ON db.db_id=scm.db_id
        INNER JOIN sys_tables tb ON tb.schema_id=scm.schema_id and tb.db_id =scm.db_id
        INNER JOIN sys_gstores gsto ON tb.gsto_no=gsto.head_no
        INNER JOIN sys_stores sto ON gsto.gsto_no=sto.gsto_no
        WHERE db_name=arg_db_name
        AND schema_name=arg_schema_name
        AND table_name=arg_table_name;
        RETURN ret.del_rows/ret.all_rows;
     END;

-- 创建表并插入 1000 条数据
SQL> CREATE TABLE tab_rebuild_heap(id INT IDENTITY(1,1),c1 CHAR(8192));
SQL> BEGIN
         FOR i IN 1..1000 LOOP
             INSERT INTO tab_rebuild_heap VALUES(DEFAULT,'test');
         END LOOP;
     END;

-- 删除表中 id 为偶数的 500 条数据
SQL> DELETE FROM tab_rebuild_heap WHERE MOD(id,2)=0;

-- 查询表中存储碎片率，此时为 0.5（50%）
SQL> SELECT func_get_table_fragmentation_rate('SYSTEM','SYSDBA','tab_rebuild_heap');
+--------------+
|    EXPR1     |
+--------------+
| 5.000000e-01 |
+--------------+

-- 执行重整表
SQL> ALTER TABLE tab_rebuild_heap REBUILD HEAP;

-- 查询表中存储碎片率，此时为 0（0%）
SQL> SELECT func_get_table_fragmentation_rate('SYSTEM','SYSDBA','tab_rebuild_heap');
+--------------+
|    EXPR1     |
+--------------+
| 0.000000e+00 |
+--------------+

六、重开表

开表是一种更新内存中表的元信息的操作，如当表上的索引失效等情况时，需要重新开表。

6.1 语法格式

6.2 参数说明

table_name：待重开表名称
opt_wait：指定当所需资源锁被占用时，锁获取超时时间，请参阅超时限制

6.3 示例

示例1
重新开表，并设置超时为5000毫秒

sql

-- 创建表
SQL> CREATE TABLE tab_reopen(id INTEGER);

-- 重开表
SQL> ALTER TABLE tab_reopen REOPEN WAIT 5000;

七、超时限制

修改表功能支持设置获取锁超时限制。

7.1 语法结构

7.2 参数说明

未指定：若未获取到锁，最多等候ddl_timeout毫秒，超时报告错误，等效于 WAIT
NOWAIT：若未获取到锁，不等候，立即报告错误
WAIT：若未获取到锁，最多等候ddl_timeout毫秒，超时报告错误，请参阅系统配置参数ddl_timeout
WAIT wait_ms：若未获取到锁，最多等候 wait_ms 毫秒，取值范围为[0,2147483647]，超时报告错误
注意
- 当为0值时，将强制使用系统配置参数ddl_timeout作为最多等候时间
- 当为大于300000值时，将强制使用300000值作为最多等候时间

修改表 ​

一、主要语法结构 ​

1.1 语法格式 ​

1.2 参数说明 ​

1.3 示例 ​

二、添加列 ​

2.1 语法结构 ​

2.2 示例 ​

三、修改列 ​

3.1 语法结构 ​

3.2 参数说明 ​

3.3 示例 ​

四、删除列 ​

4.1 语法结构 ​

4.2 参数说明 ​

4.3 示例 ​

五、重整表 ​

5.1 语法结构 ​

5.2 参数说明 ​

5.3 示例 ​

六、重开表 ​

6.1 语法格式 ​

6.2 参数说明 ​

6.3 示例 ​

七、超时限制 ​

7.1 语法结构 ​

7.2 参数说明 ​

修改表