达梦8，关于参数CTAB_SEL_WITH_CONS的验证

看到管理员手册中说到这个参数，感觉比较有趣，就想来验证下，先摘录管理员手册原话如下：

如 果 用 户 通 过 单 表 的 全 表 查 询 进 行 建 表 操 作 ， 则 可 以 通 过 将 INI 参 数CTAB_SEL_WITH_CONS 置为 1 进行原始表上约束的拷贝，列上能拷贝的约束包括默认值属性、自增属性、非空属性以及加密属性，表上能拷贝的约束包括唯一约束、PK 约束以及 CHECK约束。也可将 CTAB_SEL_WITH_CONS 置为 2 进行原始表上表结构、分区信息、存储信息以及表约束的拷贝，其中表约束包括唯一约束、PK 约束以及 CHECK 约束。也可将CTAB_SEL_WITH_CONS 置为 3 同时进行 1 和 2 的拷贝。

先查询一下这个参数的想信息：

SQL> select * from v$dm_ini where PARA_NAME like 'CTAB_SEL_WITH_CONS'; 行号     PARA_NAME          PARA_VALUE MIN_VALUE MAX_VALUE DEFAULT_VALUE MPP_CHK SESS_VALUE FILE_VALUE ---------- ------------------ ---------- --------- --------- ------------- ------- ---------- ----------            DESCRIPTION                                               PARA_TYPE            --------------------------------------------------------- --------- 1          CTAB_SEL_WITH_CONS 0          0         2         0             N       0          0            Whether to build constraints when creating table by query SYS

如上看到，这个参数最大只能支持到2，并不支持设置为3。希望管理员手册的作者在表述上可以更加严谨。实际上确实也不支持3（我在linux和windows两个平台都试了也不支持）。

SQL> alter system set 'CTAB_SEL_WITH_CONS'=3; alter system set 'CTAB_SEL_WITH_CONS'=3; [-838]:非法的配置参数值. 已用时间: 10.964(毫秒). 执行号:0.

而后可以创建下具体实验环境：

建表语句如下：

SQL> CREATE TABLE STUDENT( 2   NAME VARCHAR(20), 3   AGE INT PRIMARY KEY, 4   SEX VARCHAR(10) CHECK (SEX IN ('MAIL','FEMAIL')), GRADE INT CHECK (GRADE IN (7,8,9))) 5   PARTITION BY LIST(GRADE) 6   SUBPARTITION BY LIST(SEX) SUBPARTITION TEMPLATE 7   ( 8   SUBPARTITION Q1 VALUES('MAIL'), 9   SUBPARTITION Q2 VALUES('FEMAIL') 10  ), 11  SUBPARTITION BY RANGE(AGE) SUBPARTITION TEMPLATE 12  ( 13  SUBPARTITION R1 VALUES LESS THAN (12), 14  SUBPARTITION R2 VALUES LESS THAN (15), 15  SUBPARTITION R3 VALUES LESS THAN (MAXVALUE) 16  ) 17  ( 18  PARTITION P1 VALUES (7), 19  PARTITION P2 VALUES (8), 20  PARTITION P3 VALUES (9) 21  ); 警告: 列表分区未包含DEFAULT,可能无法定位到分区 操作已执行 已用时间: 50.302(毫秒). 执行号:602.

插入语句如下：

insert into student VALUES ('a',10,'MAIL',7); insert into student VALUES ('b',11,'MAIL',8); insert into student VALUES ('c',13,'MAIL',9); insert into student VALUES ('d',16,'MAIL',7); commit;

实际还创建了一个非全局索引。事后验证，这个索引确实不会被支持复制。支持复制的只有其表述中所说的约束和表结构。所以，如果使用这个语句进行一些类似于表容量收缩等操作，请千万谨慎。因为可能漏掉其相关的索引或外键等。

先设置此参数为2

SQL> alter system set 'CTAB_SEL_WITH_CONS'=2; DMSQL 过程已成功完成 已用时间: 12.958(毫秒). 执行号:625. SQL> create table student_new as select * from student; 操作已执行 已用时间: 137.429(毫秒). 执行号:626.

表结构及约束得到了复制：

SQL> select tabledef('SYSDBA','STUDENT_NEW'); 警告: 字符串截断 行号     TABLEDEF('SYSDBA','STUDENT_NEW')                                                                        ---------- ------------------------------------------------------------------------------------------------------- 1          CREATE TABLE "SYSDBA"."STUDENT_NEW" ( "NAME" VARCHAR(20), "AGE" INT NOT NULL, "SEX" VARCHAR(10), "GRADE" INT, NOT CLUSTER PRIMARY KEY("AGE"), CHECK(SEX IN ('MAIL', 'FEMAIL'))   --约束 ,CHECK(GRADE IN (7, 8, 9))) PARTITION BY LIST("GRADE") SUBPARTITION BY LIST("SEX") SUBPARTITION TEMPLATE ( SUBPARTITION  "Q1"  VALUES('MAIL') STORAGE(ON "MAIN"), SUBPARTITION  "Q2"  VALUES('FEMAIL') STORAGE(ON "MAIN") ), SUBPARTITION BY RANGE("AGE") SUBPARTITION TEMPLATE ( SUBPARTITION  "R1"  VALUES LESS THAN(12) STORAGE(ON "MAIN"), SUBPARTITION  "R2"  VALUES LESS THAN(15) STORAGE(ON "MAIN"), SUBPARTITION  "R3"  VALUES LESS THAN(MAXVALUE) STORAGE(ON "MAIN") ) ( PARTITION  "P1"  VALUES(7)   STORAGE(ON "MAIN", CLUSTERBTR) , PARTITION  "P2"  VALUES(8)   STORAGE(ON "MAIN", CLUSTERBTR) , PARTITION  已用时间: 2.323(毫秒). 执行号:627.

再将此参数设置为1：

SQL> alter system set 'CTAB_SEL_WITH_CONS'=1; DMSQL 过程已成功完成 已用时间: 9.582(毫秒). 执行号:646. SQL> create table student1 as select * from student; 操作已执行 已用时间: 9.791(毫秒). 执行号:647.

仅复制了约束，并未复制表结构：

SQL> select tabledef('SYSDBA','STUDENT1'); 行号     TABLEDEF('SYSDBA','STUDENT1')                                                                          ---------- ------------------------------------------------------------------------------------------------------- 1          CREATE TABLE "SYSDBA"."STUDENT1" ( "NAME" VARCHAR(20), "AGE" INT NOT NULL, "SEX" VARCHAR(10), "GRADE" INT, NOT CLUSTER PRIMARY KEY("AGE"), CHECK(SEX IN ('MAIL', 'FEMAIL')) ,CHECK(GRADE IN (7, 8, 9))) STORAGE(ON "MAIN", CLUSTERBTR) ; 已用时间: 0.751(毫秒). 执行号:648. SQL>

验证完全后，这里提一下触发器吧。因为删除表，触发器也会一起级联被删除。所以专门想尝试一下。我仅仅对表重命名，然后看表的触发器是否可用就好了。触发器创建及有效性如下：

SQL> CREATE OR REPLACE TRIGGER STUDENT_TRIGGER 2   BEFORE INSERT 3   ON STUDENT 4   FOR EACH ROW --表示行级触发器 5   6   BEGIN 7    if :new.AGE<7 then 8    RAISE_APPLICATION_ERROR(-20001,'未到达入学年龄'); 9    END if; 10  end; 11  / 操作已执行 已用时间: 9.427(毫秒). 执行号:606. SQL> insert into student VALUES ('e',6,'MAIL',7);  --可以看到触发器已生效 insert into student VALUES ('e',6,'MAIL',7); [-20001]:未到达入学年龄 -20001: STUDENT_TRIGGER line 8 . 已用时间: 1.497(毫秒). 执行号:0.

修改表名尝试如下：

SQL> ALTER TABLE STUDENT RENAME TO STUDENT_BAK; 操作已执行 已用时间: 29.489(毫秒). 执行号:628. SQL> alter table student_new rename to student; 操作已执行 已用时间: 26.868(毫秒). 执行号:629.

触发器还是否生效呢？

SQL> insert into student VALUES ('e',6,'MAIL',7); 影响行数 1

失效了，实际这个触发器还是存在的，但是就是不再有效。

重新编译下试试。

SQL> CREATE OR REPLACE TRIGGER STUDENT_TRIGGER 2   BEFORE INSERT 3   ON STUDENT 4   FOR EACH ROW --表示行级触发器 5   6   BEGIN 7    if :new.AGE<7 then 8    RAISE_APPLICATION_ERROR(-20001,'未到达入学年龄'); 9    END if; 10  end; 11  / CREATE OR REPLACE TRIGGER STUDENT_TRIGGER BEFORE INSERT ON STUDENT FOR EACH ROW --表示行级触发器 BEGIN  if :new.AGE<7 then  RAISE_APPLICATION_ERROR(-20001,'未到达入学年龄');  END if; end; 第10 行附近出现错误[-3007]:替换触发器[STUDENT_TRIGGER]属性不一致. 已用时间: 0.245(毫秒). 执行号:0. SQL> insert into student VALUES ('e',6,'MAIL',7); 影响行数 1 已用时间: 0.257(毫秒). 执行号:649. SQL> rollback; 操作已执行 已用时间: 0.310(毫秒). 执行号:650. SQL>

无法进行编译。只能删除此触发器重建了。实际仔细观察，会发现这里触发器已经被修改对象为STUDENT_BAK表了。这个坑在迁移时一定要注意。

达梦数据库技术社区：https://eco.dameng.com