达梦数据库（DM8）一些常用命令
—查看建表语句
#调用存储过程 sp_tabledef(‘用户名’,’表名’)
call sp_tabledef(‘DMTEST’,’T1′)
—密码策略
#说明
0 无限制。但总长度不得超过48个字节
1 禁止与用户名相同
2 口令长度需大于等于INI参数PWD_MIN_LEN设置的值
4 至少包含一个大写字母（A-Z）
8 至少包含一个数字（0-9）
16 至少包含一个标点符号（英文输入法状态下，除“和空格外的所有符号）
#查询
select * from v$dm_ini where para_name like ‘PWD_POLICY%’;
#修改
alter system set ‘PWD_POLICY’=7 spfile;
—数据库兼容模式
#说明
0:none,
1:SQL92
2:Oracle
3:MS SQL Server
4:MySQL
5:DM6
6:Teradata
#查询
select * from v$dm_ini where para_name like ‘COMPATIBLE_MODE%’;
#修改
alter system set ‘COMPATIBLE_MODE’=2 spfile;
—创建TEST表空间
create tablespace “TEST” datafile ‘/dm8/data/DAMENG/TEST01.DBF’ size 128 autoextend on next 2 maxsize 10240,
‘/dm8/data/DAMENG/TEST02.DBF’ size 128 autoextend on next 2 maxsize 10240
—创建角色
create role ROLEDM;
grant create table,create view,create index to ROLEDM;
—创建用户
create user “DMTEST” identified by “Dameng123” default tablespace “TEST”;
—用户授予角色权限
grant ROLEDM to DMTEST;
—创建表
create table EMP(
EMP_ID INTEGER NOT NULL,
EMP_NAME VARCHAR(20),
EMAL VARCHAR(50),
PHONE_NUM VARCHAR(20),
BIRTHDAY DATE,
HIRE_DATE DATE,
JOB_ID VARCHAR(10),
SALARY INTEGER,
DEPT_ID INTEGER,
PRIMARY KEY(EMP_ID),
FOREIGN KEY(DEPT_ID) REFERENCES DEPT(DEPT_ID)
) tablespace TEST;
create table DEPT(
DEPT_ID INTEGER NOT NULL,
DEPT_NAME VARCHAR(30),
LOCATION_ID INTEGER,
LOCATION_ADDR VARCHAR(50),
PRIMARY KEY(DEPT_ID)
) tablespace TEST;
—添加约束
alter table EMP add constraint EMP_CK check(BIRTHDAY<‘2003-01-01’)
—disql执行脚本
使用start命令
SQL> start /opt/script1.sql
—查看归档
#查看归档配置
select * from v$dm_arch_ini;
#查看归档状态
select * from v$arch_status;
select name,arch_mode from v$database;
select * from v$dm_arch_ini;
—修改归档模式
alter database mount;
alter database add archivelog ‘DEST=/dm8/arch, TYPE=local,FILE_SIZE=64,SPACE_LIMIT=0’;
alter database archivelog;
alter database open;
—备份数据库
#物理备份
BACKUP DATABASE BACKUPSET ‘/dm8/backup/FULL_BAK’;
#逻辑备份
./dexp SYSDBA/Dameng123:5238 FILE=db_full.dmp LOG=db_full.log DIRECTORY=/dm8/backup FULL=Y
—修改表空间大小
select * from v$parameter where name like ‘%TEMP%’;
alter system set ‘TEMP_SIZE’=100 spfile;
—创建视图
create view VIEW_EMP as
select a.DEPT_ID AS “部门编号”,a.DEPT_NAME as “部门名称”,b.sal as “部门平均工资” from dept a inner join
(select AVG(SALARY) as sal,DEPT_ID from EMP group by DEPT_ID HAVING AVG(SALARY)>9000)b
on a.DEPT_ID=b.DEPT_ID;
—添加索引
create index IND_EMP_NAME ON EMP(EMP_NAME);