达梦数据库8.0常用命令大全

首发于个人博客

SELECT '实例名称' 数据库选项,INSTANCE_NAME  数据库集群相关参数值 FROM V$INSTANCE UNION ALL
SELECT '数据库名',CUR_DATABASE()FROM DUAL UNION ALL
SELECT '授权客户',(SELECT AUTHORIZED_CUSTOMER FROM V$LICENSE) UNION ALL
SELECT '数据库授权码',(SELECT SERIES_NO FROM V$LICENSE) UNION ALL
SELECT '数据库有效期',CAST((SELECT EXPIRED_DATE FROM V$LICENSE)AS VARCHAR) UNION ALL
SELECT '数据库版本',SUBSTR(SVR_VERSION,INSTR(SVR_VERSION,'('))  FROM V$INSTANCE UNION ALL
SELECT '数据库版本小号',(SELECT BUILD_VERSION  FROM V$INSTANCE) UNION ALL
SELECT '数据库实例路径',(SELECT PARA_VALUE FROM V$DM_INI WHERE PARA_NAME LIKE'%SYSTEM_PATH%')  FROM V$INSTANCE UNION ALL
SELECT '数据库模式',MODE$ FROM V$INSTANCE  UNION ALL
SELECT '数据库状态',STATUS$ FROM V$INSTANCE UNION ALL
SELECT 'OGUID',CAST(OGUID AS VARCHAR) FROM V$INSTANCE UNION ALL
SELECT '归档状态_开 Y/关 N',ARCH_MODE FROM V$DATABASE UNION ALL
SELECT '长度是否以字符为单位',CASE (SELECT PARA_VALUE FROM V$DM_INI WHERE "V$DM_INI".PARA_NAME ='LENGTH_IN_CHAR') WHEN '0' THEN '否' WHEN '1' THEN '是' END UNION ALL
SELECT '大小写是否敏感_是 Y,1/否N,0',CAST(SF_GET_CASE_SENSITIVE_FLAG() AS VARCHAR) 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(PERMANENT_MAGIC AS VARCHAR) UNION ALL
SELECT 'LSN',CAST(CUR_LSN AS VARCHAR) FROM V$RLOG UNION ALL
SELECT '当前登录用户',USER;




create tablespace "TEST" datafile '/mypath/TEST.DBF' size 180 autoextend on maxsize 16777215 CACHE = NORMAL;

create user "BAAS" identified by "yourpassword" limit failed_login_attemps 3, password_lock_time 1, password_grace_time 10 default tablespace "MAIN" default index tablespace "MAIN";
grant "DBA","RESOURCE","PUBLIC","SOI" to "BAAS";
grant CREATE SCHEMA,CREATE TABLE,CREATE VIEW,CREATE PROCEDURE,CREATE SEQUENCE,CREATE TRIGGER,CREATE INDEX,CREATE CONTEXT INDEX,CREATE LINK to "BAAS";
CREATE SCHEMA "BAAS" AUTHORIZATION "BAAS";

查询所有模式 提示没有权限，就from all_objects, dab_objects, user_objects
SELECT DISTINCT object_name FROM ALL_OBJECTS WHERE OBJECT_TYPE = 'SCH';

select * from dba_objects where object_type='SCH';



select tablespace_name from user_tablespaces;

查询当前用户表 dba_tables, all_tables
select table_name from user_tables;
select * from dba_tables where owner='XXX';  用单引号
查询当前用户所在模式
select SYS_CONTEXT('USERENV','CURRENT_SCHEMA') as schema_name from dual;
select user();
查询数据库运行状态
select host_name,status$,mode$ from v$instance;

./dm_service_installer.sh -t dmserver -dm_ini /dm8/data/DAMENG/dm.ini -p DMSERVER

环境查看

SELECT '页大小',cast(PAGE()/1024 as varchar) union all   
SELECT '簇大小',cast(SF_GET_EXTENT_SIZE() as varchar) 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(SF_GET_CASE_SENSITIVE_FLAG() as varchar) union all
select 'VARCHAR类型长度是否以字符为单位',para_value from v$dm_ini where para_name='LENGTH_IN_CHAR' union all
select '端口号',para_value from v$dm_ini where para_name='PORT_NUM' union all
select '数据库版本',substr(svr_version,instr(svr_version,'(')) FROM v$instance union all 
select '数据库模式',MODE$ from v$instance union all 
SELECT '字符数据类型' NAME, CASE VALUE WHEN 0 THEN 'BYTE' WHEN 1 THEN 'CHAR' end FROM v$parameter WHERE name like '%LENGTH_IN_CHAR%' union all
select 'KEY文件属性',  cluster_type from v$license union all
select 'REDO日志大小（M）',to_char(rlog_size/1024/1024) from v$rlogfile;   //初步看一下两个环境的这些参数的区别

//查看达梦兼容模式， mysql oracle等等
select * from v$parameter where name = ‘COMPATIBLE_MODE’;

//Server compatible mode, 0:none, 1:SQL92, 2:Oracle, 3:MS SQL Server, 4:MySQL, 5:DM6, 6:Teradata
//更改兼容模式
alter system set ‘COMPATIBLE_MODE’=4 spfile;

服务注册成功后，启动数据库，如下所示：

systemctl start DmServiceDMSERVER.service
停止数据库，如下所示：

systemctl stop DmServiceDMSERVER.service
重启数据库，如下所示：

systemctl restart DmServiceDMSERVER.service
查看数据库服务状态，如下所示：

systemctl status DmServiceDMSERVER.service
可前台启动，进入 DM 安装目录下的 bin 目录下，命令如下：

./dmserver /dm/data/DAMENG/dm.ini
该启动方式为前台启动，若想关闭数据库，则输入 exit 即可。

也可进入 DM 安装目录下的 bin 目录下，启动/停止/重启数据库，如下所示：

./DmServiceDMSERVER start/stop/restart
查看数据库状态，如下所示：

./DmServiceDMSERVER status