达梦数据库命令

​​​​​​​注意事项：

1.int、bigint、smallint后不加精度定义 否则会提示“第*行附近出现错误[-6119]:无效的数据类型.”

1.查询数据库版本

select * from v$version;

2.查看授权信息

select * from  v$license;

3.查看达梦数据库库名

select name,create_time from v$database;

4.查看大小写配置是否敏感

--大小敏感（1为大小写敏感，0为大小写不敏感）
SELECT SF_GET_CASE_SENSITIVE_FLAG();
或
SELECT CASE_SENSITIVE()

5.查询数据库最大连接

select SF_GET_PARA_VALUE(2,'MAX_SESSIONS');

6.查看达梦数据库当前状态

select status$ from v$instance;

7.查询授权截止有效期

select EXPIRED_DATE  from v$license;

8.查询数据文件位置

select GROUP_ID , ID ,path,STATUS$ from v$datafile;

9.查询表空间大小

select FILE_NAME,FILE_ID,TABLESPACE_NAME,BYTES/1024/1024||'M'  from dba_data_files;

10.查询表空间使用情况

select t1.NAME tablespace_name,
t2.FREE_SIZE*SF_GET_PAGE_SIZE()/1024/1024 ||'M' free_space,
t2.TOTAL_SIZE*SF_GET_PAGE_SIZE()/1024/1024 ||'M' total_space,
t2.FREE_SIZE*100/t2.total_size "% FREE" 
from V$TABLESPACE t1, V$DATAFILE t2 
where t1.ID=t2.GROUP_ID;

11.查询所有SCHEMA

 select * from dba_objects where object_type='SCH';

12.查询所有用户

select username from dba_users;

13.切换SCHEMA

set schema "testSchema";

14.创建SCHEMA

CREATE SCHEMA "testSchema";

15.查询模式下全量表名称

select TABLE_NAME from all_tables WHERE OWNER='testSchema';

16.删除SCHEMA

drop schema "testSchema";

17.列注释

COMMENT ON COLUMN testSchema.peoples.role_id is '角色Id';

18.查询列注释

select * from SYSCOLUMNCOMMENTS 
where SCHNAME='testSchema' and TVNAME='peoples' and COLNAME='role_id';

19.表注释

comment on table testSchema.peoples is '这是一个表注释';

20.查询表注释

select * from SYSTABLECOMMENTS where SCHNAME='testSchema' and TVNAME='peoples';

21.创建表空间

CREATE TABLESPACE MANAGEMENT DATAFILE 'MANAGEMENT.DBF' SIZE 128;

22.查看表空间

select * from dba_data_files;
 select * from dba_tablespaces;
 select * from v$tablespace;

23.创建用户

CREATE USER testUser IDENTIFIED BY "testUsersPwd" DEFAULT TABLESPACE MANAGEMENT;
GRANT DBA TO testSchema;