DAMENG培训总结

达梦数据库是一款全自主的国产数据库，近年来在信息创新项目中起着至关重要的作用，作为信息项目从业人员，日常接触达梦数据库也较多，有幸参加了DCA考试，借此机会总结并分享给大家。

1.创建用户组和用户、安装数据库

[root@localhost opt]# groupadd dinstall
[root@localhost opt]# useradd -g dinstall dmdba

[root@localhost 桌面]# xhost + 
[root@localhost 桌面]# echo $DISPLAY :0 

[dmdba@localhost dm]$ echo $DISPLAY 
[dmdba@localhost dm]$ export DISPLAY=:0 
[dmdba@localhost dm]$ ./DMInstall.bin

至此，根据图形化界面和题目需求进行安装。

2.根据笔记和相关博客总结相关语句

--查询参数类型
select distinct	para_type from v$dm_ini;
--句柄相关的查询
select * FROM SYS."V$SESSIONS" WHERe n_used_stmt>=1 ;
select * FROM SYS."V$SESSIONS" ORDER by "V$SESSIONS".N_USED_STMT DESC;
select * FROM v$parameter WHERe name like 'MAX_SESSION_STATEMENT';

--查询实例状态
select status$ from v$instance;
--内连接 where子句
select a.department_id,b.department_name,count(*) as empnum
 from dmhr.employee a, dmhr.department b
 where a.department_id = b.department_id
 group by a.department_id, b.department_name
 having count (*) >=30
 order by empnum desc;
--内连接 join..on..子句
select a.department_id,b.department_name,count(*) as empnum
 from dmhr.employee a join dmhr.department b
 on a.department_id = b.department_id
 group by a.department_id, b.department_name
 having count (*) >=30
 order by empnum desc;
--左外连接
select a.department_id, b.department_name
, count(a.employee_id) as empnum
 from dmhr.employee a, dmhr.department b 
 where a.department_id = b.department_id(+)
 group by a.department_id, b.department_name
 having count(*) >=30
 order by empnum desc;
 
select a.department_id, b.department_name
,COUNT(a.employee_id) as empnum
 from dmhr.employee a left join dmhr.department b
 on a.department_id = b.department_id 
 GROUP BY a.department_id, b.department_name
 having count(*) >=30
 order by empnum desc;
 
--右外关联
insert into dmhr.DEPARTMENT(DEPARTMENT.DEPARTMENT_ID,
DEPARTMENT.DEPARTMENT_NAME)
values(888888,'Test');
commit;
select a.DEPARTMENT_ID, b.DEPARTMENT_id, b.DEPARTMENT_NAME,
count(a.employee_id) as empnum
 from dmhr.employee a, dmhr.DEPARTMENT b
where a.DEPARTMENT_ID(+) = b.DEPARTMENT_ID
 group by a.DEPARTMENT_ID, b.DEPARTMENT_id, b.DEPARTMENT_NAME;

--查询dm.ini配置文件中中参数的类型 
--READ ONLY:只读参数 仅能通过dm.ini文本文件修改该参数(重启数据库生效)
--SYS：动态(系统级)参数。可在运行数据库时修改
--SESSION：动态(会话级)参数。可在运行数据库时修改，且可只针对当前会话生效
--IN FILE：静态参数，可以在数据库运行时修改，重启数据库生效
select DISTINCT para_type from v$dm_ini;
--系统函数用于设置参数
select name FROM SYS."V$IFUN" t WHERe name LIKE 'SP_SET_%PARA%';
--修改静态参数(需重启数据库)
SP_SET_PARA_VALUE(2,'COMPATIBLE_MODE',0);
select PARA_NAME,PARA_VALUE,PARA_TYPE,T.FILE_VALUE from v$dm_ini t where t.para_name like 'COMPATIBLE_MODE';
select name,type,value,sys_value,file_value from v$parameter t where name LIKE 'COMPATIBLE_MODE';
alter system set 'COMPATIBLE_MODE'=2 spfile;
--修改系统及参数
alter system set 'LIST_TABLE'=1 memory;
--修改会话级参数
alter session set 'LIST_TABLE'=0;
select name,type,value,sys_value,file_value from v$parameter t where name LIKE 'LIST_TABLE';
--查找系统函数
select * FROM v$ifun t WHERe name LIKE 'SP_SET_PARA%';
sp_set_para_value(2,'COMPATIBLE_MODE',0);
sp_set_para_value(1,'LIST_TABLE',0);

--DM存储结构:
--数据缓冲区: BUFFER
select name,type,value,sys_value,file_value from v$parameter t where name in ('BUFFER','KEEP','FAST_POOL_PAGES','RECYCLE');
alter SYSTEM SET 'BUFFER'=1500 SPFILE;
--重做日志缓冲区 :对应INI参数RLOG_BUF_SIZE
select name,type,value,sys_value,file_value from v$parameter t where name like 'RLOG_BUF_SIZE';
--SQL缓冲区 :INI参数 CACHE_POOL_SIZE，相关数据字典
select * from v$cacheitem;
select * from v$cachesql;
select * from v$cachepln;
select * from v$cachers;
--字典缓冲区:DICT_BUF_SIZE
select * FROM v$dynamic_tables t where name like '%DICT%';
select * from v$dict_cache_item;
select * from v$dict_cache;
--共享内存池:MEMORY_EXTENT_SIZE
select name,type,value,sys_value,file_value from v$parameter t where name like 'MEM%';
select * from V$mem_pool;
--数据库层面进程和线程查询
select * from v$process;
select * from v$threads;
--不能以结束线程的方式结束会话，可能导致dm数据库挂掉


--第七部分：表空间管理
--临时表空间管理
select * FROM v$parameter where name like 'TEMP%';
--修改临时表空间初始大小：
alter system set 'TEMP_SIZE'=100 spfile;
--收缩TEMP表空间的方法
--1.重启数据库
--2.在线收缩临时表空间：SP_TRUNC_TS_FILE
select name,id from v$ifun t where name like 'SP_TRUNC%';
select * from v$ifun_arg where id=1298;
--相关数据字典
select * from dba_tablespaces;
select * FROM DBA_DATA_FILES;
select * FROM v$tablespace;
select * from v$datafile;
select * from DBA_FREE_SPACE; --数据文件剩余空间

--创建表空间等不能指定单位，否则报错
select 4096*page/1024/1024; --表空间最小大小为页大小的4096倍
create tablespace tbs DATAFILE 'TBS01.DBF' SIZE 32;
alter tablespace tbs add DATAFILE 'TBS02.DBF' SIZE 128 AUTOEXTEND on NEXT 2 MAXSIZE 20480;
alter tablespace tbs DATAFILE 'TBS01.DBF' AUTOEXTEND on NEXT 2 MAXSIZE 20480;
alter TABLESPACE tbs DATAFILE 'TBS01.DBF' AUTOEXTEND OFF;

--初始表空间大小的更改
alter tablespace tbs RESIZE DATAFILE 'TBS01.DBF' to 128;

--迁移表空间数据文件，修改数据文件路径，要求dmdba用户要对该目录有操作权限
alter TABLESPACE tbs OFFLINE;
alter tablespace tbs RENAME DATAFILE 'TBS01.DBF' TO 'F:dmdbmsdataDAMENGTBSTBS01.DBF';
alter tablespace tbs RENAME DATAFILE 'TBS02.DBF' TO 'F:dmdbmsdataDAMENGTBSTBS02.DBF';
alter TABLESPACE tbs online;

create table t_test(id int,name VARCHAr(20)) TABLESPACE tbs;
insert into T_TEST(id,name) VALUES (1,'aaa');
insert into T_TEST(id,name) VALUES (2,'bbb');
insert into T_TEST(id,name) VALUES (3,'ccc');
insert into T_TEST(id,name) VALUES (4,'ddd');
commit;
--表空间处于脱机状态下时，表空间的数据无法读写，即无法查询 
select * from t_test;

create tablespace "DMTEST" datafile 'F:dmdbmsdataDAMENGTBSTESTTBSTEST01'
 size 64 autoextend on next 2 maxsize 10240, 'F:dmdbmsdataDAMENGTBSTESTDMTEST02'
 size 64 autoextend on next 2 maxsize 10240 CACHE = NORMAL;

--调整undo_retention
select * from v$parameter t where name like 'UNDO_RET%';
alter SYSTEM set 'UNDO_RETENTION'=300 both;

--重做联机日志 
select * from v$rlogfile;
select * from v$rlog; --cur_file表示正在使用的联机日志
--修改联机日志大小,联机日志只能由系统自动切换，不能手动切换
alter DATABASE RESIZE LOGFILE 'F:dmdbmsdataDAMENGDAMENG01.log' TO 300;
alter DATABASE RESIZE LOGFILE 'F:dmdbmsdataDAMENGDAMENG02.log' TO 300;
alter database ADD LOGFILE 'F:dmdbmsdataDAMENGDAMENG03.log' SIZE 300;
--修改日志文件路径(日志迁移)
alter DATABASE MOUNT;
alter DATABASE RENAME LOGFILE 'DAMENG01.log' TO 'F:dmdbmsdataDAMENGREDODAMENG01.log';
alter DATABASE RENAME LOGFILE 'DAMENG02.log' TO 'F:dmdbmsdataDAMENGREDODAMENG02.log';
alter DATABASE RENAME LOGFILE 'DAMENG03.log' TO 'F:dmdbmsdataDAMENGREDODAMENG03.log';
alter DATABASE OPEN;

--开启归档
select arch_mode from v$database;--查询是否开启了数据库归档
--开归档
alter database mount;
alter DATABASE ARCHIVELOG;
--配置归档日志属性,归档大小不要设置太大
--alter database add archivelog 'type=local,dest=/dm8/arch,file_size=64,space_limit=10240';
alter database add archivelog 'type=local,dest=F:dmdbmsarch,file_size=64,space_limit=10240';
select * from v$dm_arch_ini;
alter database open;
--关闭归档
alter database mount;
alter database noarchivelog;
alter database delete ARCHIVELOG 'type=local,dest=F:dmdbmsarch';
alter database open;

--查询所有的归档日志文件
select * from v$arch_file;
select * from v$archived_log;
--默认是数据库自动切换日志归档，DM支持手动切换日志归档，如下三个命令均可
alter SYSTEM ARCHIVE log CURRENT;
alter system switch logfile;
alter database ARCHIVELOG CURRENT;

--查询密码策略
--系统支持的口令策略有：
--⚫ 0 无策略
--⚫ 1 禁止与用户名相同
--⚫ 2 口令长度不小于 9
--⚫ 4 至少包含一个大写字母（A-Z）
--⚫ 8 至少包含一个数字（0-9）
--⚫ 16 至少包含一个标点符号（英文输入法状态下，除“和空格外的所有符号）
--口令策略可单独应用，也可组合应用。组合应用时，如需要应用策略 2 和 4，则设置口
--令策略为 2+4=6 即可
select * FROM v$parameter t where name like 'PWD_POLICY';
--设置密码策略
alter SYSTEM set 'PWD_POLICY' = 15 BOTH;
--访问非默认模式下的对象时需要带上模式名,SYSDBA用户默认模式为SYSDBA
select * from dmhr.employee t where t.employee_id=1001;
--设置模式，设置模式后查询模式下对象时不需要带模式名
set SCHEMA DMHR;
select * from employee limit 3;
--模式管理，表管理
--创建模式
CREATE SCHEMA HRTEST AUTHORIZATION SYSDBA;
--创建表
create TABLE HRTEST.testid(id int,name varchar(20),sex bit);
--增加列
alter table HRTEST.testid add column(age int);
--删除列
alter table HRTEST.testid drop sex;

--约束
alter table "HRTEST"."TESTID" add constraint "uk_test"  unique("ID") using index tablespace "DMTEST";
alter table "HRTEST"."TESTID" add constraint "ck_test"  check(age>18);

alter table HRTEST.TESTID DISABLE CONSTRAINT "ck_test";
alter table hrtest.testid enABLE CONSTRAINT "ck_test";
alter table "HRTEST"."TESTID" drop constraint "ck_test";

--创建视图(作用：简化查询)
create view hrtest.v_emp
 as
 select a.employee_id,a.employee_name,a.email,a.phone_num from dmhr.employee a where a.department_id=1001;
select * from hrtest.v_emp;
--创建视图 ：
create view dmhr.a_emp
 as
select a.department_id,b.department_name,avg(salary) as empavgsry
 from DMHR.EMPLOYEE a,DMHR.DEPARTMENT b
 where a.department_id=b.department_id
 group by a.department_id,b.department_name
 HAVINg avg(salary)>=9000;
 

select a.DEPARTMENT_ID, count(*) as empnum
 from dmhr.employee a 
 group by a.DEPARTMENT_ID
 having count(*) >=30;



--索引提高查询速度,索引是基于某张表的某个字段创建的
--索引不是越多越好，索引会降低DML的效率(DML操作要维护索引)
select * from DMHR.EMPLOYEE;
explain select * from dmhr.employee t where t.employee_name='马学铭';
create index ix_employee_employee_name on dmhr.employee(employee_name);
explain select * from dmhr.employee t where t.employee_name='马学铭';
--删除索引
drop index dmhr.IX_EMPLOYEE_EMPLOYEE_NAME;

--收集统计信息
dbms_stats.gather_table_stats('DMHR','EMPLOYEE');
--统计信息的查看
dbms_stats.table_stats_show('DMHR','EMPLOYEE');
dbms_stats.index_stats_show('DMHR','IX_EMPLOYEE_EMPLOYEE_NAME');

--索引的监控
alter INDEX DMHR.IX_EMPLOYEE_EMPLOYEE_NAME MONITORING USAGE; --开启索引监控
alter INDEX DMHR.IX_EMPLOYEE_EMPLOYEE_NAME  NOMONITORING USAGE;--关闭索引监控
alter index DMHR.IX_EMPLOYEE_EMPLOYEE_NAME rebuild online; --索引的 重建
select * from v$object_usage;
--查询模式下的索引
select * from dba_indexes t where t.owner='DMHR';
select * from DBA_IND_COLUMNS t where t.index_owner='DMHR';

--备份与还原
select * from v$parameter where name in ('BAK_USE_AP','BAK_PATH');
SELECt * from v$backupset;
select * from v$ifun where name like 'SF_BAKSET%';
--联机备份
--全量备份
backup database full to "ONLINEBAKFULL_01" backupset 'ONLINEBAKFULL_01';
--增量备份
backup database increment to "ONLINEBAKINCR_01" backupset 'F:dmdbmsdataDAMENGbakONLINEBAKINCR_01' device type disk;
--报错检查点LSN小于备份的LSN，需执行检查点
checkpoint(100);

select SF_BAKSET_CHECK('DISK','ONLINEBAKFULL_01');

--创建作业
call SP_CREATE_JOB('JOB01',1,0,'',0,0,'',0,'');

call SP_JOB_CONFIG_START('JOB01');

call SP_ADD_JOB_STEP('JOB01', 'FULLBAK', 6, '00000000F:dmdbmsbackupfullbak', 0, 0, 0, 0, NULL, 0);

call SP_ADD_JOB_SCHEDULE('JOB01', 'FULLBAK', 1, 2, 1, 1, 0, '22:00:00', NULL, '2022-01-25 19:54:42', NULL, '');

call SP_JOB_CONFIG_COMMIT('JOB01');


select * from sysjob.sysjobs;
dbms_job.run(1643111736);
--查看运行JOB日志
select * from sysjob.SYSJOBHISTORIES2;

--开启归档后需要执行检查点
checkpoint(100);

3.自我总结

借鉴：【DM】达梦DCA培训及考试认证_Smile丶丨的博客-CSDN博客_达梦dca考试怎么考的

如有侵权，联系删除！