达梦常用命令

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 "SAAS" 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 "SAAS";
grant CREATE SCHEMA,CREATE TABLE,CREATE VIEW,CREATE PROCEDURE,CREATE SEQUENCE,CREATE TRIGGER,CREATE INDEX,CREATE CONTEXT INDEX,CREATE LINK to "SAAS";
CREATE SCHEMA "SAAS" AUTHORIZATION "SAAS";

查询所有模式 提示没有权限，就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;

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;   //初步看一下两个环境的这些参数的区别