mkdir /dm8
id dmdba

groupadd dinstall
useradd -g dinstall dmdba
id dmdba
ll
chown dmdba:dinstall /dm8
su - dmdba

vi .bash_profile
export DM_HOME=/dm8
export PATH=$PATH:$DM_HOME/bin:$DM_HOME/tool
source .bash_profile
echo $DM_HOME

mount -o loop /opt/dm8_20230104_x86_rh6_64.iso /mnt/
cd /mnt

exit #su - root
xhost +
echo $DISPLAY
:0.0

su - dmdba
export DISPLAY=:0.0
xhost +

cd /mnt
./DMInstall.bin

mount -o remount,size=4G /tmp
mount -t tmpfs -o size=4G none /tmp

root 执行/dm8/script/root/root_installer.sh

配置数据库实例
cd /dm8/tool
./dbca.sh

命令行配置实例（默认密码SYSDBA）

cd /dm8/bin
./dminit path=/dm8/data db_name=DM02 instance_name=DM02 port_num=5239
cd /dm8/script/root
./dm_service_installer.sh -t dmserver -dm_ini /dm8/data/DM02/dm.ini -p DM02

systemctl start DM02

cd /dm8/bin
./disql sysdba/A_061217@localhost:5236

控制文件
./dmctlcvt help
./dmctlcvt type=1 src=/dm8/data/DAMENG/dm.ctl dest=/tmp/dmctl.txt

查询归档
select arch_mode from v$database;
dmarch_ini;
select * from v$dm_arch_ini;

修改表空间参数
DM:SP_SET_PARA_VALUES(SCOPE,PARA_NAME,PARA_VALUE);
Scope:
1:动态参数
2：静态参数

线程
select * from v$threads;
select * from v$latches;

表空间 用户管理 考试
创建表空间
create tablespace "TEST" datafile '/dm8/data/DAMENG/TEST_01.DBF' size 32 autoextend on next 2 maxsize 32768, '/dm8/data/DAMENG/TEST_02.DBF' size 32 autoextend on next 2 maxsize 32768 CACHE = NORMAL;
查询表空间
select tablespace_name,status from user_tablespaces;
联机表空间
alter tablespace test online;

更改表空间
alter tablespace "TEST" datafile 'TEST_02.DBF' autoextend on next 2 maxsize 65536;

alter tablespace "TEST" datafile 'TEST_01.DBF' autoextend on next 2 maxsize 65536;

alter tablespace "TEST" add datafile '/dm8/data/DAMENG/TEST_03.DBF' size 32 autoextend on next 2 maxsize 65536;

select file_name,file_id,status,tablespace_name,bytes/1024/1024/ from dba_data_files;

迁移表空间存储
alter tablespace test offline;
alter tablespace "TEST" rename datafile 'TEST_03.DBF' to '/dm8/DAMENG/TEST_03.DBF';

alter tablespace "TEST" rename datafile 'TEST_02.DBF' to '/dm8/DAMENG/TEST_02.DBF';

alter tablespace "TEST" rename datafile 'TEST_01.DBF' to '/dm8/DAMENG/TEST_01.DBF';

alter tablespace "TEST" cache = "KEEP";

临时表空间
select para_name,para_value from v$dm_ini where para_name like '%TEMP%';

alter tablespace "TEMP" resize datafile 'TEMP.DBF' to 32;---可以变相清理temp 表空间减小

alter tablespace "TEMP" add datafile '/dm8/data/DAMENG/TEMP01.DBF' size 32;

修改temp size(静态参数重启实例生效：)

sp_set_para_value(2,'TEMP_SIZE',128);

回滚表空间ROLL
alter tablespace "ROLL" resize datafile 'ROLL.DBF' to 256;

UNDO_RETENTION

用户管理

select para_name,para_value,para_type from v$dm_ini where para_name like '%PWD%';

创建用户
create user "TEST" identified by "A_061217" password_policy 0
limit SESSION_PER_USER 1000 FAILED_LOGIN_ATTEMPS 2 PASSWORD_LIFE_TIME 180 PASSWORD_LOCK_TIME 5 PASSWORD_GRACE_TIME 10
default tablespace "TEST"
default index tablespace "TEST";

grant "PUBLIC","SOI" to "TEST";

查询用户权限
select grantee,granted_role from dba_role_privs where grantee='TEST';
select * from dba_sys_privs where grantee='TEST';
select * from dba_tab_privs where grantee='TEST';

grant CREATE TABLE to "TEST";

grant CREATE VIEW to "TEST";

grant CREATE INDEX to "TEST";

grant SELECT on "DMHR"."EMPLOYEE" to "TEST";

grant SELECT("PHONE_NUM") on "DMHR"."EMPLOYEE" to "TEST";

grant SELECT("EMPLOYEE_ID") on "DMHR"."EMPLOYEE" to "TEST";

grant SELECT("EMAIL") on "DMHR"."EMPLOYEE" to "TEST";

grant SELECT("EMPLOYEE_NAME") on "DMHR"."EMPLOYEE" to "TEST";

grant SELECT("DEPARTMENT_ID") on "DMHR"."EMPLOYEE" to "TEST";

select * from dba_users where username='TEST';

select * from sysusers where id='50331748';

alter user account unlock;

create role "KFROLE";

grant CREATE TABLE,CREATE VIEW to "KFROLE";

grant SELECT("JOB_ID") on "DMHR"."EMPLOYEE" to "KFROLE";

grant SELECT("PHONE_NUM") on "DMHR"."EMPLOYEE" to "KFROLE";

grant SELECT on "DMHR"."DEPARTMENT" to "KFROLE";

grant SELECT("EMPLOYEE_ID") on "DMHR"."EMPLOYEE" to "KFROLE";

grant SELECT("MANAGER_ID") on "DMHR"."EMPLOYEE" to "KFROLE";

grant SELECT("EMAIL") on "DMHR"."EMPLOYEE" to "KFROLE";

grant SELECT("EMPLOYEE_NAME") on "DMHR"."EMPLOYEE" to "KFROLE";

grant SELECT("DEPARTMENT_ID") on "DMHR"."EMPLOYEE" to "KFROLE";

达梦SQL
简单查询

select from where
select from t1 join t2 on (t1.c1=t2.c2)

查询数据库字符集
SELECT sf_get_unicode_flag();
查询事务隔离级别
select isolation from SYS."V$TRX";

调用图形化界面--安装数据库软件--初始化实例--表空间--用户--角色--权限

图形化 建表 表名大写
字符用单引号，对象用双引号；

select constraint_name,constraint_type,table_name from DBA_CONSTRAINTS where table_name='T1';
alter table test.t12 disable constraint cons134218871;
alter table test.t12 enable constraint cons134218871;

alter table test.t12 drop constraint cons134218871;

视图复杂试图
create view test.vm2 as
select d.department_id d department_name,a.avg1 from
(select department_id,avg(e.salary) as avg1 from dmhr.employee e
group by department_id having avg(e.salary)>10000) a
join dmhr.department d
on a.department_id=d.department_id;

索引
create table test.emp as select * from dmhr.employee;

查看执行计划
explain select * from test.emp where employee_id<2000;

select * from DBA_INDEXES where owner='test' and table_name='EMP';

alter index TEST.IND_EMP rebuild online;

备份还原

ODBC
odbc.ini
[dm8]
Description = DM ODBC DSND
DRIVER = DM8 ODBC DRIVER
SERVER = localhost
UID = SYSDBA
PWD = A_06121717
TCP_PORT = 5236
odbcinst.ini
[DM8 ODBC DRIVER]
Description = ODBC DRIVER FOR DM8
DRIVER = /dm8/bin/libdodbc.so