达梦DCA常用操作命令

安装：
groupadd dinstall
useradd -g dinstall -m -d /home/dmdba -s /bin/bash dmdba
passwd dmdba
mkdir /dm8
chown dmdba:dinstall /dm8
mkdir /mnt/dm
mount /opt/包名   /mnt/dm
root下:xhost +
echo $DISPLAY
su - dmdba
export DISPLAY=:0.0
cd /mnt/dm
./DMInstall.bin

--修改参数
1.可以使用console工具修改
2.直接修改dm.ini文件，静态参数需要重启数据库
3.sp_set_para_value(2,'COMPATIBLE_MODE',0);
sp_set_para_value(1,'LIST_TABLE',0);
4.alter system set 'COMPATIBLE_MODE' =2 spfile;

alter system set 'LIST_TABLE' =1 both|memory|spfile;
both:表示对内存中和参数文件都修改；
memory：修改内存中的值；
spfile：修改参数文件中的值。
对于会话级动态参数，可以只针对当前会话生效：
添加网络服务:
./dm8/tool/nca.sh

建表空间
create tablespace ebs datafile '/dm8/data/tayh/ebs1.dbf' size 32;
新建用户
create user tayh identified by Acer_515t default tablespace ebs;
grant create table to tayh;
新建角色
create role r1;
grant create table to r1;
grant select on dmhr.department to r1;
grant r1 to tayh;
建表
create table tayh.test(id int,name varchar2(20),sex bit);
新增列
alter table tayh.test add column (age,int);
删除列
alter table tayh.test drop column sex;
约束：
alter table "TAYH"."TEST" add constraint "uk_test"  unique("ID") using index tablespace "EBS";
alter table "TAYH"."TEST" add constraint "ck_test"  check(age>18);

alter table tayh.test DISABLE CONSTRAINT "ck_test";
alter table tayh.test ENABLE CONSTRAINT "ck_test";
alter table tayh.test drop constraint "ck_test";

**DMSQL
DML数据操作语言、DDL数据定义语言、DCL数据控制语言、TCL事务控制语言
--索引提高查询速度,索引是基于某张表的某个字段创建的
--索引不是越多越好，索引会降低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';

查看数据库服务状态
cd /dm8/bin
DmServiceDMSERVER status
后台启动和关闭
./DmServiceDMTESTSVR start
./DmServiceDMTESTSVR stop

打开归档：
alter database mount;
alter database add archivelog 'type=local,dest=/dm8/arch,file_size=64,space_limit=10240';
alter database archivelog;
alter database open;

备份表空间：
backup tablespace DMHR full to TS_DMHR_FULL backupset '/dm8/data/DAMENG/bak/TS_DMHR_FULL'
还原表空间：
脱机状态下
restore database '/dm8/data/DAMENG/dm.ini' tablespace "DMHR" from BACKUPSET '/dm8/data/DAMENG/bak/TS_DMHR_FULL'
recover database '/dm8/data/DAMENG/dm.ini' TABLESPACE DMHR;

查看备份进程
DMAPService status
全备前要开启归档，且要做一次检查点
checkpoint(100)
backup database;
--查看备份路径
select * from v$parameter t where name in ('BAK_PATH', 'BAK_USE_AP');
--全备到指定目录
backup database full to FULL BACKUPSET '/dm8/backup/FULL/FULLBAK';
--添加备份集目录
SF_BAKSET_BACKUP_DIR_ADD('DISK','/dm8/backup/FULL');
SF_BAKSET_BACKUP_DIR_ADD('DISK','/dm8/backup/INCR');

select * from sys."V$BACKUPSET";
增量备份
backup database increment with backupdir'/dm8/backup/FULL/' to increbak01 BACKUPSET '/dm8/backup/INCR/INCRBAK01';
备份表 
backup table hrtest.t_emp to EMPBAK01 BACKUPSET '/dm8/backup/FULL/EMPBAK01';
备份归档
backup archivelog all;
脱机备份：
DMAPService start
DmServiceDMSERVER stop
dmramn
backup database 'dm8/data/DAMENG/dm.ini' to FULLBAK03 backupset 'dm8/backup/FULL/FULLBAK03'
--还原表空间
删除DMTBS表空间
cd /dm8/data/DMT/DMTBS
rm DMTBS0*
前台方式启动 
dmserver /dm8/data/DAMENG/dm.ini
还原时关闭数据库
dmrman
restore database 'dm8/data/DAMENG/dm.ini' tablespace dmtbs from backupset  'dm8/backup/FULL/DMTBSFULLBAK01'

recover database 'dm8/data/DAMENG/dm.ini' tablespace dmtbs;
DmServiceDMSERVER start
库级还原：
restore database 'dm8/data/tayh/dm.ini' from backupset '/dm8/backup/FULL2/FULLBAK02'
recover database '/dm8/data/TADM/dm.ini' with archivedir '/dm8/arch';
recover database '/dm8/data/TADM/dm.ini' update db_magic;
DmServiceDMSERVER start

dexp和dimp逻辑备份和恢复：
全库导出：
[dmdba@KylinDCA03 dexp]$ dexp userid=sysdba/Dameng123:5236 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

全库导入：
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
表导入测试：
[dmdba@KylinDCA04 dexp]$ dimp help
[dmdba@KylinDCA04 dexp]$ 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 dimp V8 

OJDBC：
1.解压包unixojdbc-2.3.0.tar.gz
2.进入目录
./configure进行安装
3.make命令进行编译
4.make install 安装完成
5.odbcinst -j
6.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    = Acer_515t
TCP_PORT= 5236

cd /home/dmdba
su - dmdba
isql dm8 -v