- 示例:修改表 t2 中字段 d 的字段类型。
obclient>CREATE TABLE t2(d VARCHAR(3));
Query OK, 0 rows affected (0.04 sec)
obclient>ALTER TABLE t2 MODIFY d CHAR(10);
Query OK, 0 rows affected (0.04 sec)
示例:增加、删除列。
obclient> CREATE TABLE test (c1 NUMBER(30) PRIMARY KEY,c2 VARCHAR(50));
Query OK, 0 rows affected (0.07 sec)
增加列前,执行
DESCRIBE test;
命令查看表信息:obclient> DESCRIBE test;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| C1 | NUMBER(30) | NO | PRI | NULL | NULL |
| C2 | VARCHAR2(50) | YES | NULL | NULL | NULL |
+-------+--------------+------+-----+---------+-------+
执行以下命令增加 c3 列:
obclient> ALTER TABLE test ADD c3 NUMBER(30);
Query OK, 0 rows affected (0.02 sec)
增加列后,执行
DESCRIBE test;
命令查看表信息:执行以下命令删除 c3 列:
obclient> ALTER TABLE test DROP column c3;
删除列后,执行
DESCRIBE test;
命令查看表信息:obclient> DESCRIBE test;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| C2 | VARCHAR2(50) | YES | NULL | NULL | NULL |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
示例:设置表格 test 的副本数,并且增加列 c5。
obclient> ALTER TABLE test SET REPLICA_NUM=2, ADD c5 INT;
Query OK, 0 rows affected (0.02 sec)
- 为非模板化二级分区表 t_range_range1 添加二级分区 p1_r4。
obclient>ALTER TABLE t_range_range1 MODIFY partition p1 ADD subpartition p1_r4 values less than (400);
Query OK, 0 rows affected (0.08 sec)
- 删除非模板化二级分区表 t_range_range1 的二级分区 p2_r1。
- 为非模板化二级分区表 t_range_range1 添加一级分区 p4,需要同时指定一级分区的定义和该分区下的二级分区定义。
obclient>ALTER TABLE t_range_range1 ADD partition p4 values less than (500) (
subpartition p4_r1 values less than (100),
subpartition p4_r2 values less than (200),
subpartition p5_r3 values less than (300)
);
Query OK, 0 rows affected (0.08 sec)
- 为模板化二级分区表 t_range_range 添加一级分区 p3,只需要指定一级分区的定义,二级分区的定义会自动按照模板填充。
obclient>CREATE TABLE t_range_range(c1 INT, c2 INT, PRIMARY KEY(c1,c2))
PARTITION BY RANGE(c1) SUBPARTITION BY RANGE(c2) 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 (0.07 sec)
Query OK, 0 rows affected (0.07 sec)
- 修改表 t1 的并行度为 3。
obclient> ALTER TABLE t1 PARALLEL 3;
Query OK, 0 rows affected (0.06 sec)
- 修改外键约束的状态
"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 (0.09 sec)
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 (0.00 sec)
obclient> ALTER TABLE MMS_GROUPUSER ENABLE CONSTRAINT FK_MMS_GROUPUSER_02;
Query OK, 0 rows affected (0.21 sec)
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 |
- 清空分区表 t_log_part_by_range 的分区 M202001 和 M202002 中的全部数据。