DM 基础数据库管理

1.DM系统管理员的类型: 数据库管理员（DBA） ,数据库安全员（SSO）, 数据库审计员（AUDITOR）,数据库对象操作员（DBO）

创建和配置DM数据库
1.创建之前,需要规划数据库.

项目
限制内容
数据库名
名称为字符串，长度不能超过 128 个字符。不能包含特殊字符.默认为 DAMENG
DMTEST
实例名
名称为字符串，长度不能超过 128 个字符
默认为 DMSERVER
DMTEST
端口
默认 5236,服务器配置此参数，
有效值范围（1024~65534），发起连接端的端口在 1024~65535 之间随机分配
5252
文件路径
数据库存储路径,控制文件,日志文件存储路径,文件路径长度最大为 256 个字符
/opt/dm8
簇大小
16 32 64
16
页大小
4KB、8KB、16KB 或者 32KB8KB
日志文件大小
建议2G ,取值：64~2048 之间的整数，单位 M
2G
SYSDBA 和 SYSAUDITOR 等系统用户的密码
口令必须是合法的字符串，不能少于 9 个或多于 48 个字符
SYSDBA
字符集
默认GB18030
GBK
数据库表和索引
所需空间大小

10G

2.根据上述信息,使用图形,或dminit来创建数据库

根据业务需求,选择指定的数据库模板: 一般用途, 联机分析, 联机事务处理.

3.启动和关闭数据库.
启动和和关闭数据库:
(1) 使用DM服务查看器 ,win直接选择图标, linux使用dmservice.sh 脚本启动.
在DM服务查看器中,启动相应的服务.
(2) linux 系统,使用命令
(centos 7): root用户: systemctl status|start|stop| restart DmServiceDMSERVER.service
dmdba用户: DmServiceDMSERVER { start [ mount ] | stop [ -s signal ] | stop_all | status | condrestart | restart [ mount ] }

查看license信息.

SQL> select * from v$license ;

行号 LIC_VERSION SERIES_NO SERVER_SERIES SERVER_TYPE SERVER_VER EXPIRED_DATE AUTHORIZED_CUSTOMER AUTHORIZED_USER_NUMBER CONCURRENCY_USER_NUMBER MAX_CPU_NUM NOACTIVE_DEADLINE

       HARDWARE_ID CHECK_CODE PRODUCT_TYPE PROJECT_NAME CPU_TYPE OS_TYPE MAX_CORE_NUM HARDWARE_TYPE CLUSTER_TYPE DATE_GEN   SERVER_SERIES_NAME TABLE_RECORD_NUMBER_LIMIT
       ----------- ---------- ------------ ------------ -------- ------- ------------ ------------- ------------ ---------- ------------------ -------------------------
       TOTAL_SPACE_LIMIT
       -----------------

1 3.00 dm66n367 D 3 X.X.x.x 2023-05-25 DEVELOP USER 1 NULL NULL NULL
DM8 Others Others NULL 1111 1900-01-01 NULL
NULL

数据库状态和模式:

dm数据库状态和oracle数据库状态类似
dm

oralcemount
不允许访问数据库对象，只能进行控制文件维护、归档配置、
数据库模式修改等操作；
mountopen
不能进行控制文件维护、归档配置等操作，可以访问数据库对
象，对外提供正常的数据库服务；
open
SUSPEND
与 OPEN 状态的唯一区别就是，限制磁盘写入功能；一旦
修改了数据页，触发 REDO 日志、数据页刷盘，当前用户将被挂起。
read only,(写入报错)
dm数据库的模式:

普通模式（NORMAL）：用户可以正常访问数据库，操作没有限制；
主库模式（PRIMARY）：用户可以正常访问数据库，所有对数据库对象的修改强制生成 REDO 日志，在归档有效时，发送 REDO 日志到备库；
备库模式（STANDBY）：接收主库发送过来的 REDO 日志并重做。数据对用户只读。
三种模式只能在 MOUNT 状态下设置，模式之间可以相互转换。

4.管理模式对象的空间.
存储参数设置:
(1)普通表和索引:
1. 初始簇数目 INITIAL：指建立表时分配的簇个数，必须为整数，最小值为 1，最大值为 256，缺省为 1；
2. 下次分配簇数目 NEXT：指当表空间不够时，从数据文件中分配的簇个数，必须为整数，最小值为 1，最大值为 256，缺省为 1；
3. 最小保留簇数目 MINEXTENTS：当删除表中的记录后，如果表使用的簇数目小于这个值，就不再释放表空间，必须为整数，最小值为 1，最大值为 256，缺省为 1；
4. 填充比例 FILLFACTOR：指定插入数据时数据页的充满程度，取值范围从 0 到100。默认值为 0，等价于 100，表示全满填充，未充满的空间可供页内的数据更
新时使用。插入数据时填充比例的值越低，可由新数据使用的空间就越多；更新数据时填充比例的值越大，更新导致出现的页分裂的几率越大；
5. 表空间名：在指定的表空间上建表或索引，表空间必须已存在，默认为用户缺省的表空间。
–查看表空间名称:
select tablespace_name from DBA_TABLESPACES ;

–创建表示例语句:
create table PERSON.test(

persionid int IDENTITY(1,1) CLUSTER PRIMARY key ,
sex CHAR(1) not null ,
name VARCHAR(50) not null ,
email varchar(50),
phone VARCHAR(25)

) STORAGE (
INITIAL 5 ,MINEXTENTS 5, NEXT 2 , ON MAIN ,FILLFACTOR 85
) ;

–堆表
对于堆表可以指定并发分支 BRANCH 和非并发分支 NOBRANCH 的数目，其范围是
（1=<BRANCH <= 64，1<=NOBRANCH<=64），堆表最多支持 128 个链表

CREATE TABLE LIST_TABLE(C1 INT) STORAGE(BRANCH (2,4));
–HUGE表
HUGE 表需要建立在混合表空间上。

收回多余的空间
DM8 中表和索引对象的所占用的簇要么是全满的状态要么是半满的状态，空闲的簇会被系统自动回收。

用户和表上的空间限制
用户占用的空间是其下所有用户表对象占用空间的总和。
限制用户使用的空间大小(50M):
CREATE USER TEST_USER IDENTIFIED BY TEST_PASSWORD DISKSPACE LIMIT 50;

对用户使用的空间限制的修改:
ALTER USER TEST_USER DISKSPACE UNLIMITED;

表对象的空间限制
表对象占用的空间是其上所有索引占用空间的总和。
CREATE TABLE TEST (SNO INT, MYINFO VARCHAR) DISKSPACE LIMIT 500;
对表对象空间的限制也可进行更改，如修改表 TEST 的磁盘空间限制为 50M。
ALTER TABLE TEST MODIFY DISKSPACE LIMIT 50;

查看模式对象的空间使用
(1)查看用户占用的空间
SELECT USER_USED_SPACE(‘TEST_USER’)* page/1024 as size_KB;

(2)查看表占用的空间
SELECT TABLE_USED_SPACE(‘SYSDBA’, ‘TEST’) * PAGE SIZE_KB;

(2)查看索引占用的空间
select * from dba_objects where object_name =‘INDEX33555439’

SELECT INDEX_USED_SPACE(33555463);

(3)查看索引使用的页数
SELECT INDEX_USED_PAGES(33555463);

5.管理表
表的创建 ,可以指定存储空间上限 ,可以指定表的存储位置,
在创建表时，通过指定合适的表空间，有以下优点：

提高数据库系统的性能，因为不同的数据库表可能对应不同的数据文件，可减少对相同文件的竞争；
减少数据库管理的时间，数据库表分布在不同的表空间中，即使一个表空间损坏，也不影响其他表空间上数据库表的正常访问。

创建表需要 create table 权限

–创建表空间
SELECT PAGE*4096/1024/1024 ;

create tablespace USERS DATAFILE ‘/opt/dm8/data/DAMENG/USERS01.DBF’ SIZE 32 AUTOEXTEND ON NEXT 2 MAXSIZE 2048 ;

–创建表
CREATE TABLE EMPLOYEE (
EMPNO INT PRIMARY KEY,
ENAME VARCHAR(15) NOT NULL,
JOB VARCHAR(10),
MGR INT CONSTRAINT EMP_FKEY REFERENCES EMPLOYEE(EMPNO),
HIREDATE DATE DEFAULT (CURDATE),
SALARY FLOAT,
DEPTNO TINYINT NOT NULL )
STORAGE ( INITIAL 50,
NEXT 50,
MINEXTENTS 10,
FILLFACTOR 80,
ON USERS);
在表EMPLOYEE 创建在USERS 表空间中.

– 查询建表
CREATE TABLE NEW_EMP
AS
SELECT * FROM EMPLOYEE;

指定表的聚集索引:
DM 提供三种方式供用户指定聚集索引键：

CLUSTER PRIMARY KEY：指定列为聚集索引键，并同时指定为主键，称为聚集主键；
CLUSTER KEY：指定列为聚集索引键，但是是非唯一的；
CLUSTER UNIQUE KEY：指定列为聚集索引键，并且是唯一的。
– 创建表
CREATE TABLE STUDENT(
STUNO INT CLUSTER PRIMARY KEY,
STUNAME VARCHAR(15) NOT NULL,
TEANO INT,
CLASSID INT
);

注意: 指定聚集索引键后，如果查询条件中含有聚集索引键，可以定位记录在 B 树上的位置，使查询性能大大提高。然而，插入记录也需要根据聚集索引键定位插入位置，有可能导致页
面的分裂而影响插入性能。
–创建临时表

在临时表中，会话可以像普通永久表一样更新、插入和删除数据；
临时表的 DML 操作产生较少的 REDO 日志；
临时表支持建索引，以提高查询性能；
在一个会话或事务结束后，数据将自动从临时表中删除；
不同用户可以访问相同的临时表，每个用户只能看到自己的数据；
临时表的数据量很少，意味着更高效的查询效率；
临时表的表结构在数据删除后仍然存在，便于以后的使用；
临时表的权限管理跟普通表一致。
临时表 ON COMMIT 关键词指定表中的数据是事务级还是会话级的，默认情况下是事务级的。
ON COMMIT DELETE ROWS：指定临时表是事务级的，每次事务提交或回滚之后，表中所有数据都被删除；
ON COMMIT PRESERVE ROWS：指定临时表是会话级的，会话结束时才清空表，并释放临时 B 树。

create GLOBAL TEMPORARY table tmp_emp(
EMPNO INT PRIMARY KEY,
ENAME VARCHAR(15) NOT NULL ,
JOB VARCHAR(10)
) ON COMMIT DELETE ROWS ; – 事务级

更改表
需要的权限: alter table ,alter any table
通过更改表，用户可以对数据库中的表作如下修改：

添加或删除列，或修改现有的列的定义（列名、数据类型、长度、默认值）。其中，
对于添加列，当设置 INI 参数 ALTER_TABLE_OPT 为 1 时，添加列采用查询插入
实现，可能会导致 ROWID 的改变；ALTER_TABLE_OPT 为 2 时，系统开启快速加
列功能，对于没有默认值或者默认值为 NULL 的新列，系统内部会标记为附加列，
能够达到瞬间加列的效果，此时记录 ROWID 不会改变，若有默认值且默认值不为
NULL，则默认值的存储长度不能超过 4000 字节，此时仍旧采取查询插入实现；
ALTER_TABLE_OPT 为 3 时，系统会开启快速加列功能，允许指定新增列的默认值，
系统会为该列设置附加列标记，查询表中已存在的数据时，会自动为记录设置追加
列默认值，此时记录 ROWID 不会改变；
添加、修改或删除与表相关的完整性约束；
重命名一个表；
启动或停用与表相关的完整性约束；
启动或停用与表相关的触发器；
修改表的 SPACE LIMIT；
增删自增列。

删除表
需求权限: drop any table

以下语句可删除 employee 表：
DROP TABLE employee;
删除不存在的表会报错。若指定 IF EXISTS 关键字，删除不存在的表，不会报错，如：
DROP TABLE IF EXISTS employee;
如果要删除的表被其他表引用，即其他表的外键引用了表的任何主键或唯一键，则需要
在 DROP TABLE 语句中包含 CASCADE 选项，如：
DROP TABLE employee CASCADE;

清空表

使用 DELETE 语句；
使用 DROP 和 CREATE 语句；
(1) create table…as select * from … where 1=2;
(2) drop table …;
(3) alter table… rename to … ;
使用 TRUNCATE 语句。
TRUNCATE 语句为我们提供了一种快速、有效地删除表所有行的方法。并且 TRUNCATE是一个 DDL 语句，不会产生任何回滚信息。

查看表定义
创建表后，可以通过 SP_TABLEDEF 系统过程查看表的定义。
CALL SP_TABLEDEF(‘SYSDBA’, ‘EMPLOYEE’); – 用户名,表名要大写.

查看自增列信息
DM 支持 INT 和 BIGINT 两种数据类型的自增列，并提供以下函数查看表上自增列的当前值、种子和增量等信息：

IDENT_CURRENT：获得表上自增列的当前值；
IDENT_SEED：获得表上自增列的种子信息；
IDENT_INCR：获得表上自增列的增量信息。

查看表的空间使用情况
DM 支持查看表的空间使用情况，包括：

TABLE_USED_SPACE：已分配给表的页面数；
TABLE_USED_PAGES：表已使用的页面数。

6.管理索引
索引是与表相关的可选的结构（聚集索引除外），它能使对应于表的 SQL 语句执行得更快，因为有索引比没有索引能更快地定位信息。
DM8 提供了几种最常见类型的索引，对不同场景有不同的功能，它们是：

聚集索引：每一个普通表有且只有一个聚集索引；
唯一索引：索引数据根据索引键唯一；
函数索引：包含函数/表达式的预先计算的值；
位图索引：对低基数的列创建位图索引；
位图连接索引：针对两个或者多个表连接的位图索引，主要用于数据仓库中；
全文索引：在表的文本列上而建的索引。

表中插入数据后创建索引.
使用下面的准则来决定何时创建索引：

如果需要经常地检索大表中的少量的行，就为查询键创建索引；
为了改善多个表的连接的性能，可为连接列创建索引；
主键和唯一键自动具有索引，在外键上很多情况下也创建索引；
小表不需要索引。
选取表中的索引列时可以考虑以下几点：
列中的值相对比较唯一；
取值范围大，适合建立索引；
CLOB 和 TEXT 只能建立全文索引、BLOB 不能建立任何索引。

限制每个表的索引数据,索引越多,修改表数据的开销就越大.
根据使用情况,设置适当的存储参数,可以改善索引的io性能.
为索引指定专门的表空间.

创建索引.
需求权限: create index , create any index 权限

– 创建索引
CREATE INDEX emp_ename ON emp(ename)
STORAGE (
INITIAL 50,
NEXT
50,
ON
USERS);

– 创建聚集索引
重建聚集索引
CREATE CLUSTER INDEX clu_emp_name ON emp(ename);

注意: 不在数据量的表上操作, 不要在生产繁忙时操作.

– 唯一索引
CREATE UNIQUE INDEX dept_unique_index ON dept (dname)
STORAGE (ON users);

– 添加主键约束
ALTER TABLE EMP ADD CONSTRAINT PK_EMP_NAME PRIMARY KEY (NAME);

– 函数索引
例如，考虑如下 WHERE 子句中的表达式
CREATE INDEX IDX ON EXAMPLE_TAB(COLUMN_A + COLUMN_B);
SELECT * FROM EXAMPLE_TAB WHERE COLUMN_A + COLUMN_B < 10;

– 位图索引
如下例子创建一个位图索引：
CREATE BITMAP INDEX S1 ON PURCHASING.VENDOR (VENDORID);

位图索引具有以下约束：

支持普通表、堆表和水平分区表创建位图索引；
不支持对大字段创建位图索引；
不支持对计算表达式列创建位图索引；
不支持在 UNIQUE 列和 PRIMARY KEY 上创建位图索引；
不支持对存在 CLUSTER KEY 的表创建位图索引；
仅支持单列或者不超过 63 个组合列上创建位图索引；
MPP 环境下不支持位图索引的创建；
不支持快速装载建含有位图索引的表；
不支持全局位图索引；
包含位图索引的表不支持并发的插入、删除和更新操作。

索引重建
当一个表经过大量的增删改操作后，表的数据在物理文件中可能存在大量碎片，从而影响访问速度.
DM8 提供的重建索引的系统函数为：
SP_REBUILD_INDEX(SCHEMA_NAME varchar(256), INDEX_ID int);
SCHEAM_NAME 为索引所在的模式名，INDEX_ID 为索引 ID。

索引删除
如下面的语句删除 emp_ename 索引。
DROP INDEX emp_ename;
删除不存在的索引会报错。若指定 IF EXISTS 关键字，删除不存在的索引，不会报错，如：
DROP INDEX IF EXISTS emp_ename;

查看索引信息
select * from dba_objects where object_name =‘INDEX33555439’

INDEXDEF(INDEX_ID int, PREFLAG int);

7.触发器
触发器是一种特殊的存储过程，它在创建后就存储在数据库中。触发器的特殊性在于它是建立在某个具体的表或视图之上的，或者是建立在各种事件前后的，而且是自动激发执行
的，如果用户在这个表上执行了某个 DML 操作（INSERT、DELETE、UPDATE），触发器就被激发执行。
触发器常用于自动完成一些数据库的维护工作。例如，触发器可以具有以下功能：

可以对表自动进行复杂的安全性、完整性检查；
可以在对表进行 DML 操作之前或者之后进行其它处理；
进行审计，可以对表上的操作进行跟踪；
实现不同节点间数据库的同步更新。

DM 提供了三种类型的触发器：

表级触发器：基于表中的数据进行触发；
事件触发器：基于特定系统事件进行触发；
时间触发器：基于时间而进行触发

在创建触发器时应该仔细考虑它的相关信息。具体来说，应该考虑以下几个方面的问题：

触发器应该建立在哪个表/视图之上；
触发器应该对什么样的 DML 操作进行响应；
触发器在指定的 DML 操作之前激发还是在之后激发；
对每次 DML 响应一次，还是对受 DML 操作影响的每一行数据都响应一次。

创建触发器的语法格式为：
CREATE [OR REPLACE] TRIGGER 触发器名[WITH ENCRYPTION]
BEFORE|AFTER|INSTEAD OF
DELETE|INSERT|UPDATE [OF 列名]
ON 表名
[FOR EACH ROW [WHEN 条件]]
BEGIN
DMSQL 程序语句
END;

需要权限: create trigger ,create any tigger

– 表级别的dml操作
例如，下面创建的触发器是为了监视用户对表 emp 中的数据所进行的删除操作。如果有
这样的访问，则打印相应的信息。
CREATE OR REPLACE TRIGGER DEL_TRG
BEFORE DELETE
ON emp
BEGIN
PRINT ‘您正在对表 emp 进行删除操作’;
END;

– 查看触发器信息
select * from SYS.DBA_TRIGGERS ;

– 删除触发器
DROP TRIGGER [IF EXISTS] 触发器名;

– disable 触发器
ALTER TRIGGER 触发器名 DISABLE;

8.视图,序列,同义词

视图

– 创建
create [or replace ] view as select …
例如:
CREATE VIEW normal_view AS SELECT name FROM person;
CREATE VIEW special_view AS SELECT name, sex, email, phone FROM person;

– 查看
select * from DBA_VIEWS ;
SELECT b.* FROM SYS.SYSOBJECTS a, SYS.SYSTEXTS b WHERE a.ID = b.ID and a.NAME
LIKE ‘<name>’;
– 查看视图定义
CALL SP_VIEWDEF(‘SYSDBA’, ‘VIEW1’);

– 视图编译
ALTER VIEW [<模式名>.]<视图名> COMPILE;

–
– 删除
DROP VIEW [IF EXISTS] [<模式名>.]<视图名> [RESTRICT | CASCADE];

序列
一旦序列生成，用户就可以在 SQL 语句中用以下伪列来存取序列的值：

CURRVAL 返回当前的序列值；
NEXTVAL 如果为升序序列，序列值增加并返回增加后的值；如果为降序序列，序列值减少并返回减少后的值。
– 创建
CREATE SEQUENCE [ <模式名>.] <序列名> [ <序列选项列表>];
<序列选项列表> ::= <序列选项>{<序列选项>}
<序列选项> ::=
INCREMENT BY <增量值>|
START WITH <初值>|
MAXVALUE <最大值>|
NOMAXVALUE|
MINVALUE <最小值>|
NOMINVALUE|
CYCLE|
NOCYCLE|
CACHE <缓存值>|
NOCACHE|
ORDER |
NOORDER |
GLOBAL |
LOCAL

– 查看
select * from SYS.DBA_SEQUENCES ;
SELECT b.* FROM SYS.SYSOBJECTS a, SYS.SYSTEXTS b WHERE a.ID = b.ID and a.NAME
LIKE ‘<name>’;
– 修改
ALTER SEQUENCE [ <模式名>.] <序列名> [ <序列修改选项列表>];
<序列选项列表> ::= <序列修改选项>{<序列修改选项>}
<序列修改选项> ::=
INCREMENT BY <增量值>|
MAXVALUE <最大值>|
NOMAXVALUE|
MINVALUE <最小值>|
NOMINVALUE|
CYCLE|
NOCYCLE|
CACHE <缓存值>|
NOCACHE|
ORDER|
NOORDER |
CURRENT VALUE <当前值>

– 删除
DROP SEQUENCE [IF EXISTS] [ <模式名>.]<序列名>;

同义词
– 创建
CREATE [OR REPLACE] [PUBLIC] SYNONYM [<模式名>.]<同义词名> FOR [<模式名>.]<对象名>
– 查看
SELECT b.* FROM SYS.SYSOBJECTS a, SYS.SYSTEXTS b WHERE a.ID = b.ID and a.NAME
LIKE ‘<name>’;
– 删除
DROP [PUBLIC] SYNONYM [IF EXISTS] <同义词名>

– 完整性约束
– 定义完整性约束
下面的 CREATE TABLE 和 ALTER TABLE 语句在定义的时候就启用完整性约束：
CREATE TABLE t_con ( id NUMBER(5) CONSTRAINT t_con_pk PRIMARY KEY); – 创建表时创建约束
ALTER TABLE t_con ADD CONSTRAINT t_con_pk PRIMARY KEY (id); – 单独创建约束

– 修改和删除完整性约束
– 禁用已备启用的约束
alter table t_con disable constraint t_con_pk ;

– 删除约束
ALTER TABLE t_con DROP CONSTRAINT t_con_pk;

– 查看约束信息
SELECT * FROM SYSOBJECTS WHERE NAME=‘T_CON_PK’;

– 查找所有约束的信息：
SELECT * FROM SYSOBJECTS WHERE SUBTYPE$=‘CONS’;

DM 基础数据库管理

作者