达梦数据库基础知识

安装前的准备及注意事项

1. 检查系统版本、内核、内存大小、磁盘空间等,如检查glibc要求2.3以上 rpm -qa | grep glibc
2. 建议不要使用root身份进行数据库的安装
3. 麒麟6系统会有文件打开数的限制，需要修改安装用户下的文件打开数大小。麒麟7省去。

    cd /etc/security/
    vi limits.conf
    
    xxx soft nofile 65536
    xxx hard nofile 65536
    
    // 保存后，查看是否成功
    ulimit -a 

4. 设置图形界面

先检查是否能打开图形化界面:输入 `xclock`；在root下输入 `xhost +` 然后切换到操作用户
    报xclock 没有这个命令，执行`yum install xorg-x11-apps`

图形化安装

进入文件安装目录，执行./DMInstall.bin，开始一步一步。过程中会提示执行root_install.sh，启动AP服务。

图形化实例化数据库

• /tool（没有配置环境变量） 目录下执行./dbca.sh，打开数据库配置助手界面，过程中会要以root身份执行/script/root/dm_service_installer.sh 进行数据库的注册（/bin/DMServiceDMSERVER 到 /etc/rc.d/init.d/DMServiceDMSERVER）。填写数据库名、端口、用户名、密码、文件目录等信息。注：簇大小、页大小设置后不能修改。不设置环境变量，就需要进入/tool 目录执行。以下命令都是配置环境变量的写法。

命令行安装

• 如果已经存在数据库，可以在安装的根目录执行 ./uninstall.sh 进行卸载。卸载后会保留之前创建的数据库实例。
• 进入安装文件所在目录 ./DMInstall.bin -i 按照流程一步一步选择。
• 安装完成后，如果想用之前创建的数据库服务，可以在cd /script/root 执行./dm_service_installer.sh -t dmserver -p DMSERVER -i /xx/data/DAMENG/dm.ini -p: 之前创建的数据库服务名； -i : 之前服务的配置文件路径。 可执行dm_service_installer.sh -h 查看命令说明

命令行方式实例化数据库

• 查看用法 dminit.sh help
• 安装 dminit path=/dm7/data db_name=DMMENG01 instance_name=DMSERVER01 port=5237
• 注册服务 cd /script/root 执行./dm_service_installer.sh -t dmserver -p DMSERVER01 -i /dm7/data/DAMENG01/dm.ini
• 启动服务 可以在manager界面手动启动，也可以在 /etc/rc.d/init.d/ service DmServiceDMSERVER01 start

启动服务方法

• DM服务器查看器 (cd tool ./dmservice.sh下打开的界面)，手动操作
• Linux服务方式 /etc/rc.d/init.d/ service DmServiceDMSERVER01 start
• 命令方式 /bin ./dmserver path=/xx/xx/DAMENG/dm.ini 关闭窗口服务即关闭

目录说明

• /bin：disql、dminit等命令行工具
• /tool：客户端工具
  打开DM服务查看器:./dmservice.sh
  打开DM控制台：./console
  建库：./dbca.sh
  数据迁移：dts
  打开管理端：./manager
• /script/root:
  root_install.sh: 启动AP服务，一般在安装时执行一次。
  dm_service_install.sh 注册数据库服务
  dm_service_uninstaller.sh 卸载数据库服务

连接数据库：

• /bin disql 默认5236端口
• 连接其它端口 disql sysdba(用户名)/(密码)@localhost:5237
• 可以配置连接串 /etc/dm_svc.conf DMSERVER01=(127.0.0.1:5237) disql sysdba(用户名)/(密码)@DMSERVER01
• 客户端工具 cd tool manager

设置环境变量

用户所在目录下的 .bash_profile 文件 export PATH="$PATH:$DM_HOME/bin:$DM_HOME/tool"
保存后要 source .bash_profile 才能生效

sql 简单数据字典查询命令

• 查询服务配置 select * from V$DM_INI
• 查询服务配置参数类型 select DISTINCT PARA_TYPE FROM V$DM_INI
• 查询表空间 select * from V$TABLESPACE
• 查询数据文件 select * from DBA_DATA_FILES
• 查询块信息 select * from USER_SEGMENTS
• 查询用户 select * from DBA_USERS
• 设置服务参数 sp_set_paravalue(scope int, paraname varchar(256), value int64)

DM线程

• 先查进程PID ps -ef | grep dmserver
• 方法1：根据进程PID查询线程 ps -T -p PID
• 方法2：select * from V$THREADS

表空间管理

• 数据字典查询
  • select * from dba_tablespaces;
  • select * from v$tablespace;
  • select * from DBA_DATA_FILES;
  • select * from "V$DATAFILE";
• 表空间管理
  联机状态下不能对表空间数据文件重命名。数据文件不能drop、offline。表空间可以offline。表空间文件大小只能改大，不能改下。
  • MD管理工具图形化界面，表空间中创建、修改文件名、表空间名。修改文件名不需要进行表空间脱机操作，应该是工具会自动进行。
  • 命令行方式
    • 创建 create tablespace tbs02 datafile '/xx/data/DAMENG/tbs02.dbf' size 200;
    • 修改表空间数据名 alter tablespace tbs02 rename datafile'/xx/data/DAMENG/tbs02.dbf' to '/xx/data/DAMENG/tbs02.ttt'; 如果此时表空间在联机状态，会提示错误信息。需要将表空间 alter tablespace tbs02 offine，然后进行修改。修改成功后 alter tablespace online
    • 修改表空间文件大小，只能改大，不能改小。alter tablespace tbs02 resize datafile'/xx/data/DAMENG/tbs02.dbf' to 400;
    • 修改自动扩展 alter tablespace tbs02 datafile '/xx/data/DAMENG/tbs02.dbf' autoextend off;
    • 修改最大文件大小 alter tablespace tbs02 datafile '/xx/data/DAMENG/tbs02.dbf' autoextend on NEXT 4 MAXSIZE 500

重做日志

重做日志可以重命名，不能删除。修改文件名时需要在 alter database mount 状态下。修改成功后 alter database open
重做日志默认有两个，在/data/DAMENG(数据库实例名)/ DAMENG01.LOG 和DAMENG02.log

• 数据字典查询
  • select * from "V$RLOG"
  • select * from "V$RLOGFILE"
• 图形化界面操作
  • DM管理工具中的管理服务器 - > 管理服务器 -> 日志文件
• 命令行方式
  • 添加重做日志 alter database add logfile '/xx/data/DAMENG/DAMENG03.log' size 500;
  • 修改大小 alter database RESIZE logfile '/xx/data/DAMENG/DAMENG01.log' to 500;
  • 修改文件名：alert database RENAME logfile '/xx/data/DAMENG/DAMENG03.log' to '/xx/data/DAMENG/DAMENG04.log'

临时表空间（TEMP.dbf）

临时表空间可增加数据文件，但不能修改 autoextend 属性

• 查询临时表空间信息 select para_name,para_value,para_type from v$dm_init where para_name like '%TEMP%'
• 添加临时表空间文件 alter tablespace temp add datafile '/xx/data/DAMENG/TEMP01.dbf' size 200;
• 修改文件大小 alter tablespace temp resize datafile '/xx/data/DAMENG/TEMP01.dbf' to 300;

UNDO（ROLL.dbf）表空间

存放DML（insert、update、delete）的回滚操作。可以和普通表空间一样进行操作。
UNDO_RETENTION 90 保留时间90秒

• 查询临时表空间信息 select para_name,para_value,para_type from v$dm_init where para_name like '%UNDO%'
• 添加文件 alter tablespace ROLL add datafile '/xx/data/DAMEMG/ROLL02.DBF'
• 修改文件大小 alter tablespace ROLL resize datafile '/xx/data/DAMEMG/ROLL02.DBF to 250'
• 修改autoextend alter tablespace ROLL datafile '/xx/data/DAMEMG/ROLL02.DBF autoextend off'
• 修改文件最大大小 alter tablespace ROLL datafile '/xx/data/DAMEMG/ROLL02.DBF autoexteng on NEXT 4 MAXSIZE 500'

系统表空间

存放系统相关表定义，表越多、索引越多占用系统表空间越大。
select * from SYS.SYSCOLUMNS

用户管理

• DM管理工具
  • 用户 -> 用户管理
• 命令行方式
  • 创建索引表空间 create tablespace "tbs_index" datafile '/xx/data.DAMENG/tbs_index.dbf'
  • 创建用户指定表空间和索引空间 create user test IDENTIFIED by dameng123 default tablespace "tbs" default index tablespace "tbs_index"
  • 增加登录错误次数超过3次会被锁定 alter user test limit FAILED_LOGIN_ATTEMPS 3, PASSWORD_LOCK_TIME 3
  • 解锁用户 alter user test account unlock
• 给用户授权（角色）
  • grant "PUBLIC" to "TEXT" 也可以在DM管理工具操作：用户-》修改-》所属角色
  • revoke "PUBLIC" from TEST 回收权限

权限管理

• 通过数据字典查看对象、系统权限
  • 查看当前用户表（对象）权限 select * from USER_TAB_PRIVS
  • 查看当前用户系统权限 select * from USER_SYS_PRIVS
  • select * from dba_tab_privs
  • select * from dba_sys_privs
• 系统权限和对象权限
  • 系统权限：比如 创建表
  • 对象权限：查询某个表
• 赋予系统权限
  • 赋予创建表的权限 grant create table to test
• 赋予对象权限、回收权限给某用户。
  针对 select, update, insert 字段赋权的情况，不能按列回收权限，需要 revoke 回收整个表的权限。
  • 赋予某表权限给用户 grant update,delete,insert on dmhr.employee to test
  • 回收权限 'revoke update,delete,insert on dmhr.employee from test'
  • 赋予查询某表个别字段的权限 grant select(employee_id,employee) on dmhr.employee to test
• 执行insert,update,delete 需要commit 或者 rollback

角色管理

• 相关数据字典
  • select * from USER_ROLE_PRIVS
  • select * from DBA_ROLE_PRIVS
  • select * from DBA_ROLES
• 命令行方式创建 create ROLE r_test
• 赋对象权限给某个角色 grant update,delete,insert,select on dmgr.employee to r_test

模式对象管理

一般情况下，创建用户会默认创建一个和用户同名的模式。
一个用可以有多个模式，一个模式属于一个用户。一个模式下有多个对象（表、索引等）。

• 管理模式

  • 命令行操作 create schema s_test AUTHORIZATION test

• 管理表

  • 表空间关系
    • 假如用户没有指定默认表空间、创建表也没有指定默认表空间，表默认存储 MAIN 表空间
    • 假如用户指定默认表空间、创建表没有指定默认表空间，使用的是用户指定的默认表空间
    • 创建表指定默认表空间，使用的是用户指定的表空间
  • 数据字典查询 select * from USER_TABLES
  • 命令行创建
    • 默认当前用户的表空间 create table test(id int,name varchar(100))
    • 指定表空间 create table test(id int,name varchar(100)) storage(on main)
    • 指定创建某个模式的表 create table s_test.test(id int,name varchar(100)) table_s 模式名。SYSDBA才有权限，用户要有这个模式的权限。
    • 自增列 create table test(id int identity,name varchar(100))
    • 带主键约束create table test (id int inentity primary key,name varchar(100))
    • 使用某个模式的表创建一个结构一样，并复制数据的表 create table test as select * from dmhr.employee

• 添加约束

  • 查看约束相关字典
    • select * from USER_CONSTRAINT
    • select * from USER_CONS_COLUMNS
  • 主键约束
    • 给表添加主键约束 alter table test ADD CONSTRAINT PK_id primary key(id) PK_id是主键名
    • 直接创建带主键的表 create table test (id int inentity primary key,name varchar(100))
  • 外键约束
    • 给表添加外键约束 如员工表有dept_id 字段 alert table emp ADD CONSTRAINT fk_emp_deptid FOREIGN key (dept_id) REFERENCES dept(dept_ID)
    • 直接创建带外键的表 create table test (id int,name varchar(100),dept_id int FOREGIN key PEFERENCES dept(DMHR.dept_ID))
  • 唯一约束
    • 主键是唯一约束
    • create table test(dept_id int UNIOUE)
  • check 约束
    • 如年龄大于6岁 alter table test add constraint "check_age" check(AGE >=6) check_age 约束名字

切换模式

• 不是当前用户模式，不允许切换
• 命令 disql 下 set SEHEMA s_test

管理视图

视图不占用存储空间，简单视图可以做增删改操作；复杂视图比如带group by、集函数不支持DML操作

• 数据字典 select * from user_view
• 命令行创建 create view v_test AS select * from emp

管理索引

索引是双刃箭，降低DML操作速度为代价，提高查询速度

• 查询数据字典

  • select * from USER_INDEXES
  • select * from USER_IND_COLUMNS

• 命令方式创建
  不指定模式，默认当前模式

  • create index ix_emp_empNAME on emp(emp_NAME);

• 索引重建(现网推荐 online):
  alter index IX_EMP_EMPNAME rebuild
alter index IX_EMP_EMPNAME rebuild online

• 收集统计信息
  不会自动收集统计信息，需要手动收集

• 查看执行计划 disql 下explain select * from test.t_test where name = '11' 查看这条语句的执行计划。name 已经建了索引。课上执行计划没有走索引进行查询，但是亲测时是走了。课上需要进行统计信息的收集。

• 查看统计信息 disql call dbms_stats.table_stats_show(''TEST,'T_TEST') test用户的t_test表

• 执行统计信息 call dbms_stats.gather_table_status('TEST','T_TEST') 然后执行查看计划，发现走索引了。

数据字典和动态性能视图

• 数据字典
  • DBA_*:查询数据库中所有的对象，比如 DBA_TABLES， DBA_VIEWS, DBA_OBJECTS, DBA_ROLES，DBA_ROLE_PRIVS，DBA_SOURCE 源码等。
  • ALL_*:查询当前用户所访问权限范围内对象
  • USER_*:查询当前用户下的对象。

动态性能视图

• v$ 开头的是动态视图。
  如：v$datafile、v$tablespace、v$session、v$men_pool 、v$lock等等。
• 注意:当 A 删除操作，未提交，另外一个会话 B 执行删除 该记录时会产生行锁。可查看 v$lock;

数据库备份还原

• 基础知识

  • 物理备份、逻辑备份 (联机 dimp 和 dexp)

  • 联机备份、脱机备份。联机(数据库开启状态)，脱机(数据库关闭状态)

  • 备份分类：全库备份、表空间备份、表备份(只能联机下备份)、归档日志备份。

  • 物理备份包含联机备份、脱机备份

  • DM控制台工具进行脱机备份，DM服务器管理工具进行联机备份

  • 需要开启APService服务，有时AP服务会停，这时需要启动。会出现启动失败的问题，查看日志后，如果报管道文件已经存在，就到/dmdbms/bin下删除文件 rm -R DM_PIPE*

  • 备份文件包含元数据和备份文件:

  • 联机备份需要开启归档

    • 命令行方式（disql）
      • 开归档需要让数据库在mount alter database mount
      • 开启归档 alter database archivelog
      • 设置归档 alter database add archivelog 'dest=/xx/dmdbms/arch,type=local,file_size=100,space_limit=1024'
      • 打开状态 alter database open
    • 客户端工具方式 DM管理工具-》管理服务器-》系统管理进行状态转换,归档配置进行设置。
    • 查看是否开启归档（数据字典方式） select arch_mode from v$database

  • 联机备份与还原

    • 命令行方式（disql）
      • 库备份backup database full backupset 'full' 第一个full是全库备份，第二个full是备份的文件夹。不指定目录，默认在当前示例（/data/DAMENG/bak 目录）的目录。
      • 表空间备份backup tablespace "tbs_02" backupset 'tbsfull' 表空间要加双引号，不然报无效的表空间错误，如果指定目录，需要写绝对目录路径
      • 还原

      Alter tablespace "tbs_02" offline;
      restore tablespace "tbs_02" datafile 1 from backupset '/xx/data/DAMENG/bak/tbsfull';
      Alter tablespace "tbs_02" online;
      

  • 注意:数据库表空间恢复会自动恢复到最新状态，假如在备时中 A 表存在，后来 删除 A 表，这时数据库表空间恢复，会自动根据归档日志恢复到最新状态，所以没有A表。

  • DM管理工具，备份中进行操作

  • 脱机备份与还原恢复
    脱机备份，是使用工具 dmrman，在脱机状态下备份，APService 服务需要开 启。脱机备份只能进行全库备份和归档备份。不指定目录，默认创建在bak下。

    • dmrman 回车开始执行命令。最好进安装目录下的/bin 执行。
    • 命令方式备份 backup database '/xx/data/DAMENG/dm.ini' backupset '/xx/backup/dmrman'
    • 命令方式还原 restore database 'xx/data/DAMENG/dm.ini' from backupset '/xx/backup/dmrman'
    • 命令方式恢复 recover database 'xx/data/DAMENG/dm.ini'
    • 窗口工具在DM控制工具中进行操作

• 逻辑备份
  dexp ()和 dimp 在终端直接执行
  支持全库导出、按用户名导出、模式名导出、表导出

  • 导表 dexp userid=TEXT/xxxxx tables='t_test' 没有指定目录，默认创建在当前终端pwd下的目录
  • 按模式导出:dexp userid=test/xxxx schemas=s_test file=/xx/data/s_test.dmp log=/xx/data/s_test.log
  • 不指定参数默认导出登录用户同名的模式 schema
  • 导出用户下所有 schema:
    dexp userid=test/xxxx owner=test file=/xx/data/testuser.dmp log=/xx/data/testuser.log
  • 导出系统所有:
    dexp userid=sysdba/xxx full=y file=/xx/data/full.dmp log=/xx/data/full.log
  • 把数据从 test 模式导入 test01 用户
    dimp userid=sysdba/xxx file=/xx/data/test.dmp log=/xx/data/test01.log remap_schema=test:test01

作业管理

有与 oracle 兼容的 dbms_job， dbms_scheduler 系统包，可以使用这两个系统包创建作 业(默认没有创建此两个系统包，需手工创建)。达梦的这两个包作业支持调用 DM SQL、 存储过程，不支持调用外部命令。

• disql 开启 dbms_job SP_INIT_JOB_SYS(1)创建。0 关闭
  SP_INIT_DBMS_SCHEDULER_SYS(1);
  不开启执行 desc dbms_job;会报无效的对象名
• DM管理服务器，代理中进行操作。

SQL

• DCL（权限控制）：grant、revole 权限控制。
• DDL（数据控制）:create、truncate、drop、alter ，可自动提交。
• DML（数据操作）:数据操做语言，insert、update、delete
  rollback, commit;
• 集函数
  • sum() count() avg()
  • 环比:同比:LISTAGG/LISTAGG2
  • 排行榜 dense_rank(), rank(), row_number()
• 分析函数
  partition by (主要是分组)

select emp.EMPLOYEE_NAME, b.DEPARTMENT_NAME, emp.SALARY, DENSE_RANK() over(order by emp.SALARY desc) rankid1,
DENSE_RANK() over(partition by emp.department_id order by emp.SALARY desc ) rankid_dept
from dmhr.employee emp, DMHR.DEPARTMENT b where EMP.DEPARTMENT_ID = b.DEPARTMENT_ID
order by emp.SALARY desc;

ODBC Linux 配置

• 用root用户进行操作

• 解压 tar -xvf unix.tar.gz

• 检查电脑是否安装gcc rpm -qa | grep gcc

  • 麒麟6默认没有安装 yum -y install gcc
  • 执行报 couldn't resolve host 'ftp.cs2c.com.cn' 错误，cd /etc/yum.repos.d 修改 yum.repos.d 将所有baseurl 换成 http://download.cs2c.com.cn/neokylin/server/releases/6.0/x86_64/trusted/

• 执行 ./configure 、make、 make install

• 配置 odbc.ini 和 odbcinst.ini 一般文件在/usr/local/etc 下。也可以odbcinst -j 进行查看路径

• obdc.ini

  [dm7]
  Description = DM ODBC
  DRIVER = DM7 ODBC DRIVER
  SERVER = localhost
  UID = sysdba
  PWD = xxx
  TCP_PORT = 5236  

• odbcinst.ini

  [DM7 ODBC DRIVER]
  Description = ODBC Driver For DM7
  DRIVER = /home/xx/dmdbms/bin/libdodbc.so

• 配置完成后，切换到su - xxx 安装用户，通过odbc dm7 进行连接 isql dm7

其它

• Delete 没有降低水位线，truncate 降低了水位线。
• disql 下调用sql文件 start xx.sql 如导入之前导出的sql数据文件。执行完后需要commit