达梦数据库基础知识

安装前的准备及注意事项

  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 
  1. 设置图形界面
先检查是否能打开图形化界面:输入 `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:disqldminit等命令行工具
  • /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

  • 执行 ./configuremakemake 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