目录
DM 默认创建创建索引组织表。Oracle 默认创建的是堆表。
索引组织表：有唯一的聚簇索引键，表是按照聚簇索引键排序（插入有序），使用物理 rowid，rowid 占用存储空间。创建表时，如果指定主键，则主键为聚簇索引键（由参数 PK_WITH_CLUSTER），如果没有创建主键，则使用 rowid 为聚簇索引键。
堆表：插入是无序的，使用逻辑 rowid，rowid 不占用存储空间。
1、表的聚集索引
DM 提供三种方式供用户指定聚集索引键：
CLUSTER PRIMARY KEY：指定列为聚集索引键，并同时指定为主键，称为聚集主键；
CLUSTER KEY：指定列为聚集索引键，但是是非唯一的；
CLUSTER UNIQUE KEY：指定列为聚集索引键，并且是唯一的。
例 ：创建 student 表，指定 stu_no 为聚集主键。
CREATE TABLE STUDENT(
STUNO INT CLUSTER PRIMARY KEY,
STUNAME VARCHAR(15) NOT NULL,
TEANO INT,
CLASSID INT
);如果建表语句未指定聚集索引键，DM 的默认聚集索引键是 ROWID。
在 dm.ini 配置文件中，可以通过指定 PK_WITH_CLUSTER 使表中的主键自动转化为聚集主键。默认情况下，PK_WITH_CLUSTER 为 0，即建表时指定的主键不会自动转化为聚集主键；若为 1，则主键自动变为聚集主键。
SQL> sp_tabledef('TEST','STUDENT');
行号 COLUMN_VALUE
---------- ---------------------------------------------------------------------------------------------------------------------------------------------------
1 CREATE TABLE "TEST"."STUDENT" ( "STUNO" INT NOT NULL, "STUNAME" VARCHAR(15) NOT NULL, "TEANO" INT, "CLASSID" INT, CLUSTER PRIMARY KEY("STUNO")) STORAGE(ON "TBS", CLUSTERBTR) ;
已用时间: 3.098(毫秒). 执行号:508.
SQL>
CREATE TABLE STUDENT1(
STUNO INT PRIMARY KEY,
STUNAME VARCHAR(15) NOT NULL,
TEANO INT,
CLASSID INT
);
SQL> sp_tabledef('TEST','STUDENT1');
行号 COLUMN_VALUE
---------- ---------------------------------------------------------------------------------------------------------------------------------------------------
1 CREATE TABLE "TEST"."STUDENT1" ( "STUNO" INT NOT NULL, "STUNAME" VARCHAR(15) NOT NULL, "TEANO" INT, "CLASSID" INT, NOT CLUSTER PRIMARY KEY("STUNO")) STORAGE(ON "TBS", CLUSTERBTR) ;
已用时间: 0.981(毫秒). 执行号:515.
SQL> alter system set 'PK_WITH_CLUSTER'=1 both;
DMSQL 过程已成功完成
已用时间: 20.801(毫秒). 执行号:518.
SQL> show parameter PK_WITH_CLUSTER;
行号 PARA_NAME PARA_VALUE
---------- --------------- ----------
1 PK_WITH_CLUSTER 1
SQL>
CREATE TABLE STUDENT2(
STUNO INT PRIMARY KEY,
STUNAME VARCHAR(15) NOT NULL,
TEANO INT,
CLASSID INT
);
SQL> sp_tabledef('TEST','STUDENT2');
行号 COLUMN_VALUE
---------- ---------------------------------------------------------------------------------------------------------------------------------------------------
1 CREATE TABLE "TEST"."STUDENT2" ( "STUNO" INT NOT NULL, "STUNAME" VARCHAR(15) NOT NULL, "TEANO" INT, "CLASSID" INT, CLUSTER PRIMARY KEY("STUNO")) STORAGE(ON "TBS", CLUSTERBTR) ;
已用时间: 1.379(毫秒). 执行号:521.
SQL> CREATE TABLE STUDENT3(
STUNO INT PRIMARY KEY,
STUNAME VARCHAR(15) NOT NULL CLUSTER KEY,
TEANO INT,
CLASSID INT
);2 3 4 5 6
操作已执行
已用时间: 6.346(毫秒). 执行号:522.
SQL> sp_tabledef('TEST','STUDENT3');
行号 COLUMN_VALUE
---------- ---------------------------------------------------------------------------------------------------------------------------------------------------
1 CREATE TABLE "TEST"."STUDENT3" ( "STUNO" INT NOT NULL, "STUNAME" VARCHAR(15) NOT NULL, "TEANO" INT, "CLASSID" INT, CLUSTER KEY("STUNAME"), NOT CLUSTER PRIMARY KEY("STUNO")) STORAGE(ON "TBS", CLUSTERBTR) ;
已用时间: 1.226(毫秒). 执行号:523.
SQL> 2、查询建表
create table t_emp01 as select * from dmhr.employee;
create table t_emp02 like dmhr.employee;
create table t_emp03 as select * from dmhr.employee where 1=0;
--使用CREATE table as创建表时，默认不会复制表的约束信息，由参数 CTAB_SEL_WITH_CONS指定。CTAB_SEL_WITH_CONS的取值：
如果用户通过单表的全表查询进行建表操作，因 INI 参数 CTAB_SEL_WITH_CONS 默认为 0，若源列不是主键列，且显式指定了 NOT NULL 约束，则拷贝源列的非空信息；若源列是主键列，不论是否显式指定 NOT NULL 约束，都不拷贝其非空信息。
用户可以通过将 INI 参数 CTAB_SEL_WITH_CONS 置为 1 进行原始表上约束的拷贝，列上能拷贝的约束包括默认值属性、自增属性、非空属性以及加密属性，表上能拷贝的约束包括唯一约束、PK 约束以及 CHECK 约束。如果拷贝的唯一约束和 PK 约束不是聚集索引键，则不拷贝聚集索引；若源表包含虚拟列，则将虚拟列变为普通列。
用户也可将 CTAB_SEL_WITH_CONS 置为 2 进行原始表上表结构、分区信息、存储信息以及表约束的拷贝（仅限 huge 表与 huge 表之间或行表与行表之间进行拷贝），其中各列属性完全拷贝，表约束包括唯一约束、PK 约束以及 CHECK 约束，并拷贝聚集索引和系统创建的实索引，若源表包含虚拟列，则拷贝此虚拟列。需要注意，只有在全表查询时才会进行表约束和相关信息的拷贝，需保证查询项与源表列顺序与列名完全一致。
2、更改表
添加字段：
alter table hrtest.t_testpid add column email varchar(20); 修改字段类型：
alter table hrtest.t_testpid modify email varchar(50); 删除字段：
alter table hrtest.t_testpid drop logtime; 对字段添加默认值（大表不建议添加字段时给默认值，耗时很长）：
alter table hrtest.t_testpid add column logtime datetime default sysdate; 重命名表或字段：
alter table t_test rename to t_testoa;
alter table t_testoa rename column id to pid;对表和字段添加注释：
comment on column t_testpid.sex is 'sex. 0:female 1: male';
comment on table t_testpid is 'this is a test table';
select * from user_tab_comments;
select * from user_col_comments; 修改表的表空间（DM 会自动重建该表上的索引）
alter table hrtest.T_TESTPID move tablespace main;
select * from dba_tables t where t.TABLE_NAME like 'T_TEST%';数据字典：
select * from dba_tables t where t.owner='HRTEST';
select * from dba_tab_columns t where t.owner='HRTEST';
select t.TABLE_NAME, t.TABLESPACE_NAME from user_tables t;
select t.TABLE_NAME, t.COLUMN_NAME, t.NULLABLE from USER_TAB_COLS t;3、清空表
DELETE FROM employee;
TRUNCATE TABLE employee;TRUNCATE不会产生任何回滚信息。执行 TRUNCATE 会立即提交，而且不能回滚。
DM 数据库 TRUNCATE 表后，原来分配给该表的空间会被释放，供其他数据库对象使用，大大提高空间的利用效率。
4、查看表定义
CALL SP_TABLEDEF('SYSDBA', 'employee');5、表的约束
约束类型：
NOT NULL：非空约束
UNIQUE：唯一约束，列值可以为空，但值必须唯一
PRIMARY KEY：主键约束 (唯一约束+非空约束)，一张表只能有一个主键。
FOREIGN KEY：外键约束，引用另一张表的主键或者唯一键（有唯一索引），表 A 的某一
列引用 B 表某个唯一列，B 称为父表、A 称为子表。
CHECK：检验约束，用于检验列的值需要满足检验条件
非空约束
alter table hrtest.t_testpid modify pname not null; 唯一约束
alter table hrtest.t_testpid add CONSTRAINT uk_testpid_email unique (email); 主键约束
alter table hrtest.t_testpid ADD CONSTRAINT pk_testpid_pid PRIMARY KEY(pid); 检验约束
alter table hrtest.t_testpid add salary number(10,2);
alter table hrtest.t_testpid ADD CONSTRAINT ck_testpid_salary CHECK
(salary>=2100); 外键约束（外键引用另一张表的主键或者唯一键）
alter table hrtest.t_test add CONSTRAINT fk_test_id FOREIGN KEY(id) REFERENCES
hrtest.t_testpid(pid); 重命名约束
alter table T_EMP RENAME CONSTRAINT pk_emp_deptid to ffk_emp_deptid; 设置列的默认值
alter table "OA"."T_EMP" alter column "HIRE_DATE" set default (sysdate);
alter table "OA"."T_EMP" modify "HIRE_DATE" default (sysdate); 约束的禁用和启用、删除
alter table hrtest.t_test disable CONSTRAINT fk_test_id;
alter table hrtest.t_test enable CONSTRAINT fk_test_id;
alter table hrtest.t_test drop CONSTRAINT fk_test_id;批量禁用外键约束
select 'alter table '||owner||'.'||table_name||' disable constraint '|| t.CONSTRAINT_NAME ||';'
from DBA_CONSTRAINTS t
where t.OWNER ='DMTEST' and t.CONSTRAINT_TYPE = 'R';相关数据字典
select * from dba_constraints t where t.owner='HRTEST';
select * from DBA_CONS_COLUMNS t where t.owner='HRTEST';6、表的自增列
IDENTITY 自增列
语法格式：
IDENTITY [ (种子, 增量) ]
参数
1．种子：装载到表中的第一个行所使用的值；
2．增量：增量值，该值被添加到前一个已装载的行的标识值上。增量值可以为正数或负数，但不能为 0。
使用说明
1.IDENTITY 适用于 INT(-2147483648～+2147483647)、BIGINT(-263～+263-2)类型的列。每个表只能创建一个自增列；
2.不能对自增列使用 DEFAULT 约束；
3.必须同时指定种子和增量值，或者二者都不指定。如果二者都未指定，则取默认值(1,1)。若种子或增量为小数类型，报错；
4.最大值和最小值为该列的数据类型的边界；
5.建表种子和增量大于最大值或者种子和增量小于最小值时报错；
6.自增列一旦生成，无法更新，不允许用 UPDATE 语句进行修改；
7.自增列的值一旦生成，无法回滚。例如，数据表 T 中包含一个自增列，该列当前值为 n，增量为 1，对表 T 执行数据插入时，如果经约束检查发现待插入数据不满足约束条件，则会回滚数据插入操作，但此时自增列的值 n+1 已经生成，无法回滚，因此自增列的当前值变为 n+1；
8.临时表、列存储表、水平分区表不支持使用自增列。
下面我们对其做简单的测试：
SQL> CREATE TABLE IDENT_TABLE (
C1 INT IDENTITY(100, 100),
C2 INT
);
操作已执行
已用时间: 30.010(毫秒). 执行号:585.
SQL>
SQL> insert into IDENT_TABLE values (1);
影响行数 1
已用时间: 1.511(毫秒). 执行号:586.
SQL> select * from IDENT_TABLE;
行号 C1 C2
---------- ----------- -----------
1 100 1
已用时间: 5.876(毫秒). 执行号:587.
SQL> insert into IDENT_TABLE values (1);
影响行数 1
已用时间: 0.702(毫秒). 执行号:588.
SQL> select * from IDENT_TABLE;
行号 C1 C2
---------- ----------- -----------
1 100 1
2 200 1
已用时间: 0.310(毫秒). 执行号:589.可以看到我们设置IDENTITY(100, 100)，自增列的初始值为100，自增的步长为100。
SQL> update IDENT_TABLE set C1=180 where C1=200;
update IDENT_TABLE set C1=180 where C1=200;
第1 行附近出现错误[-2664]:试图修改自增列[C1].
已用时间: 0.742(毫秒). 执行号:0.
SQL> insert into IDENT_TABLE(C1,C2) values(220,1);
insert into IDENT_TABLE values(220,1);
第1 行附近出现错误[-2723]:仅当指定列列表，且SET IDENTITY_INSERT为ON时，才能对自增列赋值.
已用时间: 0.563(毫秒). 执行号:0.可以看到，不允许对自增字段进行update操作，不能对自增字段进行手动插入。
SET IDENTITY_INSERT 属性
设置是否允许将显式值插入表的自增列中。ON 是，OFF 否。
语法格式：
SET IDENTITY_INSERT [<模式名>.]<表名> ON WITH REPLACE NULL;
SET IDENTITY_INSERT [<模式名>.]<表名> OFF;
参数：
1．<模式名> 指明表所属的模式，缺省为当前模式；
2．<表名> 指明含有自增列的表名。
使用说明：
1．IDENTITY_INSERT 属性的默认值为 OFF。SET IDENTITY_INSERT 的设置是在执行或运行时进行的。当一个连接结束，IDENTITY_INSERT 属性将被自动还原为 OFF；
2．DM 要求一个会话连接中只有一个表的 IDENTITY_INSERT 属性可以设置为 ON，当设置一个新的表 IDENTITY_INSERT 属性设置为 ON 时，之前已经设置为 ON 的表会自动还原为 OFF。当一个表的 IDENTITY_INSERT 属性被设置为 ON 时，该表中的自动增量列的值由用户指定。如果插入值大于表的当前标识值(自增列当前值)，则 DM 自动将新插入值作为当前标识值使用，即改变该表的自增列当前值；否则，将不影响该自增列当前值；
3．当设置一个表的 IDENTITY_INSERT 属性为 OFF 时，新插入行中自增列的当前值由系统自动生成，用户将无法指定；
4．自增列一经插入，无法修改；
5．手动插入自增列，除了将 IDENTITY_INSERT 设置为 ON，还要求在插入列表中明确指定待插入的自增列列名。插入方式与非 IDENTITY 表是完全一样的。如果插入时，既不指定自增列名也不给自增列赋值，则新插入行中自增列的当前值由系统自动生成;
6．WITH REPLACE NULL 此模式下允许显式插入 NULL 值，同时，系统自动将 NULL 值替换为自增值。
SQL> SET IDENTITY_INSERT TEST.IDENT_TABLE ON WITH REPLACE NULL;
操作已执行
已用时间: 0.401(毫秒). 执行号:592.
SQL> insert into IDENT_TABLE(C1,C2) values(220,1);
影响行数 1
已用时间: 0.790(毫秒). 执行号:593.
SQL> select * from IDENT_TABLE;
行号 C1 C2
---------- ----------- -----------
1 100 1
2 200 1
3 220 1
已用时间: 1.177(毫秒). 执行号:596.可以看到，当IDENTITY_INSERT属性为ON时，可以手动对自增列进行插入操作。
SQL> SET IDENTITY_INSERT TEST.IDENT_TABLE OFF;
操作已执行
已用时间: 1.170(毫秒). 执行号:597.
SQL> insert into IDENT_TABLE values (1);
影响行数 1
已用时间: 0.680(毫秒). 执行号:598.
SQL> select * from IDENT_TABLE;
行号 C1 C2
---------- ----------- -----------
1 100 1
2 200 1
3 220 1
4 320 1
已用时间: 0.389(毫秒). 执行号:599.当我们把IDENTITY_INSERT重新设为OFF，对表插入数据时，刚才手动插入的自增列值成为新的标识值。
AUTO_INCREMENT 自增列
使用说明
AUTO_INCREMEN 列必须为主键或主键的部分，只支持整数类型（支持 TINYINT/SMALLINT/INT/BIGINT，不支持 dec(N, 0)等），不能违反主键的唯一性约束。
AUTO_INCREMENT 关键字需要和 <AUTO_INCREMENT 子句 >、三个 AUTO_INCREMENT 相关 INI 参数（AUTO_INCREMENT_INCREMENT，AUTO_INCREMENT_OFFSET，NO_AUTO_VALUE_ON_ZERO）一起配合使用。
<AUTO_INCREMENT 子句 >：用于指定隐式插入值的起始边界值，若不指定，起始边界值默认为 1。
AUTO_INCREMENT_INCREMENT：动态会话级，表示 AUTO_INCREMENT 的步长。取值范围 1~65535。缺省值为 1。
AUTO_INCREMENT_OFFSE：动态会话级，表示 AUTO_INCREMENT 的基准偏移。取值范围 1~65535。缺省值为 1。
NO_AUTO_VALUE_ON_ZERO：动态会话级，表示 AUTO_INCREMENT 列插入 0 时，是否自动插入自增的下一个值。
SQL> show parameter increment
行号 PARA_NAME PARA_VALUE
---------- ------------------------ ----------
1 AUTO_INCREMENT_INCREMENT 1
2 AUTO_INCREMENT_OFFSET 1
SQL> CREATE TABLE "TABLE_1"
(
"ID" INT PRIMARY KEY AUTO_INCREMENT ,
"NAME" CHAR(10)
);2 3 4 5
操作已执行
SQL> insert into TABLE_1(name) values('test');
影响行数 1
已用时间: 0.728(毫秒). 执行号:526.
SQL> select * from table_1;
行号 ID NAME
---------- ----------- ----------
1 1 test
已用时间: 0.457(毫秒). 执行号:529.
SQL> insert into TABLE_1(name) values('test');
影响行数 1
已用时间: 0.418(毫秒). 执行号:530.
SQL> select * from table_1;
行号 ID NAME
---------- ----------- ----------
1 1 test
2 2 test
已用时间: 0.301(毫秒). 执行号:532.可以看到，对主键设置AUTO_INCREMENT属性后，默认初始值为1，自增步长为1。
自增列值X的计算
隐式生成的自增列值X 由系统根据AUTO_INCREMENT_OFFSET 、AUTO_INCREMENT_INCREMENT 等因子自动计算得出。计算公式 X=AUTO_INCREMENT_OFFSET+n*AUTO_INCREMENT_INCREMENT。X 满足大于等于起始值且大于当前自增列值中最大值，n取最小值。
修改参数AUTO_INCREMENT_INCREMENT进行测试：
SQL> alter system set 'AUTO_INCREMENT_INCREMENT'=10;
DMSQL 过程已成功完成
已用时间: 4.947(毫秒). 执行号:610.
SQL> show parameter increment
行号 PARA_NAME PARA_VALUE
---------- ------------------------ ----------
1 AUTO_INCREMENT_INCREMENT 10
2 AUTO_INCREMENT_OFFSET 1
SQL> insert into IDENT_TABLE_1(name) values('test');
影响行数 1
已用时间: 0.464(毫秒). 执行号:612.
SQL> select * from IDENT_TABLE_1;
行号 ID NAME
---------- ----------- ----------
1 1 test
2 2 test
3 11 test
已用时间: 0.368(毫秒). 执行号:613.
SQL> insert into IDENT_TABLE_1(name) values('test');
影响行数 1
已用时间: 0.950(毫秒). 执行号:614.
SQL> select * from IDENT_TABLE_1;
行号 ID NAME
---------- ----------- ----------
1 1 test
2 2 test
3 11 test
4 21 test
已用时间: 0.250(毫秒). 执行号:615.可以看到AUTO_INCREMENT_INCREMENT设置为10后，下一个自增列值为1+1*10=11，1+2*10=21...
7、表的空间使用
TABLE_USED_SPACE：已分配给表的页面数；
TABLE_USED_PAGES：表已使用的页面数。
CREATE TABLE SPACE_TABLE (
C1 INT,
C2 INT
);
SELECT TABLE_USED_SPACE('SYSDBA','SPACE_TABLE');
SELECT TABLE_USED_PAGES('SYSDBA','SPACE_TABLE');
517
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
