SQL查询数据库环境(dm8达梦数据库)

环境介绍

• 某些环境没有图形化界面,可以使用sql语句查询达梦数据库环境情况
• 此sql 不能用在 dpc,mpp环境,会报错

--此sql 不能用在 dpc,mpp环境,会报错
SELECT '实例名称' 数据库选项,INSTANCE_NAME  数据库选项相关参数值 FROM V$INSTANCE UNION ALL
SELECT '授权用户',(SELECT AUTHORIZED_CUSTOMER FROM V$LICENSE) UNION ALL
SELECT '数据库授权码',(SELECT SERIES_NO FROM V$LICENSE) UNION ALL
SELECT '数据库创建时间',CAST(CREATE_TIME AS VARCHAR) FROM "SYS"."V$DATABASE" UNION ALL
SELECT '数据库有效期',CAST((SELECT EXPIRED_DATE FROM V$LICENSE)AS VARCHAR) UNION ALL
SELECT '数据库启动时间',CAST((SELECT START_TIME FROM V$INSTANCE)AS VARCHAR) UNION ALL
SELECT '数据库总大小',CAST(TOTAL_SIZE*32/1024 AS VARCHAR)||' M' FROM "SYS"."V$DATABASE" UNION ALL
SELECT '数据库版本',SUBSTR(SVR_VERSION,INSTR(SVR_VERSION,'('))  FROM V$INSTANCE UNION ALL
SELECT '数据库版本小号1',(SELECT ID_CODE)  FROM V$INSTANCE UNION ALL
SELECT '数据库版本小号2',(SELECT BUILD_VERSION)  FROM V$INSTANCE UNION ALL
SELECT '数据库实例路径',SF_GET_PARA_STRING_VALUE(1,'SYSTEM_PATH') UNION ALL
SELECT '数据库默认备份路径',SF_GET_PARA_STRING_VALUE(1,'BAK_PATH') UNION ALL
SELECT '数据库屏蔽关键字',SF_GET_PARA_STRING_VALUE(1,'EXCLUDE_RESERVED_WORDS') UNION ALL
SELECT '兼容其他数据库模式', CASE (SF_GET_PARA_VALUE(1,'COMPATIBLE_MODE'))
WHEN '0' THEN '无' WHEN '1' THEN '兼容 SQL92标准' WHEN '2' THEN '部分兼容 ORACLE' 
WHEN '3' THEN '部分兼容 SQL SERVER' WHEN '4' THEN '部分兼容 MYSQL' WHEN '5' THEN '兼容 DM6'
WHEN '6' THEN '部分兼容 TERADATA' WHEN '7' THEN '部分兼容 POSTGRES' END 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 '数据库端口',CAST(SF_GET_PARA_VALUE(1,'PORT_NUM') AS VARCHAR) UNION ALL
SELECT '归档状态',CASE ARCH_MODE WHEN 'Y' THEN '已配置归档' ELSE '未配置归档 无法备份' END FROM V$DATABASE UNION ALL
SELECT 'VARCHAR以字符为单位',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 '字符集',CASE SF_GET_UNICODE_FLAG() WHEN '0' THEN 'GBK18030' WHEN '1' THEN 'UTF-8' WHEN '2' THEN 'EUC-KR' END UNION ALL
SELECT '大小写是否敏感',CASE (CAST(SF_GET_CASE_SENSITIVE_FLAG() AS VARCHAR)) WHEN '1' THEN '是' ELSE '否' 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;

• 查询结果