达梦笔记

达梦笔记

1、查看外键

select * from dba_constraints where constraint_name='FK_MT_ACC_S_REFERENCE_MT_ACC_P';
 
 

2、超出hash空间的处理

select * from v$dm_ini where para_name like 'HJ_BUF_SIZE';
 
select * from v$dm_ini where para_name like 'HJ_BUF_GLOBAL_SIZE';
 
sp_set_para_value(1,'HJ_BUF_SIZE',NUM);
 
 
sp_set_para_value(2,'HJ_BUF_GLOBAL_SIZE',NUM); 
 
-- 修改dm.in 中 HJ_BUF_GLOBAL_SIZE、HJ_BUF_SIZE 
-- 改大HJ_BUF_GLOBAL_SIZE的值
-- 改小HJ_BUF_SIZE

3、达梦锁表处理

--达梦解锁表

select * from v$sessions;
sp_close_session(sess_id);

4、最大连接设置

HJ_BUF_GLOBAL_SIZE

5、修改clob查询设置

6、修改会话句柄数

sp_set_para_value(1,'MAX_SESSION_STATEMENT',20000);
select * from  V$DM_INI where PARA_name='MAX_SESSION_STATEMENT';

7、查询授权

select * from v$license;

8、开启sql记录日志

ALTER SYSTEM SET 'SVR_LOG' = 1 BOTH;  //开始
 
ALTER SYSTEM SET 'SVR_LOG' = 0 BOTH;  //关闭

9、相关子查询

/*+sel_item_htab_flag(1) subq_cvt_spl_flag(1)*/

10、慢日志开启

SP_SET_PARA_VALUE(1,'SVR_LOG',0); 关闭
SP_SET_PARA_VALUE(1,'SVR_LOG',1); 开启

12、查看数据库编码

select SF_GET_UNICODE_FLAG();

13、设置数据库权限

alter user "TEST" allow_ip "192.168.1.0";

14、查看数据库版本

select * from v$version;

15、查看数据库常用参数

select '实例名称' 数据库选项,INSTANCE_NAME  数据库集群相关参数值 FROM v$instance
 
union all
 
select '数据库版本',substr(svr_version,instr(svr_version,'V'))  FROM v$instance
 
union all
 
SELECT '字符集',CASE SF_GET_UNICODE_FLAG() WHEN '0' THEN 'GBK18030' WHEN '1' then 'UTF-8' when '2' then 'EUC-KR' end
 
union all
 
SELECT '页大小',cast(PAGE()/1024 as varchar)
 
union all
 
SELECT '簇大小',cast(SF_GET_EXTENT_SIZE() as varchar)
 
union all
 
SELECT '大小写敏感',cast(SF_GET_CASE_SENSITIVE_FLAG() as varchar)
 
union all
 
select '数据库模式',MODE$ from v$instance; 

16、查询数据库数据总数

select sum(table_rowcount(owner,table_name)) from dba_tables;

17、dexp与dimp

#dimp 导入
 ./dimp USERID=SYSDBA/SYSDBA@LOCALHOST:5237 FILE=gzgacw20211124.dmp DIRECTORY=/home/icm/data/dmBackUp LOG=gzgacw20211124.log FULL=Y IGNORE=Y
 
 
./dimp USERID=SYSDBA/SYSDBA@LOCALHOST:5237 FILE=gzgasys20211124.dmp DIRECTORY=/home/icm/data/dmBackUp LOG=gzgasys20211124.log FULL=Y IGNORE=Y
 
 
 

18、查看license

select * from v$license;