=================================
达梦安装
groupadd dinstall
useradd -g dinstall -m -d /home/dmdba -s /bin/bash dmdba
echo “Dameng123”|passwd --stdin dmdba
mkdir -p /opt/dm8/tmp
mkdir -p /dm8
chown -R dmdba:dinstall /dm8
chown -R dmdba:dinstall /opt/dm8/tmp

xhost +
Xdy
export DISPLAY=:2.0

vi .bash_profile
export DM_INSTALL_TMPDIR=/opt/dm8/tmp
export DM_HOME=“/dm8”
export LD_LIBRARY_PATH=“$ LD_LIBRARY_PATH:$ DM_HOME/bin”
export PATH=$ DM_HOME/bin:$ DM_HOME/tool:$ PATH

vim /etc/security/limits.conf

• soft nproc 2047
• hard nproc 16384
• soft nofile 655366
• hard nofile 655366
• soft data unlimited
• hard data unlimited
• soft fsize unlimited
• hard fsize unlimited
• soft memlock unlimited
• hard memlock unlimited

systemctl stop firewalld
systemctl disable firewalld
systemctl status firewalld

sed -i ‘s/^ *SELINUX=enforcing/SELINUX=disabled/g’ /etc/selinux/config
setenforce 0
getenforce

./DMInstall.bin

systemctl start DmServiceDMSERVER.service

systemctl start DmServicePROD.service

=======================================
常用命令
tool:
dbca.sh
nca.sh —cat /etc/dm_svc.conf
manager
console

=======================================
参数文件
cd $DM_HOME
disql sysdba/Dameng123

disql sysdba/Dameng123:5237

select distinct para_type from v$ dm_ini;
select * from v$parameter t where name like ‘UNDO_RETENTION’;

alter system set ‘COMPATIBLE_MODE’ =2 spfile;
alter system set ‘LIST_TABLE’ =1 both|memory|spfile;
alter session set ‘LIST_TABLE’ = 0;
alter system set ‘UNDO_RETENTION’= 300 both;
alter system set ‘BUFFER’=500 spfile;

命令
DM 支持执行计划的清理和绑定：

select * from v$ifun t where name like ‘%PLN%’;
select t.name, b.* from v$ifun t, SYS.“V$IFUN_ARG” B
where t.name like ‘%SP_CLEAR_PLAN_CACHE%’
and t.id = b.id;
SP_CLEAR_PLAN_CACHE(PLAN_ID); --清理执行计划
–SP_SET_PLN_BINDED --绑定执行计划

=============================================
表空间操作
select * from DBA_FREE_SPACE;

create tablespace tbs DATAFILE ‘TBS01.DBF’ size 32;
DM 数据文件大小，最大值不能低于页大小的 4096 倍（ 如果页大小是 8K， 最小值将不低于 32M），最大值为页大小的 2 的 31 次方-1（如果页大小是 8K，最大值为 16T-1）。

alter tablespace tbs offline;
alter tablespace tbs RENAME TO dmtbs;

修改表空间添加数据文件：
alter TABLESPACE tbs add DATAFILE ‘TBS02.DBF’ size 128 AUTOEXTEND on NEXT 2 MAXSIZE 20480;
修改表空间数据文件的扩展属性（SYSTEM、 TEMP 表空间不允许关闭自动扩展，且不允许限制空间大小）：
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;

迁移自定义表空间数据文件
alter tablespace tbs offline;
alter TABLESPACE tbs RENAME DATAFILE ‘TBS01.DBF’ TO ‘/dm8/data/DM/TBS/TBS01.DBF’;
alter TABLESPACE tbs RENAME DATAFILE ‘TBS02.DBF’ TO ‘/dm8/data/DM/TBS/TBS02.DBF’;
alter tablespace tbs online;

开启归档
归档开启：
alter database mount;
alter database ARCHIVELOG;
alter database ADD ARCHIVELOG ‘type=local, dest=/dm8/arch,file_size=128,space_limit=10240’;
alter database open;
查看归档配置：
select arch_mode from v$database;
select * from v$dm_arch_ini;
修改归档的配置（修改归档空间上限）：
alter database modify archivelog ‘type=local, dest=/dm8/arch, SPACE_LIMIT=20480’;

SQL 关闭归档
alter database mount;
alter database noarchivelog;
alter database delete archivelog ‘type=local,dest=/dm8/arch’;
alter database open;
select arch_mode from v$database;
select * from v$dm_arch_ini;

归档文件的删除：
select * from v$ifun t where name like ‘SF_ARCHIVELOG_%’;
SF_ARCHIVELOG_DELETE_BEFORE_LSN
SF_ARCHIVELOG_DELETE_BEFORE_TIME
Select SF_ARCHIVELOG_DELETE_BEFORE_TIME(sysdate-10); --删除10天前的归档日志

====================================================
用户管理
PWD_MIN_LEN
select * from v$parameter t where t.name = ‘PWD_POLICY’;
create user hr IDENTIFIED by dameng123;
修改系统口令策略：
alter SYSTEM set ‘PWD_POLICY’ = 7 BOTH;

角色禁用和启用，禁用后角色权限将不在生效。
sp_set_role(‘R1’,0)

grant select on dmhr.employee from r2 with grant option ；
revoke select on dmhr.employee from r2 CASCADE;

================================
表操作

create table “TEST”.“EMPLOYEE”
(
“EMPLOYEE_ID” INTEGER not null ,
“EMPLOYEE_NAME” VARCHAR(20),
“EMAIL” VARCHAR(50),
“PHONE_NUM” VARCHAR(20),
“HIRE_DATE” DATE,
“JOB_ID” VARCHAR(10),
“SALARY” INTEGER,
“DEPARTMENT_ID” INTEGER
);

create table “TEST”.“DEPARTMENT”
(
“DEPARTMENT_ID” INTEGER not null ,
“DEPARTMENT_NAME” VARCHAR(30),
“LOCATION_ID” INTEGER,
“LOCATION_ADDR” VARCHAR(30)
)
;

alter table “TEST”.“EMPLOYEE” add primary key(“EMPLOYEE_ID”);
alter table dmtest.t_testchild ADD CONSTRAINT fk_testchild_pid FOREIGN KEY (pid) REFERENCES “TEST”.“EMPLOYEE” (“EMPLOYEE_ID”);
alter table “TEST”.“DEPARTMENT” add constraint “DBA” foreign key(“DEPARTMENT_ID”) references “TEST”.“EMPLOYEE”(“EMPLOYEE_ID”);

alter table DMHR.TEST add constraint TEST_UNI unique(ID);
alter table dmtest.T_TESTCHILD add column logtime datetime DEFAULT sysdate;
alter table dmtest.t_testchild ADD CONSTRAINT fk_testchild_pid FOREIGN KEY (pid) REFERENCES dmtest.t_testpid(pid);
alter table dmtest.t_testchild ADD CONSTRAINT ck_testchild_salary CHECK (salary>=2800);

create index DMTEST.ix_emp_name on DMTEST.T_EMP (employee_name) TABLESPACE DMTBSIDX;

create or replace view test.view_salary as
SELECT b.DEPARTMENT_ID,b.DEPARTMENT_NAME,sum(a.SALARY)
From test.eMPLOYEE a ,test.DEPARTMENT b
Where a.DEPARTMENT_ID=b.DEPARTMENT_ID
group by b.DEPARTMENT_ID,b.DEPARTMENT_NAME
having sum(a.SALARY)>80000;

Create index test.ind_emp_date on test.eMPLOYEE(HIRE_DATE);

============================================
备份恢复
select * from v$parameter t where name in (‘BAK_PATH’, ‘BAK_USE_AP’);

select permanent_magic; --查询数据库永久魔数
select db_magic from v$rlog; --查询数据库当前魔数

select * from v$ifun t where t.name like ‘SF_BAKSET%’;
SF_BAKSET_BACKUP_DIR_ADD(‘DISK’,‘/dm8/backup/full/’);
SF_BAKSET_BACKUP_DIR_ADD(‘DISK’,‘/dm8/backup/incr/’);

全备：
backup database full to FULL_BAK backupset ‘/dm8/backup/FULL_BACK’;
backup database full to ONLINEBAK_01 backupset ‘/dm8/backup/full/ONLINEBAK_01’;
backup database increment BASE ON BACKUPSET ‘/dm8/backup/full/ONLINEBAK_01’ to ONLINEBAKINCR_01 backupset ‘/dm8/backup/incr/ONLINEBAK_01’ ;
backup database increment with BACKUPDIR ‘/dm8/backup/full/’ to ONLINEBAKINCR_02 backupset ‘/dm8/backup/incr/ONLINEBAK_02’ ;

还原：
dmrman
–使用全量备份集恢复
restore database ‘/dm8/data/DAMENG/dm.ini’ from backupset ‘/dm8/backup/full/ONLINEBAK_02’;
–使用增量备份集恢复
restore database ‘/dm8/data/DAMENG/dm.ini’ from backupset ‘/dm8/backup/incr/incr_88’ with backupdir ‘/dm8/backup/full’;
recover database ‘/dm8/data/DM/dm.ini’ with archivedir ‘/dm8/arch’;
recover database ‘/dm8/data/DM/dm.ini’ update db_magic;

不完全恢复的使用场景：
表误删除（DROP），可以指定归档恢复到删除的前一刻。
recover database ‘/dm8/data/DMTEST/dm.ini’ with archivedir ‘/dm8/arch’ until lsn XX;
recover database ‘/dm8/data/DMTEST/dm.ini’ with archivedir ‘/dm8/arch’ until time XX;

====================================================

导入导出：
全库导出：
dexp userid=sysdba/Dameng123:5237 directory=/dm8/backup/dexp file=full.dmp log=full.log full=y
按用户导出：
dexp userid=sysdba/Dameng123:5236 directory=/dm8/backup/dexp file=HRTEST.dmp log=HRTEST.log owner=HRTEST
按模式导出：
dexp userid=sysdba/Dameng123:5236 directory=/dm8/backup/dexp file=DMHR.dmp log=DMHR.log schemas=DMHR
按表导出：
dexp userid=sysdba/Dameng123:5236 directory=/dm8/backup/dexp file=EMPLOYEE.dmp log=EMPLOYEE.log tables=DMHR.EMPLOYEE

dexp userid=sysdba/Dameng123:5237 directory=/dm8/backup file=test.dmp log=test.log owner=test

全库导入：
dimp userid=sysdba/Dameng123:5238 directory=/dm8/backup/dexp file=full.dmp log=impfull.log
full=y
按模式导入（将 A 模式导入到 B 模式，使用 REMAP_SCHEMA 参数）：
dimp userid=sysdba/Dameng123:5238 directory=/dm8/backup/dexp file=DMHR.dmp
log=impDMHR.log REMAP_SCHEMA=DMHR:DMTEST

表导入测试：
dimp help
dimp userid=sysdba/SYSDBA:5238 directory=/dm8/backup/dexp file=t_dept.dmp log=t_dept.log tables=SYSDBA.t_dept remap_schema=SYSDBA:DMHR

=======================================
job管理：
创建代理环境
执行存储过程代理环境： sp_init_job_sys(1);
select * from v$ifun t where t.name like ‘%INIT%’;
call SP_INIT_DBMS_SCHEDULER_SYS(1); --创建 DMBS_SCHEDULER

JOB 运行和日志查看
–查看job
select * from sysjob.sysjobs;
–运行job
dbms_job.run(1640162093);
–查看job运行日志
select * from SYSJOB.SYSJOBHISTORIES2;

======================================
odbc 安装
tar -zxvf unixODBC-2.3.0.tar.gz
./configure --prefix=/usr/local/unixODBC
make
make install

cd /usr/local/etc/

vi odbcinst.ini
[DM8 ODBC DRIVER]
Description = ODBC DRIVER FOR DM8
Driver = /dm8/bin/libdodbc.so

vi odbc.ini
[DM8]
Description = DM ODBC DSN
Driver = DM8 ODBC DRIVER
SERVER = localhost
UID = SYSDBA
PWD = Dameng123
TCP_PORT = 5236

isql dm8 -v